目录
一、业务场景
最近在工作中遇到这样一个问题:需要往物联网平台里面添加应用数据,格式如下:
因为要添加一百多个,所以总不能手工敲吧,效率极低。所以就想到了用execl组织下,代码一跑就完事了。
二、用到的模块和方法
模块:xlrd 这个模块用于读(read),pymysql用于连接数据库。延伸:xlwt(write)用于写
安装命令:pip install xlrd,pip install pymysql。如果报错的话,就去网上找canda的安装包,安装完就不用pip,直接在pycharm里面就能用,很方便。
xlrd主要方法
file = xlrd.open_workbook(file_path) #打开Excel文件
sheet_1 = file.sheet_by_index(0) #根据sheet页的排序选取sheet
sheet_2 = file.sheet_by_name('Sheet2') #通过文件名获得工作表,获取工作表2
row_content = sheet_1.row_values(3) #获取指定行的数据,返回列表,排序自0开始
row_number = sheet_1.nrows #获取有数据总行数
col_number = sheet_1.ncols #获取有数据总列数
pymsql主要方法
db = pymysql.connect('localhost','username','password','dbname') #建立数据库连接
cursor = db.cursor() # 创建一个游标对象cursor
# SQL插入语句
sql = "insert into DB_LoraServer.T_Application(ApplicationId, ApplicationName, ApplicationDesc)values( %s, %s, %s)"
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
db.commit()
except:
# 发生错误时回滚
db.rollback()
# 关闭数据库连接
db.close()
三、源代码
引子:代码千万行,规范第一行;代码不规范,同事二行泪。上次有写过Excel转txt就没写太多的注释,时间长了连自己都忘了。
所以,注释很重要。在这里特别鸣谢大学同学兼室友(下铺)蒋工蒋研究生的深夜技术支持。
# -*- coding: utf-8 -*-
# @Time : 2020/3/28 0028 9:55
# @Author : Liqiju
# @File : InsertApplicationData.py
# @Software : PyCharm
#导入读模块,数据库连接模块
import xlrd
import pymysql
# 打开文件,文件data.xls放在F盘下面
data = xlrd.open_workbook('F:\data.xls') #写成F:\\data.xls双斜杠也可以
# 查看工作表
sheetnames = data.sheet_names()
print("Sheets:" + str(sheetnames))
# 通过文件名获得工作表,获取工作表1
table = data.sheet_by_name('Sheet1')
print("总行数:" + str(table.nrows))
print("总列数:" + str(table.ncols))
# 建立数据库连接
db = pymysql.connect('localhost','root','root','DB_LoraServer')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# SQL 插入语句
sql = "insert into DB_LoraServer.T_Application(ApplicationId, ApplicationName, ApplicationDesc)values( %s, %s, %s)"
# 循环取每一行的数据,然后赋值。注意range范围,1开始代表第一行不要
# 如果是第一行就是数据的话就写0
for i in range(1, table.nrows):
ApplicationId = table.cell(i, 0).value
ApplicationName = table.cell(i, 1).value
ApplicationDesc = table.cell(i, 2).value
values = (int(ApplicationId), str(ApplicationName), str(ApplicationDesc))
cursor.execute(sql, values) #执行
print(values)
#关闭游标,提交,关闭数据库连接
#如果没有这些关闭操作,执行后在数据库中查看不到数据
#关闭游标
cursor.close()
#提交
db.commit()
# 关闭数据库连接
db.close()
运行结果:
pycharm:
Navicat for MySQL:
物联网平台:
四、总结优化
1、注意要关闭游标和数据库,要不然运行成功但是查不到数据。
2、range()开区间,第一个参数为0,那就从第一行开始读,参数为1,就从第二行开始读。
3、数据库如果把第一个字段设置为primary key的话,如果数据库里面有相同的ID值,就会报错。所以可以后面可以做优化ID重复的不添加,抛异常,打印一个日志。
4、还有就是数据库表字段很多的时候,再加个循环赋值。
5、一般数据库表会加一个创建时间啊、更新时间什么的。这个调用date和time函数就可以了。