Pythonライブラリ(SQL)：SQLModel

2022年5月3日 11:04

１．概要

　SQLのDB管理ライブラリであるSQLModelを紹介します。

【SQLModelの特徴】
●PydanticとSQLAlchemyをベースに開発されている。
●SQLAlchemy-likeな動作・処理が可能
●開発者はFastAPIと同じtiangoloのためFastAPIとの連動が容易
●型ヒント(Pydantic)によりSQL Injectionへの耐性が強くなる（公式Docs）
●SQLではなくPythonベースで使用するためIDEによる自動補完機能が使用できる（SQL文を直接記載するとテキストになり補完が効かない）
●2022年4月30日現在のVersion:0.0.6のため、まだまだ発展途中（途中でAPIが変わる可能性あり）

【記事の注意点】
●語弊無いように一部の用語は公式(英語）をそのまま使用しました。
●説明がある公式Docsページに移動できるようリンクを張っています
●なるべく記事の上からコードのコピペで実行できるように心がけてはおりますがコードの順序により出力結果が異なる可能性があります。同じ出力を得るためには適宜IDE再起動やDBファイル削除＋再作成が必要となります。
●コードはJupyterで作成しています。Pythonモジュールで作成される方はmain()関数に必要な関数をまとめて下記をコードに記載ください。
if __name__ == "__main__":
　　main()

【公式Docs】　

【記事の進め方】
　tiangoloの公式Docsはわかりやすいためこれをベースに説明します。まずは必要なライブラリをインストールします。”pydantic”と”SQLAlchemy”も必要ですがsqlmodelに合わせて自動でインストールされます。

[Terminal]
pip install sqlmodel

【DB Browser for SQLite】
　SQLiteはファイルベースのDBであり、そのファイルの中身をGUIで確認できるツールとして「DB Browser for SQLite」があります。使用しなくても問題ないですがあると便利なので紹介だけしておきます。

２．DBテーブル（テーブルモデルクラス）の定義

　まずはDatabase用のテーブル定義の方法を説明します。データは公式を参照して"Hero"テーブルを作成します。

【Heroテーブル　※公式参照】

２－１．テーブルクラス作成：SQLModelの継承

　DBで使用するテーブル定義は下記の通りです。このようなあるデータを示すクラスを”model”と呼ぶためパッケージ名はSQLmodelとなります。

【SQLModelのテーブル定義の説明】
●"SQLModel"を継承してクラスを作成（SQLAlchemyでは"Base"を継承）
●引数の"table=True"を設定することでテーブルであることを示す
　ー＞設定しないとtable modelsではなくdata modelsとなる。
　ー＞data model=PydanticモデルはFastAPIでの型ヒントに利用可
●「（テーブル）クラス内で定義した変数名＝カラム名」となる
●「（テーブル）クラス内で定義した型ヒント＝DBのデータ型」となる。

[IN]
from typing import Optional
from sqlmodel import Field, SQLModel, create_engine

#DBテーブル定義
class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True) #主キー制約
    name: str
    secret_name: str
    age: Optional[int] = None #Null値が入る可能性があるため、Optional型を使う
    
[OUT]
定義のみ

　上記の通り変数にPythonの型ヒントをつけておりますが、型ヒントは下記のようなものがあります。

【SQLModel(Python)の型ヒント】
１．標準
　●str：文字列
　●int：整数
　●float：小数点
２．from typing import X で取得
　●List：リスト
　●Dict：辞書
　●Optional：（Noneも含めて）全型に対応
３．from Pydantic import Xで取得
　●condecimal：浮動小数点の文字数・桁数を設定

２－２．制約・Indexの設定：Optional／Field

　テーブルに制約（主キー（primary_key）、NOT NULLなど）やIndex機能を設定する場合は下記の通りです。

【制約の設定】
●sqlmodelの"Field"を使用して制約を設定する。
　ー＞FastAPIでは"from pydantic import Field"としており条件設定に利用
　ー＞SQLAlchemyではColumnクラスの引数に制約条件を設定
●主キー制約（primary key）の設定はFieldを使用して設定する
　ー＞(NULL値がありえないが)「主キー設定時は”default=None”を設定」
　ー＞①クラスのインスタンス作成（Python）、②DBに登録(Python)、③DB登録後にautoincrementで主キー設定(DB)　となり、瞬間的にNULL値が発生してPydanticの型チェックにかかる（詳細は公式1, 公式2）。
●NULL値(PythonではNone)の可能性がある属性は型ヒントにOptional[データ型] = Noneを使用
ー＞「Optional使用＝Nullable」となる（SQLAlchemyでのnullable=True）
●Index機能はFieldの引数index=Trueとする（Indexの詳細は公式Docs参照）
　ー＞indexはWHERE句で条件指定時に検索速度向上させるが全部につけると容量を食うため検索にかけない列にはIndex不要(公式Docs)

[IN]　※同上
from typing import Optional
from sqlmodel import Field, SQLModel, create_engine

#DBテーブル定義
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] = None #Null値が入る可能性があるため、Optional型を使う

[OUT]
定義のみ

２－３．外部キー制約(Foreign Key)：Field(foreign_key)

　外部キーの設定もPydanticの機能であるFieldを使用します。

【外部キー設定の注意点】
●Fieldクラスの引数であるforeign_key="table名.属性名"を指定する
　ー＞Class名ではなくtable名のため小文字であることに注意
　ー＞echo=Trueで確認するとTable作成時に外部キー設定を確認できる

[IN]※サンプルコードのため今の段階で実行するとエラーになります（出力も無し）

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True) #主キー制約
    name: str = Field(index=True) 
    headquarters: str

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) #Null値が入る可能性があるため、Optional型を使う

    team_id: Optional[int] = Field(default=None, foreign_key="team.id") #外部キー制約
   　 
[OUT]　
CREATE TABLE team (
	id INTEGER, 
	name VARCHAR NOT NULL, 
	headquarters VARCHAR NOT NULL, 
	PRIMARY KEY (id)
)

CREATE TABLE hero (
	id INTEGER, 
	name VARCHAR NOT NULL, 
	secret_name VARCHAR NOT NULL, 
	age INTEGER, 
	team_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(team_id) REFERENCES team (id)
)

２－４．Relationship（関連付け）

　データ結合時のテーブル同士の関連性を明確にするためにRelationshipクラスを使用します（詳細は７章）。

【Relationshipの引数】
●back_populatesの場合は関連する両テーブルに記載が必要
　ー＞back_populatesが無いとcommit()し忘れたときにエラー(公式Docs)
●型ヒントのつけ方に注意。正：List["Hero"]、誤：List[Hero]（公式Docs）
　ー＞PythonのinterpreterがHeroクラスを認識できないため文字列で記載

[IN]※サンプルコードのため今の段階で実行するとエラーになります（出力も無し）
from typing import List, Optional
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

    heroes: List["Hero"] = Relationship(back_populates="team")


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)

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    team: Optional[Team] = Relationship(back_populates="heroes")


[OUT]
-

２－５．テーブル再定義： {'extend_existing': True}

　モジュールで1度テーブルを作成するとDBファイルを削除しても下記エラーが消えませんでした。

[Terminal ※参考コード]
python -m project.app

[OUT]
raise exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: Table 'team' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

