Pandas数据库大揭秘：read_sql、to_sql 参数详解与实战篇【第81篇—Pandas数据库】

Pandas数据库大揭秘：read_sql、to_sql 参数详解与实战篇

Pandas是Python中一流的数据处理库，而数据库则是数据存储和管理的核心。将两者结合使用，可以方便地实现数据的导入、导出和分析。本文将深入探讨Pandas中用于与数据库交互的两个关键方法：read_sql和to_sql。通过详细解析这两个方法的参数，我们将为读写数据库提供清晰的指导，并附带实际代码演示，以帮助读者更好地理解和运用这些功能。

image-20240215112110620

Pandas读取数据库（read_sql）

read_sql方法简介

read_sql是Pandas提供的用于从数据库读取数据的方法。它允许我们执行SQL查询并将结果直接转换为DataFrame。下面我们将深入探讨read_sql的关键参数：

  • sql：SQL查询语句，必须提供。
  • con：数据库连接对象，可以是字符串（表示连接字符串）或SQLAlchemy引擎。
  • index_col：指定作为DataFrame索引的列。
  • parse_dates：指定需要解析为日期时间的列。
  • params：SQL查询中的参数，可以使用字典形式提供。
实例演示

假设我们有一个SQLite数据库，其中包含一张名为employees的表，结构如下：

CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT,salary REAL,hire_date DATE
);

我们可以使用以下代码查询并将结果存储到Pandas DataFrame中：

import pandas as pd
from sqlalchemy import create_engine# 创建SQLite引擎
engine = create_engine('sqlite:///example.db')# 定义SQL查询语句
sql_query = 'SELECT * FROM employees'# 使用read_sql读取数据
df = pd.read_sql(sql_query, con=engine)# 打印结果
print(df)

Pandas写入数据库（to_sql）

to_sql方法简介

to_sql是Pandas用于将DataFrame数据写入数据库的方法。它允许我们将DataFrame中的数据插入到数据库表中。下面我们将深入探讨to_sql的关键参数：

  • name：目标数据库表的名称。
  • con：数据库连接对象，可以是字符串（表示连接字符串）或SQLAlchemy引擎。
  • if_exists：如果表已存在，指定处理方式（‘fail’、‘replace’、‘append’）。
  • index：是否将DataFrame的索引写入数据库。
实例演示

假设我们有一个名为new_employees的DataFrame，我们希望将其写入数据库中的employees表：

# 创建一个新的DataFrame
new_employees = pd.DataFrame({'name': ['John', 'Alice'],'salary': [60000, 70000],'hire_date': ['2024-01-15', '2024-02-01']
})# 使用to_sql写入数据库
new_employees.to_sql(name='employees', con=engine, if_exists='append', index=False)# 验证写入结果
df_updated = pd.read_sql('SELECT * FROM employees', con=engine)
print(df_updated)

通过这两个实例，我们展示了如何使用read_sql和to_sql方法与数据库进行交互。这些方法的参数提供了灵活性，使得可以轻松处理各种数据库操作需求。读者可以根据实际情况调整参数以满足自己的数据处理需求。

高级用法和注意事项

参数优化

除了上述提到的必要参数外，read_sql和to_sql还有一些其他参数，如chunksize和dtype，用于优化性能和数据类型的处理。

  • chunksize：当从数据库读取数据时，可以指定每次读取的行数，以避免内存溢出。
  • dtype：在将数据写入数据库时，可以为列指定数据类型，以提高写入的效率。
事务管理

在使用to_sql写入数据库时，默认情况下是在一个事务中执行的。如果想要控制事务的开始和结束，可以使用if_exists='replace'参数，并结合index参数为True，在写入数据前使用SQL语句先删除表中的数据，然后再插入新数据。

# 删除表中的所有数据
engine.execute('DELETE FROM employees')# 使用to_sql写入新数据
new_employees.to_sql(name='employees', con=engine, if_exists='append', index=False)
支持的数据库类型

read_sql和to_sql方法不仅仅支持SQLite，还支持多种数据库，如MySQL、PostgreSQL、SQL Server等。只需更改create_engine中的连接字符串即可实现与其他数据库的交互。

