Migrating from SQLite to PostgreSQL: A Python Script Guide

Hey there, Darian Vance here. As the Senior DevOps Engineer at TechResolve, I’ve seen countless projects start with SQLite. It’s fantastic for prototyping and small applications. But eventually, the team hits a wall. Concurrency issues, performance bottlenecks, the need for robust user roles—it all points in one direction: it’s time to graduate to a production-grade database like PostgreSQL. I used to dread these migrations, treating them as a weekend-long chore. Now, I have a Python script that automates 90% of the work. Let me walk you through how to build your own, so you can get back to more interesting problems.

Prerequisites

Before we dive in, make sure you have the following ready:

• Python 3.6 or newer installed.
• Access to a running PostgreSQL server and the necessary credentials (user, password, database name).
• Your existing SQLite database file (e.g., app_data.db).
• The table schema in your PostgreSQL database should already be created. This script focuses on migrating data, not schema.

The Guide: From Local File to Production Server

Step 1: Setting Up Your Workspace

First things first, let’s get our project environment in order. I’ll skip the standard virtual environment setup commands, as you likely have your own workflow for that. Just make sure you’re working in an isolated environment to keep dependencies clean.

Once your environment is active, you’ll need a couple of Python libraries. You can install them using pip. We’ll need psycopg2-binary to connect to PostgreSQL and python-dotenv to handle our database credentials securely. I can’t stress this enough: never hardcode credentials in your scripts.

Step 2: Securely Store Your Credentials

In your project directory, create a file named config.env. This is where we’ll store the connection details for your PostgreSQL database. This keeps sensitive information out of your version control system.

# config.env
DB_NAME=your_db_name
DB_USER=your_db_user
DB_PASSWORD=your_secret_password
DB_HOST=localhost
DB_PORT=5432
SQLITE_DB_PATH=app_data.db

Step 3: The Python Script – Establishing Connections

Now, let’s write the Python script. We’ll call it migrate_data.py. The first part of the script will import the necessary libraries and load our environment variables to establish connections with both SQLite and PostgreSQL.

import sqlite3
import psycopg2
import os
from dotenv import load_dotenv

def get_db_connections():
    """Establishes and returns connections to SQLite and PostgreSQL."""
    load_dotenv('config.env')

    try:
        # Connect to SQLite
        sqlite_conn = sqlite3.connect(os.getenv('SQLITE_DB_PATH'))
        sqlite_conn.row_factory = sqlite3.Row  # Access columns by name
        print("Successfully connected to SQLite.")

        # Connect to PostgreSQL
        pg_conn = psycopg2.connect(
            dbname=os.getenv('DB_NAME'),
            user=os.getenv('DB_USER'),
            password=os.getenv('DB_PASSWORD'),
            host=os.getenv('DB_HOST'),
            port=os.getenv('DB_PORT')
        )
        print("Successfully connected to PostgreSQL.")
        
        return sqlite_conn, pg_conn

    except sqlite3.Error as e:
        print(f"Error connecting to SQLite: {e}")
        return None, None
    except psycopg2.Error as e:
        print(f"Error connecting to PostgreSQL: {e}")
        return None, None

if __name__ == '__main__':
    sqlite_connection, pg_connection = get_db_connections()
    if sqlite_connection and pg_connection:
        print("Connections ready for data transfer.")
        # We will add the migration logic here
        sqlite_connection.close()
        pg_connection.close()

Here, we’re using dotenv to load the variables from our config.env file. The sqlite3.Row factory is a nice touch that lets us access fetched data like a dictionary (e.g., row['username']), which is much more readable than using index numbers.

Step 4: The Core Logic – Fetching and Inserting Data

This is where the magic happens. We’ll fetch data from a source table in SQLite and insert it into a destination table in PostgreSQL. For this example, let’s assume we’re migrating a users table.

Pro Tip: Always process data in batches. Loading an entire multi-gigabyte table into memory is a recipe for disaster. Fetching a few thousand rows at a time is much more memory-efficient and resilient.

Let’s add the migration function to our script.

def migrate_table(sqlite_conn, pg_conn, table_name):
    """Fetches data from an SQLite table and inserts it into a PostgreSQL table."""
    sqlite_cursor = sqlite_conn.cursor()
    pg_cursor = pg_conn.cursor()

    # Fetch all data from the SQLite table
    # For large tables, consider using fetchmany() in a loop
    sqlite_cursor.execute(f"SELECT id, username, email, created_at FROM {table_name}")
    rows = sqlite_cursor.fetchall()
    
    if not rows:
        print(f"No data found in SQLite table '{table_name}'. Skipping.")
        return

    # Prepare the INSERT statement for PostgreSQL
    # Note: Column order must match the SELECT statement
    insert_query = f"INSERT INTO {table_name} (id, username, email, created_at) VALUES (%s, %s, %s, %s)"

    try:
        # Use executemany for efficient batch insertion
        pg_cursor.executemany(insert_query, rows)
        pg_conn.commit()
        print(f"Successfully migrated {len(rows)} rows to PostgreSQL table '{table_name}'.")

    except psycopg2.Error as e:
        print(f"An error occurred during migration: {e}")
        pg_conn.rollback() # Roll back the transaction on error
    finally:
        pg_cursor.close()
        sqlite_cursor.close()

# Update the main execution block
if __name__ == '__main__':
    sqlite_connection, pg_connection = get_db_connections()
    if sqlite_connection and pg_connection:
        # Specify the table you want to migrate
        migrate_table(sqlite_connection, pg_connection, 'users')
        
        # You can add more calls for other tables here
        # migrate_table(sqlite_connection, pg_connection, 'products')

        sqlite_connection.close()
        pg_connection.close()
        print("Migration process finished.")

In this block, we create two “cursors”—one for each database. The SQLite cursor fetches the data, and the PostgreSQL cursor executes the insert command. Using executemany is far more performant than looping and inserting one row at a time. Crucially, we wrap the insertion in a try...except block. If anything goes wrong, pg_conn.rollback() ensures we don’t end up with a partially migrated table, which can be a nightmare to debug.

Common Pitfalls: Where I Usually Mess Up

• Data Type Mismatches: This is the number one issue. SQLite is very flexible with types (you can store text in an integer column), but PostgreSQL is strict. A DATETIME in SQLite might need to be a TIMESTAMP WITH TIME ZONE in PostgreSQL. Make sure your PostgreSQL schema is an exact and correct match for your data.
• Primary Key Conflicts: If you’re running the script multiple times, you’ll hit primary key conflicts. I recommend either clearing the destination table before each run (TRUNCATE table_name;) or adding ON CONFLICT DO NOTHING to your PostgreSQL insert statement if you want to skip duplicates.
• Character Encoding: Most of the time, UTF-8 is standard. But I’ve been burned by legacy databases with weird encodings. It’s always worth double-checking if you see garbled text after migration.

Conclusion

And that’s the core of it. This script provides a solid, repeatable foundation for migrating your data. In my production setups, I often expand on this with more robust error logging, command-line arguments for table names, and better handling for data transformations. But with this guide, you’ve automated the most tedious part of the process. You’ve moved from a simple local database to a powerful, scalable one, and you did it without the manual copy-paste headache. Now, go enjoy that extra time you just saved.

Darian Vance

Lead Cloud Architect & DevOps Strategist

With over 12 years in system architecture and automation, Darian specializes in simplifying complex cloud infrastructures. An advocate for open-source solutions, he founded TechResolve to provide engineers with actionable, battle-tested troubleshooting guides and robust software alternatives.

🤖 Frequently Asked Questions