sqlalchemy-loadump

This library makes your software to dump and load data in databases with sqlalchemy.

✨Features

• Load data from file into databases
• Dump data in databases to file
• Load/Dump without defining SQLAlchemy table
• You can use this library from cli and code both

📥Installation

Installing latest version.

pip install sqlalchemy-loadump

📖Usage

📝In code

Load

Code

from pathlib import Path
from sqlalchemy_loadump.importer_switcher import default_importer_switcher
from sqlalchemy_loadump.loader import Loader
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


def main():
    dump_file_type = "json"
    dump_file_path = Path("./examples/load_example.json")
    db_url = "sqlite:///examples/load_example.db"

    importer = default_importer_switcher.get_route(dump_file_type)
    if importer is None:
        raise ValueError(f"Unknown dump file type: {dump_file_type}")
    dump_data = importer.import_from_file(dump_file_path)

    engine = create_engine(db_url)
    session_maker = sessionmaker(bind=engine)
    with session_maker() as session:
        loader = Loader(dump_data, engine, session, schema=None)
        loader.load()
        session.commit()


if __name__ == "__main__":
    main()

Dump

Code

from pathlib import Path
from sqlalchemy_loadump.exporter_switcher import default_exporter_switcher
from sqlalchemy_loadump.dumper import Dumper
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


def main():
    dump_file_type = "json"
    dump_file_path = Path("./examples/dump_example.json")
    db_url = "sqlite:///examples/dump_example.db"
    human_readable = True

    engine = create_engine(db_url)
    session_maker = sessionmaker(bind=engine)
    with session_maker() as session:
        dumper: Dumper = Dumper(engine, session, schema=None)
        dump_data = dumper.dump()

    exporter = default_exporter_switcher.get_route(dump_file_type)
    if exporter is None:
        raise ValueError(f"Unknown dump file type: {dump_file_type}")
    exporter.export_to_file(dump_data, dump_file_path, human_readable)

if __name__ == "__main__":
    main()

⌨️Commandline

Load

This command loads data from the json file and inserts it into the sqlite3 database (db.sqlite3) database.

You can try this using exmaple data with examples/ folder.

python -m sqlalchemy_loadump load --dump-file-type=json --dump-file-path=examples/load_example.json --db-url=sqlite:///examples/load_example.db

Dump

This command dumps data in the sqlite3 database (db.sqlite3) to the json file.

You can try this using exmaple data with examples/ folder.

python -m sqlalchemy_loadump dump --dump-file-type=json --dump-file-path=examples/dump_example.json --db-url=sqlite:///examples/dump_example.db

📦Dependencies

• Python 3.8+
• SQLAlchemy 2.0+

📦Development dependencies

• Poetry
• Docker
• Docker Compose

🛠️Supported Dump Formats

• JSON

🛠️Supported Databases

• SQLite3
• PostgreSQL
• Microsoft SQLServer

🛠️Supported(Tested) DataType List

SQLite3

DataType List

- BigInteger
- Boolean
- Date
- DateTime
- Double
- Enum
- Float
- Integer
- (Interval) ・・・ Treated as DateTime
- LargeBinary
- Numeric
- SmallInteger
- String
- Text
- Time
- Unicode
- UnicodeText
- Uuid

PostgreSQL

DataType List

- BigInteger
- Boolean
- Date
- DateTime
- Double
- Enum
- Float
- Integer
- Interval
- LargeBinary
- Numeric
- SmallInteger
- String
- Text
- Time
- Unicode
- UnicodeText
- Uuid

Microsoft SQLServer

DataType List

- BigInteger
- Boolean
- Date
- DateTime
- Double
- Enum
- Float
- Integer
- (Interval) ・・・ Treated as DateTime
- LargeBinary
- Numeric
- SmallInteger
- String
- Text
- Time
- Unicode
- UnicodeText
- Uuid

⚠️Limitation of the result of dumping.

The python type of the result of convertion depends on the column type of table reflected by MetaData.reflect.

As a result, there is a possibility of losing type information from the dumped data.

For instance, using SQLite3, when creating a table with a UUID(SQLAlchemy) column and then dumping it, the dumped result will become a string type.

However, for the types listed in the "Supported DataType List," we ensure the proper dumping and loading of data.

👨‍💻Development Setup

Install docker & docker-compose

This library uses docker & docker-compose to test.

You can install docker & docker-compose by following this link.

Install poetry

This library uses poetry to manage dependencies and build.

You can install poetry by following this link.

Install dependencies

Installing dependencies for this library.

poetry install