[DB-SIG] Prepared statements in python drivers

INADA Naoki songofacandy at gmail.com
Wed Apr 9 04:49:33 CEST 2014

+1 on explicit separation between prepared statement and server-side

Prepared statement holds resource on server side.
For example, following code is dangerous when combined with connection pool.

def myexecute(params):
    cur = con.cursor()
    cur.prepare(MY_STMT)  # allocate resource on server-side that is
freed when connection is closed.
    return cur.execute(params)

I think prepared statement should be bound to connection.

Connection.prapare(name, stmt, skip_on_dup=False)
Prepare stmt as name.
If skip_on_dup is true and same name is prepared before, do nothing.

Cursor.execute_prepared(name, params, stmt=None)
Execute prepared statement named name.
If stmt is not None, call Cursor.prepare(name, stmt, skip_on_dup=True)
before execute.

Example 1:

def myexecute(params):
    cur = connection.cursor()
    return cur.execute_prepared("mystmt", params, MY_STMT)

Example 2:

def create_connection(opts):
    global _connection
    _connection = MyDB.connect(**opts)
    _connection.prepare("mystmt", MY_STMT)

def myexecute(params):
    cur = _connection.cursor()
    cur.execute_prepared("mystmt", params)

On Tue, Apr 8, 2014 at 6:21 AM, Peter Eisentraut <peter_e at gmx.net> wrote:
> On 3/24/14, 12:53 PM, Daniele Varrazzo wrote:
>> - is there enough consensus - not only in the Python world - about how
>> to implement a prepared statements interface on a db driver?
> In other languages, there is often a parameter on the connection handle
> saying whether (the analogue of) a prepare+execute call should be only a
> parameter substitution or a full prepared statement.  I think this is
> due to the historical confusion between these aspects and therefore not
> a good idea for a new API.  In Python, adding an explicit prepare
> function and having that actually prepare in all cases sounds like a
> decent idea to me.
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> https://mail.python.org/mailman/listinfo/db-sig

INADA Naoki  <songofacandy at gmail.com>

More information about the DB-SIG mailing list