Introduction

SQLite is lightweight, but you still need reliable ways to inspect and export your database. With Python sqlite3, you can list tables, inspect schema details, and generate a full SQL dump using only standard library code. This article walks through each task with practical patterns that work in scripts and automation jobs.

Connect and List User Tables

Most metadata in SQLite comes from the internal table named sqlite_master. You can query it to list real application tables while excluding engine internals.

1import sqlite3
2from pathlib import Path
3
4DB_PATH = Path("app.db")
5
6with sqlite3.connect(DB_PATH) as conn:
7    conn.row_factory = sqlite3.Row
8    sql = """
9    SELECT name
10    FROM sqlite_master
11    WHERE type = 'table'
12      AND name NOT LIKE 'sqlite_%'
13    ORDER BY name
14    """
15    table_names = [row["name"] for row in conn.execute(sql)]
16
17print("Tables:")
18for name in table_names:
19    print(f"- {name}")

This query is stable across SQLite versions and avoids assumptions about your schema naming conventions.

Retrieve Table Schema and Column Metadata

You can fetch the original CREATE TABLE statements and also inspect per-column properties.

1import sqlite3
2
3
4def print_schema(conn: sqlite3.Connection, table_name: str) -> None:
5    create_sql = conn.execute(
6        "SELECT sql FROM sqlite_master WHERE type='table' AND name=?",
7        (table_name,),
8    ).fetchone()
9
10    print(f"\nSchema for {table_name}:")
11    print(create_sql[0] if create_sql else "No schema found")
12
13    print("Columns:")
14    for col in conn.execute(f"PRAGMA table_info({table_name})"):
15        cid, name, col_type, notnull, default_value, is_pk = col
16        print(
17            f"  {cid}: {name} {col_type} "
18            f"not_null={bool(notnull)} default={default_value} pk={bool(is_pk)}"
19        )
20
21
22with sqlite3.connect("app.db") as conn:
23    for table in ["users", "orders"]:
24        print_schema(conn, table)

Using both sqlite_master and PRAGMA table_info gives a complete picture: the raw definition plus normalized column metadata.

Create a Database Dump Programmatically

For backups, migrations, or test fixtures, Connection.iterdump() is the simplest method. It emits SQL statements that recreate schema and data.

1import sqlite3
2from pathlib import Path
3
4source = Path("app.db")
5dump_file = Path("app_dump.sql")
6
7with sqlite3.connect(source) as conn, dump_file.open("w", encoding="utf-8") as out:
8    for line in conn.iterdump():
9        out.write(f"{line}\n")
10
11print(f"Dump written to {dump_file.resolve()}")

If you want a fast binary backup instead of SQL text, use the backup API.

1import sqlite3
2
3with sqlite3.connect("app.db") as src, sqlite3.connect("backup.db") as dst:
4    src.backup(dst)
5
6print("Binary backup completed")

The SQL dump is portable and human-readable. The binary copy is faster and preserves page-level details.

Build a Reusable Inspection Utility

If you inspect many database files, put the metadata logic in one reusable function. That keeps maintenance scripts small and consistent across environments.

1import sqlite3
2from pathlib import Path
3
4
5def inspect_database(path: str) -> None:
6    db_path = Path(path)
7    with sqlite3.connect(db_path) as conn:
8        tables = conn.execute(
9            """
10            SELECT name
11            FROM sqlite_master
12            WHERE type='table' AND name NOT LIKE 'sqlite_%'
13            ORDER BY name
14            """
15        ).fetchall()
16
17        print(f"Database: {db_path}")
18        for (name,) in tables:
19            row_count = conn.execute(f"SELECT COUNT(*) FROM {name}").fetchone()[0]
20            print(f"- {name}: {row_count} rows")
21
22
23inspect_database("app.db")

This pattern is useful in CI jobs where you need lightweight verification after migrations.

Common Pitfalls

One common mistake is querying only sqlite_master and assuming that gives all column details. It does not. Use PRAGMA table_info when you need nullability, defaults, or primary key position.

Another issue is building SQL with string concatenation for user input. Even metadata queries should use parameters whenever possible to avoid injection risks.

Large dumps can also fail silently if disk space is low or writes are interrupted. Always check that the dump file exists and has reasonable size, then consider running a restore test in a temporary database.

Teams also forget transaction behavior. If another process writes during your export, your snapshot may be inconsistent. For critical backups, run from a controlled maintenance window or use connection settings that guarantee a stable read view.

Summary

• Query sqlite_master to list tables and get CREATE TABLE statements.
• Use PRAGMA table_info for structured column metadata.
• Use iterdump() for a portable SQL export of schema and data.
• Use backup() for fast binary copies when portability is less important.
• Validate backup artifacts and avoid unsafe SQL construction.