getting column names from Python DBAPI query results 2025-01-05

PEP-249 - Python Database API Specification (DBAPI) defines a standard interface for database access in Python that’s implemented by libraries such as psycopg and PyMySQL.

One common task with DBAPI libraries I see new users bounce off is identifying column names in query results, since fetching from a Cursor only returns a sequence of list-like rows. For instance, in the following query,

# assume a 'users' table like this one:
# user_id | created_at
# --------+--------------------
#       1 | 2025-01-02 11:30:00
#       2 | 2025-01-03 10:00:00
cursor = connection.cursor()
cursor.execute("SELECT user_id, created_at FROM users")
result = cursor.fetchall()

result is

[
    [1, datetime.datetime(2025, 1, 2, 11, 30, 0)]
    [2, datetime.datetime(2025, 1, 3, 10, 0, 0)]
] # (1)

but often the code downstream would prefer to handle a data structure where the column names are explicitely identified, such as:

[
    {"user_id": 1, "created_at": datetime.datetime(2025, 1, 2, 11, 30, 0)},
    {"user_id": 2, "created_at": datetime.datetime(2025, 1, 3, 10, 0, 0)},
] # (2)

In a simple case like this one, going from (1) to (2) using hardcoded column names is not too cumbersome:

rows = [{"user_id": r[0], "created_at": r[1]} for r in result]

However, this quickly becomes impractical when our query includes dozens of columns or even a SELECT *¹.

Thankfully, there is an easy way of retrieving column names automatically that does not involve reaching for SQLAlchemy²

Cursors have a description attribute, which is a sequence of column desciptions for the result of the last query executed. The first element of each column description is the column’s name, so we can get all the column names like this:

column_names = [c[0] for c in cursor.description]

Putting everything together, given a connection and a query, we can run the query and unpack its result into the same format as (2):

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        column_names = [c[0] for c in cursor.description]
    finally:
        cursor.close()
    return [
        {name: value for name, value in zip(column_names, row)}
        for row in result
    ]