How to return data from a select in sqlite3 as dictionaries instead of tuples in Python?

Question

I’m using sqlite3 to record some data. However, by default, when a select is made, it returns the information as a list of tuples, which is considerably harder to work with than if they were dictionaries. Example:

>>> import sqlite3
>>> bd = sqlite3.connect('python.db')
>>> cursor = bd.cursor()
>>> cursor.execute('CREATE TABLE livro (titulo text, autor text)')
>>> cursor.execute('INSERT INTO livro VALUES (?, ?)', ('Manoel', 'Minha história'))
>>> cursor.execute('SELECT * FROM livro')
>>> livros = cursor.fetchall()
>>> livros
[('Manoel', 'Minha história')]

The most obvious solution I can think of would be:

>>> lista_livros = []
>>> for livro in livros:
>>>     d = {'autor': livro[0], 'titulo': livro[1],}
>>>     lista_livros.append(d)

But that way the code gets pretty big when there are too many columns. And that way, every time there’s any change in the columns, it would be necessary to change the code as well.

Answer 1

This result can be achieved by changing the attribute row_factory cursor.

This attribute should receive a callable, which accepts as arguments the cursor and the records (Rows), as in this adapted example of the documentation:

>>> import sqlite3
>>> 
>>> def dict_factory(cursor, row):
...     d = {}
...     for idx, col in enumerate(cursor.description):
...         d[col[0]] = row[idx]
...     return d
... 
>>> bd = sqlite3.connect('python.db')
>>> cursor = bd.cursor()
>>> cursor.execute('CREATE TABLE livro (titulo text, autor text)')
>>> cursor.execute('INSERT INTO livro VALUES (?, ?)', ('Manoel', 'Minha história'))
>>> cursor.row_factory = dict_factory
>>> cursor.execute('SELECT * FROM livro')
>>> livros = cursor.fetchall()
>>> livros
[{'titulo': 'Manoel', 'autor': 'Minha história'}]
>>> livros[0]['titulo']
'Manoel'

The documentation also mentions that in cases where performance is required can be used the callable sqlite3.Row, which has functions such as column name mapping, indexing, iteration, equality testing and supports the Len() method that returns the total number of records(Rows).

Demonstration of use of sqlite3.Row taken from documentation:

>>> conn = sqlite3.connect(":memory:")
>>> c = conn.cursor()
>>> c.execute('''create table stocks (date text, trans text, symbol text, qty real, price real)''')
>>> c.execute("""insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)""")
>>> conn.commit()
>>> c.close()

>>> conn.row_factory = sqlite3.Row
>>> c = conn.cursor()
>>> c.execute('select * from stocks')
>>> r = c.fetchone()
>>> type(r)
<class 'sqlite3.Row'>
>>> tuple(r)
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
>>> len(r)
5
>>> r[2]
'RHAT'
>>> r.keys()
['date', 'trans', 'symbol', 'qty', 'price']
>>> r['qty']
100.0
>>> for member in r:
...     print(member)
...
2006-01-05
BUY
RHAT
100.0
35.14