Metainformation: sqlite_master

SQLite has a special table called sqlite_master that holds information about the "real" tables in the database. You can query this table to get information about the schema.

Example:

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Create a couple of tables
c.execute('''CREATE TABLE Customers (id, name, email, credit_limit)''')
c.execute('''CREATE TABLE Orders (order_id, customer_id, order_date, amount)''')

# Query sqlite_master to see the tables
for row in c.execute("SELECT * FROM sqlite_master WHERE type='table'"):
    print(row)

conn.close()

This will output information about the Customers and Orders tables, including the SQL used to create them.

Retrieving Column Names

The description attribute of the cursor object contains the column names of the last query.

c.execute('SELECT * from Customers')
names = [description[0] for description in c.description]
print(names)
# Output: ['id', 'name', 'email', 'credit_limit']

This is especially useful in tandem with the sqlite_master table when exploring a new database.