I am Charmie

メモとログ

SQLAlchemy: Working with Transactions and the DBAPI

このチュートリアル

Connection

  • Coreを使ってデータベースとの全てのやり取りをするためのオブジェクト
  • with で使う (データベースへの接続の開始・終了を安全に扱うため?)
  • text(textual SQL statement)はSQLの命令文(selectとか)を扱う方法
from sqlalchemy import text

with engine.connect() as connection:
    result = connection.execute(text("select 'hello world'"))
    print(result.all())

変更のcommit

from sqlalchemy import text

# 'commit as you go' style: use Engine.connect() and then Connection.commit()
with engine.connect() as connection:
    connection.execute(text("CREATE TABLE ..."))
    connection.execute(text("INSERT INTO ..."), [{...}, ..., {...}])
    connection.commit()

# 'begin once' style: use Engine.begin()
with engine.begin() as connection:
    connection.execute(text("CREATE TABLE ..."))
    connection.execute(text("INSERT INTO ..."), [{...}, ..., {...}])

Statement実行の基本

  • Connection.execute()とtext()の使用によって得られる結果をResult型のオブジェクトとして受け取る
  • 以下のサンプル
    • SELECT文の結果をresultに格納
    • 検索結果のデータ毎にRow型オブジェクトとして扱う
      • Row型オブジェクトはtuple型のように動作(インデックス,属性名でのアクセス)
with engine.connect() as connection:
    result = connection.execute(text("SELECT x, y FROM some_table"))
    for row in result:
        print(row)

パラメータ送信

  • text()にパラメータを使えるコロンフォーマット ( ":y" )を使う
with engine.connect() as connection:
    result = connconnection.execute(
        text("SELECT x, y FROM some_table WHERE y > :y"),
        {"y": 2}
    )
    for row in result:
       print(f"x: {row.x}  y: {row.y}")

with engine.connect() as connection:
    connection.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [{"x": 11, "y": 12}, {"x": 13, "y": 14}]
    )
    connection.commit()

ORM Session

  • Connectionととても似た振る舞いをするSession
  • 内部でConnectionを参照してる
  • Sessionを非ORMな構造体?と一緒に使う時はSQL文を通して命令を行う
  • with engine.connect() を with Session(engine) as session へ変更
  • Connection.execute() を Session.execute() へ変更
from sqlalchemy.orm import Session

statement = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y").bindparams(y=6)
with Session(engine) as session:
    result = session.execute(statement)
    for row in result:
       print(f"x: {row.x}  y: {row.y}")
````