[DB-SIG] query - prepared statment

[Carsten Haese]

On Fri, 2006-02-10 at 04:07, python eager wrote:
> Hi All,
>    
>   How to use prepared statment. Can u give one example  for insert, 
> delete and update statments. please.

The DB-API standard does not define a separate method for preparing a
statement. Preparing the statement is part of the cursor.execute() and
executemany() methods. execute() prepares the statement and executes it
with the given parameters. executemany() should prepare the statement
once and execute it many times over, once for each set of parameters.

For DB-API implementations that implement statement caching (informixdb
and cx_Oracle, for example), the first execute call takes the place of
preparing a statement. If you subsequently execute the same query string
on the same cursor with different parameters, the same prepared
statement is reused.

Apparently cx_Oracle implements a non-standard prepare() method that
prepares a query string for later execution by either passing the same
string or None to execute().

I'll give you an (untested because I don't use Oracle) example for
delete. I'm sure you can figure out the other cases yourself.

import cx_Oracle
conn = cx_Oracle.connect(...)
cur = conn.cursor()
cur.prepare("delete from sometable where someID = ?")
for id in somelist:
  cur.execute(None, (id,) )


Note that in a standard DB-API implementation, you'd simply write

import somedb
conn = somedb.connect(...)
cur = conn.cursor()
for id in somelist:
  cur.execute("delete from sometable where someID = ?", (id,) )

which is just as efficient if the module implements statement caching.


Hope this helps,

Carsten.