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 Linux/macOS CI status Windows CI status Test coverage

Installation

pip install sqliteschema

Usage

Full example 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 with a dictionary ---\n{}\n".format(
    json.dumps(extractor.fetch_database_schema_as_dict(), indent=4)))

print("--- dump a specific table schema with a dictionary ---\n{}\n".format(
    json.dumps(extractor.fetch_table_schema("sampletable1").as_dict(), indent=4)))
Output:
--- dump all of the table schemas with a dictionary ---
{
    "sampletable0": [
        {
            "Field": "attr_a",
            "Index": false,
            "Type": "INTEGER",
            "Null": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        },
        {
            "Field": "attr_b",
            "Index": false,
            "Type": "INTEGER",
            "Null": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        }
    ],
    "sampletable1": [
        {
            "Field": "foo",
            "Index": true,
            "Type": "INTEGER",
            "Null": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        },
        {
            "Field": "bar",
            "Index": false,
            "Type": "REAL",
            "Null": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        },
        {
            "Field": "hoge",
            "Index": true,
            "Type": "TEXT",
            "Null": "YES",
            "Key": "",
            "Default": "NULL",
            "Extra": ""
        }
    ],
    "constraints": [
        {
            "Field": "primarykey_id",
            "Index": true,
            "Type": "INTEGER",
            "Null": "YES",
            "Key": "PRI",
            "Default": "NULL",
            "Extra": ""
        },
        {
            "Field": "notnull_value",
            "Index": false,
            "Type": "REAL",
            "Null": "NO",
            "Key": "",
            "Default": "",
            "Extra": ""
        },
        {
            "Field": "unique_value",
            "Index": true,
            "Type": "INTEGER",
            "Null": "YES",
            "Key": "UNI",
            "Default": "NULL",
            "Extra": ""
        }
    ]
}

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

Extract SQLite Schemas as Tabular Text

Table schemas can be output with dumps method. dumps method requires an extra package and that can install 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  |Null|Key|Default|Index|Extra|
|------|-------|----|---|-------|:---:|-----|
|attr_a|INTEGER|YES |   |NULL   |     |     |
|attr_b|INTEGER|YES |   |NULL   |     |     |

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

# constraints
|    Field    | Type  |Null|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  |Null|Key|Default|Index|Extra|
|-----|-------|----|---|-------|:---:|-----|
|foo  |INTEGER|YES |   |NULL   |  X  |     |
|bar  |REAL   |YES |   |NULL   |     |     |
|hoge |TEXT   |YES |   |NULL   |  X  |     |

Extract SQLite Schemas as Text

Sample Code:
import sqliteschema

extractor = sqliteschema.SQLiteSchemaExtractor(sqlite_db_path)

for verbosity_level in range(5):
    print("--- dump all of the table schemas with text format: verbosity_level={} ---".format(
        verbosity_level))
    print(extractor.dumps(output_format="text", verbosity_level=verbosity_level) + "\n")

for verbosity_level in range(5):
    print("--- dump specific table schema with text format: verbosity_level={} ---".format(
        verbosity_level))
    print(extractor.fetch_table_schema("sampletable1").dumps(
        output_format="text", verbosity_level=verbosity_level) + "\n")
Output:
--- dump all of the table schemas with text format: verbosity_level=0 ---
sampletable0
sampletable1
constraints

--- dump all of the table schemas with text format: verbosity_level=1 ---
sampletable0 (attr_a, attr_b)
sampletable1 (foo, bar, hoge)
constraints (primarykey_id, notnull_value, unique_value)

--- dump all of the table schemas with text format: verbosity_level=2 ---
sampletable0 (attr_a INTEGER, attr_b INTEGER)
sampletable1 (foo INTEGER, bar REAL, hoge TEXT)
constraints (primarykey_id INTEGER, notnull_value REAL, unique_value INTEGER)

--- dump all of the table schemas with text format: verbosity_level=3 ---
sampletable0 (attr_a INTEGER Null, attr_b INTEGER Null)
sampletable1 (foo INTEGER Null, bar REAL Null, hoge TEXT Null)
constraints (primarykey_id INTEGER Key Null, notnull_value REAL Null, unique_value INTEGER Key Null)

--- dump all of the table schemas with text format: verbosity_level=4 ---
sampletable0 (
    attr_a INTEGER Null,
    attr_b INTEGER Null
)

sampletable1 (
    foo INTEGER Null,
    bar REAL Null,
    hoge TEXT Null
)

constraints (
    primarykey_id INTEGER Key Null,
    notnull_value REAL Null,
    unique_value INTEGER Key Null
)


--- dump specific table schema with text format: verbosity_level=0 ---
sampletable1

--- dump specific table schema with text format: verbosity_level=1 ---
sampletable1 (foo, bar, hoge)

--- dump specific table schema with text format: verbosity_level=2 ---
sampletable1 (foo INTEGER, bar REAL, hoge TEXT)

--- dump specific table schema with text format: verbosity_level=3 ---
sampletable1 (foo INTEGER Null, bar REAL Null, hoge TEXT Null)

--- dump specific table schema with text format: verbosity_level=4 ---
sampletable1 (
    foo INTEGER Null,
    bar REAL Null,
    hoge TEXT Null
)

Dependencies

Python 2.7+ or 3.4+

Optional dependencies

  • logbook
    • Logging using logbook 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.

Filename, size & hash SHA256 hash help File type Python version Upload date
sqliteschema-0.15.3-py2.py3-none-any.whl (12.5 kB) Copy SHA256 hash SHA256 Wheel py2.py3
sqliteschema-0.15.3.tar.gz (16.4 kB) Copy SHA256 hash SHA256 Source None

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page