Insert excel file data into MySQL database in Python

Insert excel file data into MySQL database in Python, someone asked me to explain?

In this tutorial I will show you how to save excel file data in to MySQL database using Python.


import pymysql
import pandas as pd
con = pymysql.connect(host='localhost', user='root',
                      passwd='password', charset='utf8')
cur = con.cursor()
cur.execute('create database EmpyloyeeDB character set utf8')
cur.execute('use EmpyloyeeDB')
# get document
df = pd.read_excel ("data/file_emp1.xls")
sqlSentence1 = 'create table Employee(Id int,First_Name VARCHAR(20), Last_Name VARCHAR(20), Gender VARCHAR(10), Country VARCHAR(50))'
# Get the length of the document
length = len(df)
for  i  in  range ( 0 , length ):
      # data conversion character type
      record = tuple(df.loc[i])
      # insert table data
      sqlSentence = "INSERT INTO Employee (Id,First_Name, Last_Name, Gender, Country) VALUES ( %s , %s , %s , %s , %s)"
      # Fill the empty value, or missing
      sqlSentence = sqlSentence.replace('nan', 'null').replace('None', 'null').replace('none', 'null')
      # Print sequentially according to the loop
      cur.executemany(sqlSentence, [record])
# end, close

After pasting the code you will get this below error.

"import pymysql could not be resolved from source pylance" To resolve this error you need to install pymsql.

pip install pymsql


Post your comments / questions