进阶技巧与扩展应用

自定义数据类型映射

在实际应用中，有时数据库中的数据类型可能与Pandas默认的数据类型不完全匹配。这时，可以使用dtype参数进行自定义数据类型映射，确保数据正确地写入数据库。

# 自定义数据类型映射
dtype_mapping = {'salary': sqlalchemy.types.FLOAT, 'hire_date': sqlalchemy.types.Date}# 使用to_sql写入数据库，指定dtype参数
new_employees.to_sql(name='employees', con=engine, if_exists='append', index=False, dtype=dtype_mapping)
使用 SQLAlchemy 进行更复杂的数据库操作

在实际项目中，可能需要进行更复杂的数据库操作，如事务管理、批量插入等。此时，可以使用SQLAlchemy库进行更灵活和强大的数据库交互。

from sqlalchemy import text# 使用SQLAlchemy执行复杂的SQL语句
sql_statement = text("UPDATE employees SET salary = salary * 1.1 WHERE hire_date < '2024-02-01'")
engine.execute(sql_statement)# 通过SQLAlchemy进行事务管理
with engine.begin() as connection:connection.execute("INSERT INTO employees (name, salary, hire_date) VALUES ('Bob', 75000, '2024-02-15')")connection.execute("DELETE FROM employees WHERE hire_date < '2024-01-01'")

image-20240215112213347

多数据库支持

有时，一个项目可能需要同时连接多个数据库，可以通过创建多个create_engine对象实现。这为在不同数据库之间进行数据转移和分析提供了便利。

# 连接第一个数据库
engine_db1 = create_engine('sqlite:///database1.db')# 连接第二个数据库
engine_db2 = create_engine('mysql://user:password@localhost/database2')# 从第一个数据库读取数据
df_db1 = pd.read_sql('SELECT * FROM table1', con=engine_db1)# 将数据写入第二个数据库
df_db1.to_sql(name='table2', con=engine_db2, if_exists='replace', index=False)

性能优化与最佳实践

批量插入数据

在大数据量的情况下，使用to_sql方法批量插入数据可能会比逐行插入更高效。可以通过将DataFrame分割成小块，并使用chunksize参数来实现批量插入。

chunk_size = 1000# 将DataFrame拆分为小块，每块包含1000行
for chunk in pd.read_sql('SELECT * FROM large_table', con=engine, chunksize=chunk_size):chunk.to_sql(name='new_large_table', con=engine, if_exists='append', index=False)
数据预处理

在从数据库读取数据或将数据写入数据库之前，进行适当的数据预处理是提高性能的关键。这包括处理缺失值、调整数据类型、进行索引优化等。

# 处理缺失值
df.fillna(value=0, inplace=True)# 调整数据类型
df['hire_date'] = pd.to_datetime(df['hire_date'])# 创建索引
df.set_index('id', inplace=True)# 使用to_sql写入数据库
df.to_sql(name='employees', con=engine, if_exists='replace', index=True)
使用索引加速查询

在读取数据时，可以通过在SQL查询中使用索引来提高检索速度。确保数据库表中的字段上有适当的索引，可以显著减少查询时间。

# 在数据库表的name字段上创建索引
engine.execute('CREATE INDEX idx_name ON employees (name)')

image-20240215112227967

异步操作

在处理大量数据时，考虑使用异步操作来提高效率。可以使用asyncio库与异步数据库驱动（如aiomysql、aiopg）结合，实现并发的数据读取和写入。

import asyncio
import aiomysqlasync def async_read_sql():async with aiomysql.create_pool(host='localhost', user='root', password='password', db='database') as pool:async with pool.acquire() as conn:async with conn.cursor() as cur:await cur.execute("SELECT * FROM table")result = await cur.fetchall()return result# 使用异步读取数据
loop = asyncio.get_event_loop()
data = loop.run_until_complete(async_read_sql())

异常处理与安全性

在与数据库交互时，合理的异常处理是至关重要的。通过使用try-except块，可以捕获可能的数据库操作异常，从而更好地处理错误情况。

