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
Project details
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
Built Distribution
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
Algorithm | Hash digest | |
---|---|---|
SHA256 |
d70a02d80f5c09d321632213bf957467909593fd462e5a37df66244ab6304c33
|
|
MD5 |
909372921bf08a9215b3d1a09a7f29df
|
|
BLAKE2b-256 |
90ad0d7010b15899d25ee832b89d0d79b501c4d0c7d0d03c06e84c1cd1383326
|
Provenance
The following attestation bundles were made for sqliteschema-2.0.1.tar.gz
:
Publisher:
publish.yml
on thombashi/sqliteschema
-
Statement:
-
Statement type:
https://in-toto.io/Statement/v1
-
Predicate type:
https://docs.pypi.org/attestations/publish/v1
-
Subject name:
sqliteschema-2.0.1.tar.gz
-
Subject digest:
d70a02d80f5c09d321632213bf957467909593fd462e5a37df66244ab6304c33
- Sigstore transparency entry: 175904280
- Sigstore integration time:
-
Permalink:
thombashi/sqliteschema@ad7c342eb9df712c6340398e70028ef3c2c02cab
-
Branch / Tag:
refs/tags/v2.0.1
- Owner: https://github.com/thombashi
-
Access:
public
-
Token Issuer:
https://token.actions.githubusercontent.com
-
Runner Environment:
github-hosted
-
Publication workflow:
publish.yml@ad7c342eb9df712c6340398e70028ef3c2c02cab
-
Trigger Event:
push
-
Statement type:
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
Algorithm | Hash digest | |
---|---|---|
SHA256 |
46b251ae2583fee508508ec512723e9101aa7df4834c94d770f1fef58551c867
|
|
MD5 |
a0822028a7255ca98a9a5fb592da3dad
|
|
BLAKE2b-256 |
1eea5bfae542665a9741bac98fa37c802df9fb1a6ab7f55dead8c8ba2a0ae8e0
|
Provenance
The following attestation bundles were made for sqliteschema-2.0.1-py3-none-any.whl
:
Publisher:
publish.yml
on thombashi/sqliteschema
-
Statement:
-
Statement type:
https://in-toto.io/Statement/v1
-
Predicate type:
https://docs.pypi.org/attestations/publish/v1
-
Subject name:
sqliteschema-2.0.1-py3-none-any.whl
-
Subject digest:
46b251ae2583fee508508ec512723e9101aa7df4834c94d770f1fef58551c867
- Sigstore transparency entry: 175904281
- Sigstore integration time:
-
Permalink:
thombashi/sqliteschema@ad7c342eb9df712c6340398e70028ef3c2c02cab
-
Branch / Tag:
refs/tags/v2.0.1
- Owner: https://github.com/thombashi
-
Access:
public
-
Token Issuer:
https://token.actions.githubusercontent.com
-
Runner Environment:
github-hosted
-
Publication workflow:
publish.yml@ad7c342eb9df712c6340398e70028ef3c2c02cab
-
Trigger Event:
push
-
Statement type: