[Tutor] overview of how data is handled [getting dictionaries from sql cursor / generators]

[Danny Yoo]

On Tue, 18 May 2004, John Fabiani wrote:

> The handling of data is like nothing I have ever done.  It's nothing
> like Delphi, Java, .Net or VFP.

Hi John,


The approach that the DB API 2.0 takes is similar to Java's JDBC standard.
A 'cursor' object in Python behaves like a combination of a Java's
'java.sql.Statement' and 'java.sql.ResultSet'.

When we say something in Java like:

/*** Java ***/
PreparedStatement stmt = conn.prepareStatement
    ("select title, abstract from article");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
     System.out.println(rs.getString(1));
     System.out.println(rs.getString(2));
}
/******/

then an equivalent translation of this, using Python's API, looks
something like this:

### Python
cursor = conn.cursor()
cursor.execute("select title, abstract from article")
for (title, abstract) in cursor.fetchall():
    print title
    print abstract
###

The translation isn't exact.  I've tried to improve the readability of the
Python code, but I think that's perfectly fair.  *grin*


The examples above use explicit numeric indices to get at a particular
column, but you've mentioned that you might want to use a dictionary
instead.

> OK I was able to convert the data into a dict.  But doing so required
> many lines of code (two loops).  If the program has to convert from list
> into dict for ten's of thousands of records it is going to be very slow.

Can you show us what your code looks like?  And are you sure it's going to
be too slow?

Python is relatively slow, but at the same time, tens of thousands of
records doesn't sound like too much data at all.  *grin* I would strongly
recommend not to assume that performance is poor until a real performance
measurement is taken.  Human intuition on what's fast and what's slow can
be deceptive.


Here's an example that shows how to extract dictionaries from a cursor,
assuming that our database doesn't have a native dictionary cursor
interface:

###
>>> def getDictsFromCursor(cursor):
...     while True:
...         nextRow = cursor.fetchone()
...         if not nextRow: break
...         d = {}
...         for (i, columnDesc) in enumerate(cursor.description):
...             d[columnDesc[0]] = nextRow[i]
...         yield d
...
>>> cursor.execute("select id, name from pub_term limit 3")
3L
>>> for d in getDictsFromCursor(cursor):
...     print d
...
{'id': 107152L, 'name': "'de novo' GDP-L-fucose biosynthesis"}
{'id': 5258L, 'name': "'de novo' IMP biosynthesis"}
{'id': 5259L, 'name': "'de novo' protein folding"}
###


Hope this helps!