Insert excel file data into MySQL database in 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))'
cur.execute(sqlSentence1)
# 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
cur.close()
con.commit()
con.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

VIDEO GUIDE: