Python - mysqlDB, sqlite result as dictionary

Question

When I do someting like

sqlite.cursor.execute("SELECT * FROM foo")
result = sqlite.cursor.fetchone()

I think have to remember the order the columns appear to be able to fetch them out, eg

result[0] is id
result[1] is first_name

is there a way to return a dictionary? so I can instead just use result['id'] or similar?

The problem with the numbered columns is, if you write your code then insert a column you might have to change the code eg result[1] for first_name might now be a date_joined so would have to update all the code...

Possible duplicate of [How can I get dict from sqlite query?](http://stackoverflow.com/questions/3300464/how-can-i-get-dict-from-sqlite-query) Is this about sqlite or mysql? — Ciro Santilli OurBigBook.com, Nov 24 '15 at 08:59

score 35 · Answer 1 · edited Jul 29 '16 at 15:53

35

import MySQLdb
dbConn = MySQLdb.connect(host='xyz', user='xyz', passwd='xyz', db='xyz')
dictCursor = dbConn.cursor(MySQLdb.cursors.DictCursor)
dictCursor.execute("SELECT a,b,c FROM table_xyz")
resultSet = dictCursor.fetchall()
for row in resultSet:
    print row['a']
dictCursor.close
dbConn.close()

edited Jul 29 '16 at 15:53

linuxbuild

15,843
6
60
87

answered Mar 15 '13 at 01:57

Aijazs

426
4
8

Is there a way to add the rows retrieved by a `DictCursor` into a `set()` without running into `TypeError: unhashable type: 'dict'`? – code_dredd Jun 11 '16 at 01:08

score 13 · Answer 2 · answered Nov 21 '10 at 08:01

13

Doing this in mysqlDB you just add the following to the connect function call

cursorclass = MySQLdb.cursors.DictCursor

answered Nov 21 '10 at 08:01

Wizzard

12,582
22
68
101

Matt Williamson · Answer 3 · 2010-11-10T19:46:01.423

You can do this very easily. For SQLite: my_connection.row_factory = sqlite3.Row

Check it out on the python docs: http://docs.python.org/library/sqlite3.html#accessing-columns-by-name-instead-of-by-index

UPDATE:

Python 2.6.1 (r261:67515, Feb 11 2010, 00:51:29) 
[GCC 4.2.1 (Apple Inc. build 5646)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> conn.row_factory = sqlite3.Row
>>> c = conn.cursor()
>>> c.execute('create table test (col1,col2)')
<sqlite3.Cursor object at 0x1004bb298>
>>> c.execute("insert into test values (1,'foo')")
<sqlite3.Cursor object at 0x1004bb298>
>>> c.execute("insert into test values (2,'bar')")
<sqlite3.Cursor object at 0x1004bb298>
>>> for i in c.execute('select * from test'): print i['col1'], i['col2']
... 
1 foo
2 bar

mechanical_meat · Accepted Answer · 2010-11-10T18:40:57.520

David Beazley has a nice example of this in his Python Essential Reference.
I don't have the book at hand, but I think his example is something like this:

def dict_gen(curs):
    ''' From Python Essential Reference by David Beazley
    '''
    import itertools
    field_names = [d[0].lower() for d in curs.description]
    while True:
        rows = curs.fetchmany()
        if not rows: return
        for row in rows:
            yield dict(itertools.izip(field_names, row))

Sample usage:

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
>>> c.execute('create table test (col1,col2)')
<sqlite3.Cursor object at 0x011A96A0>
>>> c.execute("insert into test values (1,'foo')")
<sqlite3.Cursor object at 0x011A96A0>
>>> c.execute("insert into test values (2,'bar')")
<sqlite3.Cursor object at 0x011A96A0>
# `dict_gen` function code here
>>> [r for r in dict_gen(c.execute('select * from test'))]
[{'col2': u'foo', 'col1': 1}, {'col2': u'bar', 'col1': 2}]

score 1 · Answer 5 · answered Jul 06 '11 at 14:37

1

a sqlite3.Row instance can be converted to dict - very handy to dump a result as json

>>> csr = conn.cursor()
>>> csr.row_factory = sqlite3.Row
>>> csr.execute('select col1, col2 from test')
>>> json.dumps(dict(result=[dict(r) for r in csr.fetchall()]))

answered Jul 06 '11 at 14:37

hooblei

3,210
2
20
17

1

this gives me a `dictionary update sequence element #0 has length 15; 2 is required` – Tobi Dec 22 '12 at 17:01

Python - mysqlDB, sqlite result as dictionary

5 Answers5

Linked