[Tutor] tutorial for dbapi 2.0 how to use data

[Danny Yoo]

On Tue, 11 May 2004, John Fabiani wrote:

> Hi,
> mycur=con.cursor()
> mycur.execute("select * from sosord")
> mydata=mycur.fetchmany(10)
>
> The above works and returns data.  But know I need to understand how to
> view the data.  Maybe there is a way to convert the returned list into a
> dictionary.  A dictionary would allow me to get information by key (the
> field name).  Anyway, is there some paper or tutorial I can read on how
> to view the return data?


Hi John,


If you are using MySQLdb, then yes, there is a way of getting a customized
cursor that returns dictionaries instead of tuples.  For example:

###
>>> import MySQLdb.cursors
>>> cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
>>> cursor.execute("select * from pub_term where is_obsolete='n'")
57562L
>>> cursor.fetchone()
{'parentage': None, 'definition': 'A complex containing a heterodimer of a
catalytic subunit and a regulatory (adaptor) subunit of any
phosphoinositide 3-kinase (PI3K).', 'pub_object_id': 47030L,
'public_comment': None, 'date_entered': <DateTime object for '2001-05-31
00:00:00.00' at 400fdc98>, 'name': 'phosphoinositide 3-kinase complex',
'reference': None, 'date_updated': <DateTime object for '2003-10-07
00:00:00.00' at 40115640>, 'is_obsolete': 'n', 'pub_gene_id': None,
'entered_by': None, 'tair_keyword_id': 1L, 'go_external_id': 'GO:0005942',
'is_temporary': 'n', 'replaced_by': None, 'type': 'comp', 'id': 291L,
'date_last_synchronized': <DateTime object for '2003-10-10 00:00:00.00' at
40115678>, 'updated_by': None}
###



Hmmm... that was a little messy.  Let me use the pprint module to
pretty-print that output:

###
>>> from pprint import pprint
>>> pprint(cursor.fetchone())
{'date_entered': <DateTime object for '2001-05-31 00:00:00.00' at
401156b0>,
 'date_last_synchronized': <DateTime object for '2003-01-21 00:00:00.00'
at 40115720>,
 'date_updated': <DateTime object for '2002-07-23 00:00:00.00' at
401156e8>,
 'definition': None,
 'entered_by': None,
 'go_external_id': 'GO:0005943',
 'id': 292L,
 'is_obsolete': 'n',
 'is_temporary': 'n',
 'name': '1-phosphatidylinositol-4-phosphate kinase, class IA complex',
 'parentage': None,
 'pub_gene_id': None,
 'pub_object_id': 47031L,
 'public_comment': None,
 'reference': None,
 'replaced_by': None,
 'tair_keyword_id': 2L,
 'type': 'comp',
 'updated_by': None}
###

There you go.  *grin*



Even if other database modules don't support this dictionary cursor
directly, the DBI API 2.0 reference at:

    http://python.org/peps/pep-0249.html

says that all cursors need to have a 'description' attribute that gets set
whenever we execute a query:

###
>>> cursor = conn.cursor()   ## using the default cursor
>>> cursor.execute('select * from pub_term limit 1')
>>> pprint(cursor.description)
(('id', 3, 3, 12, 12, 0, 0),
 ('go_external_id', 253, 10, 20, 20, 0, 1),
 ('pub_object_id', 3, 5, 12, 12, 0, 1),
 ('name', 253, 33, 200, 200, 0, 1),
 ('type', 253, 4, 4, 4, 0, 1),
 ('date_entered', 10, 10, 10, 10, 0, 1),
 ('entered_by', 3, 0, 12, 12, 0, 1),
 ('definition', 252, 133, 65535, 65535, 0, 1),
 ('reference', 253, 0, 100, 100, 0, 1),
 ('pub_gene_id', 3, 0, 12, 12, 0, 1),
 ('is_temporary', 254, 1, 1, 1, 0, 1),
 ('date_updated', 10, 10, 10, 10, 0, 1),
 ('updated_by', 3, 0, 12, 12, 0, 1),
 ('is_obsolete', 254, 1, 1, 1, 0, 0),
 ('replaced_by', 3, 0, 12, 12, 0, 1),
 ('parentage', 252, 0, 65535, 65535, 0, 1),
 ('tair_keyword_id', 3, 1, 12, 12, 0, 1),
 ('date_last_synchronized', 10, 10, 10, 10, 0, 1),
 ('public_comment', 252, 0, 65535, 65535, 0, 1))
###

and given this, we can probably cook up a 'fetch_dict()' function from any
cursor.


Hope this helps!