python3.6+处理EXCLE表格数据存入mysql&sqlsever程序

处理步骤

1、数据库表信息建立
2、excle表格准备
3、编写python程序
4、执行存储至DB

创建数据库表信息

1. 明确excle结构 ；
   
   2.建立建表sql,创建数据表
   CREATE TABLE PostData_API_Multipurpose_test
   (
   id INT NOT NULL identity(1,1),
   annotation VARCHAR(100),
   operationsteps VARCHAR(100),
   expect VARCHAR(100),
   result VARCHAR(100),
   post_value VARCHAR(8000),
   return_value VARCHAR(8000)
   )

表信息

程序开发

1.pip导包
pip install xlrd
pip install pymssql
2.创建sql连接及工具

3.处理EXCLE表格

4.sql连接调取存储数据

5.main程序执行

6.执行结果，表数据验证
完成执行：

查看表数据：

完整程序

import xlrd
import pymssql


class SQLConnect:
    def __init__(self, server, user, password, database, port):
        self.server = server
        self.user = user
        self.password = password
        self.database = database
        self.port = port

    # 获取连接
    def sqlConnect(self):
        if not self.database:
            raise (NameError, "No databases infomation .")
        self.conn = pymssql.connect(self.server, self.user, self.password, self.database, self.port, charset="utf8")
        cursor = self.conn.cursor()
        if not cursor:
            raise (NameError, "Connection mysql error .")
        else:
            return cursor

    # 查询
    def query(self, sql):
        cursor = self.sqlConnect()
        cursor.execute(sql)
        resList = cursor.fetchall()
        self.conn.close()
        return resList

    # 批量添加
    def insert(self, sql, values):
        cursor = self.sqlConnect()
        cursor.executemany(sql, values)
        # self.conn.commit() #commit() 对于oracle才会执行commit提交
        self.conn.close()


class queryExcle():
    def excle(self):
        book = None
        try:
            book = xlrd.open_workbook(
                "D:\\WeChat Files\\WeChat Files\txt\\FileStorage\\File\\2019-11\\example.xlsx")  # excle文件地址
        except Exception as e:
            print('EXCLE file path err: %s' % (e))
        sheet = book.sheet_by_name("20191104-multipurpose")  # excle表的sheet标识位
        sql = "INSERT INTO PostData_API_Multipurpose_test(annotation,operationsteps,expect,result,post_value,return_value) VALUES (%s,%s,%s,%s,%s,%s)"

        for r in range(1, sheet.nrows):  # 跳过excle第一行，如果第一行为标题信息
            values = []
            annotation = sheet.cell(r, 1).value  # 获取行的第2列
            operationsteps = sheet.cell(r, 2).value  # 获取行的第3列
            expect = sheet.cell(r, 3).value  # 获取行的第4列
            result = sheet.cell(r, 4).value  # 获取行的第5列
            post_value = sheet.cell(r, 5).value  # 获取行的第6列
            return_value = sheet.cell(r, 6).value  # 获取行的第7列

            # 将需要的excle对应到mysql表列字段位置，构建元祖，位置必须与mysql表字段位置对应
            num = (str(annotation), str(operationsteps), str(expect), str(result), str(post_value), str(return_value))
            values.append(num)  # 将元组添加到列表
            nummbers = sheet.cell(r, 0).value  # 获取行的第1列，excle内的ID标识，记录执行到哪里
            try:
                self.run_preferential(sql, values)
            except Exception as e:
                print(e)
                print("wrong with the perform to id %s ." % (str(nummbers)))
            continue

    def run_preferential(self, sql, values):
        server = "10.001.001.111"
        user = "username"
        password = "password"
        database = "databases"
        port = 1433
        connection = SQLConnect(server, user, password, database, port)
        connection.insert(sql=sql, values=values)

if __name__ == '__main__':
    print("begain start app ...")
    start = queryExcle()
    start.excle()
    print("application execution completed .")