mfenniak/pg8000 — pull request #12 by zzzeek (2012-08-29 19:52:37+00:00)
Title: Some speedups for convert_paramstyle()

Hi Mathieu -

I'm reviewing pg8000 for use with a new database platform that is not at all Postgresql, however uses Postgresql's protocol for the most part, called Akiban server (http://www.akiban.com/), which includes some very non-standard relational database features. pg8000 is an obvious choice for integrating here as it is a simple and well-written pure Python driver that's easy to modify.

Integrating pg8000 with Akiban's special features may be a matter of using it as is with some external code around it, adding new features to pg8000 itself, or just forking pg8000 altogether into a new Akiban-specific driver, as Akiban is really a different engine altogether, and ideally I'd like to add some new features to the cursor that would not have any place within a standard Postgresql driver. If you had any opinions on forking vs. extending please feel free.

Anyway, this pull request consists of some refinements to the convert_paramstyle() method to improve upon it's speed a small amount. The test below illustrates 10-20% speed improvements with various styles. I also added some more tests for paramstyles and reformatted.

- mike

from pg8000.dbapi import convert_paramstyle

import timeit

def go1():
new_query, new_args = convert_paramstyle("numeric", "SELECT :2, :1, * FROM t WHERE a=:3", (1, 2, 3))
def go2():
new_query, new_args = convert_paramstyle("format", "SELECT %s, %s, * FROM t WHERE a=%s", (1, 2, 3))
def go3():
new_query, new_args = convert_paramstyle("qmark", "SELECT ?, ?, * FROM t WHERE a=?", (1, 2, 3))
def go4():
new_query, new_args = convert_paramstyle("named", "SELECT :aname, :bname, * FROM t WHERE a=:cname", {"aname": 1, "bname": 2, "cname": 3})
def go5():
new_query, new_args = convert_paramstyle("pyformat", "SELECT %(aname)s, %(bname)s, * FROM t WHERE a=%(cname)s", {"aname": 1, "bname": 2, "cname": 3})

print "numeric:", timeit.timeit(go1, "from __main__ import go1", number=1000000)
print "format:", timeit.timeit(go2, "from __main__ import go2", number=1000000)
print "qmark:", timeit.timeit(go3, "from __main__ import go3", number=1000000)
print "named:", timeit.timeit(go4, "from __main__ import go4", number=1000000)
print "pyformat:", timeit.timeit(go5, "from __main__ import go5", number=1000000)

"""
# with patches: 806fa87a251167ea
numeric: 16.8797850609
format: 18.6624319553
qmark: 18.0139410496
named: 22.1270830631
pyformat: 23.728094101

# without: 6872e427a36364
numeric: 21.0781331062
format: 22.9806361198
qmark: 21.7251501083
named: 30.8408219814
pyformat: 27.5269310474

"""