sqlite: list non-standard pragmas in a database file

sqlite-list-non-standard-pragmas.py

#!/usr/bin/env python3
import sys
import sqlite3

# Function to get the list of PRAGMA settings
def get_pragma_list(connection):
    cur = connection.cursor()
    cur.execute('PRAGMA pragma_list;')
    return [row[0] for row in cur.fetchall()]

# Function to get the PRAGMA values of a database
def get_pragma_values(connection, pragmas):
    pragma_values = {}
    cur = connection.cursor()
    for pragma in pragmas:
        # Query the current value of the PRAGMA setting
        cur.execute(f'PRAGMA {pragma};')
        row = cur.fetchone()
        # Check if the result is not None before accessing its elements
        if row is not None:
            pragma_values[pragma] = row[0]
        else:
            pragma_values[pragma] = None
    return pragma_values

if len(sys.argv) != 2:
    print("Usage: {} database.sqlite", file=sys.stderr)
    sys.exit(1)

# Connect to an in-memory temporary database
temp_conn = sqlite3.connect(':memory:')

# Get the list of PRAGMA settings from the temporary database
pragmas = get_pragma_list(temp_conn)

# Connect to the SQLite database
conn = sqlite3.connect(sys.argv[1])

# Get the PRAGMA values of the target database
target_pragma_values = get_pragma_values(conn, pragmas)
# Get the PRAGMA values of the temporary database (defaults)
default_pragma_values = get_pragma_values(temp_conn, pragmas)

# Dictionary to store the non-default PRAGMAS
non_standard_pragmas = {}

# Compare the values
for pragma in pragmas:
    if str(target_pragma_values[pragma]).lower() != str(default_pragma_values[pragma]).lower():
        non_standard_pragmas[pragma] = target_pragma_values[pragma]

# Close the connections
conn.close()
temp_conn.close()

# Output the non-standard PRAGMA settings
for pragma, value in non_standard_pragmas.items():
    print(f'{pragma} = {value}')


# vim:set expandtab tabstop=4 shiftwidth=4 softtabstop=4 nowrap: