[Python] Taking Data from On-Premise MSSQL to On-Premise MySQL

on

** WORK IN PROGRESS / WORKING NOTES **

Overview

I have simplified my task as much as possible (there is much more to it that this, but if we can get this story done, we'll be well on our way to achieving the epic 😉.)

I need to collect time-series data from one table in an on-premise MSSQL server. And transfer this data to an on-premise MySQL server. Of course, I don't want to be constantly grabbing everything from MSSQL and dumping it all into MySQL, need to be a bit cleverer than that. Also, my tool of choice for this endeavour is Python.

*The dataset is monthly.

Designing The Flow: Iteration 1

Keeping it as simple as possible for this iteration.

  1. Connect to MSSQL and acquire counts of data per month*. Order Ascending (from oldest month to latest month.)
  2. Connect to MySQL and acquire counts of data per month*. Order Ascending (from oldest month to latest month.)
  3. Going from oldest month to latest month (from MSSQL), per month:
    1. If MySQL count is 0 for that month, collect data from MSSQL and insert into MySQL.
    2. Otherwise do nothing.

The Nitty Gritty (the Mechanics/the Python Code)

How to Collect from MSSQL using Python:

  • import pyodbc
  • server = 'tcp:SERVERFQDN'
  • database = 'DATABASENAME'
  • username = 'USERNAME'
  • password = 'PASSWORD'
  • cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password)
  • cursor = cnxn.cursor()
  • cursor.execute("SELECT InvoiceMonth,count(*) FROM TABLENAME GROUP BY InvoiceMonth ORDER BY InvoiceMonth;")
  • mssqlCounts = cursor.fetchall

How to Collect for MySQL using Python:

  • import mysql.connector
  • mydb = mysql.connector.connect(
  •   host="SERVERFQDN",
  •   user="USERNAME",
  •   password="PASSWORD",
  •   database="DATABASE"
  • )
  • mycursor = mydb.cursor()
  • mycursor.execute("SELECT InvoiceMonth,count(*) FROM TABLENAME GROUP BY InvoiceMonth"
  • myresult = mycursor.fetchall()

Going through month by month finding if we need to update (i.e. if we are missing figures) and updating as required.

To be continued...

Other Notes

Upgrading pip, MySQL driver, MSSQL driver:

  • python -m pip install --upgrade pip
  • python -m pip install mysql-connector-python
  • python -m pip install pyodbc

Comments

Post a Comment