'''
Transparent use of prepared statements with Postgresql.
Usage example with psycgopg2: We create a cursor that provides
both with dict-like field access and prepared statements.
from psycopg2.extensions import connection as _connection
from psycopg2.extras import RealDictCursor
from this_recipe import PrepCursorMixin
class Cursor(PrepCursorMixin, RealDictCursor):
pass
class Connection(_connection):
def cursor(self):
return super(Connection, self).cursor(cursor_factory=Cursor)
def connect(*a, **kw):
return Connection(*a, **kw)
'''
import re
class PrepCursorMixin(object):
'''
mix in with dbapi cursor class
formatRe fishes out all format specifiers for a given paramstyle
this one works with paramstyles 'format' or 'pyformat'
'''
formatRe = re.compile('(\%s|\%\([\w\.]+\)s)', re.DOTALL)
def __init__(self, *a, **kw):
super(PrepCursorMixin, self).__init__(*a, **kw)
# preferably store prepd statements on connection
conn = getattr(self, 'connection', None)
if conn:
pc = getattr(conn, 'prepCache', {})
self.prepCache = self.connection.prepCache = pc
else:
self.prepCache = {}
def executeps(self, cmd, args=None):
'''
execute a command using a prepared statement.
'''
prepStmt = self.prepCache.get(cmd)
if prepStmt is None:
cmdId = "ps_%d" % (len(self.prepCache) + 1)
# unique name for new prepared statement
prepStmt = self.prepCache[cmd] = \
self.prepareStatement(cmd, cmdId)
self.execute(prepStmt, args)
def prepareStatement(self, cmd, cmdId):
'''
translate a sql command into its corresponding
prepared statement, and execute the declaration.
'''
specifiers = []
def replaceSpec(mo):
specifiers.append(mo.group())
return '$%d' % len(specifiers)
replacedCmd = self.formatRe.sub(replaceSpec, cmd)
prepCmd = 'prepare %s as %s' % (cmdId, replacedCmd)
if len(specifiers) == 0: # no variable arguments
execCmd = 'execute %s' % cmdId
else: # set up argument slots in prep statement
execCmd = 'execute %s(%s)' % (cmdId, ', '.join(specifiers))
self.execute(prepCmd)
return execCmd
def executemanyps(self, cmd, seq_of_parameters):
'''
prepared statement version of executemany.
'''
for p in seq_of_parameters:
self.executeps(cmd, p)
# Don't want to leave the value of the last execute() call
try:
self.rowcount = -1
except TypeError: # fooks with psycopg
pass
if __name__ == '__main__':
'''
just demonstrate the string mangling that goes on
'''
class DummyBaseCursor(object):
def __init__(self, conn):
self.connection = conn
def execute(self, cmd, args=None):
print 'execute'
print 'cmd:', cmd
print 'args:', args
print '-' * 20
class DummyCursor(PrepCursorMixin, DummyBaseCursor):
def executeps(self, cmd, args):
print 'executeps'
print 'cmd:', cmd
print 'args:', args
super(DummyCursor, self).executeps(cmd, args)
class DummyConnection(object): pass
dc = DummyCursor(DummyConnection)
sql = \
['''
select * from dummies
where name=%s
and surname=%s
''',
'''
select * from dummies
where name=%(name)s
and surname=%(surname)s
''',
'select * from dummies']
theargs = [('Joe','Blow'), {'name':'Joe', 'surname':'Blow'}, None]
for x in range(3):
for y in range(2):
dc.executeps(sql[x], theargs[x])
