從excel表導(dǎo)入MySQL數(shù)據(jù)庫(kù)

import xlrd
import pymysql


# 寫(xiě)入excel表數(shù)據(jù)
def insert_data():
    data = xlrd.open_workbook("excel/test.xlsx")
    sheet_names = data.sheet_names()
    # print(sheet_names)
    # 連接數(shù)據(jù)庫(kù)
    db = pymysql.connect(host='localhost', user='root', passwd='123456',
                         db='excel', charset='utf8')
    cursor = db.cursor()
    for i in range(len(sheet_names)):
        # 當(dāng)前sheet的名字
        table_name = sheet_names[i]
        # 當(dāng)前的sheet
        now_table = data.sheet_by_index(i)
        # 獲得當(dāng)前sheet的列數(shù)就是屬性數(shù)
        cols_num = now_table.ncols
        # 獲得當(dāng)前表格的行數(shù)，就是有多少的數(shù)據(jù)量
        rows_num = now_table.nrows
        attrs = now_table.row_values(0)
        # 判斷表格是否存在
        flag = cursor.execute("show tables like '%s';" % table_name)
        # print(flag)
        # 表存在
        if flag:
            print("-------")
            # 將當(dāng)前的sheet插入到數(shù)據(jù)庫(kù)
            for j in range(1, rows_num):
                row_value = now_table.row_values(j)
                # 處理要插入的數(shù)據(jù)，把非字符串的數(shù)據(jù)轉(zhuǎn)換成字符串類型，同時(shí)將字符串變成 sql語(yǔ)句需要的類型
                for k in range(1, len(row_value)):
                    ctype = now_table.cell(j, k).ctype
                    # print('ctype', ctype)
                    # ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
                    if ctype == 2 and row_value[k] % 1 == 0:
                        tmp = int(row_value[k])
                        row_value[k] = str(tmp)
                    c = row_value[k]
                    row_value[k] = "'" + c + "'"
                # print(row_value)
                sql = "insert into %s(%s) values(%s);" % (table_name, ','.join(attrs[1:]), ','.join(row_value[1:]))
                cursor.execute(sql)
                db.commit()
        else:
            # 為sheet建表
            cursor.execute(f"create table {table_name} (id int primary key auto_increment);")
            db.commit()
            for l in range(1, cols_num):
                cursor.execute(f"alter table {table_name} add column {attrs[l]} varchar(200);")
                db.commit()
            # 將當(dāng)前的sheet插入到數(shù)據(jù)庫(kù)
            for j in range(1, rows_num):
                row_value = now_table.row_values(j)
                # 處理要插入的數(shù)據(jù)呛踊，把非字符串的數(shù)據(jù)轉(zhuǎn)換成字符串類型，同時(shí)將字符串變成 sql語(yǔ)句需要的類型
                for k in range(1, len(row_value)):
                    ctype = now_table.cell(j, k).ctype
                    print('ctype', ctype)
                    # ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
                    if ctype == 2 and row_value[k] % 1 == 0:
                        tmp = int(row_value[k])
                        row_value[k] = str(tmp)
                    c = row_value[k]
                    row_value[k] = "'" + c + "'"
                sql = "insert into %s(%s) values (%s);" % (table_name, ','.join(attrs[1:]), ','.join(row_value[1:]))
                cursor.execute(sql)
                db.commit()
    db.close()


if __name__ == '__main__':
    insert_data()

從MySQL數(shù)據(jù)庫(kù)寫(xiě)入excel表

import xlwt
import pymysql
import os
import datetime


# 從數(shù)據(jù)庫(kù)讀取數(shù)據(jù)寫(xiě)入excel祟昭，生成xls or xlsx格式文件
def table_export_to_xls(sql):
    table_name = 'sheet1'
    # 連接數(shù)據(jù)庫(kù)
    db = pymysql.connect(host='localhost', user='root', passwd='123456',
                         db='excel', charset='utf8')
    cursor = db.cursor()
    cursor.execute(sql)
    # 重置游標(biāo)位置
    cursor.scroll(0, mode='absolute')
    # 搜取所有的結(jié)果
    results = cursor.fetchall()
    # print(result)
    # 獲取屬性名
    attrs = cursor.description
    print(attrs)

    workbook = xlwt.Workbook()
    sheet1 = workbook.add_sheet(table_name, cell_overwrite_ok=True)
    # 寫(xiě)入表格的屬性值
    for i in range(1, len(attrs)):
        sheet1.write(0, i-1, attrs[i][0])
    # 將數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入表格
    row = 1
    col = 0
    for row in range(1, len(results)+1):
        for col in range(0, len(attrs)-1):
            sheet1.write(row, col, results[row-1][col+1])
    # 保存路徑
    now_path = os.path.dirname(__file__)
    print(now_path)
    file_path = os.path.join(now_path, 'excel')
    print(file_path)
    export_time = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    file_name = 'test-{0}.xlsx'.format(export_time)
    workbook.save(os.path.join(file_path, file_name))

    if os.path.isfile(os.path.join(file_path, file_name)):
        print('數(shù)據(jù)庫(kù)重成功導(dǎo)出數(shù)據(jù)静汤！')
        return {'path': file_path, 'name': file_name}
    else:
        print('導(dǎo)出失斊冈钦！')
        return 'error'


if __name__ == '__main__':
    sql = "select * from sheet1"
    table_export_to_xls(sql)

PostgreSQL可參考：

https://blog.csdn.net/huangmengfeng/article/details/81005505