　対策としてクラス作成時に__table_args__ = {'extend_existing': True}を渡すことでエラー対応可能です。

[IN]
class Team(SQLModel, table=True):
    __table_args__ = {'extend_existing': True} #moduleで実行したあとに残るメタデータのエラー防止
    
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

３．DB操作

３－１．Engine作成：create_engine(DBpath)

　DB接続のためDBファイルのパスをengineオブジェクトを作成します（SQLAlchemyとほぼ同じ）。この時点ではDBファイルやテーブルは作成されません。

[IN]
from sqlmodel import Field, SQLModel, create_engine

sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)
engine

[OUT]
Engine(sqlite:///database.db)

【create_engineの引数】
●echo (defalut:False)：SQL実行時にターミナルにSQL文を表示
ー＞学習・デバッグ向けだが製品向けでは外すこと
●future (default:True)：最新版のSQLAlchemyを使用
ー＞Pandasのpd.read_sql()を使用するときにFalseにしたりします
●connect_args：SQLAlchemyのConfig機能でDB接続権限を変更します。FastAPI使用時にconnect_args = {"check_same_thread": False}とします。

３－２．テスト用Engine (in-memory)

　一時的なテストとしてDBファイルを作成せずメモリ上でDB作成する場合は２パターンあります。

　３－２－１．create_engine('sqlite:///:memory:')

　SQLAlchemyの記法としてengine = create_engine('sqlite:///:memory:')　を使用することでDB切断時まで一時的に使用可能です（sqlite3記事参照）

[IN]
from typing import Optional
from sqlmodel import Field, SQLModel, create_engine

engine = create_engine('sqlite:///:memory:', echo=True)
engine

[OUT]
Engine(sqlite:///:memory:)

　３－２－２．poolclass=StaticPool

　SQLModel公式ではStaticPoolの使用を紹介しています。

[IN]
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
from sqlmodel.pool import StaticPool

#DBテーブル定義
class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True) #主キー制約
    name: str
    secret_name: str
    age: Optional[int] = None #Null値が入る可能性があるため、Optional型を使う
    
engine = create_engine(
        "sqlite://", #DBファイル名の記載は不要
        echo=True, 
        connect_args={"check_same_thread": False}, #FastAPIでのDB接続権限設定
        poolclass=StaticPool #in-memoryの使用
    )

engine

[OUT]
Engine(sqlite://)

３－３．DB及びテーブル作成：SQLModel.metadata.create_all(engine)

　作成したクラスとEngineからDBおよびテーブルを作成するには「SQLModel.metadata.create_all(engine)」を実行します（SQLAlchemyでのBase.metadata.create_all(bind=engine)と同様）。
　注意点は「クラス名＝テーブル名だがテーブル名はすべて小文字」です。

[IN]※体裁の都合によりDBfile名は変更して全コードを記載しております。

from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select

#DBテーブル定義
class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True) #主キー制約
    name: str
    secret_name: str
    age: Optional[int] = None #Null値が入る可能性があるため、Optional型を使う
    
    
sqlite_file_name = "220501_DB4note.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) #DB及びテーブルを作成
    
create_db_and_tables()

[OUT]
※engineのecho=Trueの場合（一部のみ抜粋）　※下記の通りテーブル名はhero(小文字)である。
CREATE TABLE hero (
	id INTEGER, 
	name VARCHAR NOT NULL, 
	secret_name VARCHAR NOT NULL, 
	age INTEGER, 
	PRIMARY KEY (id)
)

３－４．SQLModelのメタデータ：SQLModel.metadata

　SQLModelクラスのメタデータ取得は前節と同じSQLModel.metadataのcreate_all()メソッドで取得できます。

[IN]
SQLModel.metadata.create_all(engine)

[OUT]
main.table_info("hero")

　SQLModel.metadataメソッドの注意点は下記の通りです。

【SQLModel.metadataの注意点】
●①SQLModelを継承、②table=True設定したテーブルのみメタデータが登録
●SQLModel.metadata.create_all(engine)は順序が重要で
　ー＞実行前にSQLModelを継承したmodelクラスを作成する必要がある
　ー＞モジュールを分けた場合は実行より前にimportが必要

４．Session(DB接続・操作）

４－１．DBへの接続：Session(engine)

　SQLAlchemyではDBに接続するためにSessionオブジェクトを作成・操作してDB操作を実施しました。

[※参考用コード：SQLAlchemy]
from sqlalchemy.orm import sessionmaker, scoped_session

Session = scoped_session(
    sessionmaker(
        autocommit=False, #commit自動化の設定
        autoflush=False, #flush自動化の設定
        bind = engine
    )
)

　SQLModelではSessionクラスからsessionオブジェクトを作成して、そのオブジェクトを使用します。記法は2種ありますが"with"を使う方がおすすめです。（下記コードは参考用のため実行するとエラーになります。）

[IN]※記法１
from sqlmodel import Field, Session, SQLModel, create_engine


session = Session(engine)

～ここに処理したいクエリを記載～

session.commit()
session.close()

[IN]※記法２：withを使用してsession.close()を省略
from sqlmodel import Field, Session, SQLModel, create_engine


with Session(engine) as session:

    ～ここに処理したいクエリを記載～

    session.commit()

４－２．登録の確定：session.commit()

　トランザクション（SQLでの一連の処理）は自動でDBへ反映されない※ため、確定する場合はcommit()します（公式Docs）。
※クエリ処理するとオブジェクト（メモリ上）には保存されてもDB側への接続はされていないため

[In]
session = Session(engine)

session.commit()

４－３．session停止：session.close()

　sessionはengine接続のためのリソースをもつ（メモリを食う）ため、session処理が終わったらsession.close()を実行します。

[In]
session = Session(engine)
session.commit()
session.close()

　ただし公式Docsではsession.close()の記載忘れも考慮すると「with構文」でsessionを作成することを推奨しております。

[IN]
with Session(engine) as session:
    session.add(hero_1) #適当なクエリ

    session.commit()

５．テーブル操作（CRUD操作）

　作成したテーブルを使用してCRUD操作を実行していきます。

５－１．データ追加（INSERT）：session.add(instance)

　　参考までにSQL文でのデータ追加は下記の通りです。

[SQL文：INSERT]
INSERT INTO <テーブル名> (列1, 列2, 列3,・・・・)　VALUES (値1, 値2, 値3・・・);

　SQLModelではSQLAlchemyと同様にsession.add(instance)で追加します。

【session.addでの確認事項】
●id: Optional[int] = Field(default=None, primary_key=True)は値を渡さなくても自動で割り振られている
●age: Optional[int] = None　の設定よりNULL値が受け付けられている

[IN]
def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson") #追加データ1※age=NULL
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador") #追加データ2※age=NULL
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48) #追加データ3

    session = Session(engine) #DB接続用のsession作成

    session.add(hero_1) #データ1追加
    session.add(hero_2) #データ2追加
    session.add(hero_3) #データ3追加

    session.commit() #データをDBに保存
    session.close() #session停止

create_heroes()

[OUT] echo=Trueの場合（※出力は抜粋） 
INFO sqlalchemy.engine.Engine INSERT INTO hero (name, secret_name, age) VALUES (?, ?, ?)
INFO sqlalchemy.engine.Engine [generated in 0.00088s] ('Deadpond', 'Dive Wilson', None)
INFO sqlalchemy.engine.Engine [cached since 0.004512s ago] ('Spider-Boy', 'Pedro Parqueador', None)
INFO sqlalchemy.engine.Engine [cached since 0.006493s ago] ('Rusty-Man', 'Tommy Sharp', 48)

　下記記法でも問題ないです（with構文は公式推奨）。

[IN]
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)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()

