实现效果:从指定目录下读取新增的点位表并存入MySQL数据库
阅读提示:请直接将程序复制到你的IDE下运行,并根据报错信息进行修改。
1、读取Excel
读取Excel文件有很多种方法,比较简单的可以用openpyxl模块
1.1、安装openpyxl模块
打开终端,输入
pip install openpyxl
即可安装,如果使用pycharm,可以进入Preferences(Win下应该是Setting)-Project:文件名.py-Project Interpreter,然后点击底部的+号进入Available Packages,在搜索框输入"openpyxl",选中搜索到的项目,点击底部的Install Package即可。
1.2、导入模块与打开Excel
在该python程序目录下新建一个Excel,我们可以起名为“测试读取Excel.xlsx”,然后在Sheet1下的A1表格内任意输入一段字符,如123,之后在python程序中输入如下内容:
import openpyxl
# 打开excel文件,获取工作簿对象
wb1 = openpyxl.load_workbook('测试读取Excel.xlsx')
sheets = wb1.sheetnames
print('包含工作表如下:', sheets, type(sheets))
sheet1 = wb1['Sheet1']
print('sheet1坐标为(1,1)的内容如下:', sheet1.cell(1, 1).value)
输出:
包含工作表如下: ['Sheet1', 'Sheet2', 'Sheet3'] <class 'list'>
sheet1坐标为(1,1)的内容如下: 123
Process finished with exit code 0
可以看到打印了Excel表格的表单和sheet1中A1的内容。
2、定时读取新数据
需求上要求表格内容更新就要读取到并存入数据库,所以我们要判断文件的修改时间,每次只读取最新修改的Excel文件
2.1、获取最新的文件
# -*- coding:UTF-8 -*-
import datetime
import os
import os.path
path_name = "../1、读取Excel/"
file_list = os.listdir(path_name)
def get_file_time(file_name):
if os.path.isdir(path_name + file_name):
return 0
else:
return os.path.getmtime(path_name + file_name)
# 获取最后改动的文件名
file_list.sort(key=get_file_time)
# 获取文件时间
d = datetime.datetime.fromtimestamp(os.path.getmtime(path_name + file_list[-1]))
print('最后改动的文件是:' + file_list[-1] + ",时间:" + d.strftime("%Y年%m月%d日 %H时%M分%S秒"))
说明:
1、“…/1、读取Excel/”是指上一层目录下的《1、读取Excel》文件夹,由于我这个程序放在和Excel表格不同的文件夹下,所以通过相对目录去定位文件夹,如果你Excel文件和程序放在同一个目录下,用“./”即可,也可使用绝对路径。
2、os.listdir会获取该目录下的所有文件和文件夹,并返回一个列表
3、get_file_time函数的作用,首先判断该文件是否为文件夹,如果是文件夹则返回0,否则返回文件的修改时间
4、列表的sort方法用于排序列表,key参数用于指定排序的方式,要求传入函数。具体操作步骤为,首先将list里面每个数据都传入函数,得到一组返回值,然后将原列表按照新的返回值进行排序。举个例子
def f1(l1):
return len(l1)
aList = ['Google', 'Firefox', '360', 'QQ']
aList.sort(key=f1) # 按长度排序
print(aList)
输出:
['QQ', '360', 'Google', 'Firefox']
5、file_list[-1]是指按照文件修改时间排列后,列表的最后一个数据。
6、fromtimestamp将时间戳格式转化为字符串日期时间,如1570858824.155304转化为2019-10-12 13:40:24.155304,然后strftime将字符串时间转化为自定义排列的格式。
2.2、获取最新的Excel
通过len(file_list)得出文件列表的长度,然后从最后一个逐渐往前遍历,每个将文件名与“.xlsx”匹配,如果包含子串关系则不返回None,将该文件名传给new_file后结束循环,如果该文件不是xlsx格式,则继续寻找下一个最新文件。
import re
import os.path
import os
import datetime
path_name = "../1、读取Excel/"
def get_file_time(file_name):
if os.path.isdir(path_name + file_name):
return 0
else:
return os.path.getmtime(path_name + file_name)
def find_new_file():
file_list = os.listdir(path_name)
# 按照时间排序文件列表
file_list.sort(key=get_file_time)
new_file = file_list[-1]
for i in range(len(file_list), 0, -1):
if re.search(".xlsx", file_list[i - 1]) is not None:
new_file = file_list[i - 1]
break
# 获取文件改动时间
d = datetime.datetime.fromtimestamp(os.path.getmtime(path_name + new_file))
print('最后改动的文件是:' + new_file + ",时间:" + d.strftime("%Y年%m月%d日 %H时%M分%S秒"))
return path_name + new_file
find_new_file()
输出:
最后改动的文件是:测试读取Excel.xlsx,时间:2019年10月08日 16时19分34秒
Process finished with exit code 0
3、存至MySQL
批量存入MySQL,由于MySQL需要元组数据,所以我们需要先将Excel数据转为元组,然后以列表组织元组,批量导入。
3.1、Excel转化为元组列表
1、先定义一个元组列表,列表的长度按照Excel的行来定义,列表里存入元组,每个元组存放一行数据
2、一般第一行为表头,不需要读,所以列表长度为行数-1
3、从sheet第二行开始读,sheet.cell函数需要从1开始,假如sheet有100行,sheet1_max_row=100,下标为1-100,而list是从0开始,下标0-99,由于第一行不读,将所以需要sheet第2行放入list第0行,将sheet第100行放入list第98行,for循环是读不到最后一个数的,所以循环终止为sheet1_max_row + 1=101,当i1=100时读到最后一行,然后填入list98行,所以填入list时需要-2
4、元组内元素不可被修改,但可以添加元素,所以需要在内循环前先插入第一个元素,后续通过不断的添加补充后续数据
import openpyxl
def read_excel(file_name):
# 打开excel文件,获取工作簿对象
wb1 = openpyxl.load_workbook(file_name)
# 从工作薄中获取表单(sheet)对象
sheets = wb1.sheetnames
print('该Excel文件包含工作表如下:', sheets, type(sheets))
sheet1 = wb1.active
return sheet1
# 将Excel数据转化为元组列表,核心算法之一
def excel_to_tuple_list(sheet1):
sheet1_max_row = sheet1.max_row
sheet1_max_column = sheet1.max_column
print("最大行:{}".format(sheet1_max_row))
print("最大列:{}".format(sheet1_max_column))
# 定义一个元组列表,用来存放Excel数据
# 一行就是一个元组,100行row就是100个元组,第一行不读,故-1
# 一个元组有7个元素
tuple_list_excel_data = [0 for i2 in range(sheet1_max_row-1)]
# 首行表头忽略,从第二行开始读
for i1 in range(2, sheet1_max_row + 1):
# 将每行第一个数据放入元组
tuple1 = (sheet1.cell(i1, 1).value,)
# 从每行第二个数开始
for j1 in range(2, sheet1_max_column + 1):
# 之后每次添加一个元素,直到该行结束
tuple2 = (sheet1.cell(i1, j1).value,)
tuple1 = tuple1 + tuple2
# 处理完毕后将该元组放入列表,列表从0开始
tuple_list_excel_data[i1 - 2] = tuple1
# 检查列表数据是否完善
for tuple3 in tuple_list_excel_data:
print(tuple3)
return tuple_list_excel_data
# 文件名可以用find_new_file获取
excel_to_tuple_list(read_excel("./测试Excel.xlsx"))
执行上述代码即可实现读取Excel并转化为元组列表,并显示出来。
3.2、创建数据库
3.2.1、安装MySQL
以下仅针对MacOS,Windows请自行查找教程。
1、由于官网速度特别慢,建议找其他网站下载MySQL,比如
https://www.cr173.com/mac/430352.html
2、安装时Configuration有一步要选择Use Legacy Password Encryption,否则会跟低版本不兼容
3、用户名默认为root,同时配置密码
4、进入系统偏好设置,在最底下选择MySQL
5、点击Stars MySQL Server即可
6、使用Navicat for MySQL或Sequel Pro登录MySQL数据库,输入之前配置即可,host可输入127.0.01或localhost
3.2.2、创建数据库
1、首先需要安装mysql.connector,可以使用
pip install mysql-connector
安装,如果使用pycharm,也可以进入Preferences(Win下应该是Setting)-Project:文件名.py-Project Interpreter,然后点击底部的+号进入Available Packages,在搜索框输入"mysql.connector",选中搜索到的项目,点击底部的Install Package即可。
2、使用以下代码即可创建数据库
import mysql.connector
db1 = mysql.connector.connect(
host="localhost",
user="root",
passwd="12345678",
)
cursor1 = db1.cursor()
# 创建数据库名称有规范,不能用#和.等符号,可以用下划线
create_db_name = "test_db1"
cursor1.execute("CREATE DATABASE " + create_db_name)
# 重复创建会报错
运行成功后到Navicat刷新一下即可看到新创建的数据库
3.2.3、创建表单和主键
使用以下代码即可创建表单和主键,表单名称不可纯数字。
通过connect方法连接数据库,通过cursor方法获取对数据库的操作对象,然后通过execute方法执行相关语句。
import mysql.connector
db1 = mysql.connector.connect(
host="localhost",
user="root",
passwd="12345678",
database="test_db1"
)
cursor1 = db1.cursor()
'''
创建表的时候我们一般都会设置一个主键(PRIMARY KEY),主键内容不可重复,否则报错
'''
# 不可以纯数字
table_name = "table_name1"
create_table = ('CREATE TABLE ' + table_name +
' (' +
'data varchar(255) PRIMARY KEY, ' +
'time varchar(255), ' +
'order_number varchar(255), ' +
'start_time varchar(255), ' +
'stop_time varchar(255), ' +
'time_quantum varchar(255), ' +
'type varchar(255)' +
')')
cursor1.execute(create_table)
cursor1.execute("SHOW TABLES")
for x in cursor1:
print(x)
创建完毕后刷新,即可在Navicat看到这个数据库下新增的表单和7个字段。
3.3、插入数据
使用以下代码可以实现将Excel文件存至MySQL。
批量插入数据使用executemany方法,注意该方法的第一个参数指定插入的格式,第二个接收数据,只能接收元组格式,所以我在一开始将数据转化为了元组列表以方便批量插入。
使用IGNORE关键字可以忽略主键重复的数据,避免主键重复插入报错。
import openpyxl
import mysql.connector
def read_excel(file_name):
# 打开excel文件,获取工作簿对象
wb1 = openpyxl.load_workbook(file_name)
# 从工作薄中获取表单(sheet)对象
sheets = wb1.sheetnames
print('该Excel文件包含工作表如下:', sheets, type(sheets))
sheet1 = wb1.active
return sheet1
# 将Excel数据转化为元组列表,核心算法之一
def excel_to_tuple_list(sheet1):
sheet1_max_row = sheet1.max_row
sheet1_max_column = sheet1.max_column
print("最大行:{}".format(sheet1_max_row))
print("最大列:{}".format(sheet1_max_column))
# 定义一个元组列表,用来存放Excel数据
# 一行就是一个元组,100行row就是100个元组,第一行不读,故-1
# 一个元组有7个元素
tuple_list_excel_data = [0 for i2 in range(sheet1_max_row-1)]
# 首行表头忽略,从第二行开始读
for i1 in range(2, sheet1_max_row + 1):
# 将每行第一个数据放入元组
tuple1 = (sheet1.cell(i1, 1).value,)
# 从每行第二个数开始
for j1 in range(2, sheet1_max_column + 1):
# 之后每次添加一个元素,直到该行结束
tuple2 = (sheet1.cell(i1, j1).value,)
tuple1 = tuple1 + tuple2
# 处理完毕后将该元组放入列表,列表从0开始
tuple_list_excel_data[i1 - 2] = tuple1
# 检查列表数据是否完善
for tuple3 in tuple_list_excel_data:
print(tuple3)
return tuple_list_excel_data
def send_mysql(tuple_list):
# 连接到MySQL
# 这里要修改成自己的MySQL地址
db1 = mysql.connector.connect(
host="localhost",
user="root",
passwd="12345678",
database="test_db1"
)
cursor1 = db1.cursor()
cursor1.execute("SHOW TABLES")
for x in cursor1:
print('将插入MySQL表:', x)
sql = ("INSERT IGNORE INTO 204_207 " +
"(data, time, order_number, start_time, stop_time, time_quantum, type) " +
"VALUES (%s, %s, %s, %s, %s, %s, %s)")
val = tuple_list
cursor1.executemany(sql, val)
db1.commit() # 数据表内容有更新,必须使用到该语句
print(cursor1.rowcount, "记录插入成功。\n")
# 文件名可以用find_new_file获取
send_mysql(excel_to_tuple_list(read_excel("./测试Excel.xlsx")))
4、定时读取与按钮中止
后面部分比较简单,所以将两部分合并在一起,以下是完整代码。
1、定义一个类A用于跑线程,线程中使用true循环实现不断读取,当检测到i1为0时结束循环
2、使用tkinter模块创建一个窗口,定义该窗口的长宽和坐标,以及对应的文字和按钮
3、不同的按钮对应不同的功能,分别对应启动读取和停止读取
# -*- coding:UTF-8 -*-
import datetime
import os
import os.path
import openpyxl
import mysql.connector
import re
import time
import tkinter
import threading
# 此处需要改成点位表所在路径,可使用绝对路径
path_name = "./Excel文件目录/"
# 全局变量用于按钮操作停止线程读取
i1 = 1
def get_file_time(file_name):
if os.path.isdir(path_name + file_name):
return 0
else:
return os.path.getmtime(path_name + file_name)
def find_new_file():
file_list = os.listdir(path_name)
# 按照时间排序文件列表
file_list.sort(key=get_file_time)
new_file = file_list[-1]
for i in range(len(file_list), 0, -1):
if re.search(".xlsx", file_list[i - 1]) is not None:
new_file = file_list[i - 1]
break
# 获取文件改动时间
d = datetime.datetime.fromtimestamp(os.path.getmtime(path_name + new_file))
print('最后改动的文件是:' + new_file + ",时间:" + d.strftime("%Y年%m月%d日 %H时%M分%S秒"))
return path_name + new_file
def read_excel(file_name):
# 打开excel文件,获取工作簿对象
wb1 = openpyxl.load_workbook(file_name)
# 从工作薄中获取表单(sheet)对象
sheets = wb1.sheetnames
print('该Excel文件包含工作表如下:', sheets, type(sheets))
sheet1 = wb1.active
return sheet1
# 将Excel数据转化为元组列表,核心算法之一
def excel_to_tuple_list(sheet1):
sheet1_max_row = sheet1.max_row
sheet1_max_column = sheet1.max_column
print("最大行:{}".format(sheet1_max_row))
print("最大列:{}".format(sheet1_max_column))
# 定义一个元组列表,用来存放Excel数据
# 一行就是一个元组,100行row就是100个元组,第一行不读,故-1
# 一个元组有7个元素
tuple_list_excel_data = [0 for i2 in range(sheet1_max_row-1)]
# 首行表头忽略,从第二行开始读
for i1 in range(2, sheet1_max_row + 1):
# 将每行第一个数据放入元组
tuple1 = (sheet1.cell(i1, 1).value,)
# 从每行第二个数开始
for j1 in range(2, sheet1_max_column + 1):
# 之后每次添加一个元素,直到该行结束
tuple2 = (sheet1.cell(i1, j1).value,)
tuple1 = tuple1 + tuple2
# 处理完毕后将该元组放入列表,列表从0开始
tuple_list_excel_data[i1 - 2] = tuple1
# 检查列表数据是否完善
# for tuple3 in tuple_list_excel_data:
# print(tuple3)
return tuple_list_excel_data
def send_mysql(tuple_list):
# 连接到MySQL
db1 = mysql.connector.connect(
host="localhost",
user="root",
passwd="12345678",
database='bm'
)
cursor1 = db1.cursor()
cursor1.execute("SHOW TABLES")
for x in cursor1:
print('将插入MySQL表:', x)
sql = ("INSERT IGNORE INTO 204_207 " +
"(data, time, order_number, start_time, stop_time, time_quantum, type) " +
"VALUES (%s, %s, %s, %s, %s, %s, %s)")
val = tuple_list
cursor1.executemany(sql, val)
db1.commit() # 数据表内容有更新,必须使用到该语句
print(cursor1.rowcount, "记录插入成功。\n")
class A(threading.Thread):
def __init__(self):
threading.Thread.__init__(self)
def run(self):
global i1
i1 = 1
while True:
send_mysql(excel_to_tuple_list(read_excel(find_new_file())))
if i1 == 0:
break
time.sleep(950)
def start():
label['text'] = '读取中。。。'
a = A()
a.start()
def stop():
label['text'] = '停止读取'
global i1
i1 = 0
window = tkinter.Tk(className='读取Excel写入mysql')
# "窗口宽x窗口高+窗口位于屏幕x轴+窗口位于屏幕y轴"
window.geometry('300x100+400+300')
label = tkinter.Label(window)
label['text'] = '点击启动开始读取目录下最新Excel文件'
label.pack()
button = tkinter.Button(window)
button['text'] = '启动'
button['command'] = start
button.pack()
button2 = tkinter.Button(window)
button2['text'] = '停止'
button2['command'] = stop
button2.pack()
window.mainloop()