Checking for resultset in Python DB API

(Warning: Only tested with psycopg2)

Sometimes you want to execute an SQL query but don’t know whether it will return a resultset in advance. An example would be a psql-like interface where the user can type arbitrary queries (DQL, DML, or DDL) or a very generic interface class.

Calling fetchone() or fetchall() raises a ProgrammingError when there is no resultset. We could catch that, but that’s not elegant.

Parsing the query to determine whether it should return a resultset is not feasible.

However, the description property describes the resultset and therefore is not None iff there is one:

if csr.description:
    result = csr.fetchall()

Test script:

#!/usr/bin/python3
import pprint
import psycopg2

def q(csr, query):
    print(query)
    csr.execute(query)
    print(csr.rowcount, " rows")
    pprint.pprint(csr.description)
    if csr.description:
        pprint.pprint(csr.fetchall())
    print("")



db = psycopg2.connect("")

csr = db.cursor()

q(csr, "select * from t")
q(csr, "insert into t(t, v) values('foo', 23)")
q(csr, "insert into t(t, v) select t, v + 1 from t where v < 1000")
q(csr, "insert into t(t, v) values('foo', 23) returning id")
q(csr, "delete from t")
q(csr, "select * from t")

Output:

select * from t
13  rows
(Column(name='id', type_code=23),
 Column(name='t', type_code=25),
 Column(name='v', type_code=23))
[(1, 'a', 1000),
 (4, 'd', 1000),
 (7, 'f', 1000),
 (8, 'g', 1000),
 (3, 'c', 1100),
 (9, '20', 30),
 (10, 'a', 1000),
 (11, 'd', 1000),
 (12, 'f', 1000),
 (13, 'g', 1000),
 (14, 'c', 1100),
 (15, '20', 30),
 (16, 'test', 42)]

insert into t(t, v) values('foo', 23)
1  rows
None

insert into t(t, v) select t, v + 1 from t where v < 1000
4  rows
None

insert into t(t, v) values('foo', 23) returning id
1  rows
(Column(name='id', type_code=23),)
[(43,)]

delete from t
19  rows
None

select * from t
0  rows
(Column(name='id', type_code=23),
 Column(name='t', type_code=25),
 Column(name='v', type_code=23))
[]