Re: Solving the SQL composition problem

On 01/03/2017 08:24 AM, Jim Nasby wrote:
> On 1/2/17 10:21 AM, Daniele Varrazzo wrote:
>>> execute('insert into %s values ($1)', [42], ['my table'])
>>>
>>> Obviously this would be backwards incompatible, but I think that's
>>> manageable.
>> mmm... what I think is that if these objects' replacement rules were
>> different one could leave the %s and %(name)s placeholder untouched
>> for the query params.
>>
>> A natural choice could be to use the str.format syntax for the query
>> composition, or a subset of it. Hence my example could be:
>>
>> cur.execute(
>> sql.SQL("insert into {} values (%s,
>> %s)").format(sql.Identifier('my_table')),
>> [10, 20])
>>
>> This would largely remove the need for double escaping.
>
> What I'm suggesting is to let Postgres handle the replacement of normal
> values, using the prepared statement syntax of $1, $2, etc[1], and only
> do identifier replacement in python (using quote_ident). That means a
> lot less time spent parsing, and opens the door for eventually doing
> more efficient stuff over the wire, like using binary type formats.

I believe a path to the above is explained here:

https://www.postgresql.org/message-id/AANLkTi=ym3SCQKCQBtp8RJHUswwAPOpjXYKTXS=aHWzp@mail.gmail.com

>
> 1: https://www.postgresql.org/docs/current/static/sql-prepare.html

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com