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 t_student (id, name, field, birth_year)''')
c.execute('''CREATE TABLE t_thesis (thesis_id, phd_title, phd_year)''')

# 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 t_student and t_thesis 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 t_student')
names = [description[0] for description in c.description]
print(names)
# Output: ['id', 'name', 'field', 'birth_year']

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