Using python script for data ETL
So you need to perform simple Extract Transform Load (ETL) from different databases to a data warehouse to perform some data aggregation for business intelligence. There are alot of ETL products out there which you felt is overkilled for your simple use case.
Here I am going to walk you through on how to Extract data from mysql, sql-server and firebird, Transform the data and Load them into sql-server (data warehouse) using python 3.6.
Before we begin, let’s setup our project directory:
python_etl
|__main.py
|__db_credentials.py
|__variables.py
|__sql_queries.py
|__etl.pyAside from having sql-server, mysql and firebird installed, we also need the below 3 python modules, run pip install [module_name] and we are ready to go.
- mysql-connector-python: connecting to mysql
- pyodbc: connecting to sql-server
- fdb: connecting to firebird
Setup Database Credentials and Variables
Setup a variable to store the data warehouse database name in variables.py
datawarehouse_name = 'your_datawarehouse_name'Setup all your source databases and target database connection strings and credentials in your db_credentials.py as shown below. Store the config as list so we can iterate through many databases later.
SQL Queries
In your sql_queries.py, here is where you will store all your sql queries for extracting from source databases and loading into your target database (data warehouse)
Get Martin Yung’s stories in your inbox
Join Medium for free to get updates from this writer.
As we are dealing with different data platforms, we can use different syntax for each data platform by separate the queries according to the database type.
Extract Transform Load
In your etl.py import the following python modules and variables to get started.
# python modules
import mysql.connector
import pyodbc
import fdb# variables
from variables import datawarehouse_name
Here we will have two methods, etl() and etl_process() . etl_process() is the method to establish database source connection according to the database platform, and call the etl() method.
in etl() method, first it will run the extract query, store the sql data in the variable data, and insert it into target database which is your data warehouse. Transformation of data can be done by manipulating the data variable which is of type tuple.
Putting it All Together
In our main.py we can loop through all credentials and perform the etl for all databases.
Import all relevant variables and methods:
# variables
from db_credentials import datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config
from sql_queries import fbd_queries, sqlserver_queries, mysql_queries
from variables import *# methods
from etl import etl_process
In your terminal hit python main.py and voila, you have just build a etl using pure python script.
Do hit me up with any questions or best practices by leaving a comment or on Twitter @martin_yce, Happy coding!









