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

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')