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

Obtaining Dictionaries from SQLite Queries

Retrieving data from SQLite databases involves obtaining lists corresponding to rows. While it is possible to determine the column names using the description attribute, converting the results into dictionaries requires additional effort.

Solution Using row_factory

The row_factory parameter allows for the customization of how rows are returned. By setting it to a function that takes a cursor and a row as arguments and returns a dictionary, you can obtain dictionaries instead of lists.

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(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print(cur.fetchone()["a"])

Solution Using sqlite3.Row

The SQLite documentation recommends using the sqlite3.Row type, which provides efficient name-based access to columns. By setting row_factory to sqlite3.Row, you can both index and access columns by name.

con = sqlite3.connect(...)
con.row_factory = sqlite3.Row   # add this row
cursor = con.cursor()

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