【ORMでのデータ読み取り】
　FastAPI使用時のようにデータをJSON形式で渡す場合のオブジェクト作成メソッドは下記の通りです。

【API使用時のオブジェクト作成】
●.from_orm()：属性を含むデータ（JSONなど）からオブジェクト作成
●.parse_obj()：辞書型データからオブジェクト作成

[IN]※FastAPIを使用したときのサンプルコードです
@app.post("/heroes/", response_model=HeroRead) #HeroRead: Heroオブジェクトをレスポンスとして返す
def create_hero(hero: HeroCreate): #HeroCreate: Heroオブジェクトをリクエストとして受け取る
    with Session(engine) as session:
        db_hero = Hero.from_orm(hero) #HeroCreateオブジェクトをDBに保存するためにHeroオブジェクトに変換
        
        session.add(db_hero)
        session.commit()
        session.refresh(db_hero)
        return db_hero

【登録データの確認方法】
　ここでは簡易のデータ確認方法を説明します。サクッとpandasのpd.read_sqlでDBデータを確認しようとすると下記エラーが出ます。

[IN]
import pandas as pd
pd.read_sql("SELECT * FROM Hero", engine)

[OUT]
NotImplementedError: This method is not implemented for SQLAlchemy 2.0.

　文字通り最新のSQLAlchemyに対応していないことが原因のため（一度再起動して）engineのfuture=Falseとすると実行可能です。

[IN]
import pandas as pd
from sqlmodel import Field, Session, SQLModel, create_engine, select

sqlite_file_name = "220501_DB4note.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True, future=False)
pd.read_sql("SELECT * FROM Hero", engine)

[OUT]

　コード書換や再起動が手間なら「DB Browser for SQLite」が便利です。

