Extract, Transform and Load...Migrate any SQL Database in 4 Lines of Code. Read more here...
pip install etlalchemy
# On El Capitan:
### pip install --ignore-installed etlalchemy
# Also install the necessary DBAPI modules and SQLAlchemy dialects
# For example, for MySQL, you might use:
# pip install pymsql
from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
source = ETLAlchemySource("mssql+pyodbc://username:password@DSN_NAME")
target = ETLAlchemyTarget("mysql://username:password@hostname/db_name", drop_database=True)
target.addSource(source)
target.migrate()
Provide a list of tables to include/exclude in migration
from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
# Load ONLY the 'salaries' table
source = ETLAlchemySource("mysql://etlalchemy:etlalchemy@localhost/employees",
included_tables=["salaries"])
# Conversely, you could load ALL tables EXCEPT 'salaries'
# source = ETLAlchemySource("mysql://etlalchemy:etlalchemy@localhost/employees",\
# excluded_tables=["salaries"])
target = ETLAlchemyTarget("postgresql://etlalchemy:etlalchemy@localhost/test", drop_database=True)
target.addSource(source)
target.migrate()
Only migrate schema, or only Data, or only FKs, or only Indexes (or any combination of the 4!)
from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
source = ETLAlchemySource("mysql://etlalchemy:etlalchemy@localhost/employees")
target = ETLAlchemyTarget("postgresql://etlalchemy:etlalchemy@localhost/test", drop_database=True)
target.addSource(source)
# Note that each phase (schema, data, index, fk) is independent of all others,
# and can be run standalone, or in any combination. (Obviously you need a schema to send data, etc...)
target.migrate(migrate_fks=False, migrate_indexes=False, migrate_data=False, migrate_schema=True)
Skip columns and tables if they are empty
from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
# This will skip tables with no rows (or all empty rows), and ignore them during schema migration
# This will skip columns if they have all NULL values, and ignore them during schema migration
source = ETLAlchemySource("mysql://etlalchemy:etlalchemy@localhost/employees",\
skip_column_if_empty=True,\
skip_table_if_empty=True)
target = ETLAlchemyTarget("postgresql://etlalchemy:etlalchemy@localhost/test", drop_database=True)
target.addSource(source)
target.migrate()
Enable 'upserting' of data
from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
source = ETLAlchemySource("mysql://etlalchemy:etlalchemy@localhost/employees")
# This will leave the target DB as is, and if the tables being migrated from Source -> Target
# already exist on the Target, then rows will be updated based on PKs if they exist, or
# inserted if they DNE on the Target table.
target = ETLAlchemyTarget("postgresql://etlalchemy:etlalchemy@localhost/test", drop_database=False)
target.addSource(source)
target.migrate()
Alter schema (change column names, column types, table names, and Drop tables/columns)
from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
# See below for the simple structure of the .csv's for schema changes
source = ETLAlchemySource("mysql://etlalchemy:etlalchemy@localhost/employees",\
column_schema_transformation_file=os.getcwd() + "/transformations/column_mappings.csv",\
table_schema_transformation_file=os.getcwd() + "/transformations/table_mappings.csv")
target = ETLAlchemyTarget("postgresql://SeanH:Pats15Ball@localhost/test", drop_database=True)
target.addSource(source)
target.migrate()
column_mappings.csv | table_mappings.csv |
---|---|
Column Name,Table Name,New Column Name,New Column Type,Delete | Table Name,New Table Name,Delete |
last_name,employees,,,True | table_to_rename,new_table_name,False |
fired,employees,,Boolean,False | table_to_delete,,True |
birth_date,employees,dob,,False | departments,dept,False |
Rename any column which ends in a given 'suffix' (or skip the column during migration)
from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
# global_renamed_col_suffixes is useful to standardize column names across tables (like the date example below)
source = ETLAlchemySource("mysql://etlalchemy:etlalchemy@localhost/employees",\
global_ignored_col_suffixes=['drop_all_columns_that_end_in_this'],\
global_renamed_col_suffixes={'date': 'dt'},\ #i.e. "created_date -> created_dt"
)
target = ETLAlchemyTarget("postgresql://SeanH:Pats15Ball@localhost/test", drop_database=True)
target.addSource(source)
target.migrate()
We are always looking for contributors!
This project has its origins in solving the problem of migrating off of bulky, expensive enterprise-level databases. If the project has helped you to migrate off of these databases, and onto open-source RDBMS's, the best way to show your support is by opening Pull Requests and Issues.
Donations through Gratipay are welcome, but Pull Requests are better!
You can also support us via PayPal here.
For help installing cx_Oracle on a Mac (El Capitan + cx_Oracle = Misery), check out this blog post for help.
Run this tool from the same server that hosts your Target database to get maximum performance out of it.