Python数据库交互全攻略：从原生操作到ORM高级技巧

于 2025-05-03 13:00:00 发布
阅读量712 收藏 5
点赞数 11
分类专栏： Python全栈成长笔记 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_57755194/article/details/147626204
版权

前言

在Python应用开发中，数据库交互是不可或缺的核心技能。无论是开发小型工具还是大型Web应用，高效、安全地操作数据库都是开发者必须掌握的技能。本文将全面介绍Python与数据库交互的三种主流方式：内置SQLite3模块、PyMySQL/psycopg2驱动库以及SQLAlchemy ORM框架，帮助你构建完整的Python数据库知识体系。

一、SQLite3：轻量级数据库的内置支持

1.1 SQLite3基础操作

SQLite3是Python内置的轻量级数据库模块，无需安装额外服务，非常适合小型应用和原型开发。

基本使用流程：

import sqlite3

# 连接数据库（不存在则自动创建）
conn = sqlite3.connect('example.db')

# 创建游标对象
cursor = conn.cursor()

# 执行SQL语句
cursor.execute('''CREATE TABLE IF NOT EXISTS users
               (id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE,
                age INTEGER CHECK(age >= 18))''')

# 插入数据
cursor.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
               ('张三', 'zhangsan@example.com', 25))

# 提交事务
conn.commit()

# 查询数据
cursor.execute("SELECT * FROM users WHERE age > ?", (20,))
for row in cursor.fetchall():
    print(row)

# 关闭连接
conn.close()

1.2 高级特性

行工厂与类型转换：

# 自定义行处理
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn.row_factory = dict_factory  # 查询结果转为字典

# 注册类型适配器
def adapt_datetime(dt):
    return dt.isoformat()

sqlite3.register_adapter(datetime.datetime, adapt_datetime)

事务控制与异常处理：

try:
    conn = sqlite3.connect('app.db')
    cursor = conn.cursor()
    
    # 执行多个操作
    cursor.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (100, 1))
    cursor.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (100, 2))
    
    conn.commit()  # 提交事务
except sqlite3.Error as e:
    conn.rollback()  # 回滚事务
    print(f"数据库错误: {e}")
finally:
    conn.close()

二、PyMySQL与psycopg2：主流数据库驱动

2.1 PyMySQL连接MySQL

安装与基础使用：

pip install pymysql
import pymysql

# 建立连接
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='yourpassword',
    database='test_db',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor  # 返回字典形式结果
)

try:
    with connection.cursor() as cursor:
        # 执行SQL
        sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
        cursor.execute(sql, ('李四', 'lisi@example.com'))
    
    # 提交事务
    connection.commit()
    
    # 查询数据
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM users WHERE name LIKE %s", ('李%',))
        results = cursor.fetchall()
        for row in results:
            print(row)
finally:
    connection.close()

批量操作与事务：

# 批量插入
data = [('王五', 'wangwu@example.com'), ('赵六', 'zhaoliu@example.com')]
cursor.executemany("INSERT INTO users (name, email) VALUES (%s, %s)", data)

# 事务控制示例
try:
    with connection.cursor() as cursor:
        cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    connection.commit()
except:
    connection.rollback()
    raise

2.2 psycopg2连接PostgreSQL

安装与基础使用：

pip install psycopg2-binary
import psycopg2
from psycopg2 import sql

# 建立连接
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="yourpassword"
)

# 使用上下文管理器自动处理连接
with conn:
    with conn.cursor() as cursor:
        # 安全拼接SQL（防止注入）
        query = sql.SQL("SELECT * FROM {} WHERE age > %s").format(
            sql.Identifier('users')
        )
        cursor.execute(query, (18,))
        
        # 获取结果
        records = cursor.fetchmany(10)  # 获取前10条
        for record in records:
            print(record)

# 自动提交或关闭连接

PostgreSQL特有功能：

# 使用COPY命令高效导入数据
with conn.cursor() as cursor:
    with open('data.csv', 'r') as f:
        cursor.copy_expert("COPY users FROM STDIN WITH CSV HEADER", f)
    conn.commit()

# 使用JSONB类型
cursor.execute("""
    INSERT INTO products (id, data) 
    VALUES (%s, %s::jsonb)
""", (1, '{"name": "Laptop", "specs": {"RAM": "16GB"}}'))

三、SQLAlchemy ORM：Python式的数据库交互

3.1 核心概念与基础配置

SQLAlchemy是Python最强大的ORM框架，提供两种使用方式：

  • Core：SQL表达式语言

  • ORM：对象关系映射

安装与引擎配置：

pip install sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建引擎（连接池自动管理）
engine = create_engine(
    'mysql+pymysql://user:password@localhost/mydb',
    echo=True,  # 打印SQL日志（调试用）
    pool_size=5,  # 连接池大小
    max_overflow=10  # 最大溢出连接数
)

# 基类和会话工厂
Base = declarative_base()
Session = sessionmaker(bind=engine)

# 使用示例
session = Session()
try:
    # 数据库操作...
    session.commit()
except:
    session.rollback()
    raise
finally:
    session.close()

3.2 模型定义与基本操作

定义数据模型：

