How can I get dict from sqlite query?

The custom dictionary path with dict_factory

In some parts of the coding multiverse, Row objects can feel a little odd. What if we just want a plain dictionary instead? The row_factory can lead you down this path too if you hand it a dict_factory function. This function treats the column names and their corresponding values as best friends forever, mapping them together into simple dictionaries.

def dict_factory(cursor, row):
    d = {}  # Meet d, the humble dictionary!
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]  # Let the mapping magic happen!
    return d

# Teach the connection about our magnificent dict_factory
conn.row_factory = dict_factory

# Execute, fetch, print: The holy trinity!
dicts = conn.cursor().execute("SELECT * FROM table").fetchall()
for record in dicts:
    print(record) # Voilà, you've met dictionary's sophisticated cousin!


Understanding Index-based vs. Name-based column access

Just like asking for coffee by simply pointing at it (index-based) or saying "I'll have a flat white, please" (name-based), accessing column data can also be a point-and-get or name-and-get showdown.

# Point-and-get (a.k.a index-based) - Just point at it, bro!
value = row[0]

# Name-and-get (a.k.a name-based) - Politely ask for it by its name!
value = row['column_name']


With sqlite3.Row, you get to do both, and enjoy a case-insensitive luxury ride to your column data.

Short-term memory: In-Memory Database Connections

Every tester or newbie developer's favorite dream – an in-memory database. With :memory:, you don't need to run to the disk every time you need some SQL action. Lightning-fast development and testing at your fingertips!

conn = sqlite3.connect(":memory:")  # Remember, it's all in your head, Harry!
conn.row_factory = sqlite3.Row

# Create table, insert data. All in a day's work!
conn.cursor().execute('CREATE TABLE fantasy (id integer primary key, beast text)')
conn.cursor().execute("INSERT INTO fantasy (beast) VALUES ('dragon')")
# Fetch as dict, print and behold the marvel!
row_dict = dict(conn.cursor().execute("SELECT * FROM fantasy").fetchone())
print(row_dict)


Manual dictionary creation from query results

On the off chance that you feel the need to manually craft a dictionary from your query results, Python's got your back! With the zip() function, pair those column names and values into a super-fast dictionary!

cursor = conn.cursor()
cursor.execute("SELECT * FROM table")
columns = [column[0] for column in cursor.description]
values = cursor.fetchone()
row_dict = dict(zip(columns, values))  # The power of ZIP!

# Print. REPEAT: Print the manually crafted dict
print(row_dict)


Weighing sqlite3.Row and Dictionaries

sqlite3.Row and Dictionaries each have their strengths and weaknesses. Choose with wisdom:

• sqlite3.Row:

  • Pros: Lightweight, swift, grants you access by column name or index.
  • Cons: Not a generic dict type, sticks only to sqlite3.

• Dictionaries:

  • Pros: Universal data type, easily serialized and coupled with JSON and other data formats.
  • Cons: If manually crafted, might take more time and memory.

Skipping manual iteration with row_factory

By setting row_factory just right, say goodbye to manual iterations over every row to construct dictionaries. The sqlite3.Row object or a custom dict_factory hands you the immediate dictionary representation of your faithful data.