[SQLAlchemy] SQLAlchemy学习笔记(1): 基础使用

Installation

pip install SQLAlchemy
make sure that is sql-alchemy2

创建引擎

数据库url格式一般为dialect+driver://username:password@host:port/database

# PyMySQL - python里的数据库驱动
engine = create_engine("mysql+pymysql://scott:tiger@localhost/foo?charset=utf8mb4")

附加设置

https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.create_engine

sqlAlchemy结构 -- Core和ORM

第一次去看官方文档和教程会因为这个搞得大糊涂, 所以学习之前还得先弄清它的分层

Core

core的特点是可以直接使用接近原生sql的语法

from sqlachemy import text
 
# 数据查询
with engine.connect() as conn:
    result = conn.execute(text("select * from users"))
    print(result.all())
 
# result 可以遍历，每一个行结果是一个 Row 对象
for row in result:
    # row 对象三种访问方式都支持
    print(row.x, row.y)
    print(row[0], row[1])
    print(row["x"], row["y"])
 
# 传递参数，使用 :var 传递
result1 = conn.execute(
    text("SELECT x, y FROM some_table WHERE y > :y"), {"y": 2}
)
# 也可以预先编译好参数, stmt其实是一条sql语句
stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y").bindparams(y=6)
result2 = conn.execute(stmt)
 
# 插入时，可以直接插入多条, 以字典列表形式
result3 = conn.execute(
    text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
    [{"x": 11, "y": 12}, {"x": 13, "y": 14}]
)
 

commit有两种, 手动commit和使用engine.begin()

# "commit as you go"  需要手动 commit
with engine.connect() as conn:
    # 所有conn.execute放这里
    conn.commit()  # 注意这里的 commit
 
# engine.begin
with engine.begin() as conn:
    # 所有conn.execute放这里

ORM

和core中的engine.connect()相似, 在这里我们使用session

from sqlalchemy.orm import Session
 
# 创建一个session对象, 然后插入对象, commit
with Session(engine) as session:
    session.add(foo)
    session.commit()
 
# 还可以使用 sessionmaker 来创建一个工厂函数，这样就不用每次都输入参数了
from sqlachemy.orm import sessionmaker
new_session = sessionmaker(engine)
# new_session() = Session(engine)
with new_session() as session:
    # do something

声明式API Declarative Base

• 使用 __tablename__ 指定数据库表名
• 使用 Column/mapped_column 声明每个字段
• 使用 Integer/String... 指定字段类型
• 使用 index 参数指定索引
• 使用 unique 参数指定唯一索引
• 使用 __table_args__ 指定其他属性，比如联合索引

下面这段代码来自官方的教程

分析一下

• 行2: 定义表的名字
• 行3: 确定主键, 使用python的类型注明
• 行4: 定义列数据类型以及范围
• 行5/14: 因为该列只需要定义数据类型, 因此可以只写上类型注明; Optional<type>的意思是该列数据是可选的, 如果没有值则为null值
• 行6: 通过外键关联其他数据库 -> relationship()

class User(Base):
    __tablename__ = "users"
    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")
    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_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})"

CRUD

INSERT

再插入数据之前, 我们需要创建相应的对象 -- 这也体现了ORM的作用: 使用对象指代数据库中的数据

然后使用add方法添加数据

session.add(user)
session.add_all([user1, user2, user3, address1])

SELECT

读取数据时, 可以将对应的sql语句抽象化

• where(): 参数为一个相等表达式, 注意左边的写法
• filter_by(): 使用**kwargs作为参数
• order_by 还可以使用 User.id.desc() 表示逆序排列
• 一般情况下，当选取整个对象的时候，都要用 scalars 方法，否则返回的是一个包含一个对象的 tuple
• 查询模型单个属性时，不需要使用 scalars

stmt = select(User).where(User.name == "some_user").order_by(User.id)
stmt = select(User).filter_by(name="some_user")
 
result = session.execute(stmt)
 
for user in result.scalars():
    print(user.name)
 
result = session.execute(select(User.name))
for row in result:
    print(row.name)
 
# selecting data by primary key
user = session.get(User, pk=1)

UPDATE

更新数据需要使用 update 语句

from sqlalchemy import update

synchronize_session 有三种选项： false, "fetch", "evaluate"，默认是 evaluate

• false 表示完全不更新 Python 中的对象
• fetch 表示重新从数据库中加载一份对象
• evaluate 表示在更新数据库的同时，也尽量在 Python 中的对象上使用同样的操作

stmt = update(User).where(User.name == "some_user").values(name="Josh").execution_options(synchronize_session="fetch")
session.execute(stmt)
 
# 或者直接对属性赋值
user.name = "Josh"

我们也可以通过update方法来实现executemany

from sqlalchemy import bindparam
# 构建sql语句
stmt = (
    update(user_table)
    .where(user_table.c.name == bindparam("oldname"))
    .values(name=bindparam("newname"))
)
with Session(engine) as session:
    session.execute(
        stmt,
        [
            {"oldname": "jack", "newname": "ed"},
            {"oldname": "wendy", "newname": "mary"},
            {"oldname": "jim", "newname": "jake"},
        ],
    )

DELETE

需要使用delete语句
from sqlalchemy import delete

stmt = delete(user_table).where(user_table.c.name == "patrick")
print(stmt)

复数where

查找数据时经常会需要多个判断条件, 比如

select * from Employee e where e.id < 5 and e.salary >= 30000;

这时候可以使用多个where(无限嵌套)

stmt = select(employee).where(employee.id < 5)
                       .where(employee.salary >= 30000)

就相当于AND, 在select/update/delete中都可以使用

使用returning

用于insert/update/delete操作后返回数据

update_stmt = (
    update(user_table)
    .where(user_table.c.name == "patrick")
    .values(fullname="Patrick the Star")
    .returning(user_table.c.id, user_table.c.name)
)
print(update_stmt)

UPDATE user_account SET fullname=:fullname
WHERE user_account.name = :name_1
RETURNING user_account.id, user_account.name

delete_stmt = (
    delete(user_table)
    .where(user_table.c.name == "patrick")
    .returning(user_table.c.id, user_table.c.name)
)
print(delete_stmt)

DELETE FROM user_account
WHERE user_account.name = :name_1
RETURNING user_account.id, user_account.name

Rollback

通过sqlalchemy我们也可以实现rollback的功能来恢复数据库的状态

seesion.rollback()

关闭会话

将会话session关闭, 连接池关闭, 所有对象都会失去连接detached

seesion.close()

Tips

在sqlalchemy2中, 创建declarative class的方法与之前的版本有所不同, 原因是为了支持PEP484(?); 但旧方法依旧被支持

创建基类

在旧版本中, 可以直接通过Base = declarative_class()来创建基类, 新版本中则是需要重新创建类

class Base(DeclarativeBase):
    pass

引用建列方法

旧版本中, 使用Column来定义列

id = Column(Integer, primary_key=True)
name = Column(String(20))

而在新版本中, 可以使用mapped_column来达到同样的目的

id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(30))

但是需要注意的是, mapped_column只用在declarative mapping中, 这意味着它不能用于Table的构造器

python的类型注明

注意看上一个例子; 在旧版本中我们需要引用sqlalchemy的各种类型, 但是新版本中我们可以使用python自带的类型注明功能了

id = Column(Integer, primary_key=True)
# or
id: Mapped[int] = mapped_column(primary_key=True)

可以发现, 如果去掉类型注明, 这两种方式其实有着同样的结构

Session & Connection

sqlalchemy2里的Session在大部分时候发挥着和Connection一样的作用