Python中的数据库操作

在 Python 中操作数据库通常通过数据库驱动库（如 sqlite3、pymysql、psycopg2）或 ORM 框架（如 SQLAlchemy）实现。
1， SQLite数据库
下面的代码以SQLite数据库和sqlite3库为例，展示如何进行数据库的创建、读取、写入、修改（更新和删除）等操作。

import sqlite3

# 连接数据库（不存在时会自动创建）
conn = sqlite3.connect('pyDb.db')
# 创建一个游标对象
cursor = conn.cursor()

# 创建 users 表（包含 id, name, age 字段）
# 使用三引号可以定义一个跨越多行的字符串。这对于SQL语句特别有用，因为SQL语句通常包含多行以提高可读性。
# 使用单引号或双引号，将不得不使用字符串连接（例如，使用+）来创建多行字符串，这会使代码更混乱。
create_table_sql = '''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    UNIQUE(name, age)
)
'''
cursor.execute(create_table_sql)
conn.commit()  # 提交事务

# 插入单条数据（参数化查询防止 SQL 注入）
insert_sql = "INSERT INTO users (name, age) VALUES (?, ?)"
cursor.execute(insert_sql, ('Alice', 25))
conn.commit()

# 批量插入数据
users = [('Bob', 30), ('Charlie', 35), ('Diana', 28)]
cursor.executemany(insert_sql, users)
conn.commit()

# 查询所有数据
cursor.execute("SELECT * FROM users")
all_users = cursor.fetchall()
print("All users:", all_users)
# 输出：  All users: [(89, 'Alice', 25), (90, 'Bob', 30), (91, 'Charlie', 35), (92, 'Diana', 28)]

# 条件查询
cursor.execute("SELECT * FROM users WHERE age > ?", (28,))
filtered_users = cursor.fetchall()
print("Filtered users:", filtered_users)
# 输出：Filtered users: [(90, 'Bob', 30), (91, 'Charlie', 35)]

# 更新指定记录的 age 字段
update_sql = "UPDATE users SET age = ? WHERE name = ?"
cursor.execute(update_sql, (26, 'Alice'))
conn.commit()

# 在 Python 的 sqlite3 模块中，当使用参数化查询时，应该始终将参数作为一个元组（tuple）或列表（list）提供，即使只有一个参数。
# 在 Python 中，('Alice',) 是一个单元素元组，而 ['Alice',] 是一个单元素列表。
#  SQLite 绑定参数通常是通过元组提供的
cursor.execute("SELECT DISTINCT name, age FROM users WHERE name = ?", ('Alice',))
single_user = cursor.fetchall()
print("user Alice:", single_user)
# 输出：user Alice: [('Alice', 26)]

# 删除指定记录
delete_sql = "DELETE FROM users WHERE name = ?"
cursor.execute(delete_sql, ('Bob',))
conn.commit()

# 查询刚删除的指定记录
cursor.execute("SELECT * FROM users WHERE name = ?", ('Bob',))
single_user = cursor.fetchall()
print("after delete user Bob:", single_user)
# 输出：after delete user Bob: []

# 清空表
cursor.execute("DELETE FROM users")
conn.commit()

cursor.execute("SELECT * FROM users")
all_users = cursor.fetchall()
print("after delete,all users:", all_users)
# 输出：after delete,all users: []

# 关闭数据库游标
cursor.close()
# 关闭连接
conn.close()

在代码中需要关注游标cursor的创建与关闭，下面是采用with自动管理游标，当 with代码结束时，游标会自动关闭，即使发生异常也是如此。

import sqlite3

# 连接到数据库（这里是一个内存中的数据库，仅用于示例）
# 在 Python 的 sqlite3 模块中，sqlite3.connect(':memory:') 用于创建一个连接到内存中的 SQLite 数据库的连接。
# 这意味着数据库是临时的，并且只存在于程序的运行期间；当程序结束时，数据库将被销毁，并且所有存储的数据都将丢失。
conn = sqlite3.connect(':memory:')

