基于Python的mysql与excel互相转换

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 1.mysql转为excelgetConn函数获取mysql连接,第1个参数database为要连接的数据库。mysql2excel函数完成主要转换功能,第1个参数database为要连接的数据库,第2个参数为要转换的数据表,第3个参数为要保存的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")
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3天前
|
数据格式 Python
【Python】已解决:Excel无法打开文件test.xIsx“,因为文件格式或文件扩展名无效。请确定文件未损坏,并且文件扩展名与文件的格式匹配。
【Python】已解决:Excel无法打开文件test.xIsx“,因为文件格式或文件扩展名无效。请确定文件未损坏,并且文件扩展名与文件的格式匹配。
30 0
|
3天前
|
数据挖掘 Python
【Python】已解决:Python pandas读取Excel表格某些数值字段结果为NaN问题
【Python】已解决:Python pandas读取Excel表格某些数值字段结果为NaN问题
13 0
|
1天前
|
机器学习/深度学习 存储 数据可视化
这份Excel+Python飞速搞定数据分析手册,简直可以让Excel飞起来
微软在 UserVoice 上运营着⼀个反馈论坛,每个⼈都可以在这⾥提交新点⼦供他⼈投票。票数最⾼的功能请求是“将 Python 作为Excel 的⼀门脚本语⾔”,其得票数差不多是第⼆名的两倍。尽管⾃2015 年这个点⼦发布以来并没有什么实质性进展,但在 2020 年年末,Python 之⽗ Guido van Rossum 发布推⽂称“退休太无聊了”,他将会加入微软。此事令 Excel ⽤户重燃希望。我不知道他的举动是否影响了 Excel 和 Python 的集成,但我清楚的是,为何⼈们迫切需要结合 Excel 和 Python 的⼒量,⽽你⼜应当如何从今天开始将两者结合起来。总之,这就是本
|
4天前
|
机器学习/深度学习 存储 数据可视化
这份Excel+Python飞速搞定数据分析手册,简直可以让Excel飞起来
微软在 UserVoice 上运营着⼀个反馈论坛,每个⼈都可以在这⾥提交新点⼦供他⼈投票。票数最⾼的功能请求是“将 Python 作为Excel 的⼀门脚本语⾔”,其得票数差不多是第⼆名的两倍。尽管⾃2015 年这个点⼦发布以来并没有什么实质性进展,但在 2020 年年末,Python 之⽗ Guido van Rossum 发布推⽂称“退休太无聊了”,他将会加入微软。此事令 Excel ⽤户重燃希望。我不知道他的举动是否影响了 Excel 和 Python 的集成,但我清楚的是,为何⼈们迫切需要结合 Excel 和 Python 的⼒量,⽽你⼜应当如何从今天开始将两者结合起来。总之,这就是本
|
4天前
|
安全 关系型数据库 MySQL
【Python】已解决:pymysql.err.OperationalError:(2003 “Can’t connect to MySQL server on ‘localhost’ ([WinEr
【Python】已解决:pymysql.err.OperationalError:(2003 “Can’t connect to MySQL server on ‘localhost’ ([WinEr
16 1
|
2天前
|
数据可视化 数据挖掘 数据处理
Python对Excel两列数据进行运算【从基础到高级的全面指南】
【7月更文挑战第6天】使用Python的`pandas`库处理Excel数据,涉及安装`pandas`和`openpyxl`,读取数据如`df = pd.read_excel('data.xlsx')`,进行运算如`df['Sum'] = df['Column1'] + df['Column2']`,并将结果写回Excel。`pandas`还支持数据筛选、分组、可视化、异常处理和性能优化。通过熟练运用这些功能,可以高效分析Excel表格。
5 0
|
3天前
|
开发者 Python
【Python】已解决:(pandas read_excel 读取Excel报错)ImportError: Pandas requires version ‘2.0.1’ or newer of ‘x
【Python】已解决:(pandas read_excel 读取Excel报错)ImportError: Pandas requires version ‘2.0.1’ or newer of ‘x
8 0
|
3天前
|
Python
【Python】已解决:(Python xlwt写入Excel样式报错)ValueError: More than 4094 XFs (styles)
【Python】已解决:(Python xlwt写入Excel样式报错)ValueError: More than 4094 XFs (styles)
8 0
|
3天前
|
Python
【Python】已解决:(Python xlwt写入Excel报错)AttributeError: ‘function’ object has no attribute ‘font’
【Python】已解决:(Python xlwt写入Excel报错)AttributeError: ‘function’ object has no attribute ‘font’
9 0
|
3天前
|
Python
【Python】已解决:(Python写入Excel表格报错)‘NoneType’ object has no attribute ‘write’
【Python】已解决:(Python写入Excel表格报错)‘NoneType’ object has no attribute ‘write’
8 0