用于转储 SQLite 数据库文件的表模式的 Python 库。
项目描述
<nav class="contents" id="sqliteschema" role="doc-toc">
sqliteschema
概括
sqliteschema是一个 Python 库，用于转储 SQLite 数据库文件的表模式。
安装
从 PyPI 安装
pip install sqliteschema
安装可选依赖项
pip install sqliteschema[dumps] # to use dumps method pip install sqliteschema[logging] # to use logging
从 PPA 安装（适用于 Ubuntu）
sudo add-apt-repository ppa:thombashi/ppa sudo apt update sudo apt install python3-sqliteschema
用法
完整示例可以在 examples/get_table_schema.py 找到
将 SQLite 模式提取为 dict
- 示例代码：
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)))- 输出：
--- 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": "" } ] }
将 SQLite 模式提取为表格文本
可以使用dumps方法输出表模式。 dumps方法需要一个额外的包，可以按如下方式安装：
pip install sqliteschema[dumps]
用法如下：
- 示例代码：
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))- 输出：
--- 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 | |
将 SQLite 模式提取为文本
- 示例代码：
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")- 输出：
--- 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 )
依赖项
Python 3.6+
可选依赖项
- 洛鲁鲁
如果安装了软件包，则用于记录
- pytablewriter
通过转储方法获取带有表格文本的表模式时需要
