sqliteschema

Summary

sqliteschema is a Python library to dump table schema of a SQLite database file.

Installation

Install from PyPI

pip install sqliteschema

Install optional dependencies

pip install sqliteschema[cli]  # to use CLI
pip install sqliteschema[dumps]  # to use dumps method
pip install sqliteschema[logging]  # to use logging

Install from PPA (for Ubuntu)

sudo add-apt-repository ppa:thombashi/ppa
sudo apt update
sudo apt install python3-sqliteschema

Usage

Full example source code can be found at examples/get_table_schema.py

Extract SQLite Schemas as dict

Sample Code:

import json
import sqliteschema

extractor = sqliteschema.SQLiteSchemaExtractor(sqlite_db_path)

print(
    "--- dump all of the table schemas into a dictionary ---\n{}\n".format(
        json.dumps(extractor.fetch_database_schema_as_dict(), indent=4)
    )
)

print(
    "--- dump a specific table schema into a dictionary ---\n{}\n".format(
        json.dumps(extractor.fetch_table_schema("sampletable1").as_dict(), indent=4)
    )
)

Output:

--- dump all of the table schemas into a dictionary ---
{
    "sampletable0": [
        {
            "Field": "attr_a",
            "Index": false,
            "Type": "INTEGER",
            "Nullable": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        },
        {
            "Field": "attr_b",
            "Index": false,
            "Type": "INTEGER",
            "Nullable": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        }
    ],
    "sampletable1": [
        {
            "Field": "foo",
            "Index": true,
            "Type": "INTEGER",
            "Nullable": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        },
        {
            "Field": "bar",
            "Index": false,
            "Type": "REAL",
            "Nullable": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        },
        {
            "Field": "hoge",
            "Index": true,
            "Type": "TEXT",
            "Nullable": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        }
    ],
    "constraints": [
        {
            "Field": "primarykey_id",
            "Index": true,
            "Type": "INTEGER",
            "Nullable": "YES",
            "Key": "PRI",
            "Default": "NULL",
            "Extra": ""
        },
        {
            "Field": "notnull_value",
            "Index": false,
            "Type": "REAL",
            "Nullable": "NO",
            "Key": "",
            "Default": "",
            "Extra": ""
        },
        {
            "Field": "unique_value",
            "Index": true,
            "Type": "INTEGER",
            "Nullable": "YES",
            "Key": "UNI",
            "Default": "NULL",
            "Extra": ""
        }
    ]
}

--- dump a specific table schema into a dictionary ---
{
    "sampletable1": [
        {
            "Field": "foo",
            "Index": true,
            "Type": "INTEGER",
            "Nullable": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        },
        {
            "Field": "bar",
            "Index": false,
            "Type": "REAL",
            "Nullable": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        },
        {
            "Field": "hoge",
            "Index": true,
            "Type": "TEXT",
            "Nullable": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        }
    ]
}

Extract SQLite Schemas as Tabular Text

Table schemas can be output with the dumps method. The dumps method requires an additional package that can be installed as follows:

pip install sqliteschema[dumps]

Usage is as follows:

Sample Code:

import sqliteschema

extractor = sqliteschema.SQLiteSchemaExtractor(sqlite_db_path)

for verbosity_level in range(2):
    print("--- dump all of the table schemas with a tabular format: verbosity_level={} ---".format(
        verbosity_level))
    print(extractor.dumps(output_format="markdown", verbosity_level=verbosity_level))

for verbosity_level in range(2):
    print("--- dump a specific table schema with a tabular format: verbosity_level={} ---".format(
        verbosity_level))
    print(extractor.fetch_table_schema("sampletable1").dumps(
        output_format="markdown", verbosity_level=verbosity_level))

Output:

--- dump all of the table schemas with a tabular format: verbosity_level=0 ---
# sampletable0
| Field  |  Type   |
| ------ | ------- |
| attr_a | INTEGER |
| attr_b | INTEGER |

# sampletable1
| Field |  Type   |
| ----- | ------- |
| foo   | INTEGER |
| bar   | REAL    |
| hoge  | TEXT    |

# constraints
|     Field     |  Type   |
| ------------- | ------- |
| primarykey_id | INTEGER |
| notnull_value | REAL    |
| unique_value  | INTEGER |

--- dump all of the table schemas with a tabular format: verbosity_level=1 ---
# sampletable0
| Field  |  Type   | Nullable | Key | Default | Index | Extra |
| ------ | ------- | -------- | --- | ------- | :---: | ----- |
| attr_a | INTEGER | YES      |     | NULL    |       |       |
| attr_b | INTEGER | YES      |     | NULL    |       |       |

# sampletable1
| Field |  Type   | Nullable | Key | Default | Index | Extra |
| ----- | ------- | -------- | --- | ------- | :---: | ----- |
| foo   | INTEGER | YES      |     | NULL    |   X   |       |
| bar   | REAL    | YES      |     | NULL    |       |       |
| hoge  | TEXT    | YES      |     | NULL    |   X   |       |

