DBAPI Paramstyle

[Bob Parnes]

The following script is a one person's  comparison of three methods for
accessing a postgresql database using psycopg on a debian computer
running python2.3. Following it are the results of running it six
times.

===========================
from time import time, clock
import psycopg

MAX_COUNT = 50000

def pyMethod():
    for n in range(MAX_COUNT):
        curs.execute('''SELECT %s;''' % n)

def formatMethod():
    for n in range(MAX_COUNT):
        curs.execute('''SELECT %s;''', [n])

def pyformatMethod():
    for n in range(MAX_COUNT):
        curs.execute('''SELECT %(n)s;''', {'n':n})

conn = psycopg.connect(host='localhost', database='template1')
curs = conn.cursor()
        
for method, func in (('Python method: %f, %f', pyMethod),
                     ('Format method: %f, %f', formatMethod),
                     ('Pyformat method: %f, %f', pyformatMethod)):
    startTime = time()
    startClock = clock()
    func()
    print method % ((time() - startTime), (clock() - startClock))
===========================

bp at debian:~/demo$ ./pyformatTst.py
Python method: 9.288770, 3.55000
Format method: 9.457663, 3.820000
Pyformat method: 9.446390, 3.700000

bp at debian:~/demo$ ./pyformatTst.py
Python method: 9.152173, 3.400000
Format method: 9.314743, 3.760000
Pyformat method: 9.329343, 3.840000

bp at debian:~/demo$ ./pyformatTst.py
Python method: 9.262013, 3.490000
Format method: 9.344197, 3.570000
Pyformat method: 9.402157, 3.500000

bp at debian:~/demo$ ./pyformatTst.py
Python method: 9.170817, 3.860000
Format method: 9.509313, 3.260000
Pyformat method: 9.380756, 3.770000

bp at debian:~/demo$ ./pyformatTst.py
Python method: 9.271831, 3.540000
Format method: 9.375170, 3.650000
Pyformat method: 9.426898, 3.780000

bp at debian:~/demo$ ./pyformatTst.py
Python method: 9.192097, 3.720000
Format method: 9.244554, 3.690000
Pyformat method: 9.368582, 3.760000

Similar results occurred with an actual database table. 

I must be missing something, so perhaps someone can explain
the benefit of a paramstyle over the usual Python formatting
style and maybe suggest a test to show it. Thanks.

Bob Parnes

-- 
Bob Parnes
rparnes at megalink.net