Query Metadata

Read SQLite column names from cursor.description after SELECT, then print each row under those names without hard-coding the header.

Example

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute(
    "CREATE TABLE badge (code INTEGER PRIMARY KEY, name TEXT NOT NULL, room TEXT NOT NULL)"
)
cur.execute("INSERT INTO badge (code, name, room) VALUES (?, ?, ?)", (1, "Avery", "North"))
cur.execute("INSERT INTO badge (code, name, room) VALUES (?, ?, ?)", (2, "Jordan", "East"))
con.commit()
cur.execute("SELECT code, name, room FROM badge ORDER BY code")
for m in cur.description:
    print(m[0])
for code, name, room in cur.fetchall():
    print(code, name, room)
con.close()

The first lines are code, name, and room. The rows are 1 Avery North and 2 Jordan East. cursor.description is data about the result: one entry for each selected column. The first item of each entry is the column name. It is available after execute, including when the result has no rows. The rest of the entry includes the declared type and other fields this lesson does not print. Read the names from description so the header follows the SELECT list. If this browser has no sqlite3, the playground says so and still prints the names and rows.

Key takeaways

• Metadata is data about the result, such as the column names
• cursor.description is filled by execute
• The first item of each entry is the column name
• Print those names, then print the rows from fetchall()
• Close the connection when the result has been read
• Next: Practice List — revision tasks, a question bank, and interview tips
• Previous: Select Queries