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 get domain name information from a Domain using Python
- ModulenotFoundError: no module named 'debug_toolbar' -SOLUTION
- How to create superuser in django project hosted in cPanel without terminal
- CSS & images not loading in django admin | cpanel without terminal
- Could not build wheels for mysqlclient, which is required to install pyproject.toml-based projects
- How to sell domain name on Godaddy (2023)
- TemplateSyntaxError at / Could not parse the remainder: ' + 1' from 'forloop.counter0 + 1'
- urllib3 v2.0 only supports OpenSSL 1.1.1+, currently the 'ssl' module is compiled with 'OpenSSL 1.0
Related Article