FastAPI : SQLModel

https://sqlmodel.tiangolo.com

• SQLModel : 
  • DB 라이브러리.
  • SQLAlchemy + Pydantic = 기반으로 '직관적', '편리', '호환', '견고' 함.
• Features : 
  • Intuitive to write, Easy to use, Compatible, Extensible, Short
  • ...
• Learn - ORMs :
  • Python Code (classes and instances) <-> Database SQL 변환하는 라이브러리.
  • "Object(OOP) <-> Relational(RDB)" Mapper
    • 
• Learn - User Guide :
  • Automatic IDs, None Defaults, and Refreshing Data
    • class Hero(SQLModel, table=True):
      • id: int | None = Field(default=None, primary_key=True)
      • name: str
    • hero = Hero(name="이이이")
    • session.add(hero)         # after add -> Hero : id=None , name='이이이'
    • session.commit()           #  after commit -> Hero :   , Hero.ID : 1
    • session.refresh(hero)    # after refresh -> Hero : id=1 , name='이이이'
  • Read Data - SELECT
    • select() : .all() = , .first() = , .one() = 
    • get() : 
  • Filter Data - WHERE
    • SQLAlchemy adds some magic to the columns/fields in a model 클래스 
      • >>> Hero.name == "이이이"
        <sqlalchemy.sql.elements.BinaryExpression object at 0x7f4aec0d6c90>
    • 인스턴스 일땐!
      • >>> hero.name == "이이이"
        True
    • Multiple Expressions
      • from sqlmodel import select, or_, col 
      • statement = select(Hero).where(or_(col(Hero.age) >= 35, col(Hero.age < 40)))
  • Update Data - UPDATE
  • Delete Data - DELETE
  • Connect Tables - JOIN
    • # SELECT hero.*, team.* FROM hero LEFT JOIN team ON team.id = hero.team_id WHERE team.name = ?
    • statement = select(Hero, Team).where(Hero.team_id == Team.id).where(...)
      • 혹은
    • statement = select(Hero, Team).join(Team, isouter=True).where(...)   # LEFT OUTER
    • results = session.exec(statement)
    • hero, team = results[0]
  • Indexes
  • Relationship Attributes

    • class Team(SQLModel, table=True):
          id: int | None = Field(default=None, primary_key=True)
          name: str = Field(index=True)
          heroes: list[Hero] = Relationship(back_populates="team")
          
      class Hero(SQLModel, table=True):
          id: int | None = Field(default=None, primary_key=True)
          name: str = Field(index=True)
          team_id: int | None = Field(default=None, foreign_key="team.id")
          team: Team | None = Relationship(back_populates="heroes")
      
      # relationship 정의가 잘되있으면, 한번의 add() commit() 으로 일괄 처리됨!
      # team_id - team.id - Team - team - heroes 간의 관계를 알아서 잘 처리 해줌;;
      
      with Session(engine) as session:
         hero_0 = Hero(name="0")
         hero_1 = Hero(name="1")
         hero_2 = Hero(name="2")
          
         team = Team(name="ABC", heroes=[])
         team.heroes.append(hero_0)
         team.heroes.append(hero_1)
         team.heroes.append(hero_2)
         session.add(team)
         session.commit()
         session.refresh(hero_0)
         session.refresh(hero_1)
         session.refresh(hero_2)

    • back_populates : ...
    • Cascade : ...
  • Many to Many
• Learn - FastAPI User Guide :
  • ...
• Learn - Advanced User Guide :
  • Decimal
  • UUID

 

https://beckelman.org/code/python/sql-model

• table name
• validator
  • 
• UUID Model
  • 
• Timestamp Model
  • 
• Generic Repository

 

% (psycopg2.OperationalError) SSL connection has been closed unexpectedly 에러

• https://stackoverflow.com/questions/55457069/how-to-fix-operationalerror-psycopg2-operationalerror-server-closed-the-conn
• https://stackoverflow.com/questions/67872936/fastapi-psycopg2-operationalerror-server-closed-the-connection-unexpectedly
• SQLAlchemy pool_pre_ping
  • https://spoqa.github.io/2018/01/17/connection-pool-of-sqlalchemy.html

-끝-