python-db2sql 1.0.0

Dump any supported source database into a PostgreSQL SQL file

db2sql is a Python package providing a command-line utility to move any supported source database (SQLite, MySQL, MSSQL, PostgreSQL, Oracle) into a target dialect — PostgreSQL (default) or Microsoft SQL Server — selectable via --target.

Two output modes are supported:

• Dump mode (default) — write a SQL file (or stream to stdout) that can later be replayed with psql -f or sqlcmd -i.

• Migrate mode — open a live connection to the target database and apply the same DDL and data directly, without an intermediate file. The DDL produced is byte-identical to dump mode: a single SqlEmitter is the source of truth in both paths.

Installation

db2sql is compatible with Python 3.9+.

Use pip to install the latest stable version. Note the distribution name on PyPI is python-db2sql while the importable Python package is db2sql (same convention as python-dateutil):

$ pip install --upgrade python-db2sql

After installation, the CLI is available as db2sql and the importable module as db2sql:

from db2sql.interface.cli import main

The current development version is available on GitHub.com and can be installed directly from the git repository:

$ pip install git+https://github.com/sismicfr/python-db2sql.git

Live migration mode

Stream a source database directly into a live target — same DDL as the file dump, but without the round-trip through a .sql file:

# SQLite source → live Postgres target
$ db2sql --driver sqlite --dbname mydb.sqlite migrate \
    --target-host localhost --target-port 5432 \
    --target-dbname mytarget --target-user postgres --target-password s3cr3t

The migrate subcommand uses the SqlEmitter of the chosen --target to produce DDL and a dialect-specific TargetWriter (e.g. psycopg2.copy for Postgres, batched executemany for MSSQL) to bulk-load rows. See the CLI reference for all --target-* flags and migration options (--on-existing, --transaction-mode, --batch-size).

Replayable dumps

By default, the dump emits CREATE TABLE statements only — replaying the file against a database that already contains the target tables fails. Pass --on-existing drop (or set dump.on_existing: drop in the config) to prepend a DROP TABLE IF EXISTS for every table in reverse-dependency order:

$ db2sql --driver sqlite --dbname mydb.sqlite --on-existing drop -f dump.sql

Pass --on-existing truncate to produce a data-only script: no DDL is emitted, the dump just TRUNCATEs every managed table and reloads its rows. Use it to refresh data into a pre-existing schema:

$ db2sql --driver sqlite --dbname mydb.sqlite --on-existing truncate -f refresh.sql

Validating a configuration

Before launching a long dump, check the configuration file and (optionally) preview the export plan without producing any SQL:

# syntax check + plugin name resolution (no DB connection)
$ db2sql validate db2sql.yml

# connect to the source and print the plan, no SQL emitted
$ db2sql validate db2sql.yml --dry-run

# same plan plus one SELECT COUNT(*) per kept table
$ db2sql validate db2sql.yml --dry-run --with-counts

See the CLI reference for full details, exit codes, and the lookup order when the positional CONFIG_FILE is omitted.

Extensibility

Beyond the built-in drivers (SQLite, MySQL, MSSQL, PostgreSQL, Oracle) and targets (PostgreSQL, MSSQL), db2sql discovers third-party plugins through three entry-point groups:

• db2sql.readers — register a new source driver (--driver)

• db2sql.emitters — register a new target dialect for the file dump (--target)

• db2sql.writers — register a new target writer for live migration (used by db2sql migrate)

A step-by-step authoring guide lives in the Plugins section of the documentation, and three runnable example projects ship under examples/:

• examples/csv-producer — a custom reader (a directory of CSVs)

• examples/sqlite-emitter — a custom emitter (SQLite-flavoured SQL)

• examples/yaml-to-markdown — a single package that ships both a reader and an emitter

Each example is a standalone Python distribution: cd examples/<name> && pip install -e . makes its driver / target immediately usable from the db2sql CLI.

Bug reports

Please report bugs and feature requests at https://github.com/sismicfr/python-db2sql/issues.

Documentation

The full documentation for CLI and API is available on readthedocs.

Build the docs

We use tox to manage our environment and build the documentation:

pip install tox
tox -e docs

Contributing

For guidelines for contributing to db2sql, refer to CONTRIBUTING.rst.