MysqlMigratorPostgreSQL
pip install mysqlmigratorpostgresql
Introduction
MysqlMigratorPostgreSQL is a Python library designed to simplify the migration of entire databases from MySQL to PostgreSQL. This tool is ideal for software engineers seeking an automated solution to transfer data and table structures between these two relational database management systems.
The library offers:
- Easy connection to MySQL and PostgreSQL servers.
- Automated table migration, including columns and data types.
- Efficient connection and error handling.
- A modular and extensible design.
Reserved Word Dictionary
During migration, certain data types in MySQL do not have an exact equivalent in PostgreSQL. Below is a summary of the conversions performed by the library:
INT | INTEGER | Fixed-size integers. |
VARCHAR(n) | TEXT | PostgreSQL does not require strict text limits. |
TEXT | TEXT | Maintains the same type for long text fields. |
FLOAT | REAL | Floating-point values. |
DOUBLE | DOUBLE PRECISION | Higher precision floating-point values in PostgreSQL. |
DATE | DATE | Standard date in YYYY-MM-DD format. |
DATETIME | TIMESTAMP | Date and time with timezone. |
TINYINT(1) | BOOLEAN | Interpreted as a logical value (TRUE or FALSE ). |
ENUM | TEXT | Converted to text since PostgreSQL does not directly support the ENUM type. |
Code Details
General Structure
The library follows a modular approach. Each functionality is defined in a specific file:
connect_mysql.py
: Handles connection to a MySQL server.
connect_postgresql.py
: Handles connection to a PostgreSQL server.
migrator.py
: Orchestrates the migration of tables and data.
Changes in Data Types
The logic to convert MySQL data types to PostgreSQL is located in migrator.py
. Here is the key code fragment with explanation:
if "int" in column_type:
postgres_type = "INTEGER"
elif "varchar" in column_type or "text" in column_type:
postgres_type = "TEXT"
elif "float" in column_type or "double" in column_type:
postgres_type = "REAL"
elif "date" in column_type:
postgres_type = "DATE"
elif "tinyint(1)" in column_type:
postgres_type = "BOOLEAN"
else:
postgres_type = "TEXT"
Example Code
Here is a functional example demonstrating how to use the library to migrate all tables from a MySQL database to PostgreSQL:
from mysqlmigratorpostgresql import MysqlMigratorPostgreSQL
migrator = MysqlMigratorPostgreSQL()
migrator.connect_mysql(
host="localhost",
port=3306,
user="root",
password="password",
database="databases_name"
)
migrator.connect_postgresql(
host="localhost",
port=5432,
user="postgres",
password="password",
database="databases_name"
)
migrator.migrate_all()
migrator.close_connections()