In this tutorial I will show you how to save excel file data in to MySQL database using Python.
CODE:
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 documentdf = 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 documentlength = len(df)for i in range ( 0 , length ):# data conversion character typerecord = tuple(df.loc[i])# insert table datasqlSentence = "INSERT INTO Employee (Id,First_Name, Last_Name, Gender, Country) VALUES ( %s , %s , %s , %s , %s)"# Fill the empty value, or missingsqlSentence = sqlSentence.replace('nan', 'null').replace('None', 'null').replace('none', 'null')# Print sequentially according to the loopcur.executemany(sqlSentence, [record])# end, closecur.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:
Post your comments / questions
Recent Article
- How to fix HAXM is not installed |in Android Studio
- How to fix CMOS Checksum Error in Computer or Laptop | SOLVED
- Reactivating windows after a Hardware change on PC or Laptop
- FIXED: Windows reported that the hardware of your device has changed. Error code :0xc004F211
- "redirect" is not defined pylance("reportUndefinedVariable)
- This action cannot be completed because the file is open in SQL Server(SQLEXPRESS) - FIXED
- Unicode error 'unicodeescape' codec can't decode bytes in position 2-3: truncated UXXXXXXXX escape
- Could not find the 'angular-devkit/build-angular:dev-server' builder's node package | Angular Error
Related Article