postgresql modules and performance

[Mage]

Hello,

I started to write my PostgreSQL layer. I tried pyPgSQL and PyGreSQL. I
made a *very minimal* performance test and comparsion with the same
thing in php. Table "movie" has 129 record and many fields.

I found PyGreSQL / DB-API / fetchall horrible slow (32 sec in my test).
PHP did 13 secs and it gave the result in associative array. Maybe I did
something bad.

pyPgSQL / DB-API raised a futurewarning and didn't worked.

pyPgSQL / libpg worked well. I can create php-like dictionary result in
14 secs.

Here is the code. It's only a test...

Mage

------ test.py --------
import mpypg
import datetime

print 'This is a python postgesql module'

db = mpypg.mpypgdb(' dbname=test host=localhost' )

def test():
res = db.query('selec t * from movie')
#print res['fields']
#print res['rows']
#pass

def test2():
res = db.query('selec t * from movie')
#print res['fields']
#print len(res['rows'])
#print res['rows']
print len(res)
print res[1]

start = datetime.dateti me.now()

for i in range(100):
test()
#pass

end = datetime.dateti me.now()

print end - start

test2()

------ mpypg.py --------
from pyPgSQL import libpq
from pyPgSQL import PgSQL
import sys
import pgdb

mpypg_connect_e rror_message = 'Could not connect to the database'
mpypg_query_err or_message = 'Could not run the query'

class mpypgdb:
'my database class'
def __init__(self,s tr):
try:
self.database = libpq.PQconnect db(str)
except:
mpypg_error_msg (mpypg_connect_ error_message)

def query(self,quer y):
try:
res = self.database.q uery(query)
fields = tuple([res.fname(i) for i in range(res.nfiel ds)])
rows = []

'''
for name in fields:
rows[name] = []


for i in range(res.ntupl es):
for j in range(len(field s)):
rows[fields[j]].append(res.get value(i,j))
'''
'''
for j in range(len(field s)):
rows[fields[j]] = tuple([res.getvalue(i, j) for i in
range(res.ntupl es)])
'''

for i in range(res.ntupl es):
rows.append(dic t([(fields[j], res.getvalue(i, j)) for j
in range(len(field s))]))

res.clear()
result = {'fields': fields, 'rows': rows}
return result;
except:
mpypg_error_msg (mpypg_query_er ror_message)


class mpgdb:
'my database class'
def __init__(self,s tr):
try:
self.database = pgdb.connect(da tabase='test')
except:
mpypg_error_msg (mpypg_connect_ error_message)

def query(self,quer y):
try:
cursor = self.database.c ursor()
res = cursor.execute( query)

#result = {'fields': fields, 'rows': cursor.fetchall ()}
result = [cursor.fetchone () for i in range(cursor.ro wcount)]

#result = cursor.fetchall ()
return result;
except:
mpypg_error_msg (mpypg_query_er ror_message)



def mpypg_error_msg (message):
'Database error handler'
sys.exit(messag e)

[Reinhold Birkenfeld]

Re: postgresql modules and performance

Mage wrote:[color=blue]
> Hello,
>
> I started to write my PostgreSQL layer. I tried pyPgSQL and PyGreSQL. I
> made a *very minimal* performance test and comparsion with the same
> thing in php. Table "movie" has 129 record and many fields.
>
> I found PyGreSQL / DB-API / fetchall horrible slow (32 sec in my test).
> PHP did 13 secs and it gave the result in associative array. Maybe I did
> something bad.[/color]

Have you tried psycopg?

Reinhold

[Mage]

Re: postgresql modules and performance

Reinhold Birkenfeld wrote:
[color=blue]
>
>Have you tried psycopg?
>
>[/color]
Thank you. It did the fetchall() in 11 secs and 13 secs with dictionary
creating. It's the fastest so far.

Mage