Skip to main content

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

Project description

Summary

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

PyPI package version Supported Python versions Supported Python implementations CI status of Linux/macOS/Windows Test coverage CodeQL

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

Optional dependencies

  • loguru
    • Used for logging if the package installed

  • pytablewriter
    • Required when getting table schemas with tabular text by dumps method

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

sqliteschema-2.0.1.tar.gz (23.8 kB view details)

Uploaded Source

Built Distribution

sqliteschema-2.0.1-py3-none-any.whl (14.5 kB view details)

Uploaded Python 3

File details

Details for the file sqliteschema-2.0.1.tar.gz.

File metadata

  • Download URL: sqliteschema-2.0.1.tar.gz
  • Upload date:
  • Size: 23.8 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? Yes
  • Uploaded via: twine/6.1.0 CPython/3.12.9

File hashes

Hashes for sqliteschema-2.0.1.tar.gz
Algorithm Hash digest
SHA256 d70a02d80f5c09d321632213bf957467909593fd462e5a37df66244ab6304c33
MD5 909372921bf08a9215b3d1a09a7f29df
BLAKE2b-256 90ad0d7010b15899d25ee832b89d0d79b501c4d0c7d0d03c06e84c1cd1383326

See more details on using hashes here.

Provenance

The following attestation bundles were made for sqliteschema-2.0.1.tar.gz:

Publisher: publish.yml on thombashi/sqliteschema

Attestations: Values shown here reflect the state when the release was signed and may no longer be current.

File details

Details for the file sqliteschema-2.0.1-py3-none-any.whl.

File metadata

  • Download URL: sqliteschema-2.0.1-py3-none-any.whl
  • Upload date:
  • Size: 14.5 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? Yes
  • Uploaded via: twine/6.1.0 CPython/3.12.9

File hashes

Hashes for sqliteschema-2.0.1-py3-none-any.whl
Algorithm Hash digest
SHA256 46b251ae2583fee508508ec512723e9101aa7df4834c94d770f1fef58551c867
MD5 a0822028a7255ca98a9a5fb592da3dad
BLAKE2b-256 1eea5bfae542665a9741bac98fa37c802df9fb1a6ab7f55dead8c8ba2a0ae8e0

See more details on using hashes here.

Provenance

The following attestation bundles were made for sqliteschema-2.0.1-py3-none-any.whl:

Publisher: publish.yml on thombashi/sqliteschema

Attestations: Values shown here reflect the state when the release was signed and may no longer be current.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page