How Can I Retrieve Dictionaries Instead of Lists from SQLite Queries in Python?

Retrieving Dictionaries from SQLite Queries

In Python, retrieving data from an SQLite database typically results in lists representing table rows. While this is convenient for accessing data, it may be desirable to instead obtain dictionaries for more structured and key-based access.

One method to achieve this is through the row_factory attribute. By setting it to a custom function, you can specify how rows should be processed from a result set. For example:

import sqlite3

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

con = sqlite3.connect("test.sqlite")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select * from table")
rows = cur.fetchall()

print(rows[0]["col1"])

Another solution, as suggested in the SQLite documentation, is to use the optimized Row type as the row_factory:

con.row_factory = sqlite3.Row

cursor = con.cursor()
row = cursor.fetchone()

print(row["col1"])

This Row type provides both index-based and case-insensitive name-based access to columns with minimal memory overhead, making it a more efficient solution than custom dictionary-based approaches.

The above is the detailed content of How Can I Retrieve Dictionaries Instead of Lists from SQLite Queries in Python?. For more information, please follow other related articles on the PHP Chinese website!