Python sqlite3.Row.keys Method

Last modified April 15, 2025

This comprehensive guide explores Python's sqlite3.Row.keys method, which provides access to column names in query results. We'll cover basic usage, practical examples, and integration with database operations.

Basic Definitions

The sqlite3.Row.keys method returns a list of column names from a query result row. It's available when using sqlite3.Row as the row factory for a connection.

Key characteristics: it provides ordered column names matching the query, enables named access to row data, and works with both simple and complex queries. The method is essential for dynamic SQL handling.

Basic Row.keys Usage

Here's the simplest usage of sqlite3.Row.keys to get column names from a query result.

import sqlite3

with sqlite3.connect(':memory:') as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute('''CREATE TABLE users 
                     (id INTEGER PRIMARY KEY, name TEXT, email TEXT)''')
    cursor.execute("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')")
    
    cursor.execute("SELECT * FROM users")
    row = cursor.fetchone()
    
    print(row.keys())  # Output: ['id', 'name', 'email']

This example creates an in-memory database with a simple table. After setting sqlite3.Row as the row factory, we fetch one row and print its column names.

The output shows the column names in the order they were defined in the table, demonstrating how keys provides metadata about the result structure.

Using Keys with Dynamic Queries

The keys method is particularly useful when working with dynamic queries where column names aren't known in advance.

import sqlite3

def print_query_results(conn, query):
    cursor = conn.cursor()
    cursor.execute(query)
    
    for row in cursor:
        print("Columns:", row.keys())
        for col in row.keys():
            print(f"{col}: {row[col]}")
        print()

with sqlite3.connect(':memory:') as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute('''CREATE TABLE products
                     (id INTEGER PRIMARY KEY, name TEXT, price REAL)''')
    cursor.executemany("INSERT INTO products (name, price) VALUES (?, ?)",
                      [('Laptop', 999.99), ('Phone', 699.99)])
    
    print_query_results(conn, "SELECT * FROM products")
    print_query_results(conn, "SELECT name, price FROM products WHERE price > 700")

This example demonstrates a reusable function that prints query results along with their column names. The keys method allows the function to work with any query without knowing its structure beforehand.

The output shows different column sets for different queries, proving the flexibility of this approach when dealing with dynamic SQL.

Building Dictionaries from Rows

We can combine keys with dictionary comprehension to convert rows into dictionaries with column names as keys.

import sqlite3

with sqlite3.connect(':memory:') as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute('''CREATE TABLE employees
                     (id INTEGER, name TEXT, department TEXT, salary REAL)''')
    cursor.execute('''INSERT INTO employees VALUES
                     (1, 'John Smith', 'Engineering', 85000)''')
    
    cursor.execute("SELECT * FROM employees")
    row = cursor.fetchone()
    
    # Convert row to dictionary
    row_dict = {key: row[key] for key in row.keys()}
    print(row_dict)
    
    # Output: {'id': 1, 'name': 'John Smith', 'department': 'Engineering', 'salary': 85000.0}

This example shows how to transform a sqlite3.Row object into a regular dictionary. The keys method provides the dictionary keys.

This technique is useful when you need to serialize database rows or pass them to functions that expect dictionaries rather than row objects.

Validating Column Names

The keys method can help validate that a query returned the expected columns before processing the data.

import sqlite3

def validate_columns(row, expected_columns):
    actual_columns = row.keys()
    if set(expected_columns) != set(actual_columns):
        raise ValueError(f"Expected columns {expected_columns}, got {actual_columns}")

with sqlite3.connect(':memory:') as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute('''CREATE TABLE inventory
                     (item_id INTEGER, name TEXT, quantity INTEGER)''')
    cursor.execute('''INSERT INTO inventory VALUES
                     (101, 'Widget', 50)''')
    
    cursor.execute("SELECT item_id, name, quantity FROM inventory")
    row = cursor.fetchone()
    
    try:
        validate_columns(row, ['item_id', 'name', 'quantity'])
        print("Column validation passed")
    except ValueError as e:
        print("Column validation failed:", e)

This example demonstrates a validation function that checks if a row contains the expected columns. The keys method provides the actual column names for comparison.

Such validation is particularly valuable in applications where query results must conform to specific schemas or when working with dynamically generated SQL.

