CLI tool for exporting tables or queries from any SQL database to a SQLite file
Project description
db-to-sqlite
CLI tool for exporting tables or queries from any SQL database to a SQLite file.
Installation
Install from PyPI like so:
pip install db-to-sqlite
If you want to use it with MySQL, you can install the extra dependency like this:
pip install db-to-sqlite[mysql]
For PostgreSQL, use this:
pip install db-to-sqlite[postgresql]
Usage
Usage: db-to-sqlite [OPTIONS] CONNECTION PATH
Load data from any database into SQLite.
PATH is a path to the SQLite file to create, e.c. /tmp/my_database.db
CONNECTION is a SQLAlchemy connection string, for example:
postgresql://localhost/my_database
postgresql://username:passwd@localhost/my_database
mysql://root@localhost/my_database
mysql://username:passwd@localhost/my_database
More: https://docs.sqlalchemy.org/en/13/core/engines.html#database-urls
Options:
--version Show the version and exit.
--all Detect and copy all tables
--table TEXT Specific tables to copy
--skip TEXT When using --all skip these tables
--redact TEXT... (table, column) pairs to redact with ***
--sql TEXT Optional SQL query to run
--output TEXT Table in which to save --sql query results
--pk TEXT Optional column to use as a primary key
--index-fks / --no-index-fks Should foreign keys have indexes? Default on
-p, --progress Show progress bar
--help Show this message and exit.
For example, to save the content of the blog_entry
table from a PostgreSQL database to a local file called blog.db
you could do this:
db-to-sqlite "postgresql://localhost/myblog" blog.db \
--table=blog_entry
You can specify --table
more than once.
You can also save the data from all of your tables, effectively creating a SQLite copy of your entire database. Any foreign key relationships will be detected and added to the SQLite database. For example:
db-to-sqlite "postgresql://localhost/myblog" blog.db \
--all
When running --all
you can specify tables to skip using --skip
:
db-to-sqlite "postgresql://localhost/myblog" blog.db \
--all \
--skip=django_migrations
If you want to save the results of a custom SQL query, do this:
db-to-sqlite "postgresql://localhost/myblog" output.db \
--output=query_results \
--sql="select id, title, created from blog_entry" \
--pk=id
The --output
option specifies the table that should contain the results of the query.
Using db-to-sqlite with Heroku Postgres
If you run an application on Heroku using their Postgres database product, you can use the heroku config
command to access a compatible connection string:
$ heroku config --app myappname | grep HEROKU_POSTG
HEROKU_POSTGRESQL_OLIVE_URL: postgres://username:password@ec2-xxx-xxx-xxx-x.compute-1.amazonaws.com:5432/dbname
You can pass this to db-to-sqlite
to create a local SQLite database with the data from your Heroku instance.
You can even do this using a bash one-liner:
$ db-to-sqlite $(heroku config --app myappname | grep HEROKU_POSTG | cut -d: -f 2-) \
/tmp/wot.db --all -p
1/23: django_migrations
...
17/23: blog_blogmark
[####################################] 100%
...
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 Distributions
Built Distribution
Hashes for db_to_sqlite-1.0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 3bd02021f8e18fff2c853d7d0d953119d5f451f78a53b14253aa14bf52446616 |
|
MD5 | c49666d6189ab665ed16e47d22003f38 |
|
BLAKE2b-256 | cb64707bd352fc6195c60334ceb5d5dbaae594dccbdeb09e36cde355df67e947 |