[Python] Accessing SQLite3 Data by Column Name Using sqlite3.Row

By default, Python’s sqlite3 module returns data as tuples. However, by changing a specific setting, you can access values by specifying their “column names,” just like a dictionary.

This eliminates the need to worry about the order of columns in your SQL SELECT statements and significantly improves code readability.

Here, we will explain the specific implementation method using sqlite3.Row to retrieve data by column name.

Executable Sample Code

The following code is a complete script that creates a library catalog database and retrieves data by specifying column names (such as title or author).

import sqlite3
import os

# Database file name
DB_FILE = "library_catalog.db"

def setup_database():
    """Create test database/table and insert data"""
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)

    with sqlite3.connect(DB_FILE) as conn:
        cursor = conn.cursor()
        
        # Create table
        cursor.execute("""
            CREATE TABLE books (
                book_id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                author TEXT NOT NULL,
                published_year INTEGER
            )
        """)
        
        # Insert data
        sample_books = [
            ('The Great Gatsby', 'F. Scott Fitzgerald', 1925),
            ('1984', 'George Orwell', 1949),
            ('To Kill a Mockingbird', 'Harper Lee', 1960),
            ('Brave New World', 'Aldous Huxley', 1932)
        ]
        cursor.executemany(
            "INSERT INTO books (title, author, published_year) VALUES (?, ?, ?)",
            sample_books
        )
        conn.commit()

def main():
    # Prepare database
    setup_database()

    print("=== Data Access by Column Name ===")

    with sqlite3.connect(DB_FILE) as conn:
        # Important setting: Set row_factory to sqlite3.Row
        conn.row_factory = sqlite3.Row
        
        cursor = conn.cursor()

        # Pattern 1: Cursor Loop Access
        print("\n--- 1. Cursor Loop Access ---")
        cursor.execute("SELECT * FROM books")
        
        for row in cursor:
            # Access by column name
            print(f"ID: {row['book_id']} | Title: {row['title']}")

        # Pattern 2: fetchall() Access
        print("\n--- 2. fetchall() Access ---")
        cursor.execute("SELECT * FROM books WHERE published_year > 1940")
        rows = cursor.fetchall()
        
        for row in rows:
            # Acts like a dictionary, but is actually an sqlite3.Row object
            print(f"Title: {row['title']}, Author: {row['author']}")

        # Pattern 3: fetchone() Access
        print("\n--- 3. fetchone() Access ---")
        cursor.execute("SELECT * FROM books ORDER BY published_year DESC")
        
        # Get first record
        first_book = cursor.fetchone()
        if first_book:
            print(f"Latest Book: {first_book['title']} ({first_book['published_year']})")
        
        # Get second record
        second_book = cursor.fetchone()
        if second_book:
            print(f"Next Book: {second_book['title']} ({second_book['published_year']})")

if __name__ == "__main__":
    main()

Explanation: How sqlite3.Row Works

conn.row_factory = sqlite3.Row

Normally, sqlite3 returns results as tuples, like (1, 'The Great Gatsby', 'F. Scott Fitzgerald'). However, by setting the row_factory attribute of the connection object to the sqlite3.Row class, the returned objects change to highly functional Row objects.

Features of the Row Object

This object has hybrid characteristics, allowing you to access data in both of the following ways:

• Index Number: row[0] (Same as a traditional tuple)
• Column Name: row['title'] (Dictionary-like access)

Converting to Dictionary (dict)

The sqlite3.Row object cannot be JSON serialized as is. If you want to handle it as a pure dictionary type, convert it as follows:

book_dict = dict(row)
print(book_dict)
# Output Example: {'book_id': 1, 'title': 'The Great Gatsby', ...}