５－２．参考：登録時のid(主キー）/sessionの動作

　データ追加時／session処理における動作を確認しました（公式Docs）。

【primary_key／sessionの動作】
●session.commit()より前のid(primary key)はNULL
　ー＞id: Optional[int] = Field(default=None, primary_key=True)設定のため
●session.commit()後はオブジェクトは空になる
　ー＞session.commit()時にオブジェクトのデータをDBへ移す。commit完了後にオブジェクトデータが空(Expire)になる
　ー＞データ取得する時(hero_1.id)、sessionはオブジェクトが空であることを理解してDBへデータを取得しに行く(よってデータ取得可）
●session.refresh(instance)を実行するとsessionはDBからオブジェクト用のデータを取得しにいく（よってデータ取得可）
●session.close()（with構文の外）ではROLLBACKが実行される。

[IN] ※確認用コードのため中身そのものには意味はありません。
def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson") #追加データ1※age=NULL
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador") #追加データ2※age=NULL
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48) #追加データ3

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        print('Session/add後＋Commit前')
        print('hero_1オブジェクト：', hero_1, 'hero_2オブジェクト：', hero_2, 'hero_3オブジェクト：', hero_3) #この時点ではid=None
        print('hero1.id:：', hero_1.id, 'hero2.id:：', hero_2.id, 'hero3.id:：', hero_3.id) #この時点ではid=None
        print('hero1.name:：', hero_1.name, 'hero2.name:：', hero_2.name, 'hero3.name:：', hero_3.name) #nameは登録されている
        
        session.commit() #この時点でオブジェクト内のデータは空になる(Expire)
        
        print('Session後＋Commit後')
        print('hero_1オブジェクト：', hero_1, 'hero_2オブジェクト：', hero_2, 'hero_3オブジェクト：', hero_3) #オブジェクトは空データとなる
        #Sessionはこの時点でオブジェクトデータが空であることを理解しているー＞データ取得時はDBへ取得しに行く
        #id取得のためSessionはEngine経由でDBから最新データを取得する（データのrefresh）
        print('hero1.id:：', hero_1.id, 'hero2.id:：', hero_2.id, 'hero3.id:：', hero_3.id) #idは登録されている
        print('hero1.name:：', hero_1.name, 'hero2.name:：', hero_2.name, 'hero3.name:：', hero_3.name)#nameは登録されている
        
        session.refresh(hero_1); session.refresh(hero_2); session.refresh(hero_3) #オブジェクトのrefresh
        #refresh->Sessionはオブジェクトが空であることを理解しているため、DBから最新データを取得する※SELECT hero.id, hero.name, hero.secret_name, hero.age FROM hero 
        print('session.refresh(オブジェクト)後')
        print('hero_1オブジェクト：', hero_1, 'hero_2オブジェクト：', hero_2, 'hero_3オブジェクト：', hero_3) #オブジェクトがid込みで取得
    
    #Sessionが終わった時点で”Engine ROLLBACK”が実行ー＞commitされていない処理は無効となる   
    print('Session close後')
    print('hero_1オブジェクト：', hero_1, 'hero_2オブジェクト：', hero_2, 'hero_3オブジェクト：', hero_3) #refresh後と同じ状態

create_heroes()


[OUT]
Session/add後＋Commit前
hero_1オブジェクト： id=None name='Deadpond' secret_name='Dive Wilson' age=None
hero_2オブジェクト： id=None name='Spider-Boy' secret_name='Pedro Parqueador' age=None
hero_3オブジェクト： id=None name='Rusty-Man' secret_name='Tommy Sharp' age=48

hero1.id:： None hero2.id:： None hero3.id:： None
hero1.name:： Deadpond hero2.name:： Spider-Boy hero3.name:： Rusty-Man


Session後＋Commit後
hero_1オブジェクト：  hero_2オブジェクト：  hero_3オブジェクト： 

hero1.id:： 4 hero2.id:： 5 hero3.id:： 6
hero1.name:： Deadpond hero2.name:： Spider-Boy hero3.name:： Rusty-Man


session.refresh(オブジェクト)後
hero_1オブジェクト： name='Deadpond' secret_name='Dive Wilson' age=None id=4
hero_2オブジェクト： name='Spider-Boy' secret_name='Pedro Parqueador' age=None id=5
hero_3オブジェクト： name='Rusty-Man' secret_name='Tommy Sharp' age=48 id=6


Session close後
hero_1オブジェクト： name='Deadpond' secret_name='Dive Wilson' age=None id=4
hero_2オブジェクト： name='Spider-Boy' secret_name='Pedro Parqueador' age=None id=5
hero_3オブジェクト： name='Rusty-Man' secret_name='Tommy Sharp' age=48 id=6

５－３．データ読み込み（SELECT）

　参考までにSQL, SQLAlchemyでのテーブルデータ取得は下記の通りです。

[SQL文：データ取得]
SELECT <列名1>, <列名2>,・・・・
FROM <テーブル名>;

[全列データ取得]
SELECT *
FROM <テーブル名>;

[SQLAlchemy]
for row in Session.query(Iris).all():
    print(row.id, row.sepal_length, row.sepal_width, row.petal_length, row.petal_width, row.id_label)

　５－３－１．データ取得：session.exec(statement)

　SQLModelでのSELECT構文は①select(Class)でクエリを作成、②sessionでクエリを実行、③オブジェクトからデータ抽出　となります。

【複数データの取得方法】
（A）for文で取得：イテラブルなオブジェクトを作成してfor文で抽出
（B）リスト形式：all()メソッドを使用 " session.exec(select(Hero)).all()"

[IN　※for文形式＋動作確認] 
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select

with Session(engine) as session:
    statement = select(Hero) #queryの作成：SELECT * FROM hero;
    print(type(statement))
    print(statement)
    results = session.exec(statement) #queryの実行->resultオブジェクト(iterable)生成
    print(results)
    for hero in results: 
        print(hero)
    
[OUT]
#type(statement)
<class 'sqlmodel.sql.expression.SelectOfScalar'> 

#statement
SELECT hero.id, hero.name, hero.secret_name, hero.age 
FROM hero

#results
<sqlalchemy.engine.result.ScalarResult object at 0x0000021BA168DEB0>

#for文でのhero
name='Deadpond' secret_name='Dive Wilson' age=None id=1
name='Spider-Boy' secret_name='Pedro Parqueador' age=None id=2
name='Rusty-Man' secret_name='Tommy Sharp' age=48 id=3

[IN ※List形式]
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select

with Session(engine) as session:
    statement = select(Hero) #queryの作成：SELECT * FROM hero;
    results = session.exec(statement) #queryの実行->resultオブジェクト(iterable)生成
    heroes = results.all() #全データ取得：<class 'list'>
    print(heroes)

[OUT]
[Hero(name='Deadpond', secret_name='Dive Wilson', age=None, id=1), 
Hero(name='Spider-Boy', secret_name='Pedro Parqueador', age=None, id=2), 
Hero(name='Rusty-Man', secret_name='Tommy Sharp', age=48, id=3)]

　なお上記をシンプルに記載すると下記の通りとなります。

[IN]
with Session(engine) as session:
    heroes = session.exec(select(Hero)).all() #List形式でHeroテーブルから全データ取得
    print(heroes)

[OUT]
同上

【１つのデータの取得方法：出力はclass形式（Listではない）】
(A) first()メソッド：全レコード内の最初のレコードのみ取得（制限なし）
　ー＞データが存在しない場合はNoneで出力される
(B) one()メソッド：1つのレコードのチェック機能付き
　ー＞レコードが1つの場合は正常終了だが、0や2つ以上の場合はエラー
(C) get()メソッド：primary Keyの値を直接指定
　ー＞exec(select().where(primarykey=x)).first()とほぼ同義
　ー＞データが存在しない場合はNoneで出力される（first()と同じ）

[IN]
with Session(engine) as session:
    heroes = session.exec(select(Hero)).first() #class形式で1つのデータを出力
    print(heroes)
    print(type(heroes))


with Session(engine) as session:
    heroes = session.exec(select(Hero).where(Hero.age > 1000)).first() #年齢が1000歳以上のデータ
    print(heroes)

[OUT]
name='Deadpond' secret_name='Dive Wilson' age=None id=1
<class '__main__.Hero'>

None

[IN]
with Session(engine) as session:
    heroes = session.exec(select(Hero).where(Hero.name == 'Deadpond')).one() #Hero.nameがDeadpond
    print(heroes)

with Session(engine) as session:
    heroes = session.exec(select(Hero)).one() #Heroクラスの全データ取得
    print(heroes)

with Session(engine) as session:
    heroes = session.exec(select(Hero).where(Hero.age > 1000)).one() #年齢が1000歳以上のデータ
    print(heroes)

[OUT]
name='Deadpond' secret_name='Dive Wilson' age=None id=1

MultipleResultsFound: Multiple rows were found when exactly one was required

NoResultFound: No row was found when one was required

[IN]
with Session(engine) as session:
    hero = session.get(Hero, 1) #get()でprimary_keyであるidを指定してデータを取得
    print(heroes)

[OUT]
name='Deadpond' secret_name='Dive Wilson' age=None id=1

　５－３－２．条件指定(WHERE)：select(Class).where

　参考までにSQLAlchemyのコードを載せておきます。

[参考：SQLAlchemy-> SELECT * FROM Iris WHERE id_label = 0;]
Session.query(Iris).filter(Iris.id_label==0).all() #id_labelが{0:"setosa"}

　なお条件抽出用としてデータ追加するため①DBファイルを削除、②IDE(VS CODE)再起動、③新規データ追加しました。

[IN]
def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson") #ageはNULL
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador") #ageはNULL
    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()

create_db_and_tables()
create_heroes()
[OUT]

　取得するレコードの条件を指定する場合はwhereメソッドを使用します。

【whereメソッドのポイント】
●条件記載はclass.属性 == '条件値'であり（インスタンスではなく）modelクラスの属性を取得するため、出力はBool型ではなくオブジェクトである
●指定条件では無いデータは(Python-likeに)class.属性 != '条件値'で取得可
●その他条件は基本系（<, <=, >, >=, ==）で対応可能である
●AND条件(条件1かつ条件2)はselect(Class).where(条件1).where(条件2)またはselect(Class).where(条件1, 条件2)で可能
●OR条件(条件1or条件2)はsqlmodelの"or_"関数を使用して記載する
●age: Optional[int] = None　と設定しておりNULL値を許容しているため型ヒントでもエラーが出ない仕組みになっている（公式Docs）
●SQLModelのカラムであることを示すためにcol関数も使用できる。

[IN]※シンプル
with Session(engine) as session:
    statement = select(Hero).where(Hero.name == 'Deadpond') #queryの作成：SELECT * FROM hero;
    results = session.exec(statement) #queryの実行->resultオブジェクト(iterable)生成
    heroes = results.all() #全データ取得：<class 'list'>


print(Hero.name == 'Deadpond') #whereの条件式確認※Boolではなく特殊オブジェクト
print(statement) #query確認
print(heroes)
    
[OUT]
#Hero.name == 'Deadpond' (printで出力すると実際はhero.name = :name_1　がでる）
<sqlalchemy.sql.elements.BinaryExpression object at 0x0000021BA2C0AF10>

#statement
SELECT hero.id, hero.name, hero.secret_name, hero.age 
FROM hero 
WHERE hero.name = :name_1

#heroes 
[Hero(name='Deadpond', secret_name='Dive Wilson', age=None, id=1)]

[IN]※否定形
with Session(engine) as session:
    statement = select(Hero).where(Hero.name != 'Deadpond') #queryの作成
    results = session.exec(statement) #queryの実行->resultオブジェクト(iterable)生成
    heroes = results.all() #全データ取得：<class 'list'>


print(statement) #query確認
print(heroes)

[OUT]
#statement
SELECT hero.id, hero.name, hero.secret_name, hero.age 
FROM hero 
WHERE hero.name != :name_1

#heroes 
[Hero(name='Spider-Boy', secret_name='Pedro Parqueador', age=None, id=2), Hero(name='Rusty-Man', secret_name='Tommy Sharp', age=48, id=3), Hero(name='Tarantula', secret_name='Natalia Roman-on', age=32, id=4), Hero(name='Black Lion', secret_name='Trevor Challa', age=35, id=5), Hero(name='Dr. Weird', secret_name='Steve Weird', age=36, id=6), Hero(name='Captain North America', secret_name='Esteban Rogelios', age=93, id=7)]

[IN] OR構文
from sqlmodel import Field, Session, SQLModel, create_engine, select, or_

with Session(engine) as session:
    statement = select(Hero).where(or_(Hero.age <= 35, Hero.age > 90)) #年齢が35歳以下か90歳以上のデータを取得
    results = session.exec(statement) #queryの実行->resultオブジェクト(iterable)生成
    heroes = results.all() #全データ取得：<class 'list'>

print(statement) #query確認
print(heroes)

[OUT]
SELECT hero.id, hero.name, hero.secret_name, hero.age 
FROM hero 
WHERE hero.age <= :age_1 OR hero.age > :age_2

[Hero(name='Tarantula', secret_name='Natalia Roman-on', age=32, id=4),
 Hero(name='Black Lion', secret_name='Trevor Challa', age=35, id=5),
 Hero(name='Captain North America', secret_name='Esteban Rogelios', age=93, id=7)]

　５－３－３．データ数指定(LIMIT)：select.limit()

　取得データ数を指定する場合は"select(Class).limit(データ数)"とします※all()やsession()ではなくselect()のメソッドであることに注意

[IN]
with Session(engine) as session:
    heroes = session.exec(select(Hero).limit(3)).all() #データ数を指定:LIMIT
    print(heroes)

[OUT]
[Hero(name='Deadpond', secret_name='Dive Wilson', age=None, id=1),
 Hero(name='Spider-Boy', secret_name='Pedro Parqueador', age=None, id=2),
 Hero(name='Rusty-Man', secret_name='Tommy Sharp', age=48, id=3)]

　５－３－４．データ飛ばしの指定(OFFSET)

　取得位置（1つ目のレコードからスキップするデータ数）の指定はoffset()メソッドを使用します。もし取得レコード数<limit数　はエラーではなく取得レコードだけ表示してくれます。

[IN]
with Session(engine) as session:
    heroes = session.exec(select(Hero).offset(3).limit(3)).all() #始めのデータから3つ飛ばした位置を起点(Offset)に3つ取得(Limit)
    print(heroes)

[OUT]
[Hero(name='Tarantula', secret_name='Natalia Roman-on', age=32, id=4),
 Hero(name='Black Lion', secret_name='Trevor Challa', age=35, id=5),
 Hero(name='Dr. Weird', secret_name='Steve Weird', age=36, id=6)]

５－４．データ更新（UPDATE）

　参考としてSQL文でのテーブルデータの更新(上書き)は下記の通りです。

[SQL文：データ更新（列全体)]
UPDATE <テーブル名>
SET　<列名> = <式> ;

