[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一样的作用