# 使用 with 语句自动管理游标
try:
    with conn:
        cursor = conn.cursor()
        # 执行一些 SQL 操作
        cursor.execute('CREATE TABLE anotherUser (id INTEGER PRIMARY KEY, name TEXT)')
        cursor.execute('INSERT INTO anotherUser (name) VALUES (?)', ('Jacky',))
        cursor.execute('select * from anotherUser')
        print(cursor.fetchall())
except sqlite3.Error as e:
    print(f"An error occurred: {e}")
finally:
    # 关闭数据库连接（如果使用了 with 语句管理游标，这一步仍然需要）
    conn.close()

2，SQLAlchemy ORM
SQLAlchemy 是一个流行的 Python SQL 工具包和对象关系映射器（ORM），它提供了 SQL 的功能性和 Python 的易用性。
下面代码是一个使用 SQLAlchemy ORM（对象关系映射）框架与 SQLite 数据库进行交互的完整示例。

# 使用 SQLAlchemy ORM（对象关系映射）框架与 SQLite 数据库进行交互的完整示例
# SQLAlchemy 是一个流行的 Python SQL 工具包和对象关系映射器（ORM），它提供了 SQL 的功能性和 Python 的易用性。

# create_engine：用于创建数据库引擎，它是 SQLAlchemy 连接到数据库的方式。
# Column, Integer, String：定义了数据库表的列和列的数据类型。
from sqlalchemy import create_engine, Column, Integer, String
# declarative_base：用于生成一个基类，所有映射的类都将继承这个基类。
# from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import declarative_base
# sessionmaker：用于创建会话，会话是 ORM 操作数据库时的一个工作环境。
from sqlalchemy.orm import sessionmaker

# 创建数据库引擎（SQLite数据库）
# 创建了一个 SQLite 数据库的引擎，数据库文件名为 example_orm.db。如果文件不存在，SQLAlchemy 会自动创建它。
# echo=True 表示打印出执行的 SQL 语句，这对于调试非常有用。
# 在标准的 URL 中，// 用于分隔协议和主机名（例如，在 http://www.example.com 中）。
# 但是，SQLite 数据库连接不涉及网络主机，因此这里使用三条斜线（///）而不是两条，主要是为了在格式上保持一致，
# 并且清晰地表明这是一个特殊的、文件基础的数据库连接。实际上，这里的 /// 后面直接跟着的是数据库文件的路径。
engine = create_engine('sqlite:///example_orm.db', echo=True)

# 创建一个基类，Base 是所有映射类的基类。
# 在 SQLAlchemy 中，Base 通常是一个基类，用于声明模型（即 ORM 中的类，它们映射到数据库中的表）。
# Base 类通常通过 declarative_base() 函数创建，它返回一个基类，可以从中继承来定义需要的模型类。
Base = declarative_base()


# 定义User模型
# User 类代表数据库中的一个表，表名为 users。
class User(Base):
    __tablename__ = 'users'
    # id, name, age 是表中的列，分别映射到 Python 类的属性上。id 列是主键，且自动递增。name 和 age 列不允许为空。
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    age = Column(Integer, nullable=False)


# 创建所有表
# 这行代码会在数据库中创建 User 表（如果它还不存在的话）。
# 在 SQLAlchemy ORM（对象关系映射）框架中，Base.metadata.create_all(engine)用于在数据库中创建所有通过 SQLAlchemy 定义的表结构。
# 每个通过 Base 继承的模型类都会将其表定义贡献给 Base.metadata。
# metadata 是一个容器，它包含了所有通过 SQLAlchemy 定义的表结构的信息（如列、索引、外键等）。
# create_all() 是 MetaData 类的一个方法（Base.metadata 就是 MetaData 的一个实例），
# create_all()它接受一个数据库引擎（engine）作为参数，并在该引擎所连接的数据库中创建所有在 metadata 中定义的表。
# create_all()这个方法实际上会生成并执行创建表的 SQL 语句。
# engine 是一个 SQLAlchemy 引擎对象，它封装了数据库连接池和方言（即针对特定数据库的 SQL 语法和功能的实现）。
# 通过传递引擎给 create_all() 方法，SQLAlchemy 能够知道在哪里（即哪个数据库）以及如何使用（即使用哪种数据库的 SQL 语法）来创建表。
# 当调用 Base.metadata.create_all(engine) 时，SQLAlchemy 会查看 Base.metadata 中定义的所有表结构，并生成相应的 SQL 语句来在 engine 所连接的数据库中创建这些表。
Base.metadata.create_all(engine)

