需求:假如读取xlsx里E行和H里面的编码
然后按照这种方式写入,https://download.csdn.net/download/qq_31565379/15841157 SQLlite软件地址
思路就是:读取xlsx里面数据然后,用字典去储存,然后在Sql语句写入
#!/usr/bin/python
import sqlite3
import easygui # 增加窗口功能
from openpyxl.reader.excel import load_workbook
# 读取xlsl数据
DataDict = {} # 存放key
Datalist = [] # 存放val
oldw1 = "" # 保存上次数据
# 读取xlsl数据
DataDict = {} # 存放key
Datalist = [] # 存放val
oldw1 = "" # 保存上次数据
# wb = load_workbook(filename=r'C:\\Users\\admin\\Desktop\\附录AB.xlsx')
wb = load_workbook(filename = path) # path要处理一下
sheetnames = wb.get_sheet_names()
ws = wb.get_sheet_by_name(sheetnames[0])
for rx in range(2, ws.max_row + 1):
w1 = ws.cell(row=rx, column=5).value # 获取xlsx 第几列数据
w2 = ws.cell(row=rx, column=8).value
w3 = ws.cell(row=rx, column=9).value
if oldw1 != w1:
if len(Datalist) >= 1:
DataDict[oldw1] = Datalist
Datalist = [] # 注意这里不能clear,clear是清空这对象,上面因为key = datalist。
Datalist.append(w2)
oldw1 = w1
else :
Datalist.append(w2)
conn = sqlite3.connect('E:\\idata基础软件\\Release_x64\\modules\\idata_newGeo\\system\\NEOSURVEY_123\\NeoSurveyConfig.db')
c = conn.cursor()
conn.row_factory = sqlite3.Row
'''
# 读取db中的数据
# cursor = c.execute("select geoLayerName,Unitcode from SYS_GeoEnt")
for row in cursor:
print ("ID = ", row[0])
print ("NAME = ", row[1])
'''
# 写入db数据
# DELETE FROM SYS_GeoEnt # 删除表
for key,val in DataDict.items():
keystr = ''.join(key)
valstr = ','.join(val)
fistrval = valstr.split(',')
sql = "INSERT INTO [SYS_GeoEnt] (geoLayerName,UnitCode,UnitRootCode) VALUES('%s','%s','%s')"%(keystr,valstr,fistrval[0])
cursor = c.execute(sql)
conn.commit() #保存
conn.close()