干货 | 利用Python操作mysql数据库

作者 | Tao

来源 | 知乎

本文主要讲解如何利用python中的pymysql库来对mysql数据库进行操作。

先看一下最常见的操作：

• 从数据库中select需要的字段（对数据简单聚合处理）

• 将查找的数据导出为本地文件（csv、txt、xlsx等）

• 通过pandas的read_excel(csv、txt)将本地文件转化成python中的变量，并对数据进行相应的处理和分析

• 将处理好的数据通过pandas的to_excel(csv、txt)导出为本地文件

• 将mysql中的数据导入到python中

• 利用python处理分析数据

• 导出成excel报表

• 第一种方法:read_sql

• 第二种方法:pymysql

先看一下我们今天的数据库信息：

host：192.168.0.***

port：3306

user：root

密码：********

数据库：test

表名：weather_test

字段及数据:

read_sql(sql,con,index_col='None',coerce_float='True',params='None',parse_dates='None',columns='None',chunksize:None='None')

• sql:需要执行的sql语句
• con：连接数据库所需的engine，用其他数据库连接的包建立,例如SQLalchemy和pymysql
• index_col: 选择哪列作为index
• coerce_float:将数字形字符串转为float
• parse_dates:将某列日期型字符串转换为datetime型数据
• columns:选择想要保留的列
• chunksize：每次输出多少行数据

import pandas as pd
import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://root:******@192.168.0.***:3306/test')

sql='''
select * from weather_test where
create_time between '2020-09-21' and '2020-09-22'
and city in ('杭州','上海')
'''
df = pd.read_sql(sql,engine)
df

1.安装

2.利用pymysql操作数据库

import pandas as pd
import pymysql

import pandas as pd
import pymysql

# 打开数据库连接
db = pymysql.connect("192.168.0.***", "root", "******", "test", charset='utf8' )

import pandas as pd
import pymysql

# 打开数据库连接
db = pymysql.connect("192.168.0.***", "root", "******", "test", charset='utf8' )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()

DictCursor：返回字典（Dict）格式的数据

SSCursor：流式游标返回元组（Tuple）格式数据

SSDictCursor：流式游标返回字典（Dict）格式数据

使用其他游标时，只用在cursor()方法中加入相应的参数即可

cursor = db.cursor(pymysql.cursors.SSDictCursor)

fetchall():返回所有数据

fetchone():返回下一条数据

fetchmany(size):返回下size个数据

import pandas as pd
import pymysql

# 打开数据库连接
db = pymysql.connect("192.168.0.***", "root", "******", "test", charset='utf8' )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()

sql = """
select * from weather_test
where create_time between '2020-09-21' and '2020-09-22'
and city in ('上海','杭州')
"""

cursor.execute(sql)
cds = cursor.fetchall()
weather = pd.DataFrame(list(cds),columns=['ID','时间','省份','城市','最高温度','最低温度','白天天气','夜间天气','风力','风向'])
cursor.close()  # 关闭游标
db.close()  # 关闭数据库连接