2

Is there an easy way in Python to migrate from SQLite to Postgres table by table? I am using Peewee, can generate SQLite dumps but don't have psql available to import into Postgres. Is there a package in Python for that? I am also using Peewee as ORM and the generate schema bit is covered, the issue is the data.

The database is somewhat big at 1.3gb

SkyWalker
  • 13,729
  • 18
  • 91
  • 187
  • 1
    Python has the global module `sqlite3` that can work with Sqlite directly. For the Postgres side there is [psycopog2](https://www.psycopg.org/). You could use the first to pull out of SQLite and transfer to the second for entry into Postgres. There is also [pgloader](https://pgloader.readthedocs.io/en/latest/) in particular [Sqlite->Postgres](https://pgloader.readthedocs.io/en/latest/ref/sqlite.html?highlight=sqlite). – Adrian Klaver Jul 09 '21 at 16:10

1 Answers1

5

You have a lot of options. The simplest is probably to use Sqlite's ".dump" shell command to generate the SQL. There are a number of options or ways you can do this. You could use ".dump" which dumps all the schema and data, and then make any modifications to the dump-file and then load it into postgres.

To just dump row data for table "my_table" as CSV you can use:

sqlite> .headers on
sqlite> .mode csv
sqlite> .output model_data.csv
sqlite> SELECT * FROM my_table;

Then you can bulk load the CSVs using Postgres:

COPY table_name(col1, ...)
FROM 'model_data.csv'
DELIMITER ','
CSV HEADER;

Peewee can dump data in a variety of formats, so that's one option:

models = [User, Tweet]
for model in models:
    outfile = '%s.csv' % model._meta.table_name
    with open(outfile, 'w', newline='') as f:
        writer = csv.writer(f)
        row_iter = model.select().tuples().iterator()
        writer.writerows(row_iter)

Another option would be to use a Postgres foreign-data wrapper. Then connect to psql db, attach sqlite database using fdw, and do a straight copy from one to the other using psql shell.

https://github.com/pgspider/sqlite_fdw

coleifer
  • 24,887
  • 6
  • 60
  • 75