Working with Transactions and the DBAPI¶
Engine オブジェクトの準備ができたら、 Engine の基本的な操作と、その主要なエンドポイントである Connection と Result について説明します。また、これらのオブジェクトのためのORMの Session として知られている facade についても説明します。
Note to ORM readers
ORMを使用する場合、Engine は Session によって管理されます。最近のSQLAlchemyの Session は、以下で説明する Connection とほぼ同じトランザクションおよびSQL実行パターンを強調しています。したがって、このサブセクションはコア中心ですが、ここでの概念はすべてORMの使用にも関連しており、すべてのORM学習者に推奨されます。 Connection で使用される実行パターンは、このセクションの最後で Session と比較されます。
SQLAlchemyの主要な機能であるSQLAlchemy式言語をまだ紹介していないので、このパッケージ内で text() 構文と呼ばれる簡単な構文を使って、SQL文を textual SQL として記述します。textual SQLは日常のSQLAlchemyの使用における規則ではなく例外であることに注意してください。しかし、常に利用可能です。
Getting a Connection¶
Engine の目的は、 Connection オブジェクトを提供することによってデータベースに接続することです。Coreを直接操作する場合、 Connection オブジェクトは、データベースとのすべての相互作用が行われる方法です。 Connection はデータベースに対してオープンなリソースを作成するので、このオブジェクトの使用を特定のコンテキストに制限したいと思います。これを行う最善の方法は、 the with statement としても知られるPythonコンテキストマネージャを使用することです。以下では、”Hello World”を示すためにテキストSQL文を使用します。テキストSQLは、後で詳しく説明する text() という構成体で作成されます。
>>> from sqlalchemy import text
>>> with engine.connect() as conn:
... result = conn.execute(text("select 'hello world'"))
... print(result.all())
BEGIN (implicit)
select 'hello world'
[...] ()
[('hello world',)]
ROLLBACK
上記の例では、コンテキストマネージャがデータベース接続を作成し、トランザクション内で操作を実行します。Python DBAPIのデフォルトの動作では、トランザクションは常に進行中です。接続が:term:解放`されると、トランザクションを終了するためにROLLBACKが発行されます。トランザクションは**自動的にコミットされません**。データをコミットしたい場合は、次のセクションで説明するように :meth:`_engine.Connection.commit を呼び出す必要があります。
Tip
“autocommit” mode is available for special cases. The section Setting Transaction Isolation Levels including DBAPI Autocommit discusses this.
SELECTの結果は、後で説明する Result というオブジェクトに返されました。ここで、このオブジェクトは”connect”ブロック内で使用し、接続の範囲外では使用しない方がよいことを付け加えておきます。
Committing Changes¶
DBAPI接続が自動的にコミットされないことを学習しました。データをコミットしたい場合はどうすればよいでしょうか?上記の例を変更して、テーブルを作成し、データを挿入し、 Connection.commit() メソッドを使用してトランザクションをコミットすることができます。 内部 Connection オブジェクトがあるブロック内:
# "commit as you go"
>>> with engine.connect() as conn:
... conn.execute(text("CREATE TABLE some_table (x int, y int)"))
... conn.execute(
... text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
... [{"x": 1, "y": 1}, {"x": 2, "y": 4}],
... )
... conn.commit()
BEGIN (implicit)
CREATE TABLE some_table (x int, y int)
[...] ()
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(1, 1), (2, 4)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT
上記では、”CREATE TABLE”文 [1] とパラメータ化された”INSERT”文の2つのSQL文を実行します(パラメータ化の構文については Sending Multiple Parameters で後述します)。ブロック内で行った作業をコミットするには、トランザクションをコミットする Connection.commit() メソッドを呼び出します。この後、さらにSQL文を実行し、それらの文に対して Connection.commit() を再度呼び出すことができます。SQLAlchemyでは、このスタイルを commit as you go と呼んでいます。
データをコミットするための別のスタイルもあります。「connect」ブロックをトランザクションブロックとして事前に宣言できます。これを行うには、 Engine.connect() メソッドではなく、 Engine.begin() メソッドを使用して接続を取得します。このメソッドは Connection のスコープを管理し、ブロックが成功した場合は最後にCOMMITを、例外が発生した場合はROLLBACKを使用して、トランザクション内のすべてを囲みます。このスタイルは begin once と呼ばれます。
# "begin once"
>>> with engine.begin() as conn:
... conn.execute(
... text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
... [{"x": 6, "y": 8}, {"x": 9, "y": 10}],
... )
BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(6, 8), (9, 10)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT
“begin once”スタイルの方が短く、ブロック全体の意図が事前に示されるため、ほとんどの場合は”begin once”スタイルを使用する必要があります。ただし、このチュートリアルでは、デモの目的に対してより柔軟である”commit as you go”スタイルを使用します。
Basics of Statement Execution¶
Connection.execute() というメソッドを text() というオブジェクトと組み合わせて使用し、 Result というオブジェクトを返して、データベースに対してSQL文を実行する例をいくつか見てきました。この節では、これらのコンポーネントのメカニズムと相互作用について詳しく説明します。
Session.execute() メソッドは Connection.execute() と非常によく似た動作をしますが、ORMの結果行はCoreで使われているのと同じ Result インターフェースを使って配信されます。
Fetching Rows¶
まず、前に挿入した行を利用して、作成したテーブルに対してテキストのSELECT文を実行することで、 Result オブジェクトを詳しく説明します。:
>>> with engine.connect() as conn:
... result = conn.execute(text("SELECT x, y FROM some_table"))
... for row in result:
... print(f"x: {row.x} y: {row.y}")
BEGIN (implicit)
SELECT x, y FROM some_table
[...] ()
x: 1 y: 1
x: 2 y: 4
x: 6 y: 8
x: 9 y: 10
ROLLBACK
上記では、実行した”SELECT”文字列がテーブルからすべての行を選択しています。返されるオブジェクトは Result と呼ばれ、結果行の反復可能なオブジェクトを表します。
Result には、行を取り出したり変換したりするためのメソッドがたくさんあります。例えば、前に説明した Result.all() メソッドは、すべての Row オブジェクトのリストを返します。また、Pythonのイテレータインタフェースを実装しているので、 Row オブジェクトのコレクションを直接繰り返すことができます。
Row オブジェクト自体は、Pythonの named tuples のように動作することを意図しています。以下では、行にアクセスするさまざまな方法を説明します。
Tuple Assignment-これは最もPython特有のスタイルで、各行が受信されたときに位置的に変数を割り当てます。:
result = conn.execute(text("select x, y from some_table")) for x, y in result: ...
Integer Index-タプルはPythonシーケンスなので、通常の整数アクセスも可能です。>
result = conn.execute(text("select x, y from some_table")) for row in result: x = row[0]
Attribute Name - これらはPythonの名前付きタプルであるため、タプルには各列の名前と一致する動的な属性名があります。これらの名前は通常、SQL文が各行の列に割り当てる名前です。通常はかなり予測可能で、ラベルによって制御することもできますが、あまり定義されていない場合には、データベース固有の動作に従うことがあります:
result = conn.execute(text("select x, y from some_table")) for row in result: y = row.y # illustrate use with Python f-strings print(f"Row: {row.x} {y}")
Mapping Access - 行をPython**mapping**オブジェクトとして受け取るには、これは基本的には共通の
dictオブジェクトに対するPythonのインターフェイスの読み込み専用バージョンですが、ResultはResult.mappings()修飾子を使ってMappingResultオブジェクトに**変換**されます。これはRowオブジェクトではなく、辞書のようなRowMappingオブジェクトを生成する結果オブジェクトです:result = conn.execute(text("select x, y from some_table")) for dict_row in result.mappings(): x = dict_row["x"] y = dict_row["y"]
Sending Parameters¶
前述のINSERTの例で見たように、SQL文には通常、文自体とともに渡されるデータが付随しています。したがって、 Connection.execute() メソッドは、 bound parameters として知られるパラメータも受け入れます。初歩的な例として、SELECT文を特定の条件を満たす行だけに制限したい場合があります。たとえば、関数に渡される特定の値よりも”y”値が大きい行などです。
SQL文を固定し、ドライバが値を適切にサニタイズできるようにするために、” :y “と呼ばれる新しいパラメータを指定するWHERE条件を文に追加します。 text() 構文は、コロン形式””:y””を使用してこれらを受け入れます。” :y “の実際の値は、2番目の引数として辞書の形式で Connection.execute() に渡されます。:
>>> with engine.connect() as conn:
... result = conn.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}")
BEGIN (implicit)
SELECT x, y FROM some_table WHERE y > ?
[...] (2,)
x: 2 y: 4
x: 6 y: 8
x: 9 y: 10
ROLLBACK
ログに記録されたSQL出力では、バインドされたパラメータ :y がSQLiteデータベースに送信されたときに疑問符に変換されたことがわかります。これは、SQLiteデータベースドライバが「qmark parameter style」と呼ばれる形式を使用しているためです。これは、DBAPI仕様で許可されている6つの異なる形式の1つです。SQLAlchemyは、これらの形式を1つに抽象化します。これは、コロンを使用した”名前付き”形式です。
Sending Multiple Parameters¶
Committing Changes の例では、一度に複数の行をデータベースに挿入できるように見えるINSERT文を実行しました。”INSERT”、”UPDATE”、”DELETE”などの DML 文の場合、単一の辞書ではなく辞書のリストを渡すことで、 複数のパラメータセット を Connection.execute() メソッドに送ることができます。これは、単一のSQL文がパラメータセットごとに1回、複数回呼び出されることを示します。この実行スタイルは executemany として知られています:
>>> with engine.connect() as conn:
... conn.execute(
... text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
... [{"x": 11, "y": 12}, {"x": 13, "y": 14}],
... )
... conn.commit()
BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(11, 12), (13, 14)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT
この操作は、指定されたINSERT文をパラメータ・セットごとに1回実行するのと同じです。ただし、多くの行でパフォーマンスが向上するように操作が最適化されます。
“execute”と”executemany”の主な動作上の違いは、後者が、たとえ文にRETURNING句が含まれていても、結果行の返却をサポートしていないことです。これに対する1つの例外は、このチュートリアルの後半の Using INSERT Statements で紹介されているCore insert() 構文を使用する場合で、これは Insert.returning() メソッドを使用したRETURNINGも示します。その場合、SQLAlchemyは特別なロジックを使用してINSERT文を再編成するため、RETURNINGをサポートしながら多くの行に対して呼び出すことができます。
See also
executemany - Glossary では、ほとんどの”executemany”実行に使用されるDB APIレベルの cursor.executemany() メソッドについて説明しています。
“Insert Many Values” Behavior for INSERT statements - Working with Engines and Connections では、 Insert.returning() が”executemany”実行で結果セットを返すために使用する特殊なロジックについて説明しています。
Executing with an ORM Session¶
前述したように、上記のパターンと例のほとんどはORMでの使用にも適用されるため、ここではこの使用方法を紹介し、チュートリアルを進めるにつれて、コアとORMを一緒に使用して各パターンを説明できるようにします。
ORMを使用するときの基本的なトランザクション/データベース対話型オブジェクトは Session と呼ばれます。最近のSQLAlchemyでは、このオブジェクトは Connection と非常によく似た方法で使用されます。実際、 Session が使用されるとき、内部的にはSQLを出力するために使用する Connection を参照します。
Session がORM以外の構成体で使用された場合、それは与えられたSQL文を通過し、一般的には Connection が直接行うのとあまり変わりませんので、ここでは既に学んだ単純なテキストのSQL操作の観点から説明できます。
Session にはいくつかの異なる生成パターンがありますが、ここでは Connection がどのように使われているかを正確に追跡する最も基本的なものを説明します。 Connection はコンテキストマネージャ内でそれを構築するために使われます。:
>>> from sqlalchemy.orm import Session
>>> stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y")
>>> with Session(engine) as session:
... result = session.execute(stmt, {"y": 6})
... for row in result:
... print(f"x: {row.x} y: {row.y}")
BEGIN (implicit)
SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y
[...] (6,)
x: 6 y: 8
x: 9 y: 10
x: 11 y: 12
x: 13 y: 14
ROLLBACK
上の例は、前のセクションの`in Sending Parameters の例と比較することができます。ここでは直接、 with engine.connect() as conn の呼び出しを with Session(engine) as session に置き換え、 Connection.execute() メソッドと同じように、 Session.execute() メソッドを使用します。
また、 Connection と同様に、 Session は Session.commit() メソッドを使った”commit as you go”動作を特徴としています。以下の例では、テキストのUPDATE文を使ってデータの一部を変更しています。:
>>> with Session(engine) as session:
... result = session.execute(
... text("UPDATE some_table SET y=:y WHERE x=:x"),
... [{"x": 9, "y": 11}, {"x": 13, "y": 15}],
... )
... session.commit()
BEGIN (implicit)
UPDATE some_table SET y=? WHERE x=?
[...] [(11, 9), (15, 13)]
COMMIT
上記では、 Sending Multiple Parameters で導入されたバウンドパラメータの”executemany”スタイルの実行を使用してUPDATE文を呼び出し、”commit as you go”コミットでブロックを終了しました。
Tip
Session は、トランザクションを終了した後、実際には Connection オブジェクトを保持していません。次にデータベースに対してSQLを実行する必要があるときに、 Engine から新しい Connection を取得します。
Session には、明らかにそれよりも多くのトリックが隠されていますが、 Connection.execute() と同じ方法で使用される Session.execute() メソッドがあることを理解すれば、後で説明する例から始めることができます。
See also
Basics of Using a Session - Session オブジェクトを使用して、基本的な作成および使用パターンを提供します。