How to Efficiently Retrieve Dictionaries from SQLite Queries?

Retrieving Dictionaries from SQLite Queries

In your provided code snippet, you are currently iterating over the raw tuples returned from the SQLite query:

db = sqlite.connect("test.sqlite")
res = db.execute("select * from table")

This returns a list of tuples for each row in the table, making it necessary to manually extract the column names and construct dictionaries. However, SQLite provides two methods for obtaining dictionaries directly from queries:

1. row_factory

The row_factory option allows you to specify a custom function that is used to create an object from each row. For example, the following function returns a dictionary:

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

By setting the row_factory option to this function, each row returned from the query will be a dictionary:

con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
result = cur.fetchone()  # Returns {'a': 1}

2. sqlite3.Row

Alternatively, you can set row_factory to sqlite3.Row, which provides both index-based and name-based access to columns without any additional memory overhead:

con = sqlite3.connect(...)
con.row_factory = sqlite3.Row
cursor = con.cursor()
result = cursor.fetchone()  # Returns Row object with attribute-like access

By using either of these methods, you can easily obtain dictionaries or attribute-accessible objects from SQLite queries, eliminating the need for manual dictionary construction.

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