Re: [psycopg] Solving the SQL composition problem

On 01/01/2017 12:11 AM, Daniele Varrazzo wrote:
> 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

Yes this is going to make building dynamic SQL cleaner then the
hodgepodge of things I have created over the years. Only hiccup I had
was more me then anything else. You have this example:

from psycopg2 import sql

cur.execute(
     sql.SQL("insert into %s values (%%s, %%s)") %
[sql.Identifier('my_table')],
     [10, 20])

towards the top of the docs section. When I got down to the Placeholder
section it did not occur to me at first that I could eliminate
Placeholder instances by just putting %%s in the template. Going back
over the docs got me to the above example and clarity.


>
> Thank you very much!
>
>
> -- Daniele
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com