[SQL文：データ更新（条件指定)]
UPDATE <テーブル名>
SET　<列名> = <式> 
WHERE <条件>;

　SQLModelでデータ更新する場合は下記の通りです。

【SQLModelでのUPDATE】
①更新したいデータオブジェクトを抽出
　ー＞更新したい１つのデータを明確にするone()メソッドがベター
②変更したい属性に値を入力
③session.add()で変更後のデータを追加
④session.commit()でデータ反映(with構文でなければclose()も実施)
⑤session.refresh()でDBからオブジェクトデータを再取得

[IN]
with Session(engine) as session:
    hero = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).one() #id=2, age=NULLのデータ
    print(hero) #データ確認:age=None
    
    hero.age = 16 #UPDATE 
    session.add(hero) #heroオブジェクトへ登録※DBには未反映
    session.commit() #commit:DBへデータを保存->この時点でheroオブジェクトは空になる
    session.refresh(hero) #(空データのため)heroオブジェクトをDBから再取得
    
    print("Updated hero:", hero) #データ確認:age=16, heroオブジェクトもデータあり

[OUT]
name='Spider-Boy' secret_name='Pedro Parqueador' age=None id=2

Updated hero: name='Spider-Boy' secret_name='Pedro Parqueador' age=16 id=2

５－５．データ削除（DELETE）

　SQL文でのテーブルデータの削除は下記の通りです。

[SQL文：データ削除（全データ）]
DELETE FROM <テーブル名>;

【SQL文：データ削除（条件指定）】
DELETE FROM <テーブル名>
WHERE <条件>;

　SQLModelでデータ削除する場合は下記の通りです。

【SQLModelでのDELETE】
①更新したいデータオブジェクトを抽出
②session.delete()でオブジェクトの削除を実行
③session.commit()でデータ反映(with構文でなければclose()も実施)
　ー＞commit()後はDBにデータが無いためsession.refresh()はエラーとなる
　ー＞DBには存在しないがオブジェクト内にデータはあるためprint()可
④もしデータ削除を確認したい場合は再度DBに接続して戻り値=Noneであることを確認

[IN]
with Session(engine) as session:
    hero = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).one() #id=2
    
    session.delete(hero) #データ削除
    session.commit()

    print("session.commit()後:", hero) #print()で削除したデータが確認できる

    #データの削除確認
    hero = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).first() #one()にするとエラーになるためfirst()に変更

    if hero is None:
        print("There's no hero named Spider-Youngster")

[OUT]
session.commit()後: name='Spider-Boy' secret_name='Pedro Parqueador' age=16 id=2

There's no hero named Spider-Youngster

６．テーブル結合（JOIN）

　SQLでは正規化のため「複数のテーブル作成ー＞JOIN結合」が必要です。本章ではテーブル結合（JOIN）に関する内容を紹介していきます。
　サンプルとして新規DBファイルに"hero"と”team"テーブルを作成します。

[IN]
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select, or_

#DBテーブル定義
class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True) #主キー制約
    name: str = Field(index=True) 
    headquarters: str

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) #Null値が入る可能性があるため、Optional型を使う

    team_id: Optional[int] = Field(default=None, foreign_key="team.id") #外部キー制約, NULL値も許可
    

#DBファイルへの接続
sqlite_file_name = "DB4note_Advance.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)

create_db_and_tables()

[OUT]
DBとテーブルが作成される。

　下図はテーブルのイメージ図です。

６－１．データ追加(INSERT)：relationship無し

　外部キーも考慮したデータ追加は下記の通りです。

【外部キー設定時の注意点】
●外部キーは手動入力（例：id=2）ではなく"object.属性"で取得すること
　ー＞データ数が増えると手動で指定は不可能なため

[IN]
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select, or_

#DBテーブル定義
class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True) #主キー制約
    name: str = Field(index=True) 
    headquarters: str

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) #Null値が入る可能性があるため、Optional型を使う

    team_id: Optional[int] = Field(default=None, foreign_key="team.id") #外部キー制約
    

#DBファイルへの接続
sqlite_file_name = "DB4note_Advance.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)

#DBファイル・テーブル作成
def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

