Re: Server side prepared statements and executemany

On 15/11/2013 12:10, Daniele Varrazzo wrote:
> On Fri, Nov 15, 2013 at 8:45 AM, Luca Ferroni <luca(at)befair(dot)it> wrote:
>
>> I followed Daniele's post
>> http://initd.org/psycopg/articles/2012/10/01/prepared-statements-psycopg/
>> and I tried his implementation
>> https://gist.github.com/dvarrazzo/3797445
>>
>> but I have discovered that it does not work for a syntax error on $1
>>
>> In the doc I read that cursor.execute() prepares and executes statements.
>> I take a look at the python and c code, but I didn't get where this happens.
> Ciao Luca,
>
> can you provide an example with the query you want to execute and the
> error message?

Ciao Daniele,

you are right, here is the example. In writing it I noticed an interesting behaviour.
Prepared statements work with query like:

prepare psyco_1 as SELECT * FROM "prova" WHERE "name" = $1

but they raise ProgrammingError (syntaxerror SQL) with the operator IN

prepare psyco_1 as SELECT * FROM "prova" WHERE "name" IN $1

It's not a problem in your implementation I think but a lower level bug
or something that cannot be done in SQL and I don't understand why...

The example (without PreparingCursor factory):

import psycopg2

conn_string = "host='localhost' dbname='postgres' user='postgres' password='secret'"

def main(argv):

conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
cursor.execute("""
CREATE TEMPORARY TABLE prova (
id SERIAL PRIMARY KEY,
name VARCHAR(32)
)
""")
cursor.executemany("insert into prova (name) values (%s)", (('casa',), ('mamma',),('ape',)))

cursor.execute("prepare psyco_1 as SELECT * FROM \"prova\" WHERE \"name\" = $1");
cursor.execute("execute psyco_1 ('casa')")
print cursor.fetchall()

cursor.execute("prepare psyco_2 as SELECT * FROM \"prova\" WHERE \"name\" IN $1");
# raised
print ("...exception has been already raised...")

thank you
Luca

>
> Thank you.
>
> -- Daniele

--
Luca Ferroni
http://www.befair.it - http://www.lucaferroni.it
Tel: +39 328 9639660
LinkedIn: http://www.linkedin.com/in/lucaferroni