Python中使用pymysql和pymssql进行数据库操作的完整指南

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Python中使用pymysql和pymssql进行数据库操作的完整指南

1. 安装依赖库

在开始之前,首先需要安装pymysqlpymssql库。你可以使用以下命令进行安装:

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中使用pymysqlpymssql库执行基本的数据库操作。根据实际需求,你可以进一步学习高级用法和优化技巧。

6. 实战:查询数据并处理结果

使用pymysqlpymssql进行查询并处理结果也是常见的操作,以下是一个示例:

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中使用pymysqlpymssql库进行数据库操作,包括使用参数化查询、上下文管理器以及批量插入等高级用法。这些技术将帮助你更有效地处理数据库交互,并确保代码的性能和安全性。

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中使用pymysqlpymssql库进行MySQL和SQL Server数据库操作的基础与实战。通过一系列的代码示例,我们覆盖了以下关键方面:

  1. 基础操作: 介绍了连接数据库、查询数据、插入、更新、异常处理等基本操作,通过简单的代码展示了如何使用pymysqlpymssql库完成这些任务。

  2. 高级用法: 涵盖了参数化查询、上下文管理器、批量插入等高级用法,以及使用ORM框架SQLAlchemy进行数据库操作的实例。这些技术有助于提高代码的安全性、可读性和可维护性。

  3. 事务处理: 介绍了如何使用事务处理来确保一系列数据库操作的原子性,以维护数据的一致性。

  4. 连接池: 讲解了连接池的概念以及如何使用DBUtils库中的PooledDB创建连接池,以提高数据库连接的效率和性能。

  5. 实际应用: 提供了多个实际场景下的代码示例,包括查询、更新、事务处理和连接池的应用,帮助读者更好地理解和应用所学知识。

通过学习本文所涵盖的内容,读者可以建立起对Python中操作MySQL和SQL Server数据库的全面理解,并掌握一系列实用的技术,从而更加自信地应对各种数据库交互场景。在实际项目中,选择适合自身需求的技术和工具,并根据最佳实践进行优化,将有助于提高应用程序的性能、可靠性和安全性。希望本文能成为读者学习和应用数据库操作的有力指南。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
4天前
|
存储 NoSQL MongoDB
MongoDB数据库转换为表格文件的Python实现
MongoDB数据库转换为表格文件的Python实现
36 0
|
4天前
|
SQL 关系型数据库 数据库连接
使用 Python 访问数据库的基本方法
【5月更文挑战第12天】在Python中操作数据库涉及安装数据库驱动(如mysql-connector-python, psycopg2, pymongo)、连接数据库、执行查询/更新、处理结果集及关闭连接。使用ORM(如SQLAlchemy)可简化操作。通过上下文管理器(with语句)能更好地管理资源和错误。注意根据实际需求处理事务、错误和安全性,例如使用SSL连接。
23 2
|
3天前
|
SQL 关系型数据库 数据库
Python——数据库操作
Python——数据库操作
16 2
|
4天前
|
Oracle Java 关系型数据库
【服务器】python通过JDBC连接到位于Linux远程服务器上的Oracle数据库
【服务器】python通过JDBC连接到位于Linux远程服务器上的Oracle数据库
16 6
|
4天前
|
API 数据库 Python
Python web框架fastapi数据库操作ORM(二)增删改查逻辑实现方法
Python web框架fastapi数据库操作ORM(二)增删改查逻辑实现方法
|
4天前
|
关系型数据库 MySQL API
Python web框架fastapi数据库操作ORM(一)
Python web框架fastapi数据库操作ORM(一)
|
1天前
|
Python
10个python入门小游戏,零基础打通关,就能掌握编程基础_python编写的入门简单小游戏
10个python入门小游戏,零基础打通关,就能掌握编程基础_python编写的入门简单小游戏
|
3天前
|
网络协议 Unix Python
Python编程-----网络通信
Python编程-----网络通信
8 1
|
4天前
|
JSON 数据格式 开发者
pip和requests在Python编程中各自扮演着不同的角色
【5月更文挑战第9天】`pip`是Python的包管理器,用于安装、升级和管理PyPI上的包;`requests`是一个HTTP库,简化了HTTP通信,支持各种HTTP请求类型及数据交互。两者在Python环境中分别负责包管理和网络请求。
32 5