sqliteschema 0.10.2

A Python library to dump table schema of a SQLite database file.

sqliteschema

Table of Contents

• sqliteschema

• Summary

• Installation

• Usage

  • Extract SQLite Schemas as dict

  • Extract SQLite Schemas as Table

  • Extract SQLite Schemas as Text

• Dependencies

  • Optional dependencies

  • Test dependencies

Summary

A Python library to dump table schema of a SQLite database file.

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": [
        {
            "Attribute name": "attr_a",
            "Index": false,
            "Data type": "INTEGER",
            "PRIMARY KEY": false,
            "NOT NULL": false,
            "UNIQUE": false
        },
        {
            "Attribute name": "attr_b",
            "Index": false,
            "Data type": "INTEGER",
            "PRIMARY KEY": false,
            "NOT NULL": false,
            "UNIQUE": false
        }
    ],
    "sampletable1": [
        {
            "Attribute name": "foo",
            "Index": true,
            "Data type": "INTEGER",
            "PRIMARY KEY": false,
            "NOT NULL": false,
            "UNIQUE": false
        },
        {
            "Attribute name": "bar",
            "Index": false,
            "Data type": "REAL",
            "PRIMARY KEY": false,
            "NOT NULL": false,
            "UNIQUE": false
        },
        {
            "Attribute name": "hoge",
            "Index": true,
            "Data type": "TEXT",
            "PRIMARY KEY": false,
            "NOT NULL": false,
            "UNIQUE": false
        }
    ],
    "constraints": [
        {
            "Attribute name": "primarykey_id",
            "Index": false,
            "Data type": "INTEGER",
            "PRIMARY KEY": true,
            "NOT NULL": false,
            "UNIQUE": false
        },
        {
            "Attribute name": "notnull_value",
            "Index": false,
            "Data type": "REAL",
            "PRIMARY KEY": false,
            "NOT NULL": true,
            "UNIQUE": false
        },
        {
            "Attribute name": "unique_value",
            "Index": false,
            "Data type": "INTEGER",
            "PRIMARY KEY": false,
            "NOT NULL": false,
            "UNIQUE": true
        }
    ]
}

--- dump a specific table schema with a dictionary ---
{
    "sampletable1": [
        {
            "Attribute name": "foo",
            "Index": true,
            "Data type": "INTEGER",
            "PRIMARY KEY": false,
            "NOT NULL": false,
            "UNIQUE": false
        },
        {
            "Attribute name": "bar",
            "Index": false,
            "Data type": "REAL",
            "PRIMARY KEY": false,
            "NOT NULL": false,
            "UNIQUE": false
        },
        {
            "Attribute name": "hoge",
            "Index": true,
            "Data type": "TEXT",
            "PRIMARY KEY": false,
            "NOT NULL": false,
            "UNIQUE": false
        }
    ]
}

Extract SQLite Schemas as Table

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
|Attribute name|Data type|
|--------------|---------|
|attr_a        |INTEGER  |
|attr_b        |INTEGER  |


# sampletable1
|Attribute name|Data type|
|--------------|---------|
|foo           |INTEGER  |
|bar           |REAL     |
|hoge          |TEXT     |


# constraints
|Attribute name|Data type|
|--------------|---------|
|primarykey_id |INTEGER  |
|notnull_value |REAL     |
|unique_value  |INTEGER  |


--- dump all of the table schemas with a tabular format: verbosity_level=1 ---
# sampletable0
|Attribute name|Data type|PRIMARY KEY|NOT NULL|UNIQUE|Index|
|--------------|---------|-----------|--------|------|-----|
|attr_a        |INTEGER  |           |        |      |     |
|attr_b        |INTEGER  |           |        |      |     |


# sampletable1
|Attribute name|Data type|PRIMARY KEY|NOT NULL|UNIQUE|Index|
|--------------|---------|-----------|--------|------|-----|
|foo           |INTEGER  |           |        |      |X    |
|bar           |REAL     |           |        |      |     |
|hoge          |TEXT     |           |        |      |X    |


# constraints
|Attribute name|Data type|PRIMARY KEY|NOT NULL|UNIQUE|Index|
|--------------|---------|-----------|--------|------|-----|
|primarykey_id |INTEGER  |X          |        |      |     |
|notnull_value |REAL     |           |X       |      |     |
|unique_value  |INTEGER  |           |        |X     |     |


--- dump a specific table schema with a tabular format: verbosity_level=0 ---
# sampletable1
|Attribute name|Data type|
|--------------|---------|
|foo           |INTEGER  |
|bar           |REAL     |
|hoge          |TEXT     |


--- dump a specific table schema with a tabular format: verbosity_level=1 ---
# sampletable1
|Attribute name|Data type|PRIMARY KEY|NOT NULL|UNIQUE|Index|
|--------------|---------|-----------|--------|------|-----|
|foo           |INTEGER  |           |        |      |X    |
|bar           |REAL     |           |        |      |     |
|hoge          |TEXT     |           |        |      |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, attr_b INTEGER)
sampletable1 (foo INTEGER, bar REAL, hoge TEXT)
constraints (primarykey_id INTEGER PRIMARY KEY, notnull_value REAL NOT NULL, unique_value INTEGER UNIQUE)

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

--- 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, bar REAL, hoge TEXT)

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

Dependencies

Python 2.7+ or 3.4+

• logbook

• SimpleSQLite

• six

• tabledata

• typepy

Optional dependencies

• pytablewriter (required to get schemas with tabular formats)

Test dependencies

• pytest

• pytest-runner

• tox