Python自动连接Mysql数据库导出Excel或CSV文件

数据之美

从信息化到数据化

在使用Python之前，从数据库获取数据工作的流程，一般是先打开数据库客户端，然后编写执行 SQL语句，把数据查询出来，再把数据复制到Excel中制作报表。其实这些工作都可以用Python变成自动化，从而让我们有更多的时间，去思考、解决和业务相关的问题，而不是陷入重复使用工具的手动操作无效循环中。

安装和导入模块

Python中的SQLAlchemy模块能够操作各种数据库，包括 Oracle、PostgreSQL、MySQL、SQLite、SQL Server 等，首先通过以下命令进行安装：

pip3 install sqlalchemy

安装完成后，在Jupyter Lab中运行以下代码：

import sqlalchemy as sa
# 查看 SQLAlchemy 版本
sa.__version__

如果该模块正确安装会输出版本号，我目前使用的版本是 1.4.21。不同的数据库，需要安装不同的第三方模块，比如说，要操作Mysql，那么需要先安装

# 安装或更新 pymysql
pip3 install --upgrade pymysql

连接数据库

首先先创建一个数据库引擎，然后再连接数据库：

from sqlalchemy import create_engine
# 创建数据库引擎，替换其中的用户名、密码、主机地址、端口、数据库名
engine = create_engine('mysql+pymysql://root:xxxxxx@192.168.0.1:3306/testdb')
# 创建数据库连接
conn = engine.connect()

创建表

假设你的数据库账号拥有创建表的权限，那么就可以执行下面的语句，实现创建一个新的表：

sql = 'create table tb1(id integer, name varchar(50))'
conn.execute(sql)

增删改查

# 新增2行数据
conn.execute("insert into tb1(id, name) values(1, 'jim')")
conn.execute("insert into tb1(id, name) values(2, 'tom')")
# 删除一条数据
conn.execute('delete from tb1 where id = 1')
# 更新一条数据
conn.execute("update tb1 set name = 'jason' where id = 2")

在工作中SQLAlChemy经常配合Pandas一起使用，从而更好地解决数据处理和分析的问题。例如，按条件查询数据：

import pandas as pd
sql = 'select id, name from tb1 where id = :id'
df = pd.read_sql(sa.text(sql), engine, params={'id': 2})

将数据保存成excel或csv文档：

excel_o_file = pd.ExcelWriter('./test.xlsx')
#columns参数的顺序就是excel的列顺序
#df为需要保存的DataFrame
df.to_excel(excel_o_file, columns=['id','name'], index=False,encoding='utf-8',sheet_name='Sheet1')
#生成csv文件
#df.to_csv(r'./test.csv',columns=['id','name'],index=False,sep=',')