---

SQLite to MySQL Migration Script 🚀

This Python script migrates data from an SQLite database to a MySQL database, handling data type conversions, foreign key constraints, and bulk inserts efficiently. It is particularly useful for Django migrations or any application that needs to transition from SQLite to MySQL.

🔹 Features

✔️ Automatically detects and orders tables for dependency handling
✔️ Converts SQLite data types to MySQL-compatible formats
✔️ Supports bulk inserts for faster migration (batch size: 1000 rows)
✔️ Uses config.ini for easy database configuration
✔️ Includes setup scripts for one-click installation
✔️ Error logging and rollback support
✔️ Foreign key handling for smooth data transfer

---

🚀 Installation & Setup

1️⃣ Prerequisites

• Python 3.6+ installed
• MySQL Server running (e.g., XAMPP or standalone MySQL)
• SQLite Database File (.sqlite3) available

2️⃣ Install Required Python Packages

pip install -r requirements.txt

3️⃣ Configure the Database Settings

Edit the config.ini file before running the script:

config.ini

[sqlite]
database = path/to/your/db.sqlite3

[mysql]
host = localhost
user = root
password = 
database = your_mysql_db
charset = utf8mb4

---

📌 Usage

1️⃣ Run the script manually

python migrate_sqlite_to_mysql.py

The script will:
✅ Connect to both SQLite and MySQL databases
✅ Identify all tables and their dependencies
✅ Convert and migrate data in bulk
✅ Handle errors and rollback if necessary

2️⃣ Quick Setup Using Provided Scripts

For Linux/macOS

chmod +x setup.sh
./setup.sh

For Windows

Simply double-click setup.bat or run:

setup.bat

---

🛠 Advanced Usage

Modify Table Order

The script follows an order for tables to ensure dependencies are met. Modify this part if necessary:

def get_table_order(sqlite_cursor) -> List[str]:
    sqlite_cursor.execute("""
        SELECT name FROM sqlite_master
        WHERE type='table'
        ORDER BY CASE
            WHEN name = 'django_migrations' THEN 1
            WHEN name = 'auth_group' THEN 2
            WHEN name = 'auth_user' THEN 3
            ELSE 4
        END, name
    """)

Change Batch Insert Size

By default, data is inserted in batches of 1000 rows. Modify this in the script:

batch_size = 1000  # Change to 500 or another value if needed

---

🛑 Common Issues & Fixes

❌ MySQL Error: Unknown collation 'utf8mb4_0900_ai_ci'

💡 Solution: Modify the MySQL collation to a compatible one (utf8mb4_general_ci).

❌ SQLite Error: Table not found

💡 Solution: Ensure the SQLite database file path is correct in config.ini.

❌ MySQL Integrity Constraint Violation

💡 Solution: Ensure foreign key constraints are correct and data is in the correct order.

---

🎯 Why Use This Script?

✅ Fully Automated – No need for manual SQL exports/imports
✅ Optimized for Large Databases – Supports bulk inserts
✅ Handles Dependencies & Data Type Conversions
✅ Error Logging – Automatically logs any issues for debugging

---

📂 Contents of the ZIP Package

sqlite_to_mysql_migration.zip
│── migrate_sqlite_to_mysql.py  # Main migration script
│── config.ini                  # Database configuration file
│── requirements.txt             # Dependencies list
│── README.md                    # Instructions and documentation
│── setup.sh                      # (Optional) Setup script for Linux/macOS
│── setup.bat                     # (Optional) Setup script for Windows

---

🤝 Contributing

Feel free to fork this repo and submit pull requests! 🚀

---

Made with ❤️ by Mr Nova 🚀

---