#DBへのデータ挿入
def create_heroes():
    with Session(engine) as session:
        #teamテーブルにデータを挿入
        team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
        team_z_force = Team(name="Z-Force", headquarters="Sister Margaret’s Bar")
        session.add(team_preventers)
        session.add(team_z_force)
        session.commit()

        #heroテーブルにデータを挿入
        hero_deadpond = Hero(name="Deadpond", secret_name="Dive Wilson", team_id=team_z_force.id) #age=None
        hero_rusty_man = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, team_id=team_preventers.id)
        hero_spider_boy = Hero(name="Spider-Boy", secret_name="Pedro Parqueador") #team_idとage=NULL
        
        #sessionオブジェクトに登録
        session.add(hero_deadpond)
        session.add(hero_rusty_man)
        session.add(hero_spider_boy)
        session.commit() #DBにデータ反映

        session.refresh(hero_deadpond) #オブジェクトデータをDBから再取得
        session.refresh(hero_rusty_man) #オブジェクトデータをDBから再取得
        session.refresh(hero_spider_boy) #オブジェクトデータをDBから再取得

        print("Created hero:", hero_deadpond)
        print("Created hero:", hero_rusty_man)
        print("Created hero:", hero_spider_boy)


def main():
    create_db_and_tables()
    create_heroes()

main()

[OUT]

６－２．複数テーブルを同時読み込み（SELECT)

　複数のテーブルデータをまとめて取得する記法は下記の通りです。下記だとNULL値は取得できないためhero.id=3は取得されません。

[IN]
with Session(engine) as session:
    statement = select(Hero, Team).where(Hero.team_id == Team.id) #Hero, Teamテーブルの両データかつ同じidを取得
    results = session.exec(statement) #List内に(Hero, Team)のタプルが入る
    for hero, team in results:
        print("Hero:", hero, "Team:", team)


[OUT]
Hero: age=None id=1 name='Deadpond' team_id=2 secret_name='Dive Wilson' 
Team: name='Z-Force' headquarters='Sister Margaret’s Bar' id=2


Hero: age=48 id=2 name='Rusty-Man' team_id=1 secret_name='Tommy Sharp' 
Team: name='Preventers' headquarters='Sharp Tower' id=1

６－３．内部結合：INNER JOIN

　内部結合のイメージに関しては下記の通りです。INNER JOINではNULL値は紐づけできないため結合データには使用されません。

【SQL ModelでのINNER JOINの注意点】
●SQLでは「INNER JOIN＝JOIN」のため、メソッドはjoin()である。
●SQLでは「FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.属性 = Tbl2.属性」とONの指定が必要だがSQLModelでは外部キーで設定しているため不要
●select(Model1, Model2)でJOINするテーブルを引数で渡す
●INNER JOINのためNULL値は取得されない

[IN]
with Session(engine) as session:
    statement = select(Hero, Team).join(Team) #SELECT * FROM hero INNER JOIN team ON hero.team_id = team.id
    results = session.exec(statement)
    for hero, team in results:
        print("Hero:", hero, "Team:", team)


[OUT]
Hero: secret_name='Dive Wilson' team_id=2 age=None id=1 name='Deadpond' 
Team: headquarters='Sister Margaret’s Bar' name='Z-Force' id=2

Hero: secret_name='Tommy Sharp' team_id=1 age=48 id=2 name='Rusty-Man' 
Team: headquarters='Sharp Tower' name='Preventers' id=1

６－４．外部結合：OUTER JOIN

　外部結合のイメージに関しては下記の通りであり、結合キーのNULL値も含めて結合します（よって結合時にデータが無い部分にNULLが発生します）。

【SQL ModelでのOUTER JOINの注意点】
●SQLの外部結合では①片方のテーブル情報が全て出力される、②結合テーブルの片方をマスタ（全情報が出るテーブル）として選択　します。
●OUTER JOINはjoin(マスタではないテーブル, isouter=True)と記載

[IN]
with Session(engine) as session:
    statement = select(Hero, Team).join(Team, isouter=True) # SELECT * FROM hero LEFT OUTER JOIN team ON hero.team_id = team.id
    results = session.exec(statement)
    for hero, team in results:
        print("Hero:", hero, "Team:", team)

[OUT]
Hero: secret_name='Dive Wilson' team_id=2 age=None id=1 name='Deadpond' 
Team: headquarters='Sister Margaret’s Bar' name='Z-Force' id=2

Hero: secret_name='Tommy Sharp' team_id=1 age=48 id=2 name='Rusty-Man' 
Team: headquarters='Sharp Tower' name='Preventers' id=1

Hero: secret_name='Pedro Parqueador' team_id=None age=None id=3 name='Spider-Boy' 
Team: None

６－５．データ更新（UPDATE)：relationship無し

　外部キーのデータ更新に関して基本的な手順は５章説明の通りです。

【SQLModelでのUPDATE_外部キー：relationshipの設定無し】
①更新したいデータオブジェクトを抽出　
②変更したい属性に”Model.属性”を入力※値ではなくオブジェクトから取得
　ー＞外部キーとの連動を切りたい場合はNoneを渡す（公式Docs)
　ー＞外部キーのデータは事前にcommit()してDBに登録しておく必要がある　
③session.add()で変更後のデータを追加
④session.commit()でデータ反映(with構文でなければclose()も実施)
⑤session.refresh()でDBからオブジェクトデータを再取得

[IN]
with Session(engine) as session:
    team_preventers = session.exec(select(Team).where(Team.name == "Preventers")).one() #Preventersのデータ※one()で1件のみ取得
    hero_spider_boy = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).one() #Spider-Boyのデータ※one()で1件のみ取得

    hero_spider_boy.team_id = team_preventers.id #UPDATE：Null値だったteam_id(@Hero)にid(Team)を紐づけ
    session.add(hero_spider_boy)
    session.commit()
    session.refresh(hero_spider_boy)
    print("Updated hero:", hero_spider_boy)

[OUT]
Updated hero: secret_name='Pedro Parqueador' team_id=1 age=None id=3 name='Spider-Boy'

７．Relationship（関連テーブルの紐づけ）

　６章では関連するテーブルをField()のみで関連付け（外部キー制約）しましたが、SQLModelには関連付けをより便利にするためにRelationshipクラスがあります。

７－１．データ追加(INSERT)_Relationship

　relationで紐づけたテーブルクラスでは複数のデータ追加方法があります。

【データ追加(INSERT)方法：Relationship※back_populatesの機能】
１．通常通り①インスタンス作成、②session.add(オブジェクト)で追加
　ー＞relationshipで紐づけられているため、heroオブジェクトの外部キーで指定したteamオブジェクトはsession.add(hero)時に合わせて追加される
２．Relationshipでの属性（Teamの場合：heroes: List["Hero"] ）に直接指定したオブジェクトを渡す
　ー＞(Teamの場合)heroes属性(List内)に追加した(hero)オブジェクトは、teamオブジェクトを追加時に合わせて追加される
３．（基本的に２．と同じだが）Relationshipを設定した属性に直接オブジェクトを渡す（例※heroes属性がList形式のためappendで追加：team_preventers.heroes.append(hero_tarantula)）

[IN]
from typing import List, Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select, or_, Relationship

#DBテーブル定義
class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

    heroes: List["Hero"] = Relationship(back_populates="team") #1対多の関係のため複数形(1つのteamに複数のhero)


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)

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    team: Optional[Team] = Relationship(back_populates="heroes") #多対1の関係のため単数形(1人のheroは1つのteamに属する)

