sqlmodel_hero_crud_with_sqlite_insert_and_query.py

1from typing import Annotated, Optional
2
3from sqlmodel import Field, Session, SQLModel, create_engine, select
4
5
6class Hero(SQLModel, table=True):
7    id: Optional[int] = Field(default=None, primary_key=True)
8    name: str = Field(index=True)
9    secret_name: str
10    age: Optional[int] = Field(default=None, index=True)
11
12
13sqlite_file_name = "database.db"
14sqlite_url = f"sqlite:///{sqlite_file_name}"
15
16engine = create_engine(sqlite_url, echo=True)
17
18
19def create_db_and_tables():
20    SQLModel.metadata.create_all(engine)
21
22
23def create_heroes():
24    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
25    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
26    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
27
28    with Session(engine) as session:
29        session.add(hero_1)
30        session.add(hero_2)
31        session.add(hero_3)
32
33        session.commit()
34
35
36def select_heroes():
37    with Session(engine) as session:
38        statement = select(Hero).where(Hero.name == "Spider-Boy")
39        results = session.exec(statement)
40        for hero in results:
41            print(hero)
42
43
44def main():
45    create_db_and_tables()
46    create_heroes()
47    select_heroes()
48
49
50if __name__ == "__main__":
51    main()