Prudent Devs

Migrating data from Postgresql to Sqlite using Python

Migrate data from postgres to sqlite using python

I had to migrate data from Postgresql to Sqlite. Number of tables and number of rows in each of the tables were not so large, but not so small either—4 to 5 tables, each having about 250 rows.

I assumed that there would be a migration tool available. There was none. Then I thought I will try sql to sql migration. Sql to sql migration should be easy. At least that is what I thought. This too failed.

Being a programmer, I decided to write a program to migrate. First I thought I will use the new and shiny toy in the town—Swift. There are only few postgresql drivers available and none of them worked. All of them gave errors with Swift Package Managers.

When we run out of all options, then you turn wise!

I wrote a python program to migrate data from postgresql to sqlite. Wrote the program and migrated the data within an hour. The power of python!

import sys
import psycopg2
import sqlite3

# create a sqlite db
sqliteConnection = sqlite3.connect("olai.db")
sqliteCursor = sqliteConnection.cursor()
# ref:
sqliteConnection.text_factory = lambda x: unicode(x, 'utf-8', 'ignore')

# connect to postgresql
pgConnectString = "host='localhost' dbname='postgres' user='postgres' password=''"
pgCursor = pgConnection.cursor()

# select from the table
pgCursor.execute("SELECT * from entry")
rows = pgCursor.fetchall()

# loop and insert into sqlite
for row in rows:
    sqliteCursor.execute("INSERT INTO ENTRY (id, title ) VALUES (:id, :title)", {"id": row[0], "title": row[1]})

# close all connections
Published On:
Under: #code , #python , #sql
Sign up for my newsletter