Python3 How to read Excel data into MySQL

This article mainly introduces about Python3 reading Excel data into the MySQL method, has a certain reference value, now share to everyone, the need for friends can refer to

Python is a powerful tool for data analysis.

Using Python to Do data analysis, the first step is to learn how to read the daily work of generating various Excel reports and depositing data to facilitate subsequent data processing.

Here to share python3 how to use XLRD to read Excel, and use the Python3 Operation Pymysql module to save the data in MySQL, the need for friends to see it together.

Objective

Pymsql is a module that operates MySQL in Python and is used almost the same way as MySQLdb. However, currently Pymysql supports python3.x and the 3.x version is not supported by the latter.

The Python operation Excel mainly uses the XLRD and the XLWT two libraries, namely XLRD is reads the EXCEL,XLWT is writes the Excel the library.

Version

Python >= 3.6

MySQL >= 5.7.19

Installation

Python, MySQL installation here will not be described in detail, there is a need for friends Baidu

XLRD: Can be installed with PIP or manually download source installation, PIP installation: Pip install XLRD

Pymysql: Can be installed with PIP or manually download source installation, PIP installation: Pip install XLRD

Module

Import xlrdimport pymysqlfrom datetime import datetimefrom xlrd import xldate_as_tuple

Read Excel

data = Xlrd.open_workbook ("D:/sales_data.xls")//Read Excel table named Sales_data in D disk Table_one = Data.sheet_by_index (0)      // Get sheet content based on sheet index table_two = data.sheet_by_index (1)

Create a database connection

db = Pymysql.connect ("localhost", "root", "gaishi123", "Sales_data", Use_unicode=true, charset= "UTF8")

Gaishi123 is the root password of MySQL, Sales_data is the database name

For site in sites: # traverse Sheet1 for Nrows_one in range (1, int (table_one.nrows)): If Table_one.cell_value (nrows_one, 0) = = S Ite:payday = Table_one.cell_value (0, 8) date = DateTime (*xldate_as_tuple (payday, 0)) payday = Date.strftime ('%y/%m/ %d ') # ticketing Date sales = float (Table_one.cell_value (nrows_one, 1)) # Sales quantity_ticket = Int (table_one.cell_va Lue (Nrows_one, 2)) # Number of votes rate_electronic = float (Table_one.cell_value (Nrows_one, 3)) # e-direct ratio sales_thanlastweek = f Loat (Table_one.cell_value (Nrows_one, 4)) # Sales YoY last week sales_thanlastyear = float (Table_one.cell_value (Nrows_one, 5)) # Sales with   than last break # traverse Sheet2 for Nrows_two in range (1, int (table_two.nrows)): If Table_one.cell_value (nrows_two, 0) = = Site:  session = Int (Table_two.cell_value (nrows_two, 1)) # Traffic rate_conversion = float (Table_two.cell_value (nrows_two, 2)) # conversion Rate rate_paysuccess = float (Table_two.cell_value (Nrows_two, 3)) # payment success Rate Session_thanlastweek = float (table_two.cel L_value (Nrows_two, 4)) # Visit volume last week break # save data to database sql = "INSERT into Sales_data (SITE, PAYDAY, Sales, Quantity_ticket, Rate_electronic, Sales_tha Nlastweek, "\" Sales_thanlastyear, SESSION, Session_thanlastweek, Rate_conversion, rate_paysuccess) "\" VALUES ('%s '),   '%s ',%f,%d,%f,%f,%f,%d,%f,%f,%f) "%\ (Site, payday, sales, Quantity_ticket, Rate_electronic, Sales_thanlastweek, Sales_thanlastyear, Session, Session_ Thanlastweek, Rate_conversion, Rate_paysuccess) Try: # Create a Cursor object using the cursor () method cursor cursor = Db.cursor () cursor.execut E (SQL) except Exception as E: # rollback when error occurs db.rollback () print (str (e)) Else:db.commit () # Transaction commit print (' transacted success ')