Connect insert to SQL server MSSQL via python from MySQL

It has been really a long long time no update. Sorry to subscribers. Useful sample tool here again!

Acutally, it’s quite intuitive to have simple connection and insert execution through this way.

1. Intuitive way (bad way)

which is a bad way because then you have to fix the other issues. I’ll just focus on “over 1000 rows insert” and “column names mapping”. The rest error key words were long tours to overcome. The sample only succeeded when you have less 1000 rows and have no problem with columns mapping.

import pymysql
import datetime
import pymssql

# To connect source MySQL database
    myconn = pymysql.connect(
        host='mysql_connection_host',
        user='your_account', 
        password = "your_password",
        db='prodcuct',
        )
    mycursor = myconn.cursor()

    read_my_sql = "select id, name, product from `your_database`.`your_table` "
    mycursor.execute(read_my_sql)
    output=mycursor.fetchall()

#output#
#(123, 'aaa', 'pen'), (456, 'bbb', 'apple'), …#

# To connect target MSSQL database
 msconn = pymssql.connect(
 host='your_host',
 user='your_account',
 password='your_password',
 database='your_database'
 )

inert_ms_sql= "insert into your_database.dbo.your_tabel (id, name, product) values "

for row in enumerate(output):
  text1=str(row)+","
  if idx==0:
    text2 += inert_ms_sql+text1
  elif idx==end_point:
    text2 += text1[:-1]+";"
  else:
    text2 += text1

#print(text2)#
#insert into your_database.dbo.your_tabel (id, name, product) values (123, 'aaa', 'pen'), (456, 'bbb', 'apple'), .... ;#

mscursor= msconn.cursor()
mscursor.execute(text2)
msconn.commit()
msconn.close()

OK. You might get pissed-off by the result above and after searching many many pages online, you still got nothing because those are total messes.

2. Simple way (better way)

It’s not only neat but also effective. Leveraging the library is the best way. Stop searching useless key words online. Use my solution because I’ve done the research online (stackoverflow, medium, discussion platform, issue reporting system… whatever).

import pymysql
import datetime
import sqlalchemy as sa
from sqlalchemy import create_engine
import pandas as pd



# To connect source MySQL database
    myconn = pymysql.connect(
        host='mysql_connection_host',
        user='your_account', 
        password = "your_password",
        db='prodcuct',
        )
    mycursor = myconn.cursor()

# To connect target MSSQL database
engine = sa.create_engine('mssql+pyodbc://your_account:your_password@mssql_connection_host:1433/mart?driver=ODBC+Driver+17+for+SQL+Server',fast_executemany=True)
# establishing the connection to the databse using engine as an interface
conn = engine.connect()

    keys =  'id', 'name', 'product'
    read_my_sql = "select id, name, product from `your_database`.`your_table` "
    mycursor.execute(read_my_sql)
    output=mycursor.fetchall()
    
#column management is here below#
    output_to_dataframe= pd.DataFrame(output,columns=list(keys))
    
    output_to_dataframe.to_sql('your_MSSQL_table', conn, if_exists='append',index=False,chunksize=200)
    conn.close()

searching buzz words for this issue:

sqlalchemy pyodbc pymssql pymysql insert mssql read mysql over 1000 rows Invalid column name SQLAlchemy 2.0 error message fast execute many bulk fast_executemany python lib create engine pandas dataframe