# constraints
|     Field     |  Type   | Nullable | Key | Default | Index | Extra |
| ------------- | ------- | -------- | --- | ------- | :---: | ----- |
| primarykey_id | INTEGER | YES      | PRI | NULL    |   X   |       |
| notnull_value | REAL    | NO       |     |         |       |       |
| unique_value  | INTEGER | YES      | UNI | NULL    |   X   |       |

--- dump a specific table schema with a tabular format: verbosity_level=0 ---
# sampletable1
| Field |  Type   |
| ----- | ------- |
| foo   | INTEGER |
| bar   | REAL    |
| hoge  | TEXT    |

--- dump a specific table schema with a tabular format: verbosity_level=1 ---
# sampletable1
| Field |  Type   | Nullable | Key | Default | Index | Extra |
| ----- | ------- | -------- | --- | ------- | :---: | ----- |
| foo   | INTEGER | YES      |     | NULL    |   X   |       |
| bar   | REAL    | YES      |     | NULL    |       |       |
| hoge  | TEXT    | YES      |     | NULL    |   X   |       |

CLI Usage

Sample Code:

pip install --upgrade sqliteschema[cli]
python3 -m sqliteschema <PATH/TO/SQLITE_FILE>

Dependencies

Python 3.9+
Python package dependencies (automatically installed)

Optional dependencies

loguru
- Used for logging if the package installed
pytablewriter
- Required when getting table schemas with tabular text by dumps method

Release files for sqliteschema 2.0.1

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for sqliteschema 2.0.1
File	Size	Uploaded
sqliteschema-2.0.1.tar.gz	23.8 kB	Mar 2, 2025	Details

Built distribution (wheel)

Table of built distributions (wheels) for sqliteschema 2.0.1
File	Interpreter	ABI	Platform	Reset
sqliteschema-2.0.1-py3-none-any.whl 14.5 kB Mar 2, 2025	Python 3	none	any	Details

Total release size: 38.3 kB

Release files / sqliteschema-2.0.1.tar.gz

Download URL	sqliteschema-2.0.1.tar.gz
Size	23.8 kB
Tags	Source
SHA-256 checksum How to use checksums	`d70a02d80f5c09d321632213bf957467909593fd462e5a37df66244ab6304c33`
BLAKE2b-256 checksum How to use checksums	`90ad0d7010b15899d25ee832b89d0d79b501c4d0c7d0d03c06e84c1cd1383326`
Upload date	Mar 2, 2025
Uploaded using Trusted Publishing? What is trusted publishing?	Yes
Uploaded via	`twine/6.1.0 CPython/3.12.9`

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Identity

Publishing platform: GitHub Actions
Publishing repository: github.com/thombashi/sqliteschema
Publishing commit: github.com/thombashi/sqliteschema/tree/ad7c342eb9df712c6340398e70028ef3c2c02cab

Workflow

Publishing configuration: github.com/thombashi/sqliteschema/blob/ad7c342eb9df712c6340398e70028ef3c2c02cab/.github/workflows/publish.yml
Publishing logs: github.com/thombashi/sqliteschema/actions/runs/13611588228/attempts/1

Artifact

Subject: sqliteschema-2.0.1.tar.gz
SHA-256 checksum: d70a02d80f5c09d321632213bf957467909593fd462e5a37df66244ab6304c33 Verifying attestations

Release files / sqliteschema-2.0.1-py3-none-any.whl

Download URL	sqliteschema-2.0.1-py3-none-any.whl
Size	14.5 kB
Tags	Python 3
SHA-256 checksum How to use checksums	`46b251ae2583fee508508ec512723e9101aa7df4834c94d770f1fef58551c867`
BLAKE2b-256 checksum How to use checksums	`1eea5bfae542665a9741bac98fa37c802df9fb1a6ab7f55dead8c8ba2a0ae8e0`
Upload date	Mar 2, 2025
Uploaded using Trusted Publishing? What is trusted publishing?	Yes
Uploaded via	`twine/6.1.0 CPython/3.12.9`

Provenance

Identity

Publishing platform: GitHub Actions
Publishing repository: github.com/thombashi/sqliteschema
Publishing commit: github.com/thombashi/sqliteschema/tree/ad7c342eb9df712c6340398e70028ef3c2c02cab

Workflow

Publishing configuration: github.com/thombashi/sqliteschema/blob/ad7c342eb9df712c6340398e70028ef3c2c02cab/.github/workflows/publish.yml
Publishing logs: github.com/thombashi/sqliteschema/actions/runs/13611588228/attempts/1

Artifact

Subject: sqliteschema-2.0.1-py3-none-any.whl
SHA-256 checksum: 46b251ae2583fee508508ec512723e9101aa7df4834c94d770f1fef58551c867 Verifying attestations