[psycopg] Solving the SQL composition problem - Mailing list psycopg

Hello,

one recurring problem using psycopg is with the generation of dynamic
SQL strings, e.g. where the table or the field names change. Usually
the conversation goes:

User: this doesn't work: cur.execute("insert into %s values (%s)",
['mytable', 42])
Maintainer: yes, you can't pass tables to the arguments
User: what should I do?
Maintainer: normal string concatenation: cur.execute("insert into %s
values %%s" % 'mytable', [42])
User: what if the table name has special chars?
Maintainer: ehm...
User: what if the source of the table name is not secure?
Maintainer: ehm...

The problem is slightly improved by exposing `quote_ident()`  but not
solved altogether: people must remember to use `quote_ident()` every
time, which is dangerous, because forgetting to use it will usually
work anyway... until one table name contains a special char.
Furthermore sometimes you will want to include a SQL value in the
query, which should be done in a complete different way (calling
adapt, prepare, getquoted...).

[quote_ident()]
http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.quote_ident

My attempt to a solution: the `psycopg2.sql` module:
http://initd.org/psycopg/docs/sql.html

The design was sort of sketched in bugs #308 and #358. Unlike these
first sketches there is no new method on connections or cursors: the
new module exposes certain Composable objects (SQL, Literal,
Identifier...) which can be composed using operators and methods and
forming new Composables. Composables can be ultimately used in place
of strings in queries.

[bug #308]: https://github.com/psycopg/psycopg2/issues/308
[bug #358]: https://github.com/psycopg/psycopg2/issues/358

With these objects it is possible to call:

    from psycopg2 import sql

    cur.execute(
        sql.SQL("insert into %s values (%%s)") % [sql.Identifier('mytable')],
        [42])

If you could take a look at the documentation, or play with the
feature, and let me know what you think about the feature design, or
any other observation, it would be great. The code currently lives in
the sql-compose branch of the github repository.

[sql-compose] https://github.com/psycopg/psycopg2/tree/sql-compose

Thank you very much!


-- Daniele