Thread: Prepared Statements integration in PsycoPG
Hi all:
I'm trying to integrate the PQprepare and PQexecPrepared methods of libpq in the psycopg, but i'm not sure what API must have in python.
My initial idea is two new methods on the cursor class:
* prepare(statement_name, statement_query)
* execPrepared(statement, values)
This have some extra problems like knowing the number and type of attributes expected by prepare and execPrepared, needed by the PQprepare and PQexecPrepared. One option is to add and extra parameter that receive this information, another way is parse the statement_query to extract this information, but probably must be a simplified versión of this (force types like TEXT to not explicitly defined types).
Probably another option is take the same solution to the PQexecParams.
Any idea about this? Any suggestion?
Greetings and thanks.
On 16/12/2013 21:38, Jesús Espino wrote: > > I'm trying to integrate the PQprepare and PQexecPrepared methods of > libpq in the psycopg, but i'm not sure what API must have in python. > > My initial idea is two new methods on the cursor class: > > * prepare(statement_name, statement_query) > * execPrepared(statement, values) > > This have some extra problems like knowing the number and type of > attributes expected by prepare and execPrepared, needed by the PQprepare > and PQexecPrepared. One option is to add and extra parameter that > receive this information, another way is parse the statement_query to > extract this information, but probably must be a simplified versión of > this (force types like TEXT to not explicitly defined types). The problem is a little bit harder than that: you need more than the number of parameters. PQprepare takes an array of Oid to specify the types of all the parameters that will be used in the query (just like PQexecParams does). Python dynamic nature makes difficult to know the types beforehand and asking the user to pass an array of types more or less kills the Python philosophy. Anyway, if you manage to integrate PQprepare you'll get PQexecParams almost for free (and that's interesting because remove some computation, i.e., quoting, from the client). For the API I'd simply overload execute() with a prepared statement instead of a query string and keep all the prepared statement specific data into a new type: stmt = connection.prepare(name, query, types) curs = connection.cursor(...) curs.execute(stmt, parameters) # execute prepared statement stmt.close() # destroy prepared statement on server curs.execute(stmt, parameters) # raise an exception, obviously Having a new type is nice because you can free server-side resources when it goes out of scope and we can avoid cluttering the cursor type with all the new stuff needed to keep track of types, statement names and so on. federico -- Federico Di Gregorio federico.digregorio@dndg.it Di Nunzio & Di Gregorio srl http://dndg.it La gente sopravvaluta l'alcool e sottovaluta il contesto. -- anonymous