pandas读/写数据库

一、前言

二、读/写数据库

1.数据库数据读取

2.SQLAlchemy 连接 MySQL 数据库

​2.1在数据库连接创建完成后，可通过 read_sqlquery、read_sql_table、read_sql 函数读取数据库中的数据。

3.使用to_sql()方法写入数据

三、总结

---

一、前言

在生产环境中，绝大多数的数据都存储在数据库中。pandas库提供了读/写关系型数据库据库数据的函数与方法。除了pandas库外，还需要使用SOLAlchemy库建立对应的据库连接。SQLAlchemy是配合相应数据库的Python连接工具(如使用MySOL数据A时需要安装 mysqlclient或pymysql库，使用 Oracle 数据库时需要安装cx_oracle库)使用 create_engine 兩数，建立数据库连接。pandas 支持 MySOL、PostgreSQL、OrackSOL Server 和 SQLite 等主流数据库。下面将以 MySOL数据库为例，介绍 pandas 对责据库数据的读/写。

二、读/写数据库

1.数据库数据读取

pandas可实现对数据库数据的读取，但前提是读者在进行读取操作前确保已安装数库，并且数据库可以正常打开及使用。对数据库的读取可利用3种函数进行操作、分别是read_sql_query 所数、read_sql_table 兩数和 read_sql 函数。read_sql_query 函数只能实现查询操作，不能直接读取数据库中的某个表。read_sql_table函数只能读取数据库的某一个表格，不能实现查询操作。read_sql函数是两者的综合，既能够读取数据库中的某一个表、也能够实现查询操作。3个函数的基本使用格式如下。

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None,parse_dates=None,chunksize=None,dtype=None)

pandas.read_sql_table(table_name,con,schema=None, index_col=None, coerce_float=True,parse_dates=None,columns=None, chunksize=None)

pandas.read_sql(sql ,con ,index_col = None , coerce_float = True , params=None,parse_dates=None,columns=None,chunksize = None )

pandas的3个数据库数据读取函数的参数几乎完全一致，唯一的区别在于传入的是语句还是表名。3个函数的常用参数如下表。

参数名称	参数说明
sql, table_name	接收 str。表示读取的数据的表名或 SQL语句。无默认值
con	接收数据库连接或str。表示数据库连接信息。无默认值
index_col	接收str或 str 型列表。表示将列设置为索引。默认为 None
coerce_float	接收 bool。表示尝试将非字符串、非数字对象(如十进制数)的值转换为浮点数。默认为 True
columns	接收 list。表示要从 SQL 表中选择的列名列表。默认为 None

2.SQLAlchemy 连接 MySQL 数据库

在读取数据库数据前，需要先创建数据库连接。Python提供了SQLAlchemy库的create_engine 函数用于创建数据库连接,在 create_engine 函数中输入的是一个连接字符串在使用 Python的SQLAlchemy库时，MySQL 和 Oracle 数据库连接字符串的格式如下。

数据库产品名+连接工具名://用户名:密码@数据库IP地址:数据库端口号/数据库名称?charset-数据库数据编码格式

from sqlalchemy import create_engine
import pymysql
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/testdb?charset=utf8')
print(engine)

 执行结果如下

2.1在数据库连接创建完成后，可通过 read_sqlquery、read_sql_table、read_sql 函数读取数据库中的数据。

import pandas as pd
musicadatalist = pd.read_sql_query('show tables',con=engine)
print('testdb数据库数据表清单为：\n',musicadatalist)

 执行结果如下

musicdata = pd.read_sql_table('musicdata', con=engine)
print('使用read_sql_table函数读取音乐行业收入信息表，表的长度为：\n',len(musicdata))
musicdata = pd.read_sql('musicdata', con=engine)
print('使用read_sql函数读取音乐行业收入信息表，表的长度为：\n',len(musicdata))

执行结果如下 

3.使用to_sql()方法写入数据

        to_sql()方法的基本使用格式如下。

DataFrame.to_sql(name,con,if_exists='fail', index=True,index_label=None, chunksize=None, dtype=None, method=None)

        to_sql()方法的常用参数如下表。

参数名称	参数说明
name	接收str。表示数据库表名。无默认值
con	接收数据库连接。表示数据库连接信息。无默认值
if_exists	接收 str。表示对表进行操作的方式，可选fail、replace、append。fail 表示如果表名存在，那么不执行写人操作;replace 表示如果表名存在，那么将原数据库表删除，再重新创建;append 表示在原数据库表的基础上追加数据。默认为fail
index	接收 bool。表示是否将 DataFrame 索引写人列并使用 index _label 作为表中的列名。默认为 True
index_label	接收 str或 sequence。表示索引列的列标签。如果没有给定(默认)且imdex参数为 Tnue那么使用索引。如果DataFrame使用多重索引，那么应该给出序列。默认为None
dtype	接收 dict 或 scalar。表示指定列的数据类型。默认为 None

musicdata.to_sql('test1',con=engine,index=False,if_exists='replace')
formlist1 = pd.read_sql_query('show tables',con=engine)
print('新增一个表格后,testdb数据库数据表清单为：\n',formlist1)

执行结果如下 

三、总结

Pandas 是一个强大的数据分析库，尽管它本身并不直接支持数据库操作，但它通过与其他库的集成（如 SQLAlchemy、sqlite3、pymysql、psycopg2 等），使得读取和写入数据库变得简单而高效。

Pandas 通过与其他库（如 SQLAlchemy）的集成，提供了读取和写入数据库的强大功能。你可以使用 Pandas 的 read_sql_query(), read_sql_table(), 和 read_sql() 函数从数据库中读取数据，并使用 DataFrame.to_sql() 函数将数据写入数据库。在进行这些操作时，请确保你的数据类型与数据库兼容，并考虑使用事务来确保数据的完整性和一致性。

注意事项

• 数据类型：确保 DataFrame 中的数据类型与数据库中的列类型兼容。
• 性能：对于大型数据库和 DataFrame，读取和写入操作可能会很慢。考虑使用 chunksize 参数进行分块处理以提高性能。
• 事务：在进行写入操作时，考虑使用数据库事务来确保数据的完整性和一致性。
• 连接对象：Pandas 支持多种数据库连接对象，包括 SQLAlchemy 引擎、sqlite3 连接、pymysql 连接等。根据你的数据库选择合适的连接对象。
• SQL 注入：当使用 read_sql_query 时，确保你的 SQL 查询是安全的，避免 SQL 注入攻击。