try:# 读取数据库df = pd.read_sql('SELECT * FROM table', con=engine)# 写入数据库df.to_sql(name='new_table', con=engine, if_exists='replace', index=False)except Exception as e:print(f"An error occurred: {e}")

另外，要确保在连接数据库时，使用安全的认证方式和正确的权限设置，以保障数据库的安全性。

数据库连接池与长连接管理

在实际应用中，频繁地打开和关闭数据库连接会增加系统开销。为了提高效率，可以使用数据库连接池管理数据库连接，减少连接的创建和销毁次数。SQLAlchemy库提供了create_engine函数的pool_size和max_overflow参数，用于配置数据库连接池的大小和溢出策略。

from sqlalchemy import create_engine, pool# 创建带连接池的引擎
engine = create_engine('sqlite:///example.db', pool_size=10, max_overflow=20)
  • pool_size：指定连接池中保持的连接数。
  • max_overflow：指定允许在连接池外创建的连接数，超过该数目的连接会在连接使用后被回收。
长连接管理

在某些情况下，长连接可能是一种优化性能的方法。通过使用SQLAlchemy的pool_pre_ping参数，可以在每次连接使用前检查连接的健康状态，并在连接失效时重新连接。

# 创建带连接池的引擎，开启连接健康检查
engine = create_engine('sqlite:///example.db', pool_size=10, max_overflow=20, pool_pre_ping=True)

数据加密与安全传输

当连接到远程数据库或者在需要保护数据隐私的情况下，确保数据的传输是加密的是非常重要的。可以通过使用安全的协议和加密选项来保护数据传输。

# 使用安全连接，例如SSL/TLS
engine = create_engine('postgresql+psycopg2://user:password@localhost/database', connect_args={'sslmode': 'require'})

确保数据库服务器支持安全协议，并正确配置，以保证数据在传输过程中的机密性。

多线程与多进程操作

在多线程或多进程环境下，需要注意数据库连接的共享与同步问题。SQLAlchemy的create_engine函数提供了pool_pre_ping和pool_use_lifo等参数，用于管理连接池的多线程或多进程使用。

# 创建带连接池的引擎，适用于多线程环境
engine = create_engine('sqlite:///example.db', pool_size=10, pool_use_lifo=True, pool_pre_ping=True)
  • pool_use_lifo：指定连接池使用LIFO（后进先出）策略，适用于多线程环境。

数据库备份与恢复

在关键数据的场景中，定期进行数据库备份是一种良好的实践。可以使用数据库管理系统提供的备份工具，也可以通过编写脚本实现自动化备份。

import subprocess# 使用系统命令备份SQLite数据库
subprocess.run(['sqlite3', 'example.db', '.backup', 'backup.db'])

确保备份的数据得以加密和安全存储，以防止数据泄露和损坏。

数据库版本管理

在实际开发中，数据库结构可能随着时间的推移而发生变化。为了确保应用程序与数据库的兼容性，可以使用数据库版本管理工具。其中，Alembic是一个常用的数据库迁移工具，它可以帮助记录数据库结构的变更并协助执行这些变更。

安装 Alembic

首先，安装 Alembic：

pip install alembic
创建 Alembic 配置文件

创建一个 alembic.ini 配置文件，用于指定数据库连接信息和迁移脚本存放的路径：

# alembic.ini[alembic]
script_location = alembic
sqlalchemy.url = sqlite:///example.db
创建迁移脚本

使用以下命令初始化 Alembic：

alembic init alembic

然后在 alembic/versions 目录下创建迁移脚本，例如 alembic/versions/001_initial_migration.py：

# alembic/versions/001_initial_migration.pyfrom alembic import op
import sqlalchemy as sadef upgrade():op.create_table('new_table',sa.Column('id', sa.Integer, primary_key=True),sa.Column('name', sa.String),sa.Column('value', sa.Integer))def downgrade():op.drop_table('new_table')
执行迁移

通过以下命令执行迁移：

alembic upgrade head

这将根据定义的迁移脚本更新数据库结构。Alembic会自动跟踪当前数据库版本，以便在将来进行进一步的迁移。

