xlrd MySQL_基于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")