#DB接続：メモリ上にデータ作成
engine = create_engine('sqlite:///:memory:', echo=False)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    with Session(engine) as session:
        #追加用データオブジェクト作成１
        team_preventers = Team(name="Preventers", headquarters="Sharp Tower") #team_id=1
        team_z_force = Team(name="Z-Force", headquarters="Sister Margaret’s Bar") #team_id=2

        hero_deadpond = Hero(name="Deadpond", secret_name="Dive Wilson", team=team_z_force) #id=1
        hero_rusty_man = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, team=team_preventers) #id=2
        hero_spider_boy = Hero(name="Spider-Boy", secret_name="Pedro Parqueador") #id=3

        #データ追加(INSERT)　※Teamも紐づいているため自動で追加
        session.add(hero_deadpond)
        session.add(hero_rusty_man)
        session.add(hero_spider_boy)
        session.commit()

        session.refresh(hero_deadpond)
        session.refresh(hero_rusty_man)
        session.refresh(hero_spider_boy)

        print("Created hero:", hero_deadpond)
        print("Created hero:", hero_rusty_man)
        print("Created hero:", hero_spider_boy)
        
        #データ更新(UPDATE)
        hero_spider_boy.team = team_preventers
        session.add(hero_spider_boy)
        session.commit()
        session.refresh(hero_spider_boy)
        print("Updated hero:", hero_spider_boy)

        #追加用データオブジェクト作成２
        hero_black_lion = Hero(name="Black Lion", secret_name="Trevor Challa", age=35) #id=4
        hero_sure_e = Hero(name="Princess Sure-E", secret_name="Sure-E") #id=5
        #新規teamのheroes(※Relationshipで定義)にhero情報を追加
        team_wakaland = Team(
            name="Wakaland",
            headquarters="Wakaland Capital City",
            heroes=[hero_black_lion, hero_sure_e],
        ) #team_id=3
        
        #データ追加(INSERT)　※Team内のheroes属性に紐づいているため自動で追加
        session.add(team_wakaland)
        session.commit()
        session.refresh(team_wakaland)
        print("Team Wakaland:", team_wakaland)

        #追加用データオブジェクト作成３
        hero_tarantula = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32) #id=6
        hero_dr_weird = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36) #id=7
        hero_cap = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93) #id=8

        #Teamオブジェクト（team_preventers）のheroes属性に紐づけたいheroオブジェクトを追加
        team_preventers.heroes.append(hero_tarantula)
        team_preventers.heroes.append(hero_dr_weird)
        team_preventers.heroes.append(hero_cap)
        
        #データ更新(UPDATE)＋紐づけられたheroオブジェクトの追加(INSERT)
        session.add(team_preventers)
        session.commit()
        session.refresh(hero_tarantula)
        session.refresh(hero_dr_weird)
        session.refresh(hero_cap)
        print("Preventers new hero:", hero_tarantula)
        print("Preventers new hero:", hero_dr_weird)
        print("Preventers new hero:", hero_cap)


def main():
    create_db_and_tables()
    create_heroes()

main()

[OUT]
Created hero: secret_name='Dive Wilson' team_id=1 age=None name='Deadpond' id=1
Created hero: secret_name='Tommy Sharp' team_id=2 age=48 name='Rusty-Man' id=2
Created hero: secret_name='Pedro Parqueador' team_id=None age=None name='Spider-Boy' id=3
Updated hero: secret_name='Pedro Parqueador' team_id=2 age=None name='Spider-Boy' id=3
Team Wakaland: name='Wakaland' id=3 headquarters='Wakaland Capital City'
Preventers new hero: secret_name='Natalia Roman-on' team_id=2 age=32 name='Tarantula' id=6
Preventers new hero: secret_name='Steve Weird' team_id=2 age=36 name='Dr. Weird' id=7
Preventers new hero: secret_name='Esteban Rogelios' team_id=2 age=93 name='Captain North America' id=8

７－２．データ更新（UPDATE)：Relationship

　Relationshipを設定した場合はそこで指定した属性を使用します。

【SQLModelでのUPDATE_外部キー：relationship】
●テーブルクラスで属性を設定（heroの引数ー＞team: Optional[Team] = Relationship(back_populates="heroes")）しており、その属性に関連するテーブルモデルのオブジェクトを渡す。
●その他の手順は通常のUPDATEと同じ

[IN]※上記コード抜粋

        #データ更新(UPDATE)
        hero_spider_boy.team = team_preventers
        session.add(hero_spider_boy)
        session.commit()
        session.refresh(hero_spider_boy)
        print("Updated hero:", hero_spider_boy)

[OUT]
Updated hero: secret_name='Pedro Parqueador' team_id=2 age=None name='Spider-Boy' id=3

７－３．データ読み込み（SELECT）_関連データ

　Relationshipを用いて各オブジェクト(hero⇔team)を紐づけました。紐づけたデータは属性から取得可能です。

【SQLModelでのSELECT：relationship】
●Relationshipで指定した属性内に関連したオブジェクトデータが含まれる。
●抽出したオブジェクトの属性を使用することでシンプルな抽出も可能
（下記コードのhero_spider_boy.teamを参照）

[IN]
with Session(engine) as session:
    team = session.exec(select(Team).where(Team.name == "Wakaland")).one()
    hero_spider_boy = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).one()
    print(team) #取得データ確認
    print(team.heroes) #teamのheroes: List["Hero"] = Relationship(back_populates="team")

    print(hero_spider_boy) #取得データ確認
    print(hero_spider_boy.team) # session.exec(select(Team).where(Team.id == hero_spider_boy.team_id)).one() と同義

[OUT]
name='Wakaland' id=3 headquarters='Wakaland Capital City'
[Hero(secret_name='Trevor Challa', team_id=3, age=35, name='Black Lion', id=4), Hero(secret_name='Sure-E', team_id=3, age=None, name='Princess Sure-E', id=5)]

secret_name='Pedro Parqueador' team_id=2 age=None name='Spider-Boy' id=3
name='Preventers' id=2 headquarters='Sharp Tower'

７－４．Relationshipの関連を除去

　Relationshipの関連付けを外すデータ削除は下記の通りobject.属性=Noneとなります。

[IN]
with Session(engine) as session:
    hero_spider_boy = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).one() #データ抽出(class形式)

    hero_spider_boy.team = None #データ削除※Relationshipの関連付けも切れる
    session.add(hero_spider_boy)
    session.commit()
    session.refresh(hero_spider_boy)
    print("Spider-Boy without team:", hero_spider_boy) #team_id=Noneを確認

[OUT]
Spider-Boy without team: secret_name='Pedro Parqueador' team_id=None age=None name='Spider-Boy' id=3

８．多対多テーブルの作成

　複数あるテーブル同士の関連は下記３種があります

【テーブル同士の関連】
●1対1：２つのテーブルの主キーが一致->１つのテーブルにまとめられる
●1対多：会社-社員のように片側の1つのデータに複数のデータが紐づく
　ー＞前章では1人のheroは1つのteamにしか所属できないとしたため1対多
●多対多：部署-社員のようにそれぞれ複数のデータが紐づく
　ー＞（今いる会社が兼務だらけなので）1人の社員でも複数部署に所属

　今回はheroが複数のteamに所属可能、つまり多対多の関連がある場合のテーブル作成方法を学びます。

