更新数据 - UPDATE¶
现在让我们看看如何使用 SQLModel 更新数据。
从上一个代码继续¶
如前所述，我们将从上次的代码继续。
👀 完整文件预览
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def select_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.age <= 35)
results = session.exec(statement)
for hero in results:
print(hero)
def main():
create_db_and_tables()
create_heroes()
select_heroes()
if __name__ == "__main__":
main()
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def select_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.age <= 35)
results = session.exec(statement)
for hero in results:
print(hero)
def main():
create_db_and_tables()
create_heroes()
select_heroes()
if __name__ == "__main__":
main()
在运行示例之前，记得删除 database.db 文件，以确保获得相同的结果。
使用 SQL 更新¶
让我们快速查看如何使用 SQL 更新数据：
UPDATE hero
SET age=16
WHERE name = "Spider-Boy"
这大致意味着：
嘿，SQL 数据库 👋，我要
UPDATE名为hero的表。请将
age列的值设置为16......对于每一行，
WHERE列name的值等于"Spider-Boy"。
与 SELECT 语句类似，第一部分定义了要操作的列：要更新哪些列以及将其设置为哪个值。其余列保持不变。
第二部分则通过 WHERE 定义了应该应用此更新的行。
在此案例中，由于只有一个名字为 "Spider-Boy" 的英雄，所以更新只会应用于这一行。
Info
请注意，在 UPDATE 中，单等号（=）表示 赋值 ，即将列设置为某个值。
而在 WHERE 中，相同的单等号（=）用于 比较 两个值，以查找匹配的行。
这与 Python 和大多数编程语言的用法有所不同，在这些语言中，单等号（=）用于赋值，而双等号（==）用于比较。
你可以在 DB Browser for SQLite 中尝试执行这个 SQL：
更新后，表中的数据将如下所示，Spider-Boy 的年龄已更新为 16：
| id | name | secret_name | age |
|---|---|---|---|
| 1 | Deadpond | Dive Wilson | null |
| 2 | Spider-Boy | Pedro Parqueador | 16 ✨ |
| 3 | Rusty-Man | Tommy Sharp | 48 |
Tip
通常情况下，你会通过 id 查找要更新的行，例如：
UPDATE hero
SET age=16
WHERE id = 2
但在上面的示例中，我使用了 name 来使操作更直观。
现在，让我们用 SQLModel 在代码中做同样的更新。
为了获得相同的结果，在运行示例之前删除 database.db 文件。
从数据库读取数据¶
我们将从选择英雄 "Spider-Boy" 开始，这也是我们将要更新的数据：
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
# 下面的代码省略 👇
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
# 下面的代码省略 👇
👀 完整文件预览
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
别忘了将 update_heroes() 函数添加到 main() 函数中，这样在从命令行执行程序时会调用它：
# 上面的代码省略 👆
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
# 上面的代码省略 👆
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
👀 完整文件预览
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
到目前为止，在命令行运行程序时将输出：
$ python app.py
// 一些模板代码和之前的输出省略 😉
// SELECT with WHERE
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age
FROM hero
WHERE hero.name = ?
INFO Engine [no key 0.00017s] ('Spider-Boy',)
// 打印从数据库获取的英雄
Hero: name='Spider-Boy' secret_name='Pedro Parqueador' age=None id=2
Tip
注意到到目前为止，这个英雄的年龄仍然没有设置。
设置字段值¶
现在你已经有了一个 hero 对象，你可以简单地设置你想要的字段值（代表列的属性）。
在本例中，我们将 age 设置为 16：
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
# 下面的代码省略 👇
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
# 下面的代码省略 👇
👀 完整文件预览
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
将英雄添加到会话中¶
现在，由于 hero 对象在内存中的值已经发生了变化，特别是 age 的新值，我们需要将它添加到会话中。
这与我们在创建新的英雄实例时做的操作是一样的：
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
# 下面的代码省略 👇
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
# 下面的代码省略 👇
👀 完整文件预览
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
提交会话¶
要保存会话中的当前更改，需要 提交 会话。
这将把更新后的英雄保存到数据库中：
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
# 下面的代码省略 👇
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
# 下面的代码省略 👇
👀 完整文件预览
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
它还会保存会话中任何其他已经添加的内容。
例如，如果你之前创建了新的英雄并将这些对象添加到会话中，它们也将在这次提交中一起保存。
此提交将生成以下输出：
$ python app.py
// 一些模板输出省略 😉
// 上述输出省略 🙈
// 更新英雄数据的 SQL 语句
INFO Engine UPDATE hero SET age=? WHERE hero.id = ?
INFO Engine [generated in 0.00017s] (16, 2)
INFO Engine COMMIT
刷新对象¶
此时，英雄数据已在数据库中更新，并且新数据已经保存。
如果我们访问某个属性，例如 hero.name，对象中的数据会自动刷新。
但在这个示例中，我们并没有访问任何属性，我们只是打印对象。同时，为了更加明确，我们将直接使用 .refresh() 来刷新对象：
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
# 下面的代码省略 👇
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
# 下面的代码省略 👇
👀 完整文件预览
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
此刷新操作将触发与访问属性时自动触发的相同 SQL 查询。因此，它将生成以下输出：
$ python app.py
// 一些模板输出省略 😉
// 上述输出省略 🙈
// 执行选择最新英雄数据的 SQL 查询
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age
FROM hero
WHERE hero.id = ?
INFO Engine [generated in 0.00018s] (2,)
打印更新后的对象¶
现在我们可以直接打印出更新后的英雄：
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
# 下面的代码省略 👇
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
# 下面的代码省略 👇
👀 完整文件预览
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
print("Hero:", hero)
hero.age = 16
session.add(hero)
session.commit()
session.refresh(hero)
print("Updated hero:", hero)
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
因为我们在更新之后立即刷新了它，它现在包含最新的数据，包括我们刚刚更新的 age。
所以，打印出来将显示新的 age：
$ python app.py
// 一些模板输出省略 😉
// 上述输出省略 🙈
// 打印更新后的英雄，包含新的年龄
Updated hero: name='Spider-Boy' secret_name='Pedro Parqueador' age=16 id=2
回顾代码¶
现在让我们回顾一下所有的代码：
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy") # (1)!
results = session.exec(statement) # (2)!
hero = results.one() # (3)!
print("Hero:", hero) # (4)!
hero.age = 16 # (5)!
session.add(hero) # (6)!
session.commit() # (7)!
session.refresh(hero) # (8)!
print("Updated hero:", hero) # (9)!
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
-
Select the hero we will work with.
-
Execute the query with the select statement object.
This generates the output:
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.name = ? INFO Engine [no key 0.00017s] ('Spider-Boy',) -
Get one hero object, expecting exactly one.
Tip
This ensures there's no more than one, and that there's exactly one, not
None.This would never return
None, instead it would raise an exception. -
Print the hero object.
This generates the output:
Hero: name='Spider-Boy' secret_name='Pedro Parqueador' age=None id=2 -
Set the hero's age field to the new value
16.Now the
heroobject in memory has a different value for the age, but it is still not saved to the database. -
Add the hero to the session.
This puts it in that temporary place in the session before committing.
But it's still not saved in the database yet.
-
Commit the session.
This saves the updated hero to the database.
And this generates the output:
INFO Engine UPDATE hero SET age=? WHERE hero.id = ? INFO Engine [generated in 0.00017s] (16, 2) INFO Engine COMMIT -
Refresh the hero object to have the recent data, including the age we just committed.
This generates the output:
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.id = ? INFO Engine [generated in 0.00018s] (2,) -
Print the updated hero object.
This generates the output:
Updated hero: name='Spider-Boy' secret_name='Pedro Parqueador' age=16 id=2
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy") # (1)!
results = session.exec(statement) # (2)!
hero = results.one() # (3)!
print("Hero:", hero) # (4)!
hero.age = 16 # (5)!
session.add(hero) # (6)!
session.commit() # (7)!
session.refresh(hero) # (8)!
print("Updated hero:", hero) # (9)!
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
-
Select the hero we will work with.
-
Execute the query with the select statement object.
This generates the output:
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.name = ? INFO Engine [no key 0.00017s] ('Spider-Boy',) -
Get one hero object, expecting exactly one.
Tip
This ensures there's no more than one, and that there's exactly one, not
None.This would never return
None, instead it would raise an exception. -
Print the hero object.
This generates the output:
Hero: name='Spider-Boy' secret_name='Pedro Parqueador' age=None id=2 -
Set the hero's age field to the new value
16.Now the
heroobject in memory has a different value for the age, but it is still not saved to the database. -
Add the hero to the session.
This puts it in that temporary place in the session before committing.
But it's still not saved in the database yet.
-
Commit the session.
This saves the updated hero to the database.
And this generates the output:
INFO Engine UPDATE hero SET age=? WHERE hero.id = ? INFO Engine [generated in 0.00017s] (16, 2) INFO Engine COMMIT -
Refresh the hero object to have the recent data, including the age we just committed.
This generates the output:
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.id = ? INFO Engine [generated in 0.00018s] (2,) -
Print the updated hero object.
This generates the output:
Updated hero: name='Spider-Boy' secret_name='Pedro Parqueador' age=16 id=2
Tip
查看每行代码旁边的数字气泡，了解每行代码的作用。
多次更新¶
使用 SQLModel 进行更新的过程与创建新对象的过程差不多，你只需要将它们添加到会话中，然后提交它们。
这也意味着你可以一次更新多个字段（属性，列），并且还可以一次更新多个对象（英雄）：
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy") # (1)!
results = session.exec(statement) # (2)!
hero_1 = results.one() # (3)!
print("Hero 1:", hero_1) # (4)!
statement = select(Hero).where(Hero.name == "Captain North America") # (5)!
results = session.exec(statement) # (6)!
hero_2 = results.one() # (7)!
print("Hero 2:", hero_2) # (8)!
hero_1.age = 16 # (9)!
hero_1.name = "Spider-Youngster" # (10)!
session.add(hero_1) # (11)!
hero_2.name = "Captain North America Except Canada" # (12)!
hero_2.age = 110 # (13)!
session.add(hero_2) # (14)!
session.commit() # (15)!
session.refresh(hero_1) # (16)!
session.refresh(hero_2) # (17)!
print("Updated hero 1:", hero_1) # (18)!
print("Updated hero 2:", hero_2) # (19)!
# (20)!
# 下面的代码省略 👇
-
Select the hero
Spider-Boy. -
Execute the select statement.
This generates the output:
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.name = ? INFO Engine [no key 0.00018s] ('Spider-Boy',) -
Get one hero object, the only one that should be there for Spider-Boy.
-
Print this hero.
This generates the output:
Hero 1: name='Spider-Boy' secret_name='Pedro Parqueador' age=None id=2 -
Select another hero.
-
Execute the select statement.
This generates the output:
INFO Engine BEGIN (implicit) INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.name = ? INFO Engine [no key 0.00020s] ('Captain North America',)Tip
See the
BEGINat the top?This is SQLAlchemy automatically starting a transaction for us.
This way, we could revert the last changes (if there were some) if we wanted to, even if the SQL to create them was already sent to the database.
-
Get one hero object for this new query.
The only one that should be there for Captain North America.
-
Print this second hero.
This generates the output:
Hero 2: name='Captain North America' secret_name='Esteban Rogelios' age=93 id=7 -
Update the age for the first hero.
Set the value of the attribute
ageto16.This updates the hero object in memory, but not yet in the database.
-
Update the name of the first hero.
Now the name of the hero will not be
"Spider-Boy"but"Spider-Youngster".Also, this updates the object in memory, but not yet in the database.
-
Add this first hero to the session.
This puts it in the temporary space in the session before committing it to the database.
It is not saved yet.
-
Update the name of the second hero.
Now the hero has a bit more precision in the name. 😜
This updates the object in memory, but not yet in the database.
-
Update the age of the second hero.
This updates the object in memory, but not yet in the database.
-
Add the second hero to the session.
This puts it in the temporary space in the session before committing it to the database.
-
Commit all the changes tracked in the session.
This commits everything in one single batch.
This generates the output:
INFO Engine UPDATE hero SET name=?, age=? WHERE hero.id = ? INFO Engine [generated in 0.00028s] (('Spider-Youngster', 16, 2), ('Captain North America Except Canada', 110, 7)) INFO Engine COMMITTip
See how SQLAlchemy (that powers SQLModel) optimizes the SQL to do as much work as possible in a single batch.
Here it updates both heroes in a single SQL query.
-
Refresh the first hero.
This generates the output:
INFO Engine BEGIN (implicit) INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.id = ? INFO Engine [generated in 0.00023s] (2,)Tip
Because we just committed a SQL transaction with
COMMIT, SQLAlchemy will automatically start a new transaction withBEGIN. -
Refresh the second hero.
This generates the output:
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.id = ? INFO Engine [cached since 0.001709s ago] (7,)Tip
SQLAlchemy is still using the previous transaction, so it doesn't have to create a new one.
-
Print the first hero, now updated.
This generates the output:
Updated hero 1: name='Spider-Youngster' secret_name='Pedro Parqueador' age=16 id=2 -
Print the second hero, now updated.
This generates the output:
Updated hero 2: name='Captain North America Except Canada' secret_name='Esteban Rogelios' age=110 id=7 -
Here is the end of the
withblock statement, so the session can execute its terminating code.The session will
ROLLBACK(undo) any possible changes in the last transaction that were not committed.This generates the output:
INFO Engine ROLLBACK
# 上面的代码省略 👆
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy") # (1)!
results = session.exec(statement) # (2)!
hero_1 = results.one() # (3)!
print("Hero 1:", hero_1) # (4)!
statement = select(Hero).where(Hero.name == "Captain North America") # (5)!
results = session.exec(statement) # (6)!
hero_2 = results.one() # (7)!
print("Hero 2:", hero_2) # (8)!
hero_1.age = 16 # (9)!
hero_1.name = "Spider-Youngster" # (10)!
session.add(hero_1) # (11)!
hero_2.name = "Captain North America Except Canada" # (12)!
hero_2.age = 110 # (13)!
session.add(hero_2) # (14)!
session.commit() # (15)!
session.refresh(hero_1) # (16)!
session.refresh(hero_2) # (17)!
print("Updated hero 1:", hero_1) # (18)!
print("Updated hero 2:", hero_2) # (19)!
# (20)!
# 下面的代码省略 👇
-
Select the hero
Spider-Boy. -
Execute the select statement.
This generates the output:
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.name = ? INFO Engine [no key 0.00018s] ('Spider-Boy',) -
Get one hero object, the only one that should be there for Spider-Boy.
-
Print this hero.
This generates the output:
Hero 1: name='Spider-Boy' secret_name='Pedro Parqueador' age=None id=2 -
Select another hero.
-
Execute the select statement.
This generates the output:
INFO Engine BEGIN (implicit) INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.name = ? INFO Engine [no key 0.00020s] ('Captain North America',)Tip
See the
BEGINat the top?This is SQLAlchemy automatically starting a transaction for us.
This way, we could revert the last changes (if there were some) if we wanted to, even if the SQL to create them was already sent to the database.
-
Get one hero object for this new query.
The only one that should be there for Captain North America.
-
Print this second hero.
This generates the output:
Hero 2: name='Captain North America' secret_name='Esteban Rogelios' age=93 id=7 -
Update the age for the first hero.
Set the value of the attribute
ageto16.This updates the hero object in memory, but not yet in the database.
-
Update the name of the first hero.
Now the name of the hero will not be
"Spider-Boy"but"Spider-Youngster".Also, this updates the object in memory, but not yet in the database.
-
Add this first hero to the session.
This puts it in the temporary space in the session before committing it to the database.
It is not saved yet.
-
Update the name of the second hero.
Now the hero has a bit more precision in the name. 😜
This updates the object in memory, but not yet in the database.
-
Update the age of the second hero.
This updates the object in memory, but not yet in the database.
-
Add the second hero to the session.
This puts it in the temporary space in the session before committing it to the database.
-
Commit all the changes tracked in the session.
This commits everything in one single batch.
This generates the output:
INFO Engine UPDATE hero SET name=?, age=? WHERE hero.id = ? INFO Engine [generated in 0.00028s] (('Spider-Youngster', 16, 2), ('Captain North America Except Canada', 110, 7)) INFO Engine COMMITTip
See how SQLAlchemy (that powers SQLModel) optimizes the SQL to do as much work as possible in a single batch.
Here it updates both heroes in a single SQL query.
-
Refresh the first hero.
This generates the output:
INFO Engine BEGIN (implicit) INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.id = ? INFO Engine [generated in 0.00023s] (2,)Tip
Because we just committed a SQL transaction with
COMMIT, SQLAlchemy will automatically start a new transaction withBEGIN. -
Refresh the second hero.
This generates the output:
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero WHERE hero.id = ? INFO Engine [cached since 0.001709s ago] (7,)Tip
SQLAlchemy is still using the previous transaction, so it doesn't have to create a new one.
-
Print the first hero, now updated.
This generates the output:
Updated hero 1: name='Spider-Youngster' secret_name='Pedro Parqueador' age=16 id=2 -
Print the second hero, now updated.
This generates the output:
Updated hero 2: name='Captain North America Except Canada' secret_name='Esteban Rogelios' age=110 id=7 -
Here is the end of the
withblock statement, so the session can execute its terminating code.The session will
ROLLBACK(undo) any possible changes in the last transaction that were not committed.This generates the output:
INFO Engine ROLLBACK
👀 完整文件预览
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy") # (1)!
results = session.exec(statement) # (2)!
hero_1 = results.one() # (3)!
print("Hero 1:", hero_1) # (4)!
statement = select(Hero).where(Hero.name == "Captain North America") # (5)!
results = session.exec(statement) # (6)!
hero_2 = results.one() # (7)!
print("Hero 2:", hero_2) # (8)!
hero_1.age = 16 # (9)!
hero_1.name = "Spider-Youngster" # (10)!
session.add(hero_1) # (11)!
hero_2.name = "Captain North America Except Canada" # (12)!
hero_2.age = 110 # (13)!
session.add(hero_2) # (14)!
session.commit() # (15)!
session.refresh(hero_1) # (16)!
session.refresh(hero_2) # (17)!
print("Updated hero 1:", hero_1) # (18)!
print("Updated hero 2:", hero_2) # (19)!
# (20)!
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def update_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Spider-Boy") # (1)!
results = session.exec(statement) # (2)!
hero_1 = results.one() # (3)!
print("Hero 1:", hero_1) # (4)!
statement = select(Hero).where(Hero.name == "Captain North America") # (5)!
results = session.exec(statement) # (6)!
hero_2 = results.one() # (7)!
print("Hero 2:", hero_2) # (8)!
hero_1.age = 16 # (9)!
hero_1.name = "Spider-Youngster" # (10)!
session.add(hero_1) # (11)!
hero_2.name = "Captain North America Except Canada" # (12)!
hero_2.age = 110 # (13)!
session.add(hero_2) # (14)!
session.commit() # (15)!
session.refresh(hero_1) # (16)!
session.refresh(hero_2) # (17)!
print("Updated hero 1:", hero_1) # (18)!
print("Updated hero 2:", hero_2) # (19)!
# (20)!
def main():
create_db_and_tables()
create_heroes()
update_heroes()
if __name__ == "__main__":
main()
Tip
点击每个数字气泡，回顾每行代码的作用。👆
总结¶
更新 SQLModel 对象的方式与更新其他 Python 对象相同。🐍
只需要记得将它们 add 到 会话 中，然后执行 commit。如果需要，还可以执行 refresh 来刷新它们。