How can I get dict from sqlite query?

When working with SQLite for data operations, if you want to retrieve dictionary (dict) data from query results, you can achieve this using Python's standard library sqlite3. By default, sqlite3 returns data as tuples, but you can change this behavior by defining a custom row factory function.

The following provides a detailed step-by-step guide along with example code to demonstrate how to achieve this:

Step 1: Connect to the Database

First, establish a connection to the SQLite database. If the database file does not exist, sqlite3 will automatically create it.

python
import sqlite3

# Connect to the SQLite database
# The database file is mydatabase.db; if it does not exist, it will be automatically created in the current directory.
conn = sqlite3.connect('mydatabase.db')

Step 2: Set the Row Factory

To retrieve dictionary-form data from query results, set the row_factory attribute of the connection object to use sqlite3.Row. This factory enables accessing data by column name.

python
conn.row_factory = sqlite3.Row

Step 3: Create a Cursor Object

Execute SQL queries using a cursor.

python
cursor = conn.cursor()

Step 4: Execute the Query

Run an SQL query to fetch data from a table.

python
cursor.execute("SELECT id, name, email FROM users")

Step 5: Process the Query Results

Retrieve all rows from the cursor; each row will be a dictionary instantiated by sqlite3.Row.

python
rows = cursor.fetchall()

# Convert each sqlite3.Row object into a dictionary
users = [dict(row) for row in rows]

# Print each user's information
for user in users:
    print(user)

Complete Example Code

Combine the above steps into a single script:

python
import sqlite3

def fetch_data_as_dict():
    conn = sqlite3.connect('mydatabase.db')
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT id, name, email FROM users")
    rows = cursor.fetchall()
    
    users = [dict(row) for row in rows]
    
    for user in users:
        print(user)

    conn.close()

fetch_data_as_dict()

In this example, if the users table exists and contains the id, name, and email fields, the script will print each user's information in dictionary format.

The advantage of this method is that it allows direct access to data by field name, improving code readability and maintainability.