基于Python的mysql与excel互相转换

1.mysql转为excel

getConn函数获取mysql连接,第1个参数database为要连接的数据库。
mysql2excel函数完成主要转换功能，第1个参数database为要连接的数据库，第2个参数为要转换的数据表，第3个参数为要保存的excel文件名。
在执行cursor.execute后，利用data_list = cursor.fetchall()获取数据库中所有数据，利用cursor.description获取函数中字段的相关信息，
字段的相关信息的数据类型为元组，其中第1个为字段名。
利用xlwt.Workbook()方法实例化对象赋值给excel变量，利用excel.add_sheet()方法获取新的表格，利用sheet.write()往excel文件中写入数据。

import pymysql
import xlwt

def getConn(database='pydb'):
    args = dict(
        host='localhost',
        user='root',
        passwd='...your passwd',
        db=database,
        charset='utf8'
    )
    conn = pymysql.connect(**args)
    return conn

def mysql2excel(database='pydb',table='test',excelResult = ''):
    conn = getConn(database)
    cursor = conn.cursor()
    cursor.execute("select * from {}".format(table))
    data_list = cursor.fetchall()
    excel = xlwt.Workbook()
    sheet = excel.add_sheet("sheet1")
    row_number = len(data_list)
    column_number = len(cursor.description)
    for i in range(column_number):
        sheet.write(0,i,cursor.description[i][0])
    for i in range(row_number):
        for j in range(column_number):
            sheet.write(i+1,j,data_list[i][j])
    excelName = "mysql_{}_{}.xls".format(database,table)
    if excelResult != '':
        excelName = excelResult
    excel.save(excelName)

if __name__ == "__main__":
    mysql2excel("customdb","deposit")

2.excel转为mysql

getConn函数获取mysql连接,第1个参数database为要连接的数据库。
excel2mysql函数完成主要转换功能，第1个参数为读取的excel文件名，第2个参数为存放数据的数据库，第3个参数为保存的表名。
利用xlrd.open_workbook()方法实例化对象赋值给excel，利用excel.sheet_by_index(0)获取第1张数据薄赋值给sheet，
利用sheet.nrows获取行数赋值给row_number,利用sheet.ncols获取列数赋值给column_number
利用sheet.row_values获取第一行的内容即字段赋值给field_list，利用循环+sheet.row_values()方法获取数据内容赋值给data_list
数据库操作分为：连接——>删除原有同名数据库——>创建数据库——>插入数据——>提交并关闭连接
drop_sql变量为删除原有同名数据库的sql语句，create_sql变量为创建数据库的sql语句，insert_sql变量为往数据表中插入数据的sql语句。
最后conn.commit()和conn.close()

import xlrd
import pymysql

def getConn(database='pydb'):
    args = dict(
        host='localhost',
        user='root',
        passwd='...your passwd',
        db=database,
        charset='utf8'
    )
    conn = pymysql.connect(**args)
    return conn

def excel2mysql(excelName,database='pydb',table='test'):
    #下面代码作用：获取到excel中的字段和数据
    excel = xlrd.open_workbook(excelName)
    sheet = excel.sheet_by_index(0)
    row_number = sheet.nrows
    column_number = sheet.ncols
    field_list = sheet.row_values(0)
    data_list = []
    for i in range(1,row_number):
        data_list.append(sheet.row_values(i))
    #下面代码作用：根据字段创建表，根据数据执行插入语句
    conn = getConn(database)
    cursor = conn.cursor()
    drop_sql = "drop table if exists {}".format(table)
    cursor.execute(drop_sql)
    create_sql = "create table {}(".format(table)
    for field in field_list[:-1]:
        create_sql += "{} varchar(50),".format(field)
    create_sql += "{} varchar(50))".format(field_list[-1])
    cursor.execute(create_sql)
    for data in data_list:
        new_data = ["'{}'".format(i) for i in data]
        insert_sql = "insert into {} values({})".format(\
            table,','.join(new_data))
        cursor.execute(insert_sql)
    conn.commit()
    conn.close()

if __name__ == '__main__':
    excel2mysql("mysql_myschool_student.xls")