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

Question

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

Navigation

#1 by (2 votes)

1

I'm using sqlite3 to write some data. However, by default, when a select is made, it returns the information as a list of tuples, which is considerably more difficult to work 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 in this way the code gets considerably larger when there are many columns. And so, every time there is a change in the columns, it would be necessary to change the code as well.

python python-3.x sqlite3

asked by anonymous 28.10.2016 / 13:16

1 answer

Refresh RecyclerView from another Activity How to simulate [mouseup ()] element by element using get ()

score 2 · Accepted Answer

You can achieve this result by changing the cursor's row_factory attribute.

This attribute should receive a callable, which accepts as arguments the cursor and rows, as in this example adapted from 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 cites that in cases where performance is required 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 rows.

Demonstration of using sqlite3.Row 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