Thread: Server side prepared statements and executemany
Hi all, I'm a new subscriber of this list. I use postgresql and python-psycopg2 for some years, mostly through Django, but sometimes directly. First of all, thanks and many compliments for this piece of sotware to the authors. I write about server side prepared statements support. I asked to majordomo-owner for a sweet way to search in the archives, but I haven't got response, so I hope you forgive me if I ask about an argument you have alreasy discussed about. 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. Can you please me confirm what is written in the doc and how it works? In that case, the Daniele's post on initd.org is to be considered obsoleted? Furthermore: is there a way to call explicitly the SQL PREPARE statement? At the end: can I use "executemany" on SELECT statements, and retrieve results from different selects? how? Thanks again for your works Luca `fero` -- Luca Ferroni http://www.befair.it - http://www.lucaferroni.it Tel: +39 328 9639660 LinkedIn: http://www.linkedin.com/in/lucaferroni
On Fri, Nov 15, 2013 at 8:45 AM, Luca Ferroni <luca@befair.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? Thank you. -- Daniele
On 15/11/2013 12:10, Daniele Varrazzo wrote: > On Fri, Nov 15, 2013 at 8:45 AM, Luca Ferroni <luca@befair.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
On Fri, Nov 15, 2013 at 12:34 PM, Luca Ferroni <luca@befair.it> wrote: > 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 I suspect the () of IN are a syntactic feature and cannot be passed as a parameter. Do you want to try to replace the query with "... WHERE name = any($1)" and pass the parameter as a python list instead of a tuple? (see also http://initd.org/psycopg/docs/usage.html#adapt-list) -- Daniele
On 15/11/13 03:45, Luca Ferroni wrote: > I asked to majordomo-owner for a sweet way to search in the archives, > but I haven't got response, so I hope you forgive me if I ask about an > argument > you have alreasy discussed about. The list is archived in postgresql.org, so you can search the archives at http://www.postgresql.org/search/?m=1 Joe
On 15/11/2013 13:34, Luca Ferroni wrote: > 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 IN has always had problems, not just when used thorugh psycopg. My suggestion is to substitute it with ANY: prepare psyco_1 as SELECT * FROM "prova" WHERE "name" = ANY($1). Note that $1 should be an array (but isn't a problem when using psycopg, just pass a list). federico -- Federico Di Gregorio federico.digregorio@dndg.it Di Nunzio & Di Gregorio srl http://dndg.it If a process is potentially good, but 90%+ of the time smart and well-intentioned people screw it up, then it's a bad process. -- Steve Yegge
On Friday 15 November 2013, Daniele Varrazzo wrote: > On Fri, Nov 15, 2013 at 12:34 PM, Luca Ferroni <luca@befair.it> wrote: > > ... > > but they raise ProgrammingError (syntaxerror SQL) with the operator IN > > prepare psyco_1 as SELECT * FROM "prova" WHERE "name" IN $1 > > I suspect the () of IN are a syntactic feature and cannot be passed as > a parameter. > Indeed it is. It contains distinct parameters (as far as pq_execparams is concerned). -- Disclaimer waiver: When you send me an unencrypted email, you implicitly allow me, or any 3rd person reading our mails, to do anything I/they wish with your data (including presenting them in public). Your disclaimer, thus, is void. If you had wanted a private communication, you should have used encryption in the first place.