[DB-SIG] There is a better way
Richard Taylor
r.taylor@eris.qinetiq.com
Fri, 7 Jun 2002 11:34:24 +0100
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Following the previous helpful discussions about how best to work out what the
returns from a query are without relying on DB specific exception values, I
have a better attempt at how to do it. It would appear that using a
combination of checking cursor.description and cursor.rowcount I can get the
result that I need. The following code is what I have come up with:
def query(cnx,q):
"""Simple wrapper function for DB API query. This will always
return a list. The list will be empty for queries that do not
contain a select, queries that return an empty result set and
queries that result in errors. Errors will be reported on
stderr.
The parameters are:
cnx - an open DB API v2.0 connection object
q - an sql query string.
"""
try:
cr = cnx.cursor()
cr.execute (q)
cnx.commit()
except:
type, value = sys.exc_info()[:2]
sys.stderr.write("DB API execute failed with Exeception: %s, %s"\
% (type,value))
return []
# Trap selects that return nothing and
# queries that do not contain a select.
if cr.description == None or cr.rowcount == 0 :
return []
try:
res = cr.fetchall()
except:
type, value = sys.exc_info()[:2]
sys.stderr.write("DB API fetch failed with Exeception: %s, %s"\
% (type,value))
return []
return res
The intention is to provide a simple function for making queries on any DB API
module that is guaranteed to return a list (possibility empty). Any errors
are reported on stderr but they could be properagated.
I would welcome comments on whether this will work on all DB API modules.
I used the following simple test code to check that psycopg produced the
behavior that was expected (output below the code). It would be interesting
to see if other adaptors produce the same behavior.
cnx = psycopg.connect("user=rtaylor dbname=rtaylor")
cr = cnx.cursor()
query(cnx,'drop table telephone')
query(cnx,'create table telephone ("name" text, "tel" text)')
query(cnx,"insert into telephone values ('Richard', '12345678')")
query(cnx,"insert into telephone values ('Steve', '01002030')")
cnx.commit()
del cr
# select of zero rows
def check(q):
ex_type = 'Not Set'
ex_value = 'Not Set'
rows = 'Not Set'
cr = cnx.cursor()
cr.execute(q)
cnx.commit()
desc = repr(cr.description)
rowcount = repr(cr.rowcount)
try:
rows = cr.fetchall()
ex_thrown = 0
except:
ex_type, ex_value = sys.exc_info()[:2]
ex_thrown = 1
return (str(rowcount),str(rows),str(ex_thrown),
str(ex_type),str(ex_value),str(desc))
tests = {'Empty result': "select name from telephone where name =
'unknown'",
'Insert': "insert into telephone values('test','0000')",
'Update': "update telephone set name = 'new' where name =
'Richard'"}
format = "%-12s %-8s %-8s %-9s %-20s %-20s %-30s"
print format % ("test" , "rowcount", "rows", "ex_thrown", "ex_type",
"ex_value", "description")
print "=" * 100
for test in tests.keys():
t = (test,) + check(tests[test])
s = format % t
print s
Output from psyopg (probably horribly line wrapped):
test rowcount rows ex_thrown ex_type ex_value description
====================================================================================================
Update 1 Not Set 1 psycopg.Error no results to
fetch None
Empty result 0 [] 0 Not Set Not Set
(('name', 25, None, -1, None, None, None),)
Insert 1 Not Set 1 psycopg.Error no results to
fetch None
Many thanks for everyones help in getting this right. I am much happier not
relying on DB specific exception values.
Richard
- --
QinetiQ
B105 Woodward Building
St. Andrews Road
Malvern
Worcs WR14 3PS
The Information contained in this E-Mail and any subsequent correspondence
is private and is intended solely for the intended recipient(s).
For those other than the recipient any disclosure, copying, distribution,
or any action taken or omitted to be taken in reliance on such information is
prohibited and may be unlawful.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE9AIww7Z7YaKfan9kRAgpyAKDhJZycIHl1TJyniFS8RRxiS0HrGgCgjcv9
LNPzDNCjxQ95x4uUXxbld6Q=
=RX7q
-----END PGP SIGNATURE-----