数据库监控与性能优化

使用数据库监控工具来监视数据库的性能，并识别潜在的性能瓶颈。一些流行的数据库监控工具包括 Prometheus、Grafana 等。通过监测关键性能指标，可以及时发现和解决性能问题，确保数据库运行的稳定性。

数据库自动化测试

在开发和维护数据库应用时，编写自动化测试是至关重要的。使用测试框架，例如 pytest，编写测试用例来验证数据库查询、更新和删除等操作的正确性。这有助于捕获潜在的问题，并确保代码的稳定性。

# 示例 pytest 测试用例import pytest
from sqlalchemy import create_engine@pytest.fixture
def db_engine():return create_engine('sqlite:///:memory:')def test_database_operations(db_engine):# 编写测试用例# ...# 断言期望的结果assert True

image-20240215112249987

总结：

在本文中，我们深入探讨了与数据库交互时的关键技术，主要聚焦于使用Pandas库进行数据读写，以及一系列性能优化、安全性、多线程/多进程操作、数据库版本管理、监控与性能优化以及自动化测试等最佳实践。以下是本文的主要要点：

  1. Pandas与数据库交互： 我们详细介绍了Pandas中的read_sql和to_sql方法，通过这两个方法实现了从数据库读取数据和将数据写入数据库的操作，并提供了参数详解和实际代码演示。

  2. 性能优化与最佳实践： 我们探讨了在数据库操作中的性能优化技巧，包括批量插入数据、数据预处理、使用索引加速查询等。同时，介绍了异常处理、安全传输、数据库连接池与长连接管理等方面的最佳实践，以提高效率和可靠性。

  3. 进阶技巧与扩展应用： 我们深入研究了一些进阶技巧，如自定义数据类型映射、使用SQLAlchemy进行更复杂的数据库操作、多数据库支持等。这些技巧为读者在实际项目中处理复杂数据库操作提供了更多选择。

  4. 数据库版本管理： 引入了数据库版本管理工具Alembic，通过迁移脚本的创建和执行，使数据库结构的变更更加可控和可维护。

  5. 数据库监控与性能优化： 强调了使用数据库监控工具，如Prometheus、Grafana等，来监测数据库性能，并提到了性能优化的一些建议。

  6. 数据库自动化测试： 强调了编写自动化测试用例的重要性，使用测试框架如pytest，以确保数据库操作的正确性和稳定性。

通过本文的指导，读者可以更全面地了解如何高效、安全地进行数据库操作，并在实际项目中应用这些最佳实践，以构建出稳定、高效的数据库应用。这些技巧和建议有助于提高开发和维护数据库应用的效率，同时确保数据的可靠性和安全性。希望本文能够为读者在数据库交互方面提供有益的指导。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/260368.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

【Go语言】Go项目工程管理

【Go语言】Go项目工程管理

GO 项目工程管理&#xff08;Go Modules&#xff09; Go 1.11 版本开始&#xff0c;官方提供了 Go Modules 进行项目管理&#xff0c;Go 1.13开始&#xff0c;Go项目默认使用 Go Modules 进行项目管理。 使用 Go Modules的好处是不再需要依赖 GOPATH&#xff0c;可以在任意位…
阅读更多...
JS逆向进阶篇【去哪儿旅行登录】【中篇-滑动轨迹破解补浏览器环境破参数】

JS逆向进阶篇【去哪儿旅行登录】【中篇-滑动轨迹破解补浏览器环境破参数】

目录&#xff1a; 每篇前言&#xff1a;0、整体分析1、逆向轨迹snapshot&#xff08;1&#xff09;分析&#xff1a;&#xff08;2&#xff09;Python轨迹生成&#xff1a;&#xff08;3&#xff09;AES加密&#xff1a;&#xff08;4&#xff09;轨迹加密&#xff1a;&#xf…
阅读更多...
机器学习中梯度下降法的缺点

机器学习中梯度下降法的缺点

