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