from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.orm import relationship

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    email = Column(String(100), unique=True)
    created_at = Column(DateTime, default=datetime.now)
    
    # 一对多关系
    articles = relationship("Article", back_populates="author")

class Article(Base):
    __tablename__ = 'articles'
    
    id = Column(Integer, primary_key=True)
    title = Column(String(100))
    content = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))
    
    # 多对一关系
    author = relationship("User", back_populates="articles")

# 创建表
Base.metadata.create_all(engine)

CRUD操作：

# 创建
new_user = User(name='王五', email='wangwu@example.com')
session.add(new_user)

# 批量创建
session.add_all([
    User(name='赵六', email='zhaoliu@example.com'),
    User(name='钱七', email='qianqi@example.com')
])

# 查询
user = session.query(User).filter_by(name='王五').first()
users = session.query(User).filter(User.name.like('王%')).all()

# 更新
user.email = 'new_email@example.com'

# 删除
session.delete(user)

# 提交
session.commit()

3.3 高级查询技巧

复杂查询：

from sqlalchemy import and_, or_, func

# 多条件查询
results = session.query(User).filter(
    and_(
        User.age >= 18,
        or_(
            User.name.like('张%'),
            User.email.contains('example')
        )
    )
).order_by(User.created_at.desc()).limit(10)

# 聚合查询
count = session.query(func.count(User.id)).scalar()
avg_age = session.query(func.avg(User.age)).filter(User.gender == 'M').scalar()

# 连接查询
result = session.query(User, Article).join(Article, User.id == Article.user_id).all()

# 原生SQL
result = session.execute("SELECT * FROM users WHERE age > :age", {'age': 18})

性能优化：

# 延迟加载与预加载
from sqlalchemy.orm import joinedload, subqueryload

# 避免N+1查询问题
users = session.query(User).options(joinedload(User.articles)).all()

# 批量处理
for chunk in session.query(User).yield_per(100):  # 每次处理100条
    process_users(chunk)

四、实战对比与选型建议

4.1 技术对比

特性SQLite3PyMySQL/psycopg2SQLAlchemy ORM
学习曲线简单中等较复杂
性能高(嵌入式)高中等(有ORM开销)
功能完整性基础功能完整功能最完整
适用场景小型应用/移动端传统数据库操作复杂业务系统
事务支持支持支持支持
跨数据库兼容性无无优秀

4.2 选型建议

  1. 小型工具/单机应用：优先选择SQLite3

    • 无需额外安装数据库服务

    • 数据存储在单个文件中

    • 适合配置管理、本地缓存等场景

  2. 传统业务系统：PyMySQL/psycopg2 + 少量SQLAlchemy Core

    • 需要精细控制SQL

    • 已有成熟的SQL代码

    • 对性能要求极高的场景

  3. 现代Web应用：SQLAlchemy ORM

    • 需要快速开发迭代

    • 业务逻辑复杂

    • 可能需要支持多种数据库

    • 团队协作开发

4.3 混合使用模式

实际项目中可以灵活组合这些技术：

# 在SQLAlchemy中使用原生SQL获取特定优化查询
result = session.execute("""
    WITH active_users AS (
        SELECT user_id, COUNT(*) as post_count 
        FROM posts 
        WHERE created_at > NOW() - INTERVAL '30 days'
        GROUP BY user_id
    )
    SELECT u.name, au.post_count
    FROM users u
    JOIN active_users au ON u.id = au.user_id
    ORDER BY au.post_count DESC
    LIMIT 10
""")

# 将结果转为ORM对象
for row in result:
    user = session.merge(User(**row))  # 将结果合并到会话
    process_user(user)

五、安全最佳实践

5.1 防止SQL注入

危险做法：

# 直接拼接SQL字符串（绝对避免！）
cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

安全做法：

# 使用参数化查询
cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))

# SQLAlchemy的安全查询
session.query(User).filter(User.name == user_input)

5.2 连接管理

推荐模式：

# 使用上下文管理器确保连接关闭
with sqlite3.connect('db.sqlite') as conn:
    # 操作数据库...
    pass  # 自动关闭连接

# 使用连接池（SQLAlchemy自动管理）
engine = create_engine('...', pool_size=5)

5.3 敏感信息保护

# 从环境变量读取配置，避免硬编码
import os
from sqlalchemy import create_engine

db_url = os.getenv('DATABASE_URL', 'sqlite:///default.db')
engine = create_engine(db_url)

结语

通过本文的学习，你应该已经掌握了Python操作数据库的三种主要方式。每种技术都有其适用场景，关键在于根据项目需求做出合理选择。记住，好的数据库交互设计应该：

  1. 保证数据安全（防止注入）

  2. 合理管理连接资源

  3. 平衡性能与开发效率

  4. 提供良好的错误处理机制

进一步学习建议：

  1. 研究SQLAlchemy的异步支持（asyncpg/aiomysql）

  2. 学习数据库迁移工具（Alembic）

  3. 探索Django ORM的比较与选择

  4. 了解大数据场景下的特殊处理（分库分表）

如果你在项目实践中遇到任何问题，欢迎在评论区留言讨论。觉得本文有帮助的话，请点赞收藏支持！

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值