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