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.
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
Release history Release notifications | RSS feed
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.0.tar.gz
(20.3 kB
view hashes)
Built Distribution
Close
Hashes for sqliteschema-2.0.0-py3-none-any.whl
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | 29e0a3e0d5dd8f21e06816850e5d171bfa61155ddabfb2e7422b73215b033714 |
|
| MD5 | 7779a6037abb49bbabb14e1a0cdfc983 |
|
| BLAKE2b-256 | 878b2ca6e8aa73716547f97f9c8d7b4a3b320e904b2afd49ad0f5d304348cdef |
