pgsqlite
=======
Load SQLite3 databases into PostgresSQL.
Usage:
```
usage: pgsqlite.py [-h] -f SQLITE_FILENAME -p POSTGRES_CONNECT_URL [-d DEBUG] [--drop_tables DROP_TABLES] [--drop_everything DROP_EVERYTHING] [--drop_tables_after_import DROP_TABLES_AFTER_IMPORT]
optional arguments:
-h, --help show this help message and exit
-f SQLITE_FILENAME, --sqlite_filename SQLITE_FILENAME
sqlite database to import
-p POSTGRES_CONNECT_URL, --postgres_connect_url POSTGRES_CONNECT_URL
Postgres URL for the database to import into
-d DEBUG, --debug DEBUG
Set log level to DEBUG
--drop_tables DROP_TABLES
Prior to import, drop tables in the target database that have the same name as tables in the source database
--drop_everything DROP_EVERYTHING
Prior to import, drop everything (tables, views, triggers, etc, etc) in the target database before the import
--drop_tables_after_import DROP_TABLES_AFTER_IMPORT
Drop all tables in the target database after import; useful for testing
```
Examples:
Import into the bit.io database `adam/AMEND`, with DEBUG-level logging.
```
python pgsqlite.py -f ../example_dbs/Chinook_Sqlite.sqlite -p postgresql://adam:<password>@db.bit.io/adam/AMEND --debug true
```
Import into the bit.io database `adam/AMEND`, dropping all tables in the target database that match tables in the source database:
```
python pgsqlite.py -f ../example_dbs/Chinook_Sqlite.sqlite -p postgresql://adam:<password>@db.bit.io/adam/AMEND --drop_tables true
```
Most of the drop options are used for testing - be aware they are destructive operations!
Testing
=======
There's a set of open-source databases in the `example_dbs/` directory, and
`./import_examples.sh` script that will test importing of all those databases.
You'll need to set `POSTGRES_CREDS_STRING` to your connect string before hand,
and also be aware this script will drop everything in the target database, so be careful!
How This Works
==============
For more details, read: https://innerjoin.bit.io/introducing-pgsqlite-a-pure-python-module-to-import-sqlite-databases-into-postgres-bf3940cfa19f
SQLite is far more forgiving a database then Postgree. Look at this `CREATE TABLE`:
```
CREATE TABLE Customer_Ownership(
customer_id INTEGER NOT NULL,
vin INTEGER NOT NULL,
purchase_date DATE NOT NULL,
purchase_price INTEGER NOT NULL,
warantee_expire_date DATE,
dealer_id INTEGER NOT NULL,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
FOREIGN KEY (vin) REFERENCES Car_Vins(vin),
FOREIGN KEY (dealer_id) REFERENCES Dealers(dealer_id)
PRIMARY KEY (customer_id, vin)
);
```
This is totally valid in SQLite and is missing a comma on the second to last line. In fact, this is what
you'd get back from `.schema` in the sqlite command line tool.
For pgsqlite, this means we cannot use the excellent `sqlglot` module to transpile the schema creation SQL
as the module is too strict for some sqlite databases.
We need the (also excellent) `sqlite-utils` module. `sqlite-utils` gives us python objects that represent
the database entities, which lets us then create Postgres-valid SQL to create these entities.
We use psycopg (version 3) to gain access to the very fast `COPY` protocol. We filter that incoming data
to make sure we have nulls set correctly, and to do any transforms on the literal values that are required
(like the BOOLEAN example in Known Issues, below).
Known Issues
============
Most of the issues are around constraints that involve SQL that requires literals. For example, a `BOOLEAN` column may have a `CHECK` constraint
like `IN (1, 0)` which is valid in SQLite but not in Postgres (in SQLite the integers `1` and `0` are true/false, but not in Postgres). To fix
this we'd need to parse the SQL, identify the literals and which columns they map to, then "fix" the literal's type. This also impacts views & triggers.
TODOS
=====
* Unit tests
* Append mode
* Async loading of data
* With async, a status property that tells us, eg "x of y rows loaded in table z"
Raw data
{
"_id": null,
"home_page": "https://github.com/bitdotioinc/pgsqlite",
"name": "pgsqlite",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.9",
"maintainer_email": "",
"keywords": "bit.io,Database,postgres,postgresql,sqlite,sqlite3",
"author": "bit.io",
"author_email": "python@bit.io",
"download_url": "https://files.pythonhosted.org/packages/96/8d/30a3e25396777aaf63051e718755b9556413287a7dd6a283fb425e586b49/pgsqlite-1.0.3.tar.gz",
"platform": null,
"description": "pgsqlite\n=======\nLoad SQLite3 databases into PostgresSQL.\n\nUsage:\n```\nusage: pgsqlite.py [-h] -f SQLITE_FILENAME -p POSTGRES_CONNECT_URL [-d DEBUG] [--drop_tables DROP_TABLES] [--drop_everything DROP_EVERYTHING] [--drop_tables_after_import DROP_TABLES_AFTER_IMPORT]\n\noptional arguments:\n -h, --help show this help message and exit\n -f SQLITE_FILENAME, --sqlite_filename SQLITE_FILENAME\n sqlite database to import\n -p POSTGRES_CONNECT_URL, --postgres_connect_url POSTGRES_CONNECT_URL\n Postgres URL for the database to import into\n -d DEBUG, --debug DEBUG\n Set log level to DEBUG\n --drop_tables DROP_TABLES\n Prior to import, drop tables in the target database that have the same name as tables in the source database\n --drop_everything DROP_EVERYTHING\n Prior to import, drop everything (tables, views, triggers, etc, etc) in the target database before the import\n --drop_tables_after_import DROP_TABLES_AFTER_IMPORT\n Drop all tables in the target database after import; useful for testing\n```\n\n\nExamples:\n\nImport into the bit.io database `adam/AMEND`, with DEBUG-level logging.\n```\npython pgsqlite.py -f ../example_dbs/Chinook_Sqlite.sqlite -p postgresql://adam:<password>@db.bit.io/adam/AMEND --debug true\n```\n\nImport into the bit.io database `adam/AMEND`, dropping all tables in the target database that match tables in the source database: \n```\npython pgsqlite.py -f ../example_dbs/Chinook_Sqlite.sqlite -p postgresql://adam:<password>@db.bit.io/adam/AMEND --drop_tables true\n```\n\nMost of the drop options are used for testing - be aware they are destructive operations!\n\nTesting\n=======\nThere's a set of open-source databases in the `example_dbs/` directory, and\n`./import_examples.sh` script that will test importing of all those databases. \nYou'll need to set `POSTGRES_CREDS_STRING` to your connect string before hand, \nand also be aware this script will drop everything in the target database, so be careful!\n\nHow This Works\n==============\n\nFor more details, read: https://innerjoin.bit.io/introducing-pgsqlite-a-pure-python-module-to-import-sqlite-databases-into-postgres-bf3940cfa19f\n\n\nSQLite is far more forgiving a database then Postgree. Look at this `CREATE TABLE`:\n\n```\nCREATE TABLE Customer_Ownership(\n customer_id INTEGER NOT NULL,\n vin INTEGER NOT NULL,\n purchase_date DATE NOT NULL,\n purchase_price INTEGER NOT NULL,\n warantee_expire_date DATE,\n dealer_id INTEGER NOT NULL,\n FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),\n FOREIGN KEY (vin) REFERENCES Car_Vins(vin),\n FOREIGN KEY (dealer_id) REFERENCES Dealers(dealer_id)\n PRIMARY KEY (customer_id, vin)\n);\n```\n\nThis is totally valid in SQLite and is missing a comma on the second to last line. In fact, this is what\nyou'd get back from `.schema` in the sqlite command line tool. \n\nFor pgsqlite, this means we cannot use the excellent `sqlglot` module to transpile the schema creation SQL \nas the module is too strict for some sqlite databases. \nWe need the (also excellent) `sqlite-utils` module. `sqlite-utils` gives us python objects that represent\nthe database entities, which lets us then create Postgres-valid SQL to create these entities.\n\n\nWe use psycopg (version 3) to gain access to the very fast `COPY` protocol. We filter that incoming data \nto make sure we have nulls set correctly, and to do any transforms on the literal values that are required \n(like the BOOLEAN example in Known Issues, below). \n\n\nKnown Issues\n============\nMost of the issues are around constraints that involve SQL that requires literals. For example, a `BOOLEAN` column may have a `CHECK` constraint\nlike `IN (1, 0)` which is valid in SQLite but not in Postgres (in SQLite the integers `1` and `0` are true/false, but not in Postgres). To fix\nthis we'd need to parse the SQL, identify the literals and which columns they map to, then \"fix\" the literal's type. This also impacts views & triggers.\n\n\nTODOS\n=====\n* Unit tests\n* Append mode\n* Async loading of data\n * With async, a status property that tells us, eg \"x of y rows loaded in table z\"\n\n\n\n\n",
"bugtrack_url": null,
"license": "",
"summary": "Loader to import sqlite3 databases into Postgres",
"version": "1.0.3",
"split_keywords": [
"bit.io",
"database",
"postgres",
"postgresql",
"sqlite",
"sqlite3"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "93afe22ae25d94194fde43b4d18dc7090128f1ea147c6c9592659fb634051922",
"md5": "449f99cced47ebba7679caefb8daf1b2",
"sha256": "a2d1e87a0e6fd3a90c0894d2bd13d31b9b0f0bb53b1888fb81e4ecca95cd41d3"
},
"downloads": -1,
"filename": "pgsqlite-1.0.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "449f99cced47ebba7679caefb8daf1b2",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.9",
"size": 10480,
"upload_time": "2023-02-07T21:37:01",
"upload_time_iso_8601": "2023-02-07T21:37:01.921098Z",
"url": "https://files.pythonhosted.org/packages/93/af/e22ae25d94194fde43b4d18dc7090128f1ea147c6c9592659fb634051922/pgsqlite-1.0.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "968d30a3e25396777aaf63051e718755b9556413287a7dd6a283fb425e586b49",
"md5": "db9062907fcff3a6813b1fba02a1e973",
"sha256": "3e778c5266aec65e36fc67cf3dca1d84982941f58e9510a39e9d0188e9929f82"
},
"downloads": -1,
"filename": "pgsqlite-1.0.3.tar.gz",
"has_sig": false,
"md5_digest": "db9062907fcff3a6813b1fba02a1e973",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9",
"size": 12049,
"upload_time": "2023-02-07T21:37:03",
"upload_time_iso_8601": "2023-02-07T21:37:03.520010Z",
"url": "https://files.pythonhosted.org/packages/96/8d/30a3e25396777aaf63051e718755b9556413287a7dd6a283fb425e586b49/pgsqlite-1.0.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-02-07 21:37:03",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "bitdotioinc",
"github_project": "pgsqlite",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "pgsqlite"
}
JSON
