This recipe defines a mixin class for DBAPI cursors that gives them an 'executeps' method. This method transparently converts any SQL query into a prepared statement, which gets cached and executed instead of the original query.
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 | '''
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])
|
Prepared statements offer performance improvements and also can help to protect against SQL injection. This recipe makes them painless to use, by transparently substituting a prepared statement for an original query when this is passed to the 'executeps' or 'executemanyps' methods defined by the cursor mixin class.
Note that we cannot just override 'execute', since the database may not accept all translations as valid. For example, the statement
SELECT table_name, column_name, column_default, udt_name FROM information_schema.columns WHERE table_name in %(tables)s
only works with execute but not with executeps on my postgres 8.3.6 installation.
Prepared statements seem to live as long as the underlying connection does, which therefore is the right place to store them. We try to get hold of the connection as the .connection attribute, which occurs for example in psycopg and bpgsql. Failing that, we store the statements on the cursor instance itself; this is only useful if the cursor instance is re-used, obviously.