机器学习中的梯度下降法是一种寻找函数最小值的优化算法&#xff0c;广泛应用于训练各种模型&#xff0c;尤其是在深度学习中。尽管其应用广泛&#xff0c;但梯度下降法也存在一些不可忽视的缺点&#xff1a; 1. 局部最小值和鞍点 局部最小值问题&#xff1a; 对于非凸函数&a…
阅读更多...
milvus insert数据在s3的存储

milvus insert数据在s3的存储

insert数据在s3的存储 对segment进行flush操作&#xff0c;会将数据持久化至s3对象存储。 相关核心代码位置: ibNode.flushManager.flushBufferData()主要代码在flushBufferData()函数。 代码位置:internal\datanode\flush_manager.go // flushBufferData notifies flush …
阅读更多...
【Docker】Docker存储卷

【Docker】Docker存储卷

文章目录 一、什么是存储卷二、为什么需要存储卷三、存储卷分类四、管理卷Volume创建卷方式一&#xff1a;Volume 命令操作方式二&#xff1a;-v 或者--mount 指定方式三&#xff1a;Dockerfile 匿名卷 操作案例Docker 命令创建管理卷Docker -v 创建管理卷Docker mount 创建管理…
阅读更多...
java中容易被忽视的toString()方法

java中容易被忽视的toString()方法

之前一直认为toString就是将数据转换成字符类型&#xff0c;直到最近写出了一个bug才对toString有了新的认识 不同数据类型&#xff0c;toString() 有不同的操作 定义一个student类&#xff0c;包含姓名 String类型、性别 String类型、年龄 int 类型、分数列表 String类型的li…
阅读更多...
适合tiktok运营的云手机需要满足什么条件？

适合tiktok运营的云手机需要满足什么条件？

TikTok作为一款全球热门的社交媒体平台&#xff0c;具有无限的市场潜力。然而&#xff0c;卖家在运营过程中常常会面临到视频0播、账号被降权、限流等问题&#xff0c;甚至可能因为多人同时使用一个IP而导致封号的风险。为了规避这些问题&#xff0c;越来越多的卖家将目光投向了…
阅读更多...
论UI的糟糕设计：以百度网盘为例

论UI的糟糕设计：以百度网盘为例

上面这一排鼠标一经过就会弹出来&#xff08;不是点才弹出来&#xff09;&#xff0c;然后挡住你的各种操作&#xff0c; 弹出来时你就必须等它消失&#xff0c;卡一下才能操作。 在用户顺畅地操作内容时&#xff0c;经常就卡一下、卡一下、卡一下…… 1、比如鼠标从下到上&am…
阅读更多...
《基于CEEMDAN一小波包自适应阈值混凝土声发射信号降噪研究》算法思路笔记

《基于CEEMDAN一小波包自适应阈值混凝土声发射信号降噪研究》算法思路笔记

