Python之xlsx文件从MySQL数据库导入导出

ゝ一世哀愁。 2022-06-07 13:08 178阅读 0赞

### Python之excel文件从MySQL数据库导入导出 ###

#### excel文件导入MySQL数据库 ####

import pymysql
    import xlrd
    import xlwt
    
    
    def get_conn():
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='db_xlsx', charset='utf8')
        return conn
    
    
    def insert(cur, sql, args):
        cur.execute(sql, args)
    
    
    def read_xlsx_to_mysql(filename):
        excel = xlrd.open_workbook(filename)  # 打开xlsx文件,返回一个对象
        sheet = excel.sheet_by_index(0)  # 获取第一个sheet表格
        conn = get_conn()
        cur = conn.cursor()
        sql = 'insert into tb_xlsx values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        print(sheet.nrows)
        for row in range(sheet.nrows):
            print(row)
            args = sheet.row_values(row)
            print(args)
            print(type(args))
            if row == 0:
                continue
            if args[1] is None or args[1] == '':
                continue
            insert(cur, sql, args=args)
        conn.commit()
        cur.close()
        conn.close()
    
    
    if __name__ == '__main__':
        read_xlsx_to_mysql('1.xlsx')

`1.xlsx`文件的表头是：

['序号', '合同编号', '义务人', '档案柜号', '柜内编号', '权利人', '放款金额', '放款日期', '他项权利证编号', '抵押物地址', '结清出库日期', '备注', '地区']

对应的mysql的tb\_xlsx的表字段：

CREATE TABLE tb_xlsx( xuhao VARCHAR(20), htcode VARCHAR(20), yiwuren VARCHAR(20), dagh VARCHAR(20), gncode VARCHAR(20), quanliren VARCHAR(20), fkmoney VARCHAR(20), fkdata VARCHAR(20), qitacode VARCHAR(20), diyaaddr VARCHAR(100), jqdata VARCHAR(30), beizhu VARCHAR(30), zone VARCHAR(30), PRIMARY KEY(htcode) )CHARSET=utf8;

#### MySQL数据库导出Excel文件 ####

import pymysql
    import xlwt
    
    
    def get_conn():
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='db_xlsx', charset='utf8')
        return conn
    
    
    def query_all(cur, sql, args):
        cur.execute(sql, args)
        return cur.fetchall()
    
    
    def read_mysql_to_xlsx(filename):
        list_table_head = ['序号', '合同编号', '义务人', '档案柜号', '柜内编号', '权利人', '放款金额', '放款日期', '他项权利证编号', '抵押物地址', '结清出库日期', '备注',
                           '地区']
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet('data', cell_overwrite_ok=True)
        for i in range(len(list_table_head)):
            sheet.write(0, i, list_table_head[i])
    
        conn = get_conn()
        cur = conn.cursor()
        sql = 'select * from tb_xlsx'
        results = query_all(cur, sql, None)
        conn.commit()
        cur.close()
        conn.close()
        row = 1
        for result in results:
            col = 0
            print(type(result))
            print(result)
            for item in result:
                print(item)
                sheet.write(row, col, item)
                col += 1
            row += 1
        workbook.save(filename)
    
    
    if __name__ == '__main__':
        read_mysql_to_xlsx('2.xlsx')