How to read Excel data and store it in MySQL using Python3

This article mainly introduces the method of reading Excel data and storing it in MySQL with Python3. It has a certain reference value. Now I share it with you. Friends in need can refer to it

Python is A powerful tool for data analysis.

The first step in using Python for data analysis is to learn how to read various excel reports generated in daily work and store them in the data to facilitate subsequent data processing.

Here I would like to share with you how python3 uses xlrd to read excel, and use Python3 to operate the pymysql module to store data in Mysql. Friends in need should take a look.

Preface

pymsql is a module in Python that operates MySQL. Its usage is almost the same as MySQLdb. But currently pymysql supports python3.x and the latter does not support version 3.x.

Python mainly uses the two libraries xlrd and xlwt to operate excel, that is, xlrd is a library for reading excel, and xlwt is a library for writing excel.

Version

python >= 3.6

mysql >= 5.7.19

Installation

The installation of python and mysql will not be described in detail here. Friends who need it can Baidu by themselves

xlrd: You can also use pip to install it. Manually download the source code for installation, pip installation: pip install xlrd

pymysql: You can use pip installation or manually download the source code for installation, pip installation: pip install xlrd

##module

import xlrd
import pymysql
from datetime import datetime
from xlrd import xldate_as_tuple

Read excel

data = xlrd.open_workbook("D:/sales_data.xls") //读取D盘中名为sales_data的excel表格
table_one = data.sheet_by_index(0)      //根据sheet索引获取sheet的内容
table_two = data.sheet_by_index(1)

Create database connection

db = pymysql.connect("localhost", "root", "gaishi123", "sales_data", use_unicode=True, charset="utf8")

##gaishi123 is the password of mysql root, sales_data is the database name

for site in sites:
 # 遍历sheet1
 for nrows_one in range(1, int(table_one.nrows)):
  if table_one.cell_value(nrows_one, 0) == site:
   payday = table_one.cell_value(0, 8)
   date = datetime(*xldate_as_tuple(payday, 0))
   payday = date.strftime('%Y/%m/%d')         # 出票日期
   sales = float(table_one.cell_value(nrows_one, 1))     # 销量
   quantity_ticket = int(table_one.cell_value(nrows_one, 2))   # 票数
   rate_electronic = float(table_one.cell_value(nrows_one, 3))  # 电子直销占比
   sales_thanlastweek = float(table_one.cell_value(nrows_one, 4))  # 销量同比上周
   sales_thanlastyear = float(table_one.cell_value(nrows_one, 5))  # 销量同比去年
   break
 # 遍历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))     # 访问量
   rate_conversion = float(table_two.cell_value(nrows_two, 2))  # 转化率
   rate_paysuccess = float(table_two.cell_value(nrows_two, 3))  # 支付成功率
   session_thanlastweek = float(table_two.cell_value(nrows_two, 4)) # 访问量同比上周
   break
 # 将数据存入数据库
 sql = "insert into sales_data(SITE, PAYDAY, SALES, QUANTITY_TICKET, RATE_ELECTRONIC, SALES_THANLASTWEEK," \
   "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:
  # 使用 cursor() 方法创建一个游标对象 cursor
  cursor = db.cursor()
  cursor.execute(sql)
 except Exception as e:
  # 发生错误时回滚
  db.rollback()
  print(str(e))
 else:
  db.commit() # 事务提交
  print('事务处理成功')

Related recommendations:

How to read file names and generate lists using python

Read with python csv file and put the file into a list to explain the example

The above is the detailed content of How to read Excel data and store it in MySQL using Python3. For more information, please follow other related articles on the PHP Chinese website!