Handling Aliased Columns

The keys method reflects column aliases used in SQL queries, showing exactly what names are available in the result set.

import sqlite3

with sqlite3.connect(':memory:') as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute('''CREATE TABLE orders
                     (order_id INTEGER, customer TEXT, total REAL)''')
    cursor.execute('''INSERT INTO orders VALUES
                     (1001, 'Acme Corp', 1250.75)''')
    
    # Query with column aliases
    cursor.execute('''SELECT 
                     order_id AS id, 
                     customer AS client, 
                     total AS amount 
                     FROM orders''')
    row = cursor.fetchone()
    
    print("Column names:", row.keys())
    # Output: Column names: ['id', 'client', 'amount']
    
    # Access using aliases
    print(f"Order {row['id']} for {row['client']}: ${row['amount']}")

This example shows how column aliases in SQL queries affect the names returned by keys. The method provides the aliased names, not the original column names from the table.

This behavior is important to understand when working with complex queries that rename columns for clarity or compatibility with application code.

Working with Joined Tables

When querying multiple tables with joins, keys shows all available columns, including duplicate names from different tables.

import sqlite3

with sqlite3.connect(':memory:') as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # Create two related tables
    cursor.execute('''CREATE TABLE departments
                     (id INTEGER, name TEXT)''')
    cursor.execute('''CREATE TABLE employees
                     (id INTEGER, name TEXT, dept_id INTEGER)''')
    
    # Add sample data
    cursor.execute("INSERT INTO departments VALUES (1, 'Engineering')")
    cursor.execute("INSERT INTO employees VALUES (101, 'Alice', 1)")
    
    # Query with join
    cursor.execute('''SELECT e.id AS emp_id, e.name AS emp_name,
                             d.id AS dept_id, d.name AS dept_name
                      FROM employees e
                      JOIN departments d ON e.dept_id = d.id''')
    
    row = cursor.fetchone()
    print("Column names:", row.keys())
    # Output: Column names: ['emp_id', 'emp_name', 'dept_id', 'dept_name']
    
    # Access specific columns
    print(f"{row['emp_name']} works in {row['dept_name']}")

This example demonstrates how keys handles column names from joined tables. We use explicit aliases to avoid ambiguity in the result set.

The method shows all available columns after the join, with names reflecting our aliases. This makes it clear which columns come from which tables.

Advanced: Dynamic Data Processing

For maximum flexibility, we can use keys to process query results without any prior knowledge of the table structure.

import sqlite3
from tabulate import tabulate

def execute_and_display(conn, query):
    cursor = conn.cursor()
    cursor.execute(query)
    
    if cursor.description is None:
        print("No results to display")
        return
    
    # Get all rows
    rows = cursor.fetchall()
    if not rows:
        print("No rows returned")
        return
    
    # Display as table
    headers = rows[0].keys()
    data = [[row[col] for col in headers] for row in rows]
    print(tabulate(data, headers=headers, tablefmt="grid"))

with sqlite3.connect(':memory:') as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # Create sample data
    cursor.execute('''CREATE TABLE sales
                     (region TEXT, product TEXT, amount REAL, quarter INTEGER)''')
    cursor.executemany("INSERT INTO sales VALUES (?, ?, ?, ?)",
                      [('West', 'A', 1000, 1),
                       ('East', 'B', 1500, 1),
                       ('North', 'A', 800, 2)])
    
    # Display different queries
    execute_and_display(conn, "SELECT * FROM sales")
    execute_and_display(conn, "SELECT region, SUM(amount) AS total FROM sales GROUP BY region")

This advanced example shows a generic function that can execute any query and display the results in a formatted table. The keys method provides the column headers automatically.

The function handles different query types (simple selects, aggregates) without modification, demonstrating the power of dynamic column name access.

Best Practices

• Use Row factory: Always set row_factory=sqlite3.Row for named access
• Prefer named access: Use row['column'] instead of positional access
• Handle case sensitivity: Column names in keys preserve case
• Cache column lists: Store keys results if reused frequently
• Combine with description: Use cursor.description for more metadata

Source References

• Python sqlite3 Documentation
• SQLite Column Names API
• Python DB API 2.0 Specification

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Python tutorials.