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

Retrieve Dictionaries from SQLite Queries

Retrieving data from a SQLite database typically results in a list of lists, where each inner list corresponds to a row in the database. While this method is functional, it can be inconvenient for accessing data by column name. This article explores alternative approaches to obtain dictionaries from SQLite queries, providing a more intuitive way to work with data.

Row Factory

The row_factory function allows you to specify how the result rows are returned. By setting it to dict_factory, you can convert query results into dictionaries, where keys are column names and values are column values. Here's an 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(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print(cur.fetchone()["a"])

SQLite3.Row

Another option is to use the sqlite3.Row type as the row factory. It offers both index-based and case-insensitive name-based access to columns with minimal memory overhead:

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

With sqlite3.Row, you can access column values either by index or by name:

row = cursor.fetchone()
value_by_index = row[0]
value_by_name = row["col_name"]

Conclusion

Both the row_factory function and the sqlite3.Row type provide effective ways to retrieve dictionaries from SQLite queries, making it easier to work with data by column names. The choice between the two depends on specific requirements and preferences.

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