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
>
> Thank you very much!
Wow, that is very cool. I have gotten as far reading the docs,
especially like Placeholder. Still need to check out the branch and
play. Will report back.
>
>
> -- Daniele
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
