python使用SQLAlchemy 库操作本地的mysql数据库

一、SQLAlchemy是什么

SQLAlchemy ORM — SQLAlchemy 2.0 Documentation

SQLAlchemy SQL 工具包和对象关系映射器是一套全面的工具，用于处理数据库和 Python。它有几个不同的功能领域，可以单独使用或组合使用。其主要组件如下所示，组件依赖关系按层组织

上面，SQLAlchemy 最重要的两个面向前端的部分是 对象关系映射器 (ORM) 和 Core。

Core 包含 SQLAlchemy 的 SQL 和数据库集成及描述服务的广度，其中最突出的部分是 SQL 表达式语言。

SQL 表达式语言本身就是一个工具包，独立于 ORM 包，它提供了一个构建 SQL 表达式的系统，这些表达式由可组合的对象表示，然后可以在特定事务的范围内“执行”以针对目标数据库，并返回结果集。插入、更新和删除（即 DML）是通过传递表示这些语句的 SQL 表达式对象以及表示每个语句要使用的参数的字典来实现的。

ORM 构建在 Core 之上，提供了一种使用映射到数据库模式的域对象模型的方法。当使用 ORM 时，SQL 语句的构造方式与使用 Core 时基本相同，但是 DML 的任务（这里指的是业务对象在数据库中的持久性）使用称为 工作单元 的模式自动化，该模式将可变对象的状态更改转换为 INSERT、UPDATE 和 DELETE 构造，然后根据这些对象调用这些构造。SELECT 语句也通过 ORM 特定的自动化和以对象为中心的查询功能得到增强。

与使用 Core 和 SQL 表达式语言呈现以模式为中心的数据库视图以及以不变性为导向的编程范例相反，ORM 在此基础上构建了以域为中心的数据库视图，编程范例更明确地以面向对象和依赖可变性。由于关系数据库本身是一种可变服务，因此区别在于 Core/SQL 表达式语言是面向命令的，而 ORM 是面向状态的。

二、SQLAlchemy的基础

2.1 ORM是什么

ORM（Object-Relational Mapping）是一种编程技术，它允许开发者使用面向对象的方式来操作数据库，而不必直接编写SQL语句。

2.2 ORM快速入门

2.2.1 定义类表

在这里，我们定义模块级别的结构，这些结构将形成我们从数据库中查询的框架。这种结构称为声明式映射，它同时定义了一个 Python 对象模型，以及描述特定数据库中实际存在或将要存在的 SQL 表的元数据：

from typing import List
from typing import Optional
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )
    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

class Address(Base):
    __tablename__ = "address"
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
    user: Mapped["User"] = relationship(back_populates="addresses")
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

映射以一个基础类开始，上面称为 Base ，它是通过创建 DeclarativeBase 类的简单子类来创建的。然后通过创建 Base 的子类来创建单独的映射类。映射类通常引用单个特定的数据库表，其名称通过使用 __tablename__ 类级别的属性来指示。

这里创建了两个“类表”，分别是User【user_account】、Address【address】类表。主键分别是 User.id 和 Address.id 

原则

每个“类表”必须继承Base(DeclarativeBase)类表，才有相关的映射的更新。

所有 ORM 映射类至少需要一个列被声明为主键的一部分。

__repr__() 方法，虽然不是必需的，但用于调试很有用。

 2.2.2 创建数据库链接引擎

任何 SQLAlchemy 应用程序的起点是一个称为 Engine 的对象。该对象充当连接到特定数据库的中心来源，既提供工厂，也提供一个称为连接池的存储空间来存放这些数据库连接。引擎通常是针对特定数据库服务器创建的全局对象，并使用描述其如何连接到数据库主机或后端的 URL 字符串进行配置。

from sqlalchemy import create_engine
engine = create_engine("sqlite://", echo=True)

echo=True 参数表示连接发出的 SQL 将被记录到标准输出。

创建一个连接sqlite的引擎，我们实际开发中一般是链接mysql数据库。

# 更新以下字段为你本地数据库的实际用户名、密码和数据库名

username = 'root'

hostname = '192.168.110.131'

database_name = 'test'

from urllib.parse import quote

# 使用 quote 函数对密码进行编码

password_encoded = quote('Suker)&@(')

# 现在使用编码后的密码构建连接字符串

SQLALCHEMY_DATABASE_URI = f"mysql+pymysql://{username}:{password_encoded}@{hostname}/{database_name}?charset=utf8mb4"

from sqlalchemy import create_engine

engine = create_engine(

    SQLALCHEMY_DATABASE_URI,

    echo=True

)

这里对密码进行URL编码确保了连接字符串在传输和解析过程中不会因特殊字符被误解，是建立数据库连接的可靠实践。 

2.2.3 创建类表

使用我们的表元数据和引擎，我们可以使用名为 MetaData.create_all() 的方法在我们的目标 mysql数据库中一次性生成我们的定义好的类表

 Base.metadata.create_all(engine)

2.2.4 创建 Session 对象操作数据库

现在我们可以向数据库中插入数据了。我们通过创建 User 和 Address 类的实例来实现，这些类已经自动通过声明式映射过程拥有了 __init__() 方法。然后我们使用一个名为 Session 的对象将这些实例传递给数据库，该对象利用 Engine 来与数据库交互。这里使用 Session.add_all() 方法来一次性添加多个对象，而 Session.commit() 方法则用于将所有待处理的更改刷新到数据库，并提交当前的数据库事务——只要 Session 被使用，就会一直进行这个数据库事务。 

from sqlalchemy.orm import Session

with Session(engine) as session:
    spongebob = User(
        name="spongebob",
        fullname="Spongebob Squarepants",
        addresses=[Address(email_address="spongebob@sqlalchemy.org")],
    )
    sandy = User(
        name="sandy",
        fullname="Sandy Cheeks",
        addresses=[
            Address(email_address="sandy@sqlalchemy.org"),
            Address(email_address="sandy@squirrelpower.org"),
        ],
    )
    sandy = User(name="patrick", fullname="Patrick Star")
    session.add_all([spongebob, sandy, patrick])
    session.commit()

创建了三个对象  spongebob、sandy、sandy，session.add_all添加到User类表中，session.commit()提交到数据库。