# 创建会话
# sessionmaker 是一个工厂函数，用于创建 Session 类的实例。
# bind=engine 表示这个会话将绑定到之前创建的数据库引擎上。
# 通过调用 sessionmaker(bind=engine)，创建了一个新的 Session 类（实际上是一个类工厂返回的类）。
# 这里的 bind=engine 参数指定了这个 Session 类创建的实例将默认连接到哪个数据库引擎。
# 这意味着，使用这个 Session 类创建的任何会话都将自动与指定的数据库引擎关联。
Session = sessionmaker(bind=engine)
# 通过调用 Session(),创建了一个 Session 实例。
# 一旦有了 session 实例，就可以开始使用它来与数据库交互了。
# 例如，可以使用 session.query() 来执行查询，或者调用 session.add() 来添加新对象到会话中，然后调用 session.commit() 来将更改持久化到数据库中。
session = Session()

# 插入数据
# 创建一个新的 User 对象，并将其添加到会话中。
new_user = User(name='Charlie', age=35)
session.add(new_user)
# session.commit() 提交事务，将更改保存到数据库中。
session.commit()

# 查询数据
# 查询 User 表中的所有记录。
users = session.query(User).all()
# 遍历查询结果，并打印出每个用户的 ID、姓名和年龄。
for user in users:
    print(f'query result:=== ID: {user.id}, Name: {user.name}, Age: {user.age}')
# 上面代码输出：query result:=== ID: 1, Name: Charlie, Age: 35

# 更新数据
# 查询名为 'Charlie' 的用户。
user_to_update = session.query(User).filter_by(name='Charlie').first()
# 更新该用户的年龄。
user_to_update.age = 36
# 提交事务，将更改保存到数据库中。
session.commit()

# 删除数据
# 查询名为 'Charlie' 的用户。
user_to_delete = session.query(User).filter_by(name='Charlie').first()
# 从会话中删除该用户。
session.delete(user_to_delete)
# 提交事务，将更改保存到数据库中。
session.commit()

# 关闭会话
session.close()

另外还可以通过with语句来管理会话的打开与关闭，示例代码如下：

# 使用上下文管理器（即 with 语句）来自动管理会话的开启和关闭。
# 在 SQLAlchemy ORM 中，with session.begin(): 语句是一种非常方便的上下文管理方式，用于自动处理数据库会话的开始、提交和回滚。
# SQLAlchemy 会确保在 with 块内部执行的数据库操作要么全部成功并提交到数据库，要么在遇到异常时全部回滚，以保持数据的一致性。
with session.begin():
    new_user = User(name='Mary', age=35)
    session.add(new_user)
    users = session.query(User).all()
    # 遍历查询结果，并打印出每个用户的 ID、姓名和年龄。
    for user in users:
        print(f'with query result:=== ID: {user.id}, Name: {user.name}, Age: {user.age}')
    # 上面的代码输出：with query result:=== ID: 1, Name: Mary, Age: 35
# 离开 with 块：当离开 with 块时（即代码执行到 with 块的末尾），SQLAlchemy 会检查是否有异常发生。
# 如果没有异常：SQLAlchemy 会自动调用 session.commit() 来提交事务。这意味着所有在事务中执行的数据库操作都会被永久保存到数据库中。
# 如果有异常：SQLAlchemy 会自动调用 session.rollback() 来回滚事务。这意味着所有在事务中执行的数据库操作都会被撤销，数据库将恢复到事务开始之前的状态。