一个读写Excel以及读写数据库的接口（实现脱机操作）

一个读写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)