기술블로그 바이수

https://sqlmodel.tiangolo.com

• SQLModel : 
  • DB 라이브러리.
  • SQLAlchemy + Pydantic = 기반으로 '직관적', '편리', '호환', '견고' 함.
  • (1)Intuitive to write , (2)Easy to use , (3)Compatible , (4)Extensible , (5)Short
• Features : 
  
  • ...
• Learn - Databases :
  • ...
  • 
• 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() : 
    • offset , limit
  • 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]
  • 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)

  • Many to Many

    • class HeroTeamLink(SQLModel, table=True):
          team_id: int | None = Field(default=None, foreign_key="team.id", primary_key=True)
          hero_id: int | None = Field(default=None, foreign_key="hero.id", primary_key=True)
          is_working: bool = False
          
          team: "Team" = Relationship(back_populates="hero_links")
          hero: "Hero" = Relationship(back_populates="team_links")
      
      
      class Hero(SQLModel, table=True):
          id: int
          name: str | None = Field(default=None)
          
          #team_list: list[Team] = Relationship(back_populates="hero_list", link_model=HeroTeamLink)
          team_links: list[HeroTeamLink] = Relationship(back_populates="hero")
      
      
      class Team(SQLModel, table=True):
          id: int
          name: str | None = Field(default=None)
          
          #hero_list: list[Hero] = Relationship(back_populates="team_list", link_model=HeroTeamLink)
          hero_links: list[HeroTeamLink] = Relationship(back_populates="team")

  • % back_populates
    • 서로가 관계를 참조 할 경우, 양방향 동기화(bidirectional sync) 를 자동으로 해줌.
    • 예) Team.heros 은 -> Hero.team 과 연동됨
    • 예) Hero.team 은 -> Team.heros 와 연동됨
  • % REFERENCES ON DELETE/UPDATE 옵션
    • CASCADE : 부모행 삭제시, 자동삭제.
    • SET NULL (SET DEFAULT) : 부모행 삭제시, 값셋팅.
    • RESTRICT (NO ACTION) : 부모행 참조 자식행 존재시, 삭제제약.
• Learn - Indexes(Optimize Queries) :
  • ...
• Learn - FastAPI & Pydantic (and CodeStructure) :
  • FastAPI is also built on top of Pydantic.
  • Being able to combine SQLModel table models with pure data models.
  • 
  • (Circular Imports)
• 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

-끝-