How to Connect SQLite and MySQL in Python Easily

SQLite offers lightweight, file-based storage ideal for mobile apps and prototypes, while MySQL provides robust server-based scalability for production environments. Developers often need to bridge these databases for data migration, synchronization, or hybrid architectures. Python’s standard libraries make this integration straightforward without third-party dependencies.

This connection enables seamless workflows, such as syncing local SQLite data from Android apps to a central MySQL server. Common use cases include offline-first mobile apps, ETL processes, and transitioning from development to deployment.

Prerequisites and Setup

Install MySQL Connector for Python via pip: pip install mysql-connector-python. SQLite comes built-in with Python’s sqlite3 module, requiring no extra setup. Ensure MySQL server runs locally or remotely with a database, user credentials, and necessary permissions.

Basic requirements include Python 3.8+, a MySQL instance (e.g., localhost:3306), and sample databases in both systems. Test connectivity first: for SQLite, use sqlite3.connect('example.db'); for MySQL, verify with mysql.connector.connect() parameters.

Reading from SQLite

Start by connecting to an SQLite database and fetching data. Here’s a code example to query a users table:

pythonimport sqlite3

# Connect to SQLite
sqlite_conn = sqlite3.connect('local_app.db')
cursor = sqlite_conn.cursor()
cursor.execute("SELECT id, name, email FROM users")
sqlite_data = cursor.fetchall()
sqlite_conn.close()

print("SQLite data:", sqlite_data)

This retrieves records efficiently for transfer. Handle errors with try-except blocks and use context managers (with statements) for auto-closing connections.

Connecting to MySQL

Establish a MySQL connection with host, user, password, and database details:

pythonimport mysql.connector

mysql_conn = mysql.connector.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='production_db'
)
cursor = mysql_conn.cursor()

Replace credentials accordingly. For remote servers, specify the IP and port. Always use parameterized queries to prevent SQL injection.

Data Synchronization Script

Combine connections to sync data from SQLite to MySQL. This script reads from SQLite, then inserts/updates MySQL:

pythonimport sqlite3
import mysql.connector
from mysql.connector import Error

try:
    # SQLite connection
    sqlite_conn = sqlite3.connect('local_app.db')
    sqlite_cursor = sqlite_conn.cursor()
    sqlite_cursor.execute("SELECT id, name, email FROM users")
    data = sqlite_cursor.fetchall()

    # MySQL connection
    mysql_conn = mysql.connector.connect(
        host='localhost', user='user', password='pass', database='main_db'
    )
    mysql_cursor = mysql_conn.cursor()

    for row in data:
        sql = "INSERT INTO users (id, name, email) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE name=%s, email=%s"
        mysql_cursor.execute(sql, (row[0], row[1], row[2], row[1], row[2]))

    mysql_conn.commit()
    print("Sync complete!")

except Error as e:
    print("Error:", e)
finally:
    if mysql_conn.is_connected():
        mysql_cursor.close()
        mysql_conn.close()
    sqlite_conn.close()

This handles inserts and updates using ON DUPLICATE KEY UPDATE. Extend for larger datasets with batch processing via executemany(). Total script length stays under 50 lines for simplicity.

Advanced Techniques

For two-way sync, implement timestamps or change tracking. Export SQLite schema to SQL dump, then import to MySQL:

python# Export SQLite to SQL file
with open('dump.sql', 'w') as f:
    for line in sqlite3.connect('db.db').iterdump():
        f.write(f'{line}\n')

# Import to MySQL
db = mysql.connector.connect(...)  # as above
cursor = db.cursor()
with open('dump.sql', 'r') as f:
    sql_file = f.read()
for statement in sql_file.split(';'):
    if statement.strip(): cursor.execute(statement)

Tools like MySQL Shell GUI support direct SQLite connections for VS Code users. Schedule syncs with cron jobs or APScheduler for automation.