发现宝藏
前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。【点击进入巨牛的人工智能学习网站】。
Python中操作MySQL和SQL Server数据库的基础与实战
在Python中，我们经常需要与各种数据库进行交互，其中MySQL和SQL Server是两个常见的选择。本文将介绍如何使用pymysql和pymssql库进行基本的数据库操作，并通过实际代码示例来展示这些操作。
1. 安装依赖库
在开始之前，首先需要安装pymysql和pymssql库。你可以使用以下命令进行安装：
pip install pymysql
pip install pymssql
2. 连接MySQL数据库
import pymysql
# 建立数据库连接
connection = pymysql.connect(
host='your_mysql_host',
user='your_username',
password='your_password',
database='your_database',
port=3306
)
# 创建游标对象
cursor = connection.cursor()
# 执行SQL查询
cursor.execute("SELECT * FROM your_table")
# 获取查询结果
result = cursor.fetchall()
# 打印结果
for row in result:
print(row)
# 关闭游标和连接
cursor.close()
connection.close()
3. 连接SQL Server数据库
import pymssql
# 建立数据库连接
connection = pymssql.connect(
host='your_sql_server_host',
user='your_username',
password='your_password',
database='your_database'
)
# 创建游标对象
cursor = connection.cursor()
# 执行SQL查询
cursor.execute("SELECT * FROM your_table")
# 获取查询结果
result = cursor.fetchall()
# 打印结果
for row in result:
print(row)
# 关闭游标和连接
cursor.close()
connection.close()
4. 实战：插入数据
下面是一个简单的示例，演示如何插入数据到MySQL数据库：
import pymysql
# 建立数据库连接
connection = pymysql.connect(
host='your_mysql_host',
user='your_username',
password='your_password',
database='your_database',
port=3306
)
# 创建游标对象
cursor = connection.cursor()
# 插入数据
insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
data_to_insert = ('value1', 'value2')
cursor.execute(insert_query, data_to_insert)
# 提交事务
connection.commit()
# 关闭游标和连接
cursor.close()
connection.close()
5. 实战：更新数据
以下是一个演示如何使用pymssql更新SQL Server数据库中的数据的示例：
import pymssql
# 建立数据库连接
connection = pymssql.connect(
host='your_sql_server_host',
user='your_username',
password='your_password',
database='your_database'
)
# 创建游标对象
cursor = connection.cursor()
# 更新数据
update_query = "UPDATE your_table SET column1 = %s WHERE column2 = %s"
data_to_update = ('new_value', 'condition_value')
cursor.execute(update_query, data_to_update)
# 提交事务
connection.commit()
# 关闭游标和连接
cursor.close()
connection.close()
通过这些简单的代码示例，你可以开始在Python中使用pymysql和pymssql库执行基本的数据库操作。根据实际需求，你可以进一步学习高级用法和优化技巧。
6. 实战：查询数据并处理结果
使用pymysql和pymssql进行查询并处理结果也是常见的操作，以下是一个示例：
import pymysql
# 建立数据库连接
connection = pymysql.connect(
host='your_mysql_host',
user='your_username',
password='your_password',
database='your_database',
port=3306
)
# 创建游标对象
cursor = connection.cursor()
# 查询数据
select_query = "SELECT * FROM your_table WHERE column1 = %s"
condition_value = 'desired_value'
cursor.execute(select_query, (condition_value,))
# 获取查询结果
result = cursor.fetchall()
# 处理结果
for row in result:
print(row)
# 关闭游标和连接
cursor.close()
connection.close()
7. 实战：异常处理
在实际应用中，异常处理是至关重要的。以下是一个简单的异常处理的示例：
import pymysql
try:
# 建立数据库连接
connection = pymysql.connect(
host='your_mysql_host',
user='your_username',
password='your_password',
database='your_database',
port=3306
)
# 创建游标对象
cursor = connection.cursor()
# 执行SQL查询
cursor.execute("SELECT * FROM your_table")
# 获取查询结果
result = cursor.fetchall()
# 打印结果
for row in result:
print(row)
except pymysql.Error as e:
print(f"Error: {e}")
finally:
# 关闭游标和连接
cursor.close()
connection.close()
9. 实战：使用参数化查询
参数化查询是防止SQL注入攻击的一种重要方法。以下是一个使用参数化查询的实例：
import pymysql
# 建立数据库连接
connection = pymysql.connect(
host='your_mysql_host',
user='your_username',
password='your_password',
database='your_database',
port=3306
)
# 创建游标对象
cursor = connection.cursor()
# 参数化查询
parametrized_query = "SELECT * FROM your_table WHERE column1 = %s AND column2 = %s"
query_params = ('value1', 'value2')
cursor.execute(parametrized_query, query_params)
# 获取查询结果
result = cursor.fetchall()
# 处理结果
for row in result:
print(row)
# 关闭游标和连接
cursor.close()
connection.close()
10. 实战：使用上下文管理器
使用上下文管理器可以确保在操作完成后及时关闭数据库连接，以下是一个使用with语句的实例：
import pymysql
# 使用上下文管理器确保在操作完成后关闭数据库连接
with pymysql.connect(
host='your_mysql_host',
user='your_username',
password='your_password',
database='your_database',
port=3306
) as connection:
# 创建游标对象
with connection.cursor() as cursor:
# 执行SQL查询
cursor.execute("SELECT * FROM your_table")
# 获取查询结果
result = cursor.fetchall()
# 处理结果
for row in result:
print(row)
11. 实战：批量插入数据
如果需要插入大量数据，最好使用批量插入以提高性能。以下是一个简单的批量插入示例：
import pymysql
# 建立数据库连接
connection = pymysql.connect(
host='your_mysql_host',
user='your_username',
password='your_password',
database='your_database',
port=3306
)
# 创建游标对象
cursor = connection.cursor()
# 批量插入数据
insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
data_to_insert = [('value1', 'value2'), ('value3', 'value4'), ('value5', 'value6')]
cursor.executemany(insert_query, data_to_insert)
# 提交事务
connection.commit()
# 关闭游标和连接
cursor.close()
connection.close()
通过这些实战示例，你可以更深入地了解如何在Python中使用pymysql和pymssql库进行数据库操作，包括使用参数化查询、上下文管理器以及批量插入等高级用法。这些技术将帮助你更有效地处理数据库交互，并确保代码的性能和安全性。
12. 实战：使用ORM框架
除了直接使用数据库连接库，你还可以考虑使用ORM（对象关系映射）框架来简化数据库操作。这里以SQLAlchemy为例进行示范：
首先，确保已经安装SQLAlchemy：
pip install sqlalchemy
然后，以下是一个使用SQLAlchemy进行简单查询的实例：
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 定义数据模型
Base = declarative_base()
class YourTable(Base):
__tablename__ = 'your_table'
id = Column(Integer, primary_key=True)
column1 = Column(String)
column2 = Column(String)
# 创建数据库连接引擎
engine = create_engine('mysql+pymysql://your_username:your_password@your_mysql_host:3306/your_database')
# 创建数据表
Base.metadata.create_all(engine)
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# 查询数据
result = session.query(YourTable).filter_by(column1='desired_value').all()
# 处理结果
for row in result:
print(row.column1, row.column2)
14. 实战：处理事务
事务是数据库操作中的重要概念，用于确保一组相关操作要么全部成功，要么全部失败。以下是一个简单的事务处理实例：
import pymysql
# 建立数据库连接
connection = pymysql.connect(
host='your_mysql_host',
user='your_username',
password='your_password',
database='your_database',
port=3306
)
# 创建游标对象
cursor = connection.cursor()
try:
# 开始事务
connection.begin()
# 执行多个SQL语句
cursor.execute("UPDATE your_table SET column1 = %s WHERE column2 = %s", ('new_value', 'condition_value'))
cursor.execute("INSERT INTO your_table (column1, column2) VALUES (%s, %s)", ('value1', 'value2'))
# 提交事务
connection.commit()
except pymysql.Error as e:
# 出现错误时回滚事务
connection.rollback()
print(f"Error: {e}")
finally:
# 关闭游标和连接
cursor.close()
connection.close()
在这个示例中，如果执行的所有SQL语句成功，commit()将提交事务，否则rollback()将回滚事务。这有助于保持数据的一致性。
15. 实战：使用连接池
在高并发环境中，使用数据库连接池能够有效地管理和复用数据库连接，提高性能和效率。以下是一个使用pymysql连接池的实例：
首先，确保已经安装DBUtils库：
pip install DBUtils
然后，使用连接池的代码示例：
from DBUtils.PooledDB import PooledDB
import pymysql
# 配置连接池
pool = PooledDB(
creator=pymysql, # 使用pymysql库创建连接
maxconnections=5, # 连接池允许的最大连接数
mincached=2, # 初始化时连接池中至少创建的空闲的连接，0表示不创建
maxcached=5, # 连接池中最多闲置的连接，0和None表示不限制
maxshared=3, # 连接池中最多共享的连接数量，0和None表示全部共享
blocking=True, # 当连接池达到最大数量时，是否阻塞等待连接释放
maxusage=None, # 单个连接最多被重复使用的次数，None表示无限制
)
# 从连接池获取连接
connection = pool.connection()
# 使用连接进行操作
cursor = connection.cursor()
cursor.execute("SELECT * FROM your_table")
result = cursor.fetchall()
for row in result:
print(row)
# 关闭游标和连接
cursor.close()
connection.close()
连接池的使用可以显著提高数据库连接的效率，尤其在并发访问高的情况下。
总结
在本篇文章中，我们深入探讨了在Python中使用pymysql和pymssql库进行MySQL和SQL Server数据库操作的基础与实战。通过一系列的代码示例，我们覆盖了以下关键方面：
基础操作： 介绍了连接数据库、查询数据、插入、更新、异常处理等基本操作，通过简单的代码展示了如何使用pymysql和pymssql库完成这些任务。 高级用法： 涵盖了参数化查询、上下文管理器、批量插入等高级用法，以及使用ORM框架SQLAlchemy进行数据库操作的实例。这些技术有助于提高代码的安全性、可读性和可维护性。 事务处理： 介绍了如何使用事务处理来确保一系列数据库操作的原子性，以维护数据的一致性。 连接池： 讲解了连接池的概念以及如何使用DBUtils库中的PooledDB创建连接池，以提高数据库连接的效率和性能。 实际应用： 提供了多个实际场景下的代码示例，包括查询、更新、事务处理和连接池的应用，帮助读者更好地理解和应用所学知识。
通过学习本文所涵盖的内容，读者可以建立起对Python中操作MySQL和SQL Server数据库的全面理解，并掌握一系列实用的技术，从而更加自信地应对各种数据库交互场景。在实际项目中，选择适合自身需求的技术和工具，并根据最佳实践进行优化，将有助于提高应用程序的性能、可靠性和安全性。希望本文能成为读者学习和应用数据库操作的有力指南。
文章来源
您阅读本篇文章共花了：
发表评论