![1]杨智中,林军志,汪魁等.基于CEEMDAN-小波包自适应阈值混凝土声发射信号降噪研究[J].振动与冲击,2023,42(03):139-149.DOI:10.13465/j.cnki.jvs.2023.03.016.](https://img-blog.csdnimg.cn/direct/9814ff64cc474cd3aa06ecaea60f2f75.png) 首先对周期循环荷载作用下混凝土试…
阅读更多...
【RPG Maker MV 仿新仙剑 战斗场景UI （二）】

【RPG Maker MV 仿新仙剑 战斗场景UI （二）】

RPG Maker MV 仿新仙剑 战斗场景UI 二 战斗指令菜单原仙剑战斗指令图RMMV战斗指令对应代码战斗指令菜单代码效果 战斗指令菜单 原仙剑战斗指令菜单是使用方向键控制&#xff0c;同时按照使用情况正好对应四个指令和四个方向&#xff0c;同时没有选中的菜单用黑色透明图片覆盖&…
阅读更多...
App启动优化笔记 1

App启动优化笔记 1

app大致的启动流程。有Launcher进程，system_server进程，zygote进程，APP进程。 Launcher进程：启动activity来启动应用 system_server进程：（ams是其中的一个binder）：发送一个socket消息给Zygote。 zygote进程：收到消息后，fork新的进程，---》app进程启动 APP进程：…
阅读更多...
国际语言代码 Language Code 对照表速查

国际语言代码 Language Code 对照表速查

前言 语言代码是英国教育社会学家伯恩斯坦的术语。指在一定的语言集团中&#xff0c;特定的人群在特定的社会环境下使用的特定的言语。分为限定代码&#xff08;restricted code&#xff09;和精制代码&#xff08;elaborated code&#xff09;。语言代码是由字母或数字组成的…
阅读更多...
STM32引脚重定义问题

STM32引脚重定义问题

最近在搞资源管理&#xff0c;发现有些引脚不能用 比如这个PE引脚。我想用他输出PWM&#xff0c;但是不能用&#xff0c;我也重定义了&#xff0c;还是不能用。回去翻看了技术手册。 RCC_APB2PeriphClockCmd(RCC_APB2Periph_AFIO, ENABLE); //重映射引脚功能&#xff0c;需…
阅读更多...
MB-106UP——进口抛光树脂的技术优势

MB-106UP——进口抛光树脂的技术优势

超纯水的制备和稳定性一直是相关领域极为重视的&#xff0c;那么超纯水中常会用到的抛光树脂技术&#xff0c;进口和国产对比起来究竟谁更甚一筹呢&#xff1f;接下来为大家分享的技术就是超纯水制备中常会用到的进口品牌&#xff1a;美国Tulsimer杜笙树脂中抛光树脂MB-106UP的…
阅读更多...
CORS就是跨域吗？

CORS就是跨域吗？

首先&#xff0c;跨域的域是什么&#xff1f; 跨域的英文是&#xff1a;Cross-Origin。 Origin 中文含义为&#xff1a;起源&#xff0c;源头&#xff0c;出生地。 在跨域中&#xff0c;"域"指的是一个 Web 资源&#xff08;比如网页、脚本、图片等&#xff09;的…
阅读更多...
压缩感知（Compressed Sensing，CS）的基础知识

压缩感知（Compressed Sensing，CS）的基础知识

压缩感知&#xff08;Compressed Sensing&#xff0c;CS&#xff09;是一种用于信号处理的技术&#xff0c;旨在以少于奈奎斯特采样定理所要求的样本频率来重构信号。该技术利用信号的稀疏性&#xff0c;即信号可以用较少的非零系数表示。压缩感知在图像获取中的应用使得在采集…
阅读更多...
Kubernetes概述

Kubernetes概述

目录 1.K8S 是什么 2.为什么要用 K8S Kubernetes 主要功能如下&#xff1a; 3.Kubernetes 集群架构与组件 Master 组件 Kube-apiserver Kube-controller-manager Kube-scheduler 配置存储中心 etcd Node 组件 Kubelet Kube-Proxy docker 或 rocket 4.Kubernete…
阅读更多...
css2背景

css2背景

css2背景 一.背景颜色二.背景图片三.背景平铺四.背景图片位置五.背景图像固定六.复合型写法七.背景颜色半透明八.总结 一.背景颜色 默认是transparent(透明&#xff09; 二.背景图片 默认是none 三.背景平铺 默认是background-repeat(平铺&#xff09; 四.背景图片位置…
阅读更多...
Vue中$root的使用方法

Vue中$root的使用方法

查看本专栏目录 关于作者 还是大剑师兰特&#xff1a;曾是美国某知名大学计算机专业研究生&#xff0c;现为航空航海领域高级前端工程师&#xff1b;CSDN知名博主&#xff0c;GIS领域优质创作者&#xff0c;深耕openlayers、leaflet、mapbox、cesium&#xff0c;canvas&#x…
阅读更多...
力扣题目训练（17）

力扣题目训练（17）

2024年2月10日力扣题目训练 2024年2月10日力扣题目训练551. 学生出勤记录 I557. 反转字符串中的单词 III559. N 叉树的最大深度241. 为运算表达式设计优先级260. 只出现一次的数字 III126. 单词接龙 II 2024年2月10日力扣题目训练 2024年2月10日第十七天编程训练&#xff0c;今…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023