【python】PyMySQL和pandas操作数据库

目录

使用PyMySQL模块操作数据库

使用pandas进一步操作数据库

to_sql()把DataFrame数据存回数据库

---

使用PyMySQL模块操作数据库

下载并安装pymysql模块。

使用connect()函数连接到数据库。host、port为连接本地MYSQL数据库的IP和端口。user、password为登录本地MYSQL的账号和密码。database指定读取哪个数据库。charest设置数据编码。

import pymysql
conn = pymysql.connect(host='128.0.0.1', port=3306, user='root', password='11111', database='test', charset='utf8')

创建游标，用于与数据库之间交互的对象。pymysql模块提供返回元组类型数据和返回字典类型数据的两种游标。

# 返回元组类型的游标
cursor = conn.cursor()
# 返回字典类型的游标
cursor_dict = conn.cursor(pymysql.cursors.DictCursor)

使用excute()执行SQL命令，执行insert、delete、update等改变数据库数据的SQL语句时，在excute()后需要commit()提交事务。同时最好配合try-catch异常抓获语句使用，以便出错时可以rollback()回滚事务。

执行excute()后返回受该SQL影响的数据的条数，执行SQL返回的数据存放在游标cursor中。

执行fetchone()可以从cursor中取出一条数据，fetchmany()可以指定取出数据的条数（取出多条数据）。

import pymysql
conn = pymysql.connect(host='128.0.0.1', port=3306, user='root', password='11111', database='test', charset='utf8')

# 创建游标
cursor = conn.cursor(pymysql.curosrs.DictCursor)

name = '小明'
sql = 'SELECT * FROM student WHERE name=%s'    # 构造SQL
result = cursor.execute(sql, (name))    # 在execute中拼接SQL提高安全性
# result为该SQL影响的数据记录的条数，此时取出的数据在cursor中
data = cursor.fetchalll()    # 读取数据
cursor.close()    # 关闭游标
conn.close()    # 关闭连接

---

使用pandas进一步操作数据库

pymysql取出来的数据只有元组或字典类型。使用pandas模块的read_sql_query()可以把取出来的数据转为DataFrame模式。

但是read_sql_qurey()需要传入指定数据库的连接引擎，由SQLAlchemy模块的create_engine()函数搭配PyMySQL模块构造连接引擎。注意create_engine()的构造格式

import pandas as pda
import pymysql
from sqlalchemy import create_engine

#构造连接引擎
# 最前面mysql指定构造数据库类型
# 接着传入数据库驱动://数据库用户名root:密码@服务器IP:端口/数据库名?连接选项
conn = create_engine('mysql+pymysql://root:123456@localhost:3306/test?charset=utr8')

#读取数据
# 指定num列作为行标签
data = pad.red_sql_query(sql='SELECT * FROM student', con=con, index_col=['num'])

与直接使用pymysql模块相比，本方法取出的数据为DataFrame格式方便后续的分析处理。对数据的增删改操作不同commit，可以直接生效。

另外还提供了read_sql_table和read_sql()同样用于操作数据库的函数。

---

to_sql()把DataFrame数据存回数据库

pandas模块提供了to_sql()把DataFrame数据直接写入数据库，不需要数据库中有对应的数据表。可以添加dtype参数指定数据类型，否则使用默认字段。

除了name指定数据库表，con指定连接引擎，还有一些可能用到的参数：

if_exists	当表存在时的操作。replace 替换原表，append在原表后追加，fail 写入失败抛出异常
chunksize	指定一次写入的记录条数
index	默认True，把DataFrame的行标签作为字段写入表
index_label	指定行标签写入表后的字段名

import pandas as pda
import pymysql
from sqlalchemy import create_engine

#构造连接引擎
conn = create_engine('mysql+pymysql://root:123456@localhost:3306/test?charset=utr8')

# 读取数据
data = pad.red_sql_query(sql='SELECT * FROM student', con=con, index_col=['num'])

# 写入数据
data.to_sql(name='new_student', con=con, index=True, index_label=['学号'], if_existse='replace')