sqliteschema 2.0.0

Description:

sqliteschema 2.0.0

sqliteschema

Summary
Installation

Install from PyPI
Install from PPA (for Ubuntu)


Usage

Extract SQLite Schemas as dict
Extract SQLite Schemas as Tabular Text
CLI Usage


Dependencies

Optional dependencies





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.7+
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