【python】PyMySQL和pandas操作数据库

2023-07-25 20:24| 来源: 网络整理| 查看: 265

使用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')

【本文地址】

公司简介

联系我们