８－１．テーブル作成：多対多

　多対多のテーブル作成は下記の通りです。

【多対多テーブル作成の注意点】
●多対多関連の場合は中間テーブル(link table)を作成する。
●上記の通りheroとteamは紐づけ専用のidは持たない(heroテーブルのteam_idは削除->中間テーブルでprimary keyを使用する）
●中間テーブルではユニークにデータ抽出するため両列に主キー制約を設定
●中間テーブルリンクのためRelationshipで"link_model= <link table>"指定
●先に定義するclassでのRelationshipの型ヒントはList[string]とする

[IN]
from typing import List, Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select, or_, Relationship

class HeroTeamLink(SQLModel, table=True):
    team_id: Optional[int] = Field(default=None, foreign_key="team.id", primary_key=True) #2つの列に主キー制約
    hero_id: Optional[int] = Field(default=None, foreign_key="hero.id", primary_key=True) #2つの列に主キー制約


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

    heroes: List["Hero"] = Relationship(back_populates="teams", link_model=HeroTeamLink) #多対多を示すlink_modelを指定


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) #Nullable設定
    
    #多対多のためteam->teams、Optional[Team]->List[Team]に変更 (1人のheroが複数のteamに所属する)
    teams: List[Team] = Relationship(back_populates="heroes", link_model=HeroTeamLink) 

[OUT]
クラス定義だけのため出力は無し

８－２．データ追加・更新（INSERT・UPDATE)

　データ追加・更新は基本的に通常と同じためコードのみ紹介します。

[IN]
from typing import List, Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select, or_, Relationship


class HeroTeamLink(SQLModel, table=True):
    team_id: Optional[int] = Field(default=None, foreign_key="team.id", primary_key=True) #2つの列に主キー制約
    hero_id: Optional[int] = Field(default=None, foreign_key="hero.id", primary_key=True) #2つの列に主キー制約


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

    heroes: List["Hero"] = Relationship(back_populates="teams", link_model=HeroTeamLink) #多対多を示すlink_modelを指定


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) #Nullable設定
    
    #多対多のためteam->teams、Optional[Team]->List[Team]に変更 (1人のheroが複数のteamに所属する)
    teams: List[Team] = Relationship(back_populates="heroes", link_model=HeroTeamLink) 


#DB接続：メモリ上にデータ作成
engine = create_engine('sqlite:///:memory:', echo=False)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    with Session(engine) as session:
        team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
        team_z_force = Team(name="Z-Force", headquarters="Sister Margaret’s Bar")

        hero_deadpond = Hero(name="Deadpond",secret_name="Dive Wilson",teams=[team_z_force, team_preventers]) #2つのteamに所属, age=NULL
        hero_rusty_man = Hero(name="Rusty-Man",secret_name="Tommy Sharp",age=48,teams=[team_preventers])
        hero_spider_boy = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", teams=[team_preventers]) #age=NULL
        
        session.add(hero_deadpond)
        session.add(hero_rusty_man)
        session.add(hero_spider_boy)
        session.commit()

        session.refresh(hero_deadpond)
        session.refresh(hero_rusty_man)
        session.refresh(hero_spider_boy)

        print("Deadpond:", hero_deadpond)
        print("Deadpond teams:", hero_deadpond.teams) #2つのチーム所属を確認
        print("Rusty-Man:", hero_rusty_man)
        print("Rusty-Man Teams:", hero_rusty_man.teams)
        print("Spider-Boy:", hero_spider_boy)
        print("Spider-Boy Teams:", hero_spider_boy.teams)
        
        #UPDATE
        hero_spider_boy = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).one()
        team_z_force = session.exec(select(Team).where(Team.name == "Z-Force")).one()

        team_z_force.heroes.append(hero_spider_boy) #Spider-BoyがZ-Forceチーム兼務に設定
        session.add(team_z_force) #Relationship(back_populates)が機能しているためsessionにhero_spider_boy追加は不要
        session.commit()

        print("Updated Spider-Boy's Teams:", hero_spider_boy.teams)
        print("Z-Force heroes:", team_z_force.heroes)

def main():
    create_db_and_tables()
    create_heroes()
    
main()

[OUT]
Deadpond: id=1 age=None secret_name='Dive Wilson' name='Deadpond'
Deadpond teams: [Team(id=1, headquarters='Sister Margaret’s Bar', name='Z-Force'), Team(id=2, headquarters='Sharp Tower', name='Preventers')]
Rusty-Man: id=2 age=48 secret_name='Tommy Sharp' name='Rusty-Man'
Rusty-Man Teams: [Team(id=2, headquarters='Sharp Tower', name='Preventers')]
Spider-Boy: id=3 age=None secret_name='Pedro Parqueador' name='Spider-Boy'
Spider-Boy Teams: [Team(id=2, headquarters='Sharp Tower', name='Preventers')]

#UPDATE後
Updated Spider-Boy's Teams: [Team(id=2, headquarters='Sharp Tower', name='Preventers'), Team(id=1, headquarters='Sister Margaret’s Bar', name='Z-Force')]
Z-Force heroes: [Hero(id=1, age=None, secret_name='Dive Wilson', name='Deadpond'), Hero(id=3, age=None, secret_name='Pedro Parqueador', name='Spider-Boy', teams=[Team(id=2, headquarters='Sharp Tower', name='Preventers'), Team(id=1, headquarters='Sister Margaret’s Bar', name='Z-Force', heroes=[...])])]

８－３．データ削除（DELETE)：remove()

　1対多ではRelationshipの関連を切るためにNoneを使いましたが多対多ではclass.属性のremove()メソッド※を使用します（※PythonのList操作です）。

[IN]
with Session(engine) as session:
    hero_spider_boy = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).one()
    team_z_force = session.exec(select(Team).where(Team.name == "Z-Force")).one()

    hero_spider_boy.teams.remove(team_z_force) #Z-Forceチームから脱退
    session.add(team_z_force) #データ更新
    session.commit() #DB反映

    print("Reverted Z-Force's heroes:", team_z_force.heroes) #Z-ForceからSpider-Boyが削除されている
    print("Reverted Spider-Boy's teams:", hero_spider_boy.teams) #Spider-Boyの所属チームにZ-Forceがないことを確認

[OUT]
Reverted Z-Force's heroes: [Hero(id=1, age=None, secret_name='Dive Wilson', name='Deadpond')]
Reverted Spider-Boy's teams: [Team(id=2, headquarters='Sharp Tower', name='Preventers')]

９．応用編

　追って別記事追加

参考記事

参考記事（仮想環境用）

　本記事では未使用（SQLModelでは仮想環境の使用を推奨していますが理解のハードルが高いため）ですが、参考用として記事を張っておきます。

あとがき

　使用できるSQLが①sqlite3, ②SQLAlchemy、③SQLModel、④PostgreSQLと選択肢が増えた（⑤MySQLはなんか環境構築ができない）ので、あとは状況に応じてベストなものを選びたい。

　それにしてもSQLModelがSQLAlchemyと同じモデルなのは本当にうれしかった。SQLAlchemyに死ぬほど(ほぼ1か月）時間かけたのに別物だったら結構つらかった。

　ER図を作ることができるライブラリもあるみたいなので、今度時間があるときに使ってみたい。」