[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-----