Describing an unknown SQLite database

Recently we were given SQlite database and tasked with writing queries to extract various insights. Problem was, we weren’t given any information about the database tables.

While talking with a fellow student about how to figure out what was inside the database, I realized this function I wrote that reconstructs the layout of an unknown SQLite table might be useful.

def describe_table(cursor, table_name):
    """
    Returns description of SQLite table as CREATE TABLE statement.
    """
    cursor.execute('PRAGMA table_info("{}");'.format(table_name))
    table_info = cursor.fetchall()

    indent = ' '*4
    primary_keys = []
    name_width = 2 + max([len(col_info[1]) for col_info in table_info])
    fmt = '{:' + str(name_width) + 's}{:10s}{} DEFAULT {}\n'

    desc = 'CREATE TABLE "{}" (\n'.format(table_name)
    for col_info in table_info:
        _, name, type_, allow_null, default, primary = col_info
        desc += indent + fmt.format(
            name, type_, '' if allow_null else ' NOT NULL', default
        )
        if primary:
            primary_keys.append(name)

    if primary_keys:
        desc += indent + 'PRIMARY KEY ({})\n'.format(', '.join(primary_keys))
    desc += ');\n'
    return desc

You pass the function a sqlite3 cursor and table name, and it returns a CREATE TABLE string describing the table.

SQLite stores the table names in its sqlite_master table. This snippet describes all the table in a file:

cnx.execute('SELECT name FROM sqlite_master WHERE type="table"')
for name in cnx.fetchall()[1:]:
    print(describe_table(cnx, name[0]))

Where cnx is the sqlite3 cursor. E.g.,

import sqlite3
cnx = sqlite3.connect('database.sqlite').cursor()

Written on February 22, 2017