一个读写Excel以及读写数据库的接口,可以实现脱机操作:
读取数据:规定Excel第一行为Title,读取后的数据形成一个字典序列([{},{},{}]),每一个字典为title和对应行的value。数据库同理。
写数据:以一个二维列表维数据写入Excel表中或数据库中,第一行为Title,对应Excel的第一行,数据库的字段名。
需要注意的是:对于一个Excel文件,有不同的表格,对应到数据库,则为,一个数据库(database),有不同的表格(table)。
import abc
class RW(object):
@abc.abstractmethod
def read(self,path,name):
pass
@abc.abstractmethod
def write(self, path, name,value):
pass
import xlrd
from xlrd import xldate_as_tuple
import datetime
import openpyxl
class ExcelData(RW):
# 定义一个读取excel表的方法
def read(self, data_path, sheetname):
data = xlrd.open_workbook(data_path)
table = data.sheet_by_name(sheetname)
keys = table.row_values(0)
rowNum = table.nrows
colNum = table.ncols
datas = []
for i in range(1, rowNum):
sheet_data = {}
for j in range(colNum):
c_type = table.cell(i, j).ctype
c_cell = table.cell_value(i, j)
if c_type == 2 and c_cell % 1 == 0: # 如果是整形
c_cell = int(c_cell)
elif c_type == 3:
date = datetime.datetime(*xldate_as_tuple(c_cell, 0))
c_cell = date.strftime('%Y/%d/%m %H:%M:%S')
elif c_type == 4:
c_cell = True if c_cell == 1 else False
sheet_data[keys[j]] = c_cell
datas.append(sheet_data)
return datas
# 定义一个写excel表的方法
def write(self,data_path, sheetname,value):
index = len(value)
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = sheetname
for i in range(0, index):
for j in range(0, len(value[i])):
sheet.cell(row=i + 1, column=j + 1, value=str(value[i][j]))
workbook.save(data_path)
print("xlsx格式表格写入数据成功!")
import pymysql
class DB(RW):
def __init__(self):
self.connect = pymysql.Connect(
host='localhost',
port=3306,
user='root',
passwd='',
db='TT',
charset='utf8'
)
self.cursor = self.connect.cursor()
def read(self,tablespace,table):
try:
sql = "use "+tablespace
self.cursor.execute(sql)
self.connect.commit()
except Exception as e:
print(str(e))
print("不存在{}表空间!".format(tablespace))
try:
sql = "select * from "+table
self.cursor.execute(sql)
self.connect.commit()
description = self.cursor.description
title = []
for data in description:
title.append(data[0])
datas=[]
for row in self.cursor.fetchall():
sheelData = {}
for col in range(len(row)):
sheelData[title[col]]=row[col]
datas.append(sheelData)
# print(datas)
return datas
except Exception as e:
print(str(e))
print("数据读取错误")
def write(self,tablespace,table,value):
title = value[0]
colnum=len(title)
# 如果不存在tablespace表空间则新建之
try:
import warnings
sql = "create schema if not exists "+tablespace
warnings.filterwarnings("error",category=pymysql.Warning)
self.cursor.execute(sql)
self.connect.commit()
except pymysql.Warning as e:
print(str(e))
except Exception as e:
print(str(e))
print("新建表空间表失败")
return
# 使用对应表空间
try:
sql = "use "+tablespace
self.cursor.execute(sql)
self.connect.commit()
except Exception as e:
print(str(e))
print("不存在{}表空间!".format(tablespace))
# 如果表格存在则删除之
try:
sql = "drop table if exists "+table
self.cursor.execute(sql)
self.connect.commit()
except pymysql.Warning as e:
print(str(e))
except Exception as e:
print(str(e))
print("删除表失败")
return
# 建表
try:
sql = "create table "+table+"(" \
"id int primary key,"
for index in range(colnum):
sql+= "{} VARCHAR(50),".format(title[index])
sql = sql.strip(",")
sql +=");"
self.cursor.execute(sql)
self.connect.commit()
except Exception as e:
print(str(e))
print("建表失败")
return
# 插入数据
try:
base = "insert into "+table+"(id,"
# EnglishName, ChineseName, Type) VALUES
for index in range(colnum):
base+="{},".format(title[index])
base = base.strip(",")
base += ") VALUES \n"
# plus = "({},'{}','{}','{}'),"
cnt=1
for indexi in range(1,len(value)):
sql=base
sql+="({},".format(cnt)
for indexj in range(colnum):
tmp = str(value[indexi][indexj])
tmp = self.__addslashes(tmp)
sql+="'{}',".format(tmp)
sql = sql.strip(",")
sql+=");"
print(sql)
self.cursor.execute(sql)
cnt+=1
self.connect.commit()
print()
except Exception as e:
print(str(e))
print("插入失败!")
print(sql)
def __addslashes(self,s):
d = {'"':'\\"', "'":"\\'", "\0":"\\\0", "\\":"\\\\"}
return ''.join(d.get(c, c) for c in s)