Working with Engines and Connections

この節では、Engine , Connection および関連するオブジェクトの直接の使用法について詳しく説明します。SQLAlchemy ORMを使用する場合、これらのオブジェクトは一般にアクセスされないことに注意してください。代わりに、 Session オブジェクトがデータベースへのインタフェースとして使用されます。しかし、ORMの上位レベルの管理サービスの関与なしに、テキストのSQL文やSQL式の構成体を直接使用して構築されたアプリケーションの場合、 Engine と Connection はキング(そしてクイーン?)です - 続きを読んでください。

Basic Usage

Engine Configuration から、 Engine が create_engine() 呼び出しによって作成されることを思い出してください:

engine = create_engine("mysql+mysqldb://scott:tiger@localhost/test")

create_engine() の一般的な使い方は、特定のデータベースURLごとに1回、単一のアプリケーションプロセスの存続期間にわたってグ行バルに保持されます。単一の Engine は、プロセスに代わって多くの個別の DBAPI 接続を管理し、同時に呼び出されることを意図しています。 Engine は、ただ1つの接続リソースを表すDBAPIの connect() 関数と 同義では ありません 。 Engine は、オブジェクト単位や関数単位の呼び出しではなく、アプリケーションのモジュールレベルで1回だけ作成すると最も効率的です。

Engine の最も基本的な機能は、SQL文を呼び出すことができる Connection へのアクセスを提供することです。データベースにテキスト文を出力するには、次のようにします:

from sqlalchemy import text

with engine.connect() as connection:
    result = connection.execute(text("select username from users"))
    for row in result:
        print("username:", row.username)

上の例では、 Engine.connect() メソッドが Connection オブジェクトを返し、それをPythonのコンテキストマネージャ(例えば with: 文)で使用することで、 Connection.close() メソッドがブロックの最後で自動的に呼び出されます。 Connection は、実際のDBAPI接続の プロキシ オブジェクトです。DBAPI接続は、 Connection が作成された時点で接続プールから取得されます。

返されるオブジェクトは CursorResult として知られています。これはDBAPIカーソルを参照し、DBAPIカーソルと同様の行を取り出すためのメソッドを提供します。DBAPIカーソルは、その結果の行(もしあれば)がすべて使い果たされると、 CursorResult によって閉じられます。UPDATE文のように(行が返されない)行を返さない CursorResult は、構築されるとすぐにカーソルリソースを解放します。

Connection が with: ブロックの最後で閉じられた場合、参照されたDBAPI接続は接続プールに対して released されます。データベース自体の観点から見ると、接続プールに次の使用のためにこの接続を保存する余地があると仮定すると、接続プールは実際には接続を”close”しません。接続が再利用のためにプールに戻されると、プーリング機構はDBAPI接続に対して rollback() 呼び出しを発行して、トランザクション状態やロックが削除され(これは Reset On Return として知られています)、接続が次に使用できるようになります。

上の例はテキストのSQL文字列の実行を示しています。これは text() 構文を使って呼び出され、テキストのSQLを使いたいことを示します。 Connection.execute() メソッドはもちろんそれ以上のことにも対応できます。チュートリアルについては SQLAlchemy Unified Tutorial の Working with Data を参照してください。

Using Transactions

Note

このセクションでは、 Engine および Connection オブジェクトを直接操作する場合にトランザクションを使用する方法について説明します。SQLAlchemy ORMを使用する場合、トランザクション制御のための公開APIは Session オブジェクトを経由します。これは Transaction オブジェクトを内部的に使用します。詳細は Managing Transactions を参照してください。

Commit As You Go

Connection オブジェクトは、常にトランザクションブロックのコンテキスト内でSQL文を発行します。SQL文を実行するために Connection.execute() メソッドが最初に呼び出されると、このトランザクションは autobegin として知られる動作を使用して自動的に開始されます。トランザクションは、 Connection.commit() または Connection.rollback() メソッドが呼び出されるまで、 Connection オブジェクトのスコープ内でそのまま残ります。トランザクションが終了した後、 Connection は Connection.execute() メソッドが再度呼び出されるのを待ち、呼び出された時点で再び自動開始します。

この呼び出しスタイルは commit as you go と呼ばれ、以下の例で説明されています:

with engine.connect() as connection:
    connection.execute(some_table.insert(), {"x": 7, "y": "this is some data"})
    connection.execute(
        some_other_table.insert(), {"q": 8, "p": "this is some more data"}
    )

    connection.commit()  # commit the transaction

“commit as you go”スタイルでは、 Connection.commit() メソッドと Connection.rollback() メソッドを、 Connection.execute() を使用して発行された他の文の進行中のシーケンス内で自由に呼び出すことができます。トランザクションが終了して新しい文が発行されるたびに、新しいトランザクションが暗黙的に開始されます:

with engine.connect() as connection:
    connection.execute(text("<some statement>"))
    connection.commit()  # commits "some statement"

    # new transaction starts
    connection.execute(text("<some other statement>"))
    connection.rollback()  # rolls back "some other statement"

    # new transaction starts
    connection.execute(text("<a third statement>"))
    connection.commit()  # commits "a third statement"

New in version 2.0の”commit: as you go”スタイルは、SQLAlchemy 2.0の新機能です。”future”スタイルのエンジンを使用している場合は、SQLAlchemy 1.4の”transitional”モードでも使用できます。

Begin Once

Connection オブジェクトは、 begin once として知られる、より明示的なトランザクション管理スタイルを提供します。”commit as you go”とは対照的に、”begin once”では、トランザクションの開始点を明示的に記述することができ、トランザクション自体をコンテキストマネージャブロックとしてフレームアウトして、トランザクションの終了を暗黙的にすることができます。”begin once”を使用するには、 Connection.begin() メソッドを使用します。このメソッドは、DBAPIトランザクションを表す Transaction オブジェクトを返します。このオブジェクトは、独自の Transaction.rollback() メソッドによる明示的な管理もサポートしていますが、推奨される方法としては、コンテキストマネージャインターフェイスもサポートしています。コンテキストマネージャインターフェイスでは、ブロックが正常に終了したときに自分自身をコミットし、例外が発生した場合は例外を外部に伝播する前に行ルバックを発行します。以下に、”begin once”ブロックの形式を示します:

with engine.connect() as connection:
    with connection.begin():
        connection.execute(some_table.insert(), {"x": 7, "y": "this is some data"})
        connection.execute(
            some_other_table.insert(), {"q": 8, "p": "this is some more data"}
        )

    # transaction is committed

Connect and Begin Once from the Engine

上記の”begin once”ブロックの便利な省略形は、 Engine.connect() と Connection.begin() の2つの別々の手順を実行するのではなく、元の Engine オブジェクトのレベルで Engine.begin() メソッドを使用することです。 Engine.begin() メソッドは、 Connection のコンテキストマネージャと、通常 Connection.begin() メソッドによって返される Transaction のコンテキストマネージャの両方を内部で維持する特別なコンテキストマネージャを返します:

with engine.begin() as connection:
    connection.execute(some_table.insert(), {"x": 7, "y": "this is some data"})
    connection.execute(
        some_other_table.insert(), {"q": 8, "p": "this is some more data"}
    )

# transaction is committed, and Connection is released to the connection
# pool

Tip

Engine.begin() ブロック内では、 Connection.commit() または Connection.rollback() メソッドを呼び出すことができます。これらのメソッドは、通常はブロックによって事前に区切られているトランザクションを終了させます。しかし、これを行うと、ブロックが終了するまで、 Connection に対してそれ以上のSQL操作が行われなくなります:

>>> from sqlalchemy import create_engine
>>> e = create_engine("sqlite://", echo=True)
>>> with e.begin() as conn:
...     conn.commit()
...     conn.begin()
2021-11-08 09:49:07,517 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-11-08 09:49:07,517 INFO sqlalchemy.engine.Engine COMMIT
Traceback (most recent call last):
...
sqlalchemy.exc.InvalidRequestError: Can't operate on closed transaction inside
context manager.  Please complete the context manager before emitting
further commands.

Mixing Styles

“commit as you go”スタイルと”begin once”スタイルは、 Connection.begin() への呼び出しが”autobegin”の動作と競合しない限り、単一の Engine.connect() ブロック内で自由に混在させることができます。これを実現するには、 Connection.begin() は、SQL文が発行される前か、前回の Connection.commit() または Connection.rollback() の呼び出しの直後にのみ呼び出す必要があります:

with engine.connect() as connection:
    with connection.begin():
        # run statements in a "begin once" block
        connection.execute(some_table.insert(), {"x": 7, "y": "this is some data"})

    # transaction is committed

    # run a new statement outside of a block. The connection
    # autobegins
    connection.execute(
        some_other_table.insert(), {"q": 8, "p": "this is some more data"}
    )

    # commit explicitly
    connection.commit()

    # can use a "begin once" block here
    with connection.begin():
        # run more statements
        connection.execute(...)

“begin once”を使用するコードを開発する場合、ライブラリはトランザクションが既に”autobegun”されていれば InvalidRequestError を発生させます。

Setting Transaction Isolation Levels including DBAPI Autocommit

ほとんどのDBAPIは、設定可能なトランザクション isolation レベルの概念をサポートしています。これらは伝統的に”READ UNCOMMITTED”、”READ COMMITTED”、”REPEATABLE READ”、”SERIALIZABLE”の4つのレベルです。これらは通常、新しいトランザクションを開始する前にDBAPI接続に適用されますが、ほとんどのDBAPIはSQL文が最初に発行されたときに暗黙的にこのトランザクションを開始することに注意してください。

独立性レベルをサポートするDBAPIは、通常、真の”autocommit”の概念もサポートします。これは、DBAPI接続自体が非トランザクション・オートコミット・モードになることを意味します。これは通常、データベースに”BEGIN”を自動的に発行するという一般的なDBAPIの動作が発生しなくなることを意味しますが、他のディレクティブが含まれる場合もあります。SQLAlchemyでは、”autocommit”の概念を他の独立性レベルと同様に扱います。つまり、”autocommit”は”read commited”だけでなく、原子性も失う独立性レベルです。

Tip

以下のセクションの Understanding the DBAPI-Level Autocommit Isolation Level で詳しく説明しますが、”autocommit”独立性レベルは、他の独立性レベルと同様に、 Connection オブジェクトの”transactional”動作に 影響しない ことに注意してください。このオブジェクトは、DBAPIの .commit()` メソッドと .rollback() メソッドを呼び出し続けます(これらはオートコミットでは効果がありません)。また、 .begin() メソッドは、DBAPIが暗黙的にトランザクションを開始することを想定しています(つまり、SQLAlchemyの”begin” はオートコミットモードを変更しません )。

SQLAlchemyダイアレクトでは、これらの独立性レベルをサポートするとともに、可能な限り大きなオートコミットをサポートする必要があります。

Setting Isolation Level or DBAPI Autocommit for a Connection

Engine.connect() から取得した個々の Connection オブジェクトに対して、 Connection.execution_options() メソッドを使用して、その Connection オブジェクトの期間に対して独立性レベルを設定することができます。パラメータは Connection.execution_options.isolation_level として知られており、値は文字列で、通常は次の名前のサブセットです:

# possible values for Connection.execution_options(isolation_level="<value>")

"AUTOCOMMIT"
"READ COMMITTED"
"READ UNCOMMITTED"
"REPEATABLE READ"
"SERIALIZABLE"

すべてのDBAPIがすべての値をサポートしているわけではありません。サポートされていない値が特定のバックエンドで使用されると、エラーが発生します。

たとえば、特定の接続でREPEATABLE READを強制するには、次のようにトランザクションを開始します:

with engine.connect().execution_options(
    isolation_level="REPEATABLE READ"
) as connection:
    with connection.begin():
        connection.execute(text("<statement>"))

Tip

Connection.execution_options() メソッドの戻り値は、このメソッドが呼び出された Connection オブジェクトと同じです。つまり、 Connection オブジェクトの状態を適切に変更します。これはSQLAlchemy 2.0の新しい動作です。この動作は Engine.execution_options() メソッドには適用されません。このメソッドは Engine のコピーを返します。また、以下に説明するように、実行オプションが異なる複数の Engine オブジェクトを構築するために使用できますが、これらのオブジェクトは同じダイアレクトと接続プールを共有します。

Note

Connection.execution_options.isolation_level パラメータは、Executable.execution_options() のような文レベルのオプションには適用されず、このレベルで設定された場合は拒否されます。これは、このオプションはトランザクションごとにDBAPI接続で設定する必要があるためです。

Setting Isolation Level or DBAPI Autocommit for an Engine

Connection.execution_options.isolation_level オプションは、よくあるようにエンジン全体に設定することもできます。これは、 create_engine.isolation_level パラメータを create_engine() に渡すことで実現できます:

from sqlalchemy import create_engine

eng = create_engine(
    "postgresql://scott:tiger@localhost/test", isolation_level="REPEATABLE READ"
)

上記の設定では、新しいDBAPI接続が作成された時点で、以降のすべての操作に対して "REPEATABLE READ" の独立性レベルが設定されます。

Maintaining Multiple Isolation Levels for a Single Engine

独立性レベルは、 create_engine() の create_engine.execution_options パラメータか、 Engine.execution_options() メソッドのどちらかを使用して、エンジンごとに設定することもできます。後者は、元のエンジンのダイアレクトと接続プールを共有する Engine のコピーを作成しますが、接続ごとに独自の独立性レベル設定があります:

from sqlalchemy import create_engine

eng = create_engine(
    "postgresql+psycopg2://scott:tiger@localhost/test",
    execution_options={"isolation_level": "REPEATABLE READ"},
)

上記の設定では、DBAPI接続は、開始された新しいトランザクションごとに "REPEATABLE READ" の独立性レベル設定を使用するように設定されますが、プールされた接続は、接続が最初に発生したときに存在していた元の独立性レベルにリセットされます。 create_engine() のレベルでは、終了効果は create_engine.isolation_level パラメータを使用した場合と何も変わりません。

しかし、異なる独立性レベル内で操作を実行することを頻繁に選択するアプリケーションは、それぞれが異なる独立性レベルに設定される、リード Engine の複数の”sub-engines”を作成したい場合があります。そのようなユースケースの1つは、”トランザクション”操作と”読み取り専用”操作に分割される操作を持つアプリケーションで、 "AUTOCOMMIT" を使用する別の Engine をメインエンジンから分離することができます:

from sqlalchemy import create_engine

eng = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")

autocommit_engine = eng.execution_options(isolation_level="AUTOCOMMIT")

上の例では、 Engine.execution_options() メソッドが元の Engine のシャ行コピーを作成します。 eng と autocommit_engine は同じダイアレクトと接続プールを共有します。しかし、 autocommit_engine から取得された接続には”AUTOCOMMIT”モードが設定されます。

独立性レベルの設定は、どの設定であっても、接続が接続プールに戻されると無条件に元に戻されます。

See also

SQLite Transaction Isolation

PostgreSQL Transaction Isolation

MySQL Transaction Isolation

SQL Server Transaction Isolation

Oracle Transaction Isolation

Setting Transaction Isolation Levels / DBAPI AUTOCOMMIT - for the ORM

Using DBAPI Autocommit Allows for a Readonly Version of Transparent Reconnect - DBAPIオートコミットを使用して、読み込み専用操作のためにデータベースに透過的に再接続するレシピ

Understanding the DBAPI-Level Autocommit Isolation Level

親のセクションでは、 Connection.execution_options.isolation_level パラメータの概念と、それを使用してデータベースの独立性レベルを設定する方法を紹介しました。これには、SQLAlchemyによって別のトランザクション独立性レベルとして扱われるDB APIレベルの”autocommit”も含まれます。このセクションでは、このアプ行チの意味を明確にすることを試みます。

Connection オブジェクトをチェックアウトして”autocommit”モードを使いたい場合は、次のようにします:

with engine.connect() as connection:
    connection.execution_options(isolation_level="AUTOCOMMIT")
    connection.execute(text("<statement>"))
    connection.execute(text("<statement>"))

上の図は、”DBAPI autocommit”モードの通常の使用法を示しています。 Connection.begin() や Connection.commit() のようなメソッドを使う必要はありません。全ての文は即座にデータベースにコミットされます。ブロックが終了すると、 Connection オブジェクトは”autocommit”独立性レベルを元に戻し、DBAPI接続はコネクションプールに解放されます。そこではDBAPIの connection.rollback() メソッドが通常呼び出されますが、上の文は既にコミットされているので、この行ルバックはデータベースの状態を変更しません。

“autocommit”モードは Connection.begin() メソッドが呼び出されても持続することに注意してください。DBAPIはデータベースに対してBEGINを発行しませんし、 Connection.commit() が呼び出されてもCOMMITを発行しません。”autocommit”独立性レベルは、トランザクションコンテキストを想定して作成されたコードに適用されることが想定されているため、この使用方法もエラーシナリオではありません。”独立性レベル”は、他の独立性レベルと同様に、トランザクション自体の設定の詳細です。

次の例では、SQLAlchemyレベルのトランザクションブロックに関係なく、文は autocommitting のままです:

with engine.connect() as connection:
    connection = connection.execution_options(isolation_level="AUTOCOMMIT")

    # this begin() does not affect the DBAPI connection, isolation stays at AUTOCOMMIT
    with connection.begin() as trans:
        connection.execute(text("<statement>"))
        connection.execute(text("<statement>"))

ロギングを有効にして上記のようなブロックを実行すると、ロギングはDBAPIレベルの .commit() が呼び出されている間、オートコミットモードのためにおそらく何の効果もないことを示そうとします。

INFO sqlalchemy.engine.Engine BEGIN (implicit)
...
INFO sqlalchemy.engine.Engine COMMIT using DBAPI connection.commit(), DBAPI should ignore due to autocommit mode

同時に、SQLAlchemyのトランザクションセマンティクスである”DBAPI autocommit”、つまり Connection.begin() のPython内での振る舞いや “autobegin” の振る舞いを使用しても、 DBAPI接続自体には影響しませんが、そのまま残ります 。たとえば、以下のコードでは、autobeginがすでに発生した後に Connection.begin() が呼び出されるため、エラーが発生します:

with engine.connect() as connection:
    connection = connection.execution_options(isolation_level="AUTOCOMMIT")

    # "transaction" is autobegin (but has no effect due to autocommit)
    connection.execute(text("<statement>"))

    # this will raise; "transaction" is already begun
    with connection.begin() as trans:
        connection.execute(text("<statement>"))

上記の例では、”autocommit”分離レベルが基礎となるデータベース・トランザクションの構成の詳細であり、SQLAlchemy Connectionオブジェクトの開始/コミット動作とは無関係であるという同じテーマについても説明しています。”autocommit”モードは Connection.begin() とは一切相互作用せず、 Connection はトランザクションに関して自身の状態を変更する際にこの状態を参照しません(ただし、エンジン・ロギング内では、これらのブロックが実際にコミットされていないことが示唆されます)。この設計の理論的根拠は、 Connection と完全に一貫した使用パターンを維持することにあります。ここで、DBAPI-autocommitモードは、他の場所でコードの変更を示すことなく、独立して変更できます。

Changing Between Isolation Levels

オートコミット・モードを含む独立性レベルの設定は、接続が解放されて接続プールに戻されたときに自動的にリセットされます。したがって、単一の Connection オブジェクトで独立性レベルを切り替えようとすることは、過剰な冗長性につながるため、避けることをお勧めします。

単一の Connection チェックアウトの範囲内でアドホックモードで”autocommit”を使用する方法を説明するには、 Connection.execution_options.isolation_level パラメータを以前の独立性レベルで再適用する必要があります。前のセクションでは、オートコミットが行われている間にトランザクションを開始するために Connection.begin() を呼び出そうとする試みを説明しました。 Connection.begin() を呼び出す前に、まず独立性レベルを元に戻すことで、実際にそうするように例を書き直すことができます:

# if we wanted to flip autocommit on and off on a single connection/
# which... we usually don't.

with engine.connect() as connection:
    connection.execution_options(isolation_level="AUTOCOMMIT")

    # run statement(s) in autocommit mode
    connection.execute(text("<statement>"))

    # "commit" the autobegun "transaction"
    connection.commit()

    # switch to default isolation level
    connection.execution_options(isolation_level=connection.default_isolation_level)

    # use a begin block
    with connection.begin() as trans:
        connection.execute(text("<statement>"))

上記では、独立性レベルを手動で元に戻すために、 Connection.default_isolation_level を使用してデフォルトの独立性レベルを復元しました(ここではそれが必要であると仮定しています)。しかし、チェックイン時に自動的に独立性レベルのリセットを処理する Connection のアーキテクチャで作業する方がおそらく良い考えでしょう。上記の 推奨 方法は、2つのブロックを使用することです:

# use an autocommit block
with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as connection:
    # run statement in autocommit mode
    connection.execute(text("<statement>"))

# use a regular block
with engine.begin() as connection:
    connection.execute(text("<statement>"))

まとめると:

  1. “DBAPI level autocommit”独立性レベルは、Connection オブジェクトの”begin”と”commit”の概念から完全に独立しています。

  2. 独立性レベルごとに個別の Connection チェックアウトを使用します。1回の接続チェックアウトで「自動コミット」の間を行ったり来たりすることは避けてください。デフォルトの独立性レベルを復元する作業はエンジンに任せてください。

Using Server Side Cursors (a.k.a. stream results)

一部のバックエンドでは、”サーバー側カーソル”と”クライアント側カーソル”の概念が明示的にサポートされています。ここでいうクライアント側カーソルとは、データベース・ドライバが文の実行から戻る前に、結果セットからすべての行を完全にメモリにフェッチすることを意味します。PostgreSQLやMySQL/MariaDBなどのドライバでは、通常、デフォルトでクライアント側カーソルが使用されます。これとは対照的に、サーバー側カーソルは、結果行がクライアントによってコンシュームされるときに、結果行がデータベース・サーバの状態内で保留中のままであることを示します。Oracleのドライバでは、通常、たとえば”サーバー側”モデルが使用されます。SQLiteダイアレクトでは、実際の”クライアント/サーバ”アーキテクチャは使用されませんが、結果行が消費される前にプロセス・メモリの外部に残すバッファなしの結果フェッチ・アプ行チが使用されます。

この基本的なアーキテクチャから、”サーバ側カーソル”は、非常に大きな結果セットをフェッチする場合にメモリ効率が高くなりますが、同時に、クライアント/サーバ通信プロセスがより複雑になり、小さな結果セット(通常は10000行未満)の場合には効率が低くなる可能性があります。

バッファリングされた結果またはバッファリングされていない結果を条件付きでサポートするダイアレクトでは、通常、”バッファリングされていない”、つまりサーバー側カーソル・モードの使用に注意が必要です。たとえば、psycopg2ダイアレクトを使用する場合、サーバー側カーソルが任意の種類のDMLまたはDDL文とともに使用されると、エラーが発生します。サーバー側カーソルでMySQLドライバを使用すると、DBAPI接続はより脆弱な状態になり、エラー状態から正常に回復せず、カーソルが完全に閉じるまで行ルバックを続行できません。

このため、SQLAlchemyのダイアレクトでは、常にデフォルトでエラーが発生しにくいバージョンのカーソルが使用されます。つまり、PostgreSQLおよびMySQLのダイアレクトでは、デフォルトでバッファされた”クライアント側”のカーソルが使用され、カーソルからフェッチメソッドが呼び出される前に、結果の完全なセットがメモリにプルされます。この操作モードは、 大多数 の場合に適しています。バッファされていないカーソルは、一般的には有用ではありません。ただし、アプリケーションがチャンク内の非常に多数の行をフェッチするというまれなケースでは、これらの行の処理が、さらに行がフェッチされる前に完了する可能性があります。

クライアント側およびサーバー側のカーソルオプションを提供するデータベースドライバの場合、 Connection.execution_options.stream_results および Connection.execution_options.yield_per 実行オプションは、 Connection ごとまたはステートメントごとに”サーバー側カーソル”へのアクセスを提供します。同様のオプションは、ORM Session を使用する場合にも存在します。

Streaming with a fixed buffer via yield_per

完全にバッファリングされていないサーバ側カーソルを使用した個々の行フェッチ操作は、通常、一度に行のバッチをフェッチするよりもコストがかかるため、 Connection.execution_options.yield_per 実行オプションは、 Connection またはステートメントが利用可能なサーバ側カーソルを使用するように設定し、同時に、消費された行をバッチでサーバから取得する行の固定サイズのバッファを設定します。このパラメータは、 Connection の Connection.execution_options() メソッド、または Executable.execution_options() メソッドを使用したステートメントで、正の整数値にすることができます。

New in version 1.4.40: Connection.execution_options.yield_per がコアのみのオプションとして追加されました。これはSQLAlchemy 1.4.40で新しくなりました。1.4より前のバージョンでは、 Connection.execution_options.stream_results を Result.yield_per() と直接組み合わせて使用してください。

このオプションを使用することは、次のセクションで説明する Connection.execution_options.stream_results オプションを手動で設定し、指定された整数値を使用して Result オブジェクトで Result.yield_per() メソッドを呼び出すことと同じです。どちらの場合も、この組み合わせには次のような効果があります。

  • 指定されたバックエンドでサーバサイドカーソルモードが選択されている(利用可能で、そのバックエンドのデフォルトの動作になっていない場合)

  • 結果の行が取り出されると、それらはバッチ単位でバッファリングされます。ここで、最後のバッチまでの各バッチのサイズは、 Connection.execution_options.yield_per オプションまたは Result.yield_per() メソッドに渡された整数引数と等しくなります。最後のバッチは、このサイズより小さい残りの行に対してサイズ設定されます。

  • Result.partitions() メソッドで使用されるデフォルトのパーティションサイズも、この整数サイズと等しくなります。

これらの3つの動作を次の例に示します:

with engine.connect() as conn:
    with conn.execution_options(yield_per=100).execute(
        text("select * from table")
    ) as result:
        for partition in result.partitions():
            # partition is an iterable that will be at most 100 items
            for row in partition:
                print(f"{row}")

上の例では 、Result.partitions() メソッドを使用して、サーバから取得したサイズに一致するバッチの行に対して処理を実行することと、 yield_per=100 という組み合わせを示しています。 Result.partitions() の使用はオプションであり、 Result が直接反復される場合、取得された100行ごとに新しいバッチの行がバッファされます。 Result.all() のようなメソッドを呼び出すことは しないでください 。なぜなら、これは残りのすべての行を一度に完全に取得し、 yield_per を使用する目的を損なうからです。

Tip

Result オブジェクトは、上で説明したように、コンテキストマネージャとして使用することができます。サーバ側のカーソルで反復する場合、これは、反復プロセス内で例外が発生した場合でも、 Result オブジェクトが閉じられるようにする最善の方法です。

Connection.execution_options.yield_per オプションはORMにも移植できます。これは Session がORMオブジェクトを取得するために使用し、一度に生成されるORMオブジェクトの量も制限します。ORMで Connection.execution_options.yield_per を使用する背景については、 ORM Querying Guide の Fetching Large Result Sets with Yield Per 節を参照してください。

New in version 1.4.40: Coreレベルの実行オプションとして Connection.execution_options.yield_per が追加されました。これにより、ストリーミング結果、バッファサイズ、およびパーティションサイズを、ORMの同様のユースケースに転送可能な方法で一度に設定できます。

Streaming with a dynamically growing buffer using stream_results

特定のパーティションサイズを持たないサーバ側カーソルを有効にするには、 Connection.execution_options.stream_results オプションを使用します。これは、 Connection.execution_options.yield_per と同様に、 Connection オブジェクトまたはステートメントオブジェクトで呼び出すことができます。

Connection.execution_options.stream_results オプションを使用して配信された Result オブジェクトが直接反復される場合、行はデフォルトのバッファリング方式を使用して内部的にフェッチされます。この方式では、最初に小さな行のセットがバッファリングされ、次に、事前に設定された1,000行の制限まで、各フェッチでより大きなバッファがバッファリングされます。このバッファの最大サイズは、 Connection.execution_options.max_row_buffer 実行オプションを使用して変更できます:

with engine.connect() as conn:
    with conn.execution_options(stream_results=True, max_row_buffer=100).execute(
        text("select * from table")
    ) as result:
        for row in result:
            print(f"{row}")

Connection.execution_options.stream_results オプションは Result.partitions() メソッドの使用と組み合わせることができますが、結果全体がフェッチされないように、特定のパーティションサイズを Result.partitions() に渡す必要があります。通常、 Result.partitions() メソッドを使用するように設定する場合は、 Connection.execution_options.yield_per オプションを使用する方が簡単です。

See also

Fetching Large Result Sets with Yield Per - in the ORM Querying Guide

Result.partitions()

Result.yield_per()

Translation of Schema Names

共通のテーブルセットを複数のスキーマに分散するマルチテナンシアプリケーションをサポートするために、 Connection.execution_options.schema_translate_map 実行オプションを使用して、 Table オブジェクトのセットを変更せずに別のスキーマ名でレンダリングすることができます。

Given a table:

user_table = Table(
    "user",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("name", String(50)),
)

Table.schema 属性で定義されているこの Table の”スキーマは None です。 Connection.execution_options.schema_translate_map では、 None のスキーマを持つすべての Table オブジェクトが、代わりに use_schema_one としてスキーマを描画することを指定できます:

connection = engine.connect().execution_options(
    schema_translate_map={None: "user_schema_one"}
)

result = connection.execute(user_table.select())

上記のコードは、次の形式のデータベースに対してSQLを呼び出します:

SELECT user_schema_one.user.id, user_schema_one.user.name FROM
user_schema_one.user

つまり、スキーマ名は変換された名前に置き換えられます。マップには、任意の数のtarget->destinationスキーマを指定できます:

connection = engine.connect().execution_options(
    schema_translate_map={
        None: "user_schema_one",  # no schema name -> "user_schema_one"
        "special": "special_schema",  # schema="special" becomes "special_schema"
        "public": None,  # Table objects with schema="public" will render with no schema
    }
)

Connection.execution_options.schema_translate_map パラメータは、 Table または Sequence オブジェクトから派生した、SQL式言語から生成されたすべてのDDLおよびSQL構文に影響します。 text() 構文または Connection.execute() に渡されたプレーンな文字列を介して使用されるリテラル文字列SQLには影響 しません 。

この機能は、スキーマの名前が Table または Sequence の名前から直接派生している場合に のみ 有効です。文字列スキーマ名が直接渡されるメソッドには影響しません。このパターンでは、 MetaData.create_all() や drop_all `などのメソッドによって実行される"can create" / "can drop"チェック内で有効になり、 :class:`_schema.Table() オブジェクトを指定してテーブルリフレクションを使用するときに有効になります。ただし、スキーマ名がこれらのメソッドに明示的に渡されるので 、Inspector オブジェクトに存在する操作には 影響しません 。

Tip

ORM Engine のレベルで設定してから、そのエンジンを Session に渡します。 Session はトランザクションごとに新しい Connection を使用します:

schema_engine = engine.execution_options(schema_translate_map={...})

session = Session(schema_engine)

...

Warning

ORM Session を拡張なしで使用する場合、スキーマ変換機能は セッションごとに1つのスキーマ変換マップ としてのみサポートされます。ORM Session は現在のスキーマ変換値を個々のオブジェクトに対して考慮しないため、異なるスキーマ変換マップが文ごとに指定されている場合は 機能しません 。

単一の Session を複数の schema_translate_map 設定で使用するには、 Horizontal Sharding 拡張を使用できます。 Horizontal Sharding の例を参照してください。

SQL Compilation Caching

New in version 1.4: SQLAlchemyに透過的なクエリキャッシュシステムが追加されました。これにより、CoreとORMの両方で、SQL文の構成をSQL文字列に変換する際のPythonの計算オーバーヘッドが大幅に削減されます。 Transparent SQL Compilation Caching added to All DQL, DML Statements in Core, ORM の導入部分を参照してください。

SQLAlchemyには、SQLコンパイラとそのORMバリアントのための包括的なキャッシュシステムが含まれています。このキャッシュシステムは Engine 内で透過的であり、特定のCoreまたはORM SQLステートメントのSQLコンパイルプロセスと、そのステートメントの結果フェッチ機構を組み立てる関連計算が、エンジンの”コンパイルされたキャッシュ”内に特定の構造が残っている間、そのステートメントオブジェクトと同じ構造を持つ他のすべてのオブジェクトに対して一度だけ発生することを提供します。「同じ構造を持つステートメントオブジェクト」とは、一般に、関数内で構築され、その関数が実行されるたびに構築されるSQLステートメントに対応します:

def run_my_statement(connection, parameter):
    stmt = select(table)
    stmt = stmt.where(table.c.col == parameter)
    stmt = stmt.order_by(table.c.id)
    return connection.execute(stmt)

上の文は、 SELECT id, col FROM table WHERE col = :col ORDER BY id のようなSQLを生成します。ただし、 parameter の値は文字列や整数などのプレーンなPythonオブジェクトですが、バインドされたパラメータを使用するため、文字列のSQL形式の文にはこの値が含まれません。上記の run_my_statement() 関数の後続の呼び出しでは、パフォーマンスを向上させるために connection.execute() 呼び出しの範囲内でキャッシュされたコンパイル構文が使用されます。

Note

SQLコンパイルキャッシュがキャッシュしているのは、データベースに渡された SQL文字列のみ であり、 クエリによって返されたデータ ではないことに注意してください。これは決してデータキャッシュではなく、特定のSQL文に対して返された結果に影響を与えず、結果行のフェッチに関連したメモリの使用を意味しません。

SQLAlchemyは初期の1.xシリーズから初歩的なステートメントキャッシュを持っており、さらにORMのための”Baked Query”拡張を特徴としているが、これらのシステムは両方とも、キャッシュを効果的にするために高度な特別なAPI使用を必要とした。1.4年の新しいキャッシュは完全に自動的であり、効果的にするためにプログラミングスタイルを変更する必要はない。

キャッシュは構成を変更せずに自動的に使用されるため、キャッシュを有効にするための特別な手順は必要ありません。次のセクションでは、キャッシュの構成と高度な使用パターンについて詳しく説明します。

Configuration

キャッシュ自体は、 LRUCache と呼ばれる辞書のようなオブジェクトです。これは、特定のキーの使用を追跡し、キャッシュのサイズが特定のしきい値に達したときに最も使用されていない項目を削除する定期的な”プルーニング”ステップを特徴とする内部SQLAlchemy辞書サブクラスです。このキャッシュのサイズはデフォルトで500で、 create_engine.query_cache_size パラメータを使用して設定できます:

engine = create_engine(
    "postgresql+psycopg2://scott:tiger@localhost/test", query_cache_size=1200
)

キャッシュのサイズは、ターゲットサイズに縮小される前に、指定されたサイズの150%の係数になるように大きくすることができます。したがって、サイズ1200以上のキャッシュは、サイズが1800要素になるように大きくすることができ、その時点で1200に縮小されます。

Estimating Cache Performance Using Logging

上記のキャッシュサイズ1200は、実際にはかなり大きくなります。小規模なアプリケーションでは、100のサイズで十分です。キャッシュの最適なサイズを推定するには、ターゲットホストに十分なメモリが存在すると仮定して、キャッシュのサイズは、使用中のターゲットエンジンに対してレンダリングされる一意のSQL文字列の数に基づく必要があります。これを確認する最も便利な方法は、SQLエコーを使用することです。これは、 create_engine.echo フラグを使用するか、Pythonロギングを使用することによって、最も直接的に有効になります。ロギング設定の背景については、 Configuring Logging のセクションを参照してください。

例として、次のプログラムによって生成されたログを調べます:

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session

Base = declarative_base()


class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    data = Column(String)
    bs = relationship("B")


class B(Base):
    __tablename__ = "b"
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey("a.id"))
    data = Column(String)


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

s.add_all([A(bs=[B(), B(), B()]), A(bs=[B(), B(), B()]), A(bs=[B(), B(), B()])])
s.commit()

for a_rec in s.scalars(select(A)):
    print(a_rec.bs)

実行されると、ログに記録される各SQL文には、渡されたパラメータの左側に括弧で囲まれたキャッシュ統計バッジが含まれます。表示される可能性のある4つのタイプのメッセージは、次のように要約されます。

  • [raw sql] - ドライバまたはエンドユーザが Connection.exec_driver_sql() を使用して生のSQLを発行しました - キャッシュは適用されません

  • [no key] - 文オブジェクトがキャッシュされていないDDL文であるか、ユーザ定義の構文や任意の大きさのVALUES句など、キャッシュできない要素が文オブジェクトに含まれています。

  • [generated in Xs] - この文は キャッシュミス であり、コンパイルされてからキャッシュに格納される必要がありました。コンパイルされた構文を生成するのにX秒かかりました。数値Xは小数点以下の秒数になります。

  • [cached since Xs ago] - 文は キャッシュヒット であり、再コンパイルする必要はありませんでした。文はX秒前からキャッシュに保存されています。Xという数字は、アプリケーションが実行されていた時間と文がキャッシュされていた時間に比例します。たとえば、24時間の場合は86400になります。

各バッジについては、以下で詳しく説明します。

上記のプログラムで最初に表示される文は、”a”テーブルと”b”テーブルの存在をチェックするSQLiteダイアレクトです:

INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("a")
INFO sqlalchemy.engine.Engine [raw sql] ()
INFO sqlalchemy.engine.Engine PRAGMA main.table_info("b")
INFO sqlalchemy.engine.Engine [raw sql] ()

上記の2つのSQLite PRAGMA文の場合、バッジには [raw sql] と表示されます。これは、ドライバが Connection.exec_driver_sql() を使用してPython文字列を直接データベースに送信していることを示します。このような文は既に文字列形式で存在するため、キャッシュは適用されません。また、SQLAlchemyは事前にSQL文字列を解析しないため、どのような種類の結果行が返されるかについては何もわかりません。

次の文は、CREATE TABLE文です:

INFO sqlalchemy.engine.Engine
CREATE TABLE a (
  id INTEGER NOT NULL,
  data VARCHAR,
  PRIMARY KEY (id)
)

INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
INFO sqlalchemy.engine.Engine
CREATE TABLE b (
  id INTEGER NOT NULL,
  a_id INTEGER,
  data VARCHAR,
  PRIMARY KEY (id),
  FOREIGN KEY(a_id) REFERENCES a (id)
)

INFO sqlalchemy.engine.Engine [no key 0.00006s] ()

これらの文のそれぞれに対して、バッジには [no key 0.00006s] と書かれています。これは、DDL指向の CreateTable 構文がキャッシュキーを生成しなかったために、これら2つの特定の文のキャッシュが行われなかったことを示しています。DDL構文は通常、2回目の繰り返しの対象にならないため、キャッシュには関与しません。また、DDLはパフォーマンスがそれほど重要ではないデータベース構成ステップでもあるためです。

[no key] バッジが重要な理由はもう1つあります。これは、現在キャッシュできない特定のサブ構文を除いて、キャッシュできるSQL文に対して生成される可能性があるためです。この例としては、キャッシュパラメータを定義しないカスタムのユーザ定義SQL要素や、任意の長さで再現性のないSQL文字列を生成するいくつかの構文があります。主な例としては、 Values 構文や、 Insert.values() メソッドで「複数値の挿入」を使用する場合などです。

これまでのところ、キャッシュはまだ空です。次の文はキャッシュされますが、セグメントは次のようになります。

INFO sqlalchemy.engine.Engine INSERT INTO a (data) VALUES (?)
INFO sqlalchemy.engine.Engine [generated in 0.00011s] (None,)
INFO sqlalchemy.engine.Engine INSERT INTO a (data) VALUES (?)
INFO sqlalchemy.engine.Engine [cached since 0.0003533s ago] (None,)
INFO sqlalchemy.engine.Engine INSERT INTO a (data) VALUES (?)
INFO sqlalchemy.engine.Engine [cached since 0.0005326s ago] (None,)
INFO sqlalchemy.engine.Engine INSERT INTO b (a_id, data) VALUES (?, ?)
INFO sqlalchemy.engine.Engine [generated in 0.00010s] (1, None)
INFO sqlalchemy.engine.Engine INSERT INTO b (a_id, data) VALUES (?, ?)
INFO sqlalchemy.engine.Engine [cached since 0.0003232s ago] (1, None)
INFO sqlalchemy.engine.Engine INSERT INTO b (a_id, data) VALUES (?, ?)
INFO sqlalchemy.engine.Engine [cached since 0.0004887s ago] (1, None)

上記の例では、基本的に2つのユニークなSQL文字列、すなわち "INSERT INTO a(data) VALUES(?)" と "INSERT INTO b (a_id, data) VALUES(?,?)" があります。SQLAlchemyは全てのリテラル値にバウンドパラメータを使用するので、これらの文が異なるオブジェクトに対して何度も繰り返されても、パラメータが分離されているので、実際のSQL文字列は同じままです。

Note

上記の2つのステートメントは、ORMの作業単位プロセスによって生成され、実際には、各マッパーに対して行カルな個別のキャッシュにこれらをキャッシュします。ただし、仕組みと用語は同じです。以下のセクション Disabling or using an alternate dictionary to cache some (or all) statements では、ユーザー向けコードがステートメントごとに代替キャッシュコンテナを使用する方法について説明します。

これらの2つの文の最初の出現に対して表示されるキャッシュバッジは、 [generated in 0.00011s] です。これは、文が キャッシュ内になく、.00011sで文字列にコンパイルされ、その後キャッシュされた ことを示します。 [generated] バッジを見ると、これは キャッシュミス があったことを意味していることがわかります。これは、特定の文の最初の出現に対して予想されることです。しかし、一般的に同じ一連のSQL文を何度も使用している長時間実行されるアプリケーションに対して、多くの新しい [generated] バッジが観察される場合、これは create_engine.query_cache_size パラメータが小さすぎることを示している可能性があります。キャッシュされた文が、LRUキャッシュの使用頻度の低い項目のプルーニングによりキャッシュから削除されると、次に使用されるときに [generated] バッジが表示されます。

これら2つの文のそれぞれの後続の出現に対して表示されるキャッシュバッジは、 [cached since 0.0003533s ago] のようになります。これは、文 がキャッシュ内で検出され、元々キャッシュ内に0003533秒前に置かれていたことを示します 。 [generated] バッジと [cached since] バッジは秒数を表しますが、意味は異なります。 [generated] バッジの場合、秒数は文のコンパイルにかかった時間の大まかなタイミングであり、非常に短い時間になります。 [cached since] の場合、これは文がキャッシュ内に存在していた合計時間です。6時間実行されているアプリケーションの場合、この数値は [cached since 21600 seconds ago] と表示されます。これは良いことです。”cached since”の値が大きい場合は、これらの文が長い間キャッシュ・ミスの対象になっていないことを示しています。アプリケーションが長時間実行されていても、”cached since”の数が少ない文は、これらの文がキャッシュミスを頻繁に起こし、 create_engine.query_cache_size を増やす必要があることを示している可能性があります。

次に、この例のプログラムでは、”a”テーブルのSELECTと、それに続く”b”テーブルの遅延行ドに対して、”generated”と”cached”の同じパターンが見られるいくつかのSELECTを実行します:

INFO sqlalchemy.engine.Engine SELECT a.id AS a_id, a.data AS a_data
FROM a
INFO sqlalchemy.engine.Engine [generated in 0.00009s] ()
INFO sqlalchemy.engine.Engine SELECT b.id AS b_id, b.a_id AS b_a_id, b.data AS b_data
FROM b
WHERE ? = b.a_id
INFO sqlalchemy.engine.Engine [generated in 0.00010s] (1,)
INFO sqlalchemy.engine.Engine SELECT b.id AS b_id, b.a_id AS b_a_id, b.data AS b_data
FROM b
WHERE ? = b.a_id
INFO sqlalchemy.engine.Engine [cached since 0.0005922s ago] (2,)
INFO sqlalchemy.engine.Engine SELECT b.id AS b_id, b.a_id AS b_a_id, b.data AS b_data
FROM b
WHERE ? = b.a_id

上記のプログラムから、完全な実行では、合計4つの異なるSQL文字列がキャッシュされていることが示されます。これは、 4 のキャッシュ・サイズで十分であることを示しています。これは明らかに非常に小さいサイズであり、デフォルトのサイズ500はデフォルトのままで問題ありません。

How much memory does the cache use?

前のセクションでは、 create_engine.query_cache_size を大きくする必要があるかどうかをチェックするいくつかのテクニックについて詳しく説明しました。キャッシュが大きすぎないかどうかはどうすればわかりますか? create_engine.query_cache_size を特定の数より大きくしないように設定したい理由は、検索UXからオンザフライでクエリを構築するアプリケーションなど、非常に多数の異なるステートメントを使用する可能性のあるアプリケーションがあるためです。また、たとえば、過去24時間に10万の異なるクエリが実行され、それらがすべてキャッシュされた場合に、ホストのメモリが不足しないようにするためです。

Pythonのデータ構造がどれだけのメモリを占有しているかを測定するのは非常に困難ですが、 top を介してメモリの増加を測定するプロセスを使用すると、一連の250の新しいステートメントがキャッシュに追加されるため、中程度のCoreステートメントは約12Kを消費しますが、小さなORMステートメントは約20Kを消費します。これには、ORMにとってはるかに大きな結果フェッチ構造も含まれます。

Disabling or using an alternate dictionary to cache some (or all) statements

使用される内部キャッシュは LRUCache として知られていますが、これはほとんど単なる辞書です。 Connection.execution_options.compiled_cache オプションを実行オプションとして使用することで、任意の辞書を任意の一連の文のキャッシュとして使用できます。実行オプションは、ORM Session.execute() メソッドをSQLAlchemy-2.0スタイルの呼び出しに使用する場合と同様に、文、 Engine または Connection に設定できます。たとえば、一連のSQL文を実行し、それらを特定の辞書にキャッシュするには:

my_cache = {}
with engine.connect().execution_options(compiled_cache=my_cache) as conn:
    conn.execute(table.select())

SQLAlchemy ORMは上記のテクニックを使用して、 Engine で設定されたデフォルトキャッシュとは別の、作業単位の”フラッシュ”プロセス内のマッパーごとのキャッシュを保持します。

この引数に None を指定してキャッシュを無効にすることもできます:

# disable caching for this connection
with engine.connect().execution_options(compiled_cache=None) as conn:
    conn.execute(table.select())

Caching for Third Party Dialects

キャッシュ機能では、そのSQL文字列にキー付けされた特定のキャッシュキーが与えられた場合、多くの文の呼び出しで安全に再利用できるSQL文字列をダイアレクトのコンパイラが生成する必要があります。これは、SELECTのLIMIT/OFFSET値など、文の中のリテラル値は、コンパイルされた文字列が再利用できないため、ダイアレクトのコンパイルスキームでハードコードできないことを意味します。SQLAlchemyは、 BindParameter.render_literal_execute() メソッドを使用してレンダリングされたバインドパラメータをサポートしています。このメソッドは、カスタムコンパイラによって既存の Select._limit_clause および Select._offset_clause 属性に適用できます。これらについては、このセクションの後半で説明します。

多くのサードパーティのダイアレクトがあり、その多くは新しい”リテラル実行”機能の恩恵を受けずにSQL文からリテラル値を生成している可能性があるため、バージョン1.4.5のSQLAlchemyはダイアレクトに Dialect.supports_statement_cache として知られる属性を追加しました。この属性は、すでにスーパークラスに存在している場合でも、実行時に特定のダイアレクトのクラスに直接存在するかどうかがチェックされます。そのため、既存のキャッシュ可能なSQLAlchemyダイアレクトをサブクラス化しているサードパーティのダイアレクト(例えば``SQLAlchemy.dialects.postgresql.PGDialect``)であっても、キャッシュを有効にするためにはこの属性を明示的に含める必要があります。この属性は、ダイアレクトが必要に応じて変更され、異なるパラメータでコンパイルされたSQL文の再利用性がテストされた場合にのみ有効にする必要があります。

この属性をサポートしていないすべてのサードパーティのダイアレクトでは、そのようなダイアレクトのロギングは dialect does not support caching と表示されます。

ダイアレクトがキャッシュに対してテストされ、特にSQLコンパイラがSQL文字列内のリテラルLIMIT/OFFSETを直接レンダリングしないように更新された場合、ダイアレクトの作成者は次のように属性を適用できます:

from sqlalchemy.engine.default import DefaultDialect

class MyDialect(DefaultDialect):
    supports_statement_cache = True

このフラグは、ダイアレクトのすべてのサブクラスにも適用する必要があります:

class MyDBAPIForMyDialect(MyDialect):
    supports_statement_cache = True

New in version 1.4.5: Dialect.supports_statement_cache 属性を追加しました。

The typical case for dialect modification follows.

特殊なケースの修正の典型的な例を次に示します。

Example: Rendering LIMIT / OFFSET with post compile parameters

例として、ある書き方が SQLCompiler.limit_clause() メソッドをオーバーライドし、次のようにSQL文の”LIMIT / OFFSET”句を生成するとします:

# pre 1.4 style code
def limit_clause(self, select, **kw):
    text = ""
    if select._limit is not None:
        text += " \n LIMIT %d" % (select._limit,)
    if select._offset is not None:
        text += " \n OFFSET %d" % (select._offset,)
    return text

上記のルーチンは、 Select._limit および Select._offset の整数値をSQL文に埋め込まれたリテラル整数としてレンダリングします。これは、SELECT文のLIMIT/OFFSET句内でのバウンドパラメータの使用をサポートしていないデータベースの一般的な要件です。しかし、初期コンパイル段階で整数値をレンダリングすることは、キャッシュと直接 互換性がありません 。 Select オブジェクトの制限整数値とオフセット整数値はキャッシュキーの一部ではないため、異なる制限/オフセット値を持つ多くの Select 文は正しい値でレンダリングされません。

上記のコードを修正するには、リテラル整数をSQLAlchemyの post-compile 機能に移動します。これにより、リテラル整数は最初のコンパイル段階の外でレンダリングされますが、実行時には文がDBAPIに送信される前にレンダリングされます。これには、コンパイル段階で BindParameter.render_literal_execute() メソッドを使用し、同時に Select._limit_clause および Select._offset_clause 属性を使用してアクセスします。これらの属性は、次のようにLIMIT/OFFSETを完全なSQL式として表します:

# 1.4 cache-compatible code
def limit_clause(self, select, **kw):
    text = ""

    limit_clause = select._limit_clause
    offset_clause = select._offset_clause

    if select._simple_int_clause(limit_clause):
        text += " \n LIMIT %s" % (
            self.process(limit_clause.render_literal_execute(), **kw)
        )
    elif limit_clause is not None:
        # assuming the DB doesn't support SQL expressions for LIMIT.
        # Otherwise render here normally
        raise exc.CompileError(
            "dialect 'mydialect' can only render simple integers for LIMIT"
        )
    if select._simple_int_clause(offset_clause):
        text += " \n OFFSET %s" % (
            self.process(offset_clause.render_literal_execute(), **kw)
        )
    elif offset_clause is not None:
        # assuming the DB doesn't support SQL expressions for OFFSET.
        # Otherwise render here normally
        raise exc.CompileError(
            "dialect 'mydialect' can only render simple integers for OFFSET"
        )

    return text

上記の方法では、次のようなコンパイル済みのSELECT文が生成されます。

SELECT x FROM y
LIMIT __[POSTCOMPILE_param_1]
OFFSET __[POSTCOMPILE_param_2]

上記の場合、 __[POSTCOMPILE_param_1] および __[POSTCOMPILE_param_2] インジケータには、SQL文字列がキャッシュから取得された後、文の実行時に対応する整数値が設定されます。

上記のような変更が適切に行われた後、 Dialect.supports_statement_cache フラグを True に設定する必要があります。サードパーティのダイアレクトでは、 dialect third party test suite を使用することを強くお勧めします。これは、LIMIT/OFFSETを使用したSELECTのような操作が正しくレンダリングされ、キャッシュされることを主張します。

See also

Why is my application slow after upgrading to 1.4 and/or 2.x? - in the Frequently Asked Questions section

Using Lambdas to add significant speed gains to statement production

Deep Alchemy

このテクニックは、非常にパフォーマンス集約的なシナリオを除いて、一般的に必須ではなく、経験豊富なPythonプログラマを対象としています。かなり単純ですが、初心者のPython開発者には適していないメタプログラミングの概念を含んでいます。lambdaアプ行チは、最小限の労力で、後で既存のコードに適用できます。

一般的にlambdaとして表現されるPython関数は、lambda関数自体のPythonコードの場所とlambda内のク行ジャー変数に基づいてキャッシュ可能なSQL式を生成するために使用できます。その理論的根拠は、lambdaシステムが使用されていない場合のSQLAlchemyの通常の動作のように、SQL文字列でコンパイルされた形式のSQL式構文だけでなく、SQL式構文自体のPython内での構成もキャッシュできるようにすることであり、これにもある程度のPythonオーバーヘッドがあります。

lambda SQL式機能は、パフォーマンスを向上させる機能として使用できます。また、一般的なSQLフラグメントを提供するために、 with_loader_criteria() ORMオプションでもオプションで使用されます。

Synopsis

Lambdaステートメントは lambda_stmt() 関数を使用して構築されます。この関数は StatementLambdaElement のインスタンスを返します。これ自体が実行可能なステートメント構造です。追加の修飾子と条件は、Pythonの加算演算子 + 、またはより多くのオプションを可能にする StatementLambdaElement.add_criteria() メソッドを使用してオブジェクトに追加されます。

lambda_stmt() 構文は、アプリケーション内で何度も使用されることが予想される包含関数またはメソッド内で呼び出されることを想定しています。そのため、最初の実行以降の後続の実行では、キャッシュされているコンパイル済みSQLを利用できます。Pythonの包含関数内でlambdaが構築されると、ク行ジャー変数も必要になります。これは、アプ行チ全体にとって重要です:

from sqlalchemy import lambda_stmt


def run_my_statement(connection, parameter):
    stmt = lambda_stmt(lambda: select(table))
    stmt += lambda s: s.where(table.c.col == parameter)
    stmt += lambda s: s.order_by(table.c.id)

    return connection.execute(stmt)


with engine.connect() as conn:
    result = run_my_statement(some_connection, "some parameter")

上の例では、SELECT文の構造を定義するために使用される3つの lambda 呼び出し可能オブジェクトは1回だけ呼び出され、結果のSQL文字列はエンジンのコンパイルキャッシュにキャッシュされます。その後、 run_my_statement() 関数は何度でも呼び出すことができ、その中の lambda 呼び出し可能オブジェクトは呼び出されず、すでにコンパイルされたSQLを取得するためのキャッシュキーとしてのみ使用されます。

Note

lambdaシステムが使用されていない場合は、すでにSQLキャッシュが配置されていることに注意してください。lambdaシステムは、SQL構文自体の構築をキャッシュし、より単純なキャッシュキーを使用することによって、呼び出されるSQLステートメントごとに作業削減の追加レイヤーを追加するだけです。

Quick Guidelines for Lambdas

何よりも、lambda SQLシステム内で強調されているのは、lambdaに対して生成されたキャッシュキーと、それが生成するSQL文字列との間に不一致がないことを保証することです。 LambdaElement および関連するオブジェクトは、指定されたlambdaを実行して分析し、各実行時にどのようにキャッシュされるべきかを計算し、潜在的な問題を検出しようとします。基本的なガイドラインは次のとおりです。

  • あらゆる種類の文がサポートされています - select() 構文が lambda_stmt() の主要なユースケースであることが期待されていますが、 insert() や update() などのDML文も同じように使用できます:

    def upd(id_, newname):
    stmt = lambda_stmt(lambda: users.update())
    stmt += lambda s: s.values(name=newname)
    stmt += lambda s: s.where(users.c.id == id_)
    return stmt


with engine.begin() as conn:
    conn.execute(upd(7, "foo"))

  • ORMユースケースも直接サポートされています - lambda_stmt() はORM機能に完全に対応でき、 Session.execute() で直接使用されます:

    def select_user(session, name):
    stmt = lambda_stmt(lambda: select(User))
    stmt += lambda s: s.where(User.name == name)

    row = session.execute(stmt).first()
    return row

  • バウンドパラメータは自動的に調整されます - SQLAlchemyの以前の”baked query”システムとは対照的に、lambda SQLシステムは自動的にSQLバウンドパラメータになるPythonリテラル値に対応します。これは、特定のlambdaが1回しか実行されない場合でも、バウンドパラメータになる値は実行のたびにlambdaの ク行ジャー から抽出されることを意味します。

    >>> def my_stmt(x, y):
...     stmt = lambda_stmt(lambda: select(func.max(x, y)))
...     return stmt
>>> engine = create_engine("sqlite://", echo=True)
>>> with engine.connect() as conn:
...     print(conn.scalar(my_stmt(5, 10)))
...     print(conn.scalar(my_stmt(12, 8)))

    SELECT max(?, ?) AS max_1
[generated in 0.00057s] (5, 10)

    10

    SELECT max(?, ?) AS max_1
[cached since 0.002059s ago] (12, 8)

    12

  • lambdaは理想的には全ての場合で同一のSQL構造を生成するべきです - 入力に基づいて異なるSQLを生成する可能性のある条件やカスタム呼び出し可能をlambda内で使用することは避けてください;関数が条件付きで2つの異なるSQLフラグメントを使用する可能性がある場合は、2つの別々のlambdaを使用してください:

    # **Don't** do this:


def my_stmt(parameter, thing=False):
    stmt = lambda_stmt(lambda: select(table))
    stmt += lambda s: (
        s.where(table.c.x > parameter) if thing else s.where(table.c.y == parameter)
    )
    return stmt


# **Do** do this:


def my_stmt(parameter, thing=False):
    stmt = lambda_stmt(lambda: select(table))
    if thing:
        stmt += lambda s: s.where(table.c.x > parameter)
    else:
        stmt += lambda s: s.where(table.c.y == parameter)
    return stmt

    lambdaが一貫性のあるSQL構文を生成しない場合に発生する可能性のあるさまざまな障害があり、現時点では簡単に検出できないものもあります。

  • バインド値を生成するためにlambda内の関数を使用しないでください - バインド値を追跡するアプ行チでは、SQL文で使用される実際の値がlambdaのク行ジャー内に行カルに存在する必要があります。値が他の関数から生成された場合、これは不可能であり、 LambdaElement は通常、これを実行しようとするとエラーを発生します:

    >>> def my_stmt(x, y):
...     def get_x():
...         return x
...
...     def get_y():
...         return y
...
...     stmt = lambda_stmt(lambda: select(func.max(get_x(), get_y())))
...     return stmt
>>> with engine.connect() as conn:
...     print(conn.scalar(my_stmt(5, 10)))
Traceback (most recent call last):
  # ...
sqlalchemy.exc.InvalidRequestError: Can't invoke Python callable get_x()
inside of lambda expression argument at
<code object <lambda> at 0x7fed15f350e0, file "<stdin>", line 6>;
lambda SQL constructs should not invoke functions from closure variables
to produce literal values since the lambda SQL system normally extracts
bound values without actually invoking the lambda or any functions within it.

  • デフォルトではキャッシュ可能ではないため、lambda内の非SQL構成体を参照することは避けてください - この問題は、 LambdaElement がステートメント内の他のク行ジャー変数からキャッシュキーを作成する方法を参照しています。正確なキャッシュキーの最善の保証を提供するために、lambdaのク行ジャー内にあるすべてのオブジェクトは重要であると見なされ、デフォルトではキャッシュキーに適切であると見なされるものはありません。そのため、次の例でもかなり詳細なエラーメッセージが表示されます:

    >>> class Foo:
...     def __init__(self, x, y):
...         self.x = x
...         self.y = y
>>> def my_stmt(foo):
...     stmt = lambda_stmt(lambda: select(func.max(foo.x, foo.y)))
...     return stmt
>>> with engine.connect() as conn:
...     print(conn.scalar(my_stmt(Foo(5, 10))))
Traceback (most recent call last):
  # ...
sqlalchemy.exc.InvalidRequestError: Closure variable named 'foo' inside of
lambda callable <code object <lambda> at 0x7fed15f35450, file
"<stdin>", line 2> does not refer to a cacheable SQL element, and also
does not appear to be serving as a SQL literal bound value based on the
default SQL expression returned by the function.  This variable needs to
remain outside the scope of a SQL-generating lambda so that a proper cache
key may be generated from the lambda's state.  Evaluate this variable
outside of the lambda, set track_on=[<elements>] to explicitly select
closure elements to track, or set track_closure_variables=False to exclude
closure variables from being part of the cache key.

Cache Key Generation

lambda SQL構文で発生するいくつかのオプションと動作を理解するには、キャッシュシステムの理解が役立ちます。

SQLAlchemyのキャッシュシステムは通常、与えられたSQL式の構成体から、その構成体の中のすべての状態を表す構造体を生成して、キャッシュキーを生成します:

>>> from sqlalchemy import select, column
>>> stmt = select(column("q"))
>>> cache_key = stmt._generate_cache_key()
>>> print(cache_key)  # somewhat paraphrased
CacheKey(key=(
  '0',
  <class 'sqlalchemy.sql.selectable.Select'>,
  '_raw_columns',
  (
    (
      '1',
      <class 'sqlalchemy.sql.elements.ColumnClause'>,
      'name',
      'q',
      'type',
      (
        <class 'sqlalchemy.sql.sqltypes.NullType'>,
      ),
    ),
  ),
  # a few more elements are here, and many more for a more
  # complicated SELECT statement
),)

上記のキーは、本質的には辞書であるキャッシュに格納され、値は、とりわけSQL文の文字列形式(この場合は”SELECT q”というフレーズ)を格納する構造体です。非常に短いクエリであっても、キャッシュキーは、レンダリングされ、実行される可能性のあるものに関して変化する可能性のあるすべてを表現しなければならないため、非常に冗長であることがわかります。

対照的に、lambda構築システムは別の種類のキャッシュキーを作成します。

>>> from sqlalchemy import lambda_stmt
>>> stmt = lambda_stmt(lambda: select(column("q")))
>>> cache_key = stmt._generate_cache_key()
>>> print(cache_key)
CacheKey(key=(
  <code object <lambda> at 0x7fed1617c710, file "<stdin>", line 1>,
  <class 'sqlalchemy.sql.lambdas.StatementLambdaElement'>,
),)

上記では、非lambda文のキャッシュキーよりも大幅に短いキャッシュキーが表示されています。さらに、 select(column("q")) 構文自体の生成は必要ありませんでした。Pythonのlambda自体には、アプリケーションの実行時に不変で永続的なPythonコードオブジェクトを参照する __code__ という属性が含まれています。

lambdaにク行ジャー変数も含まれている場合、これらの変数が列オブジェクトなどのSQL構成体を参照する通常のケースでは、これらはキャッシュキーの一部になります。また、バインドされるパラメータとなるリテラル値を参照する場合は、キャッシュキーの別の要素に配置されます:

>>> def my_stmt(parameter):
...     col = column("q")
...     stmt = lambda_stmt(lambda: select(col))
...     stmt += lambda s: s.where(col == parameter)
...     return stmt

上の StatementLambdaElement には2つのlambdaが含まれていて、どちらもク行ジャー変数の col を参照しているので、キャッシュキーはこれらのセグメントと column() オブジェクトの両方を表します:

>>> stmt = my_stmt(5)
>>> key = stmt._generate_cache_key()
>>> print(key)
CacheKey(key=(
  <code object <lambda> at 0x7f07323c50e0, file "<stdin>", line 3>,
  (
    '0',
    <class 'sqlalchemy.sql.elements.ColumnClause'>,
    'name',
    'q',
    'type',
    (
      <class 'sqlalchemy.sql.sqltypes.NullType'>,
    ),
  ),
  <code object <lambda> at 0x7f07323c5190, file "<stdin>", line 4>,
  <class 'sqlalchemy.sql.lambdas.LinkedLambdaElement'>,
  (
    '0',
    <class 'sqlalchemy.sql.elements.ColumnClause'>,
    'name',
    'q',
    'type',
    (
      <class 'sqlalchemy.sql.sqltypes.NullType'>,
    ),
  ),
  (
    '0',
    <class 'sqlalchemy.sql.elements.ColumnClause'>,
    'name',
    'q',
    'type',
    (
      <class 'sqlalchemy.sql.sqltypes.NullType'>,
    ),
  ),
),)

キャッシュキーの2番目の部分は、文が呼び出されたときに使用されるバウンドパラメータを取得しています。

>>> key.bindparams
[BindParameter('%(139668884281280 parameter)s', 5, type_=Integer())]

“lambda” キャッシングとパフォーマンス比較の一連の例については、 Performance パフォーマンス例内の”short_selects”テストスイートを参照してください。

“Insert Many Values” Behavior for INSERT statements

New in version 2.0: サンプルパフォーマンステストを含む変更の背景については Optimized ORM bulk insert now implemented for all backends other than MySQL を参照してください

Tip

insertmanyvalues 機能は、必要に応じて実行するためにエンドユーザの介入を必要としない、 透過的に利用可能な パフォーマンス機能です。このセクションでは、この機能のアーキテクチャと、特にORMで使用されるバルクインサートステートメントの速度を最適化するために、パフォーマンスを測定し、動作を調整する方法について説明します。

より多くのデータベースがINSERT..RETURNINGのサポートを追加するにつれて、SQLAlchemyは、サーバが生成した値を取得する必要があるINSERT文の主題へのアプ行チにおいて大きな変化を経験しました。最も重要なのは、新しい行を後続の操作で参照できるようにするサーバが生成したプライマリ・キー値です。特に、このシナリオは、 identity map を正しく生成するためにサーバが生成したプライマリ・キー値を取得できることに依存しているORMでは、長い間大きなパフォーマンスの問題でした。

SQLiteとMariaDBに最近追加されたRETURNINGのサポートにより、SQLAlchemyは、ほとんどのバックエンドで DBAPI によって提供される単一行のみの cursor.lastrowid 属性に依存する必要がなくなりました。RETURNINGは、MySQLを除くすべての SQLAlchemy-included バックエンドで使用できるようになりました。 cursor.executemany() DBAPIメソッドが行のフェッチを許可しないという残りのパフォーマンス制限は、ほとんどのバックエンドで解決されます。これは、 executemany() の使用をやめ、代わりに個々のINSERT文を再構築して、それぞれが cursor.execute() を使用して呼び出される1つの文に多数の行を収容するようにすることで解決されます。このアプ行チは、SQLAlchemyが最近のリリースシリーズで徐々にサポートを追加した、 psycopg2 DBAPIの psycopg2 fast execution helpers 機能に由来しています。

Current Support

この機能は、RETURNINGをサポートするSQLAlchemyに含まれるすべてのバックエンドで有効になります。ただし、cx_OracleドライバとOracleDBドライバの両方が独自の同等機能を提供しているOracleの場合は例外です。この機能は通常、 returning メソッドを executemany 実行と組み合わせて使用するときに行われます。この実行は、辞書のリストを Connection.execute() または Session.execute() メソッドの Connection.execute.parameters パラメータに渡すときに行われます( asyncio の同等のメソッドや Session.scalars() のようなショートハンドメソッドも同様です)。また、ORM unit of work プロセス内で、 Session.add_all() などのメソッドを使用して行を追加するときにも行われます。

SQLAlchemyに含まれるダイアレクトのサポートまたは同等のサポートは、現在次のとおりです:

  • SQLite - SQLiteバージョン3.35以上でサポートされます。

  • PostgreSQL - サポートされているすべてのPostgreSQLバージョン(9以降)

  • SQL Server- サポートされているすべてのSQL Serverバージョン[#]_

  • MariaDB - MariaDBバージョン10.5以上でサポート

  • MySQL - サポートされていません。RETURNING機能はありません。

  • Oracle - サポートされているすべてのOracleバージョン9以上で、複数行のOUTパラメータを使用して、ネイティブcx_Oracle/OracleDB APIを使用したexecutemanyでのRETURNINGをサポートします。これは”executemanyvalues”と同じ実装ではありませんが、同じ使用パターンと同等のパフォーマンス上の利点があります。

Changed in version 2.0.10:

Disabling the feature

Engine 全体で、指定されたバックエンドの”insertmanyvalues”機能を無効にするには、 create_engine.use_insertmanyvalues パラメータを False として create_engine() に渡します:

engine = create_engine(
    "mariadb+mariadbconnector://scott:tiger@host/db", use_insertmanyvalues=False
)

Table.implicit_returning パラメータを False として渡すことで、この機能が特定の Table オブジェクトに対して暗黙的に使用されないようにすることもできます:

t = Table(
    "t",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("x", Integer),
    implicit_returning=False,
)

特定のテーブルに対してRETURNINGを無効にしたい理由は、バックエンド固有の制限を回避するためです。

Batched Mode Operation

この機能には2つの操作モードがあり、方言ごと、 Table ごとに透過的に選択されます。1つは バッチモード で、次の形式のINSERT文を書き換えることで、データベースのラウンドトリップ回数を減らします:

INSERT INTO a (data, x, y) VALUES (%(data)s, %(x)s, %(y)s) RETURNING a.id

次のような”バッチ”形式に変換します:

INSERT INTO a (data, x, y) VALUES
    (%(data_0)s, %(x_0)s, %(y_0)s),
    (%(data_1)s, %(x_1)s, %(y_1)s),
    (%(data_2)s, %(x_2)s, %(y_2)s),
    ...
    (%(data_78)s, %(x_78)s, %(y_78)s)
RETURNING a.id

上記の場合、文は入力データのサブセット(“バッチ”)に対して編成され、そのサイズはデータベースバックエンドによって決定されます。また、各バッチ内のパラメータの数は、文のサイズ/パラメータ数の既知の制限に対応します。次に、この機能は、すべてのレコードが消費されるまで、入力データの各バッチに対して1回INSERT文を実行し、各バッチのRETURNING結果を、単一の Result オブジェクトから利用可能な単一の大きな行セットに連結します。

この”バッチ”形式を使用すると、より少ないデータベース・ラウンドトリップで多数の行のINSERTが可能になり、サポートされているほとんどのバックエンドでパフォーマンスが大幅に向上することが示されています。

Correlating RETURNING rows to parameter sets

New in version 2.0.10.

前のセクションで説明した”バッチ”モードのクエリは、返されるレコードの順序が入力データの順序と一致することを保証しません。SQLAlchemy ORM unit of work プロセスや、返されたサーバ生成の値を入力データと関連付けるアプリケーションで使用される場合、 Insert.returning() および UpdateBase.return_defaults() メソッドには、オプション Insert.returning.sort_by_parameter_order が含まれます。これは、”insertmanyvalues”モードがこの対応を保証することを示します。これは、データベースバックエンドによって実際にレコードが挿入される順序とは 関係ありません 。これは、いかなる状況においても 想定されていません 。返されたレコードは、元の入力データが渡された順序に対応するように、受信時に整理される必要があるだけです。

Insert.returning.sort_by_parameter_order パラメータが存在する場合、サーバが生成した整数のプライマリキー値を使用するテーブル(例えば、 IDENTITY 、PostgreSQLの SERIAL 、MariaDBの AUTO_INCREMENT 、SQLiteの ROWID スキームなど)では、”バッチ”モードは、より複雑なINSERT.RETURNING形式を、戻り値に基づく実行後の行のソートと組み合わせて使用することを選択するか、そのような形式が利用できない場合、”insertmanyvalues”機能は、各パラメータセットに対して個々のINSERT文を実行する”非バッチ”モードに優雅に劣化する可能性があります。

例えば、SQL Serverでは、自動的にインクリメントされる IDENTITY 列がプライマリキーとして使用される場合、次のSQL形式が使用されます。

INSERT INTO a (data, x, y)
OUTPUT inserted.id, inserted.id AS id__1
SELECT p0, p1, p2 FROM (VALUES
    (?, ?, ?, 0), (?, ?, ?, 1), (?, ?, ?, 2),
    ...
    (?, ?, ?, 77)
) AS imp_sen(p0, p1, p2, sen_counter) ORDER BY sen_counter

同様の形式は、主キー列がSERIALまたはIDENTITYを使用する場合にPostgreSQLでも使用されます。上記の形式は、 行が挿入される順序を保証しません 。ただし、IDENTITYまたはSERIALの値が各パラメータセット [1] の順序で作成されることは保証します。”insertmanyvalues”機能は、上記のINSERT文に対して返された行を、整数IDENTITYを増分することによってソートします。

SQLiteデータベースの場合、新しいROWID値の生成とパラメータセットが渡される順序を関連付けることができる適切なINSERTフォームはありません。その結果、サーバー生成の主キー値を使用する場合、SQLiteバックエンドは、順序付きRETURNINGが要求されると「非バッチ」モードに低下します。MariaDBの場合、insertmanyvaluesで使用されるデフォルトのINSERTフォームで十分です。これは、InnoDB [2] を使用する場合、このデータベースバックエンドはAUTO_INCREMENTの順序と入力データの順序を一致させるためです。

Pythonの uuid.uuid4() 関数を使用して Uuid 列の新しい値を生成する場合のように、クライアント側で生成された主キーの場合、”insertmanyvalues”機能はこの列をRETURNINGレコードに透過的に含め、その値を指定された入力レコードの値と相関させます。これにより、入力レコードと結果行の間の対応が維持されます。このことから、クライアント側で生成された主キー値が使用される場合、すべてのバックエンドでは、バッチ化されたパラメータ相関のあるRETURNING順序が可能になることがわかります。

“insertmanyvalues” “batch”モードが、入力パラメータとRETURNING行との対応点として使用する1つ以上の列をどのように決定するかという問題は、 insert sentinel として知られています。これは、そのような値を追跡するために使用される特定の1つ以上の列です。”insert sentinel”は通常自動的に選択されますが、非常に特殊な場合にはユーザが設定することもできます。 Configuring Sentinel Columns で説明されています。

入力値と決定論的に一致したサーバ生成値を提供できる適切なINSERT形式を提供しないバックエンドや、他の種類のサーバ生成主キー値を特徴とする Table 構成では、保証されたRETURNING順序が要求された場合、”insertmanyvalues”モードは non-batched モードを使用します。

See also

Non-Batched Mode Operation

クライアント側のプライマリキー値を持たず、サーバが生成したプライマリキー値を提供する(あるいはプライマリキーを持たない) Table 構成で、問題のデータベースが複数のパラメータセットに対して決定性やソート性のある方法で呼び出すことができない場合、 Insert 文の Insert.returning.sort_by_parameter_order 要件を満たすための”insertmanyvalues”機能は、代わりに 非バッチモード を使用することを選択できます。

このモードでは、INSERTの元のSQL形式が維持され、代わりに「insertmanyvalues」機能によって、各パラメータセットに対して指定されたとおりにステートメントが個別に実行され、返された行が完全な結果セットに編成されます。以前のSQLAlchemyバージョンとは異なり、Pythonのオーバーヘッドを最小限に抑えるタイトなループで実行されます。SQLiteなどの場合、 “非バッチ”モードは”バッチ”モードとまったく同じように実行されます。

Statement Execution Model

“バッチ”モードと”非バッチ”モードの両方で、この機能は、コアレベルの Connection.execute() メソッドへの**単一**呼び出しの範囲内で、DBAPIの cursor.execute() メソッドを使用して、 複数のINSERT文 を呼び出す必要があります。各ステートメントには、パラメータセットの固定された制限が含まれます。この制限は、以下の Controlling the Batch Size で説明するように設定できます。 cursor.execute() への個別の呼び出しは個別にログに記録され、 ConnectionEvents.before_cursor_execute() などのイベントリスナにも個別に渡されます(下記の Logging and Events を参照)。

Configuring Sentinel Columns

典型的なケースでは、確定的な行順序でINSERT.RETURNINGを提供するための”insertmanyvalues”機能は、指定されたテーブルの主キーから自動的にセンチネル列を決定し、識別できない場合は”row at a time”モードに優雅に劣化します。完全に オプション の機能として、デフォルトの生成関数が”センチネル”ユースケースと互換性のないサーバ生成主キーを持つテーブルの完全な”insertmanyvalues”バルクパフォーマンスを得るために、他の非主キー列は、特定の要件を満たすことを前提として”センチネル”列としてマークされることがあります。典型的な例は、Pythonの uuid.uuid4() 関数のようなクライアント側のデフォルトを持つ非主キー Uuid 列です。また、”insertmanyvalues”ユースケース向けのクライアント側の整数カウンタを持つ単純な整数列を作成する構成体もあります。

Sentinel列は、 Column.insert_sentinel を修飾列に追加することで示すことができます。最も基本的な”修飾”列は、次のようなUUID列のような、クライアント側のデフォルトを持つNULL不可の一意の列です:

import uuid

from sqlalchemy import Column
from sqlalchemy import FetchedValue
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy import Uuid

my_table = Table(
    "some_table",
    metadata,
    # assume some arbitrary server-side function generates
    # primary key values, so cannot be tracked by a bulk insert
    Column("id", String(50), server_default=FetchedValue(), primary_key=True),
    Column("data", String(50)),
    Column(
        "uniqueid",
        Uuid(),
        default=uuid.uuid4,
        nullable=False,
        unique=True,
        insert_sentinel=True,
    ),
)

ORM宣言モデルを使用する場合、 mapped_column 構文を使用して同じ形式を使用できます:

import uuid

from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


class Base(DeclarativeBase):
    pass


class MyClass(Base):
    __tablename__ = "my_table"

    id: Mapped[str] = mapped_column(primary_key=True, server_default=FetchedValue())
    data: Mapped[str] = mapped_column(String(50))
    uniqueid: Mapped[uuid.UUID] = mapped_column(
        default=uuid.uuid4, unique=True, insert_sentinel=True
    )

デフォルトジェネレータが生成する値は 一意でなければなりません が、上記の”sentinel”列に対する実際のUNIQUE制約は、 unique=True パラメータで示されますが、それ自体はオプションであり、必要でなければ省略することができます。

また、”insert sentinel”という特別な形式もあります。これは、”insertmanyvalues”操作中にのみ使用される特別なデフォルトの整数カウンタを利用する、NULL許容整数列です。追加の動作として、列はSQL文と結果セットから自身を省略し、ほとんど透過的に動作します。ただし、実際のデータベーステーブル内に物理的に存在する必要があります。このスタイルの Column は、関数 insert_sentinel() を使用して構築できます:

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy import Uuid
from sqlalchemy import insert_sentinel

Table(
    "some_table",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("data", String(50)),
    insert_sentinel("sentinel"),
)

ORM宣言型を使用する場合、宣言型に対応したバージョンの insert_sentinel() が利用できます。これは orm_insert_sentinel() と呼ばれ、Baseクラスまたはmixinで使用できます。 declared_attr() を使用してパッケージ化された場合、列は結合された継承階層内を含むすべてのテーブルバウンドサブクラスに適用されます:

from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import orm_insert_sentinel


class Base(DeclarativeBase):
    @declared_attr
    def _sentinel(cls) -> Mapped[int]:
        return orm_insert_sentinel()


class MyClass(Base):
    __tablename__ = "my_table"

    id: Mapped[str] = mapped_column(primary_key=True, server_default=FetchedValue())
    data: Mapped[str] = mapped_column(String(50))


class MySubClass(MyClass):
    __tablename__ = "sub_table"

    id: Mapped[str] = mapped_column(ForeignKey("my_table.id"), primary_key=True)


class MySingleInhClass(MyClass):
    pass

上記の例では、”my_table”と”sub_table”の両方に”_sentinel”という名前の整数列が追加されます。この列は、ORMで使用されるバルク挿入を最適化するために”insertmanyvalues”機能で使用できます。

Controlling the Batch Size

“insertmanyvalues”の重要な特徴は、INSERT文のサイズが”values”句の固定された最大数と、一度に1つのINSERT文で表現できるバウンドパラメータの方言固有の固定された合計数に制限されていることです。与えられたパラメータ辞書の数が固定された制限を超えた場合、または単一のINSERT文でレンダリングされるバウンドパラメータの合計数が固定された制限を超えた場合(2つの固定された制限は別々です)、単一の Connection.execute() 呼び出しのスコープ内で複数のINSERT文が呼び出されます。それぞれの呼び出しは、”バッチ”と呼ばれるパラメータ辞書の一部を収容します。各”バッチ”内で表現されるパラメータ辞書の数は、”バッチサイズ”と呼ばれます。たとえば、バッチサイズが500の場合、生成される各INSERT文は最大500行をINSERTします。

バッチサイズを調整できることが重要になる可能性があります。なぜなら、値セット自体が比較的小さいINSERTでは、バッチサイズを大きくする方がパフォーマンスが高くなる可能性があります。また、非常に大きい値セットを使用するINSERTでは、バッチサイズを小さくする方が適切である可能性があります。非常に大きい値セットを使用するINSERTでは、レンダリングされるSQLのサイズと1つの文で渡されるデータの合計サイズの両方が、バックエンドの動作とメモリの制約に基づいて特定のサイズに制限されることでメリットが得られる可能性があります。このため、バッチサイズは、文ごとだけでなく、 Engine ごとにも設定できます。一方、パラメータの制限は、使用中のデータベースの既知の特性に基づいて固定されます。

バッチサイズのデフォルトは、ほとんどのバックエンドで1000であり、追加の方言ごとの”パラメータの最大数”制限要因により、ステートメントごとにバッチサイズがさらに削減される可能性があります。パラメータの最大数は、方言とサーバのバージョンによって異なります。最大サイズは32700です(PostgreSQLの制限である32767とSQLiteの最新の制限である32766からの適切な距離として選択されますが、ステートメント内の追加パラメータとDBAPIの奇抜さのための余地が残されています)。SQLiteの古いバージョン(3.32.0より前)では、この値は999に設定されます。MariaDBには確立された制限はありませんが、32700はSQLメッセージサイズの制限要因として残っています。

“バッチサイズ”の値は、 create_engine.insertmanyvalues_page_size パラメータを介して Engine の広さに影響を与えることができます。たとえば、INSERT文に影響を与えて、各文に最大100個のパラメータセットを含めるには:

e = create_engine("sqlite://", insertmanyvalues_page_size=100)

バッチサイズは、実行ごとに Connection.execution_options.insertmanyvalues_page_size 実行オプションを使用して、文ごとに影響を受けることもあります。たとえば、次のようになります:

with e.begin() as conn:
    result = conn.execute(
        table.insert().returning(table.c.id),
        parameterlist,
        execution_options={"insertmanyvalues_page_size": 100},
    )

または、文自体で設定します:

stmt = (
    table.insert()
    .returning(table.c.id)
    .execution_options(insertmanyvalues_page_size=100)
)
with e.begin() as conn:
    result = conn.execute(stmt, parameterlist)

Logging and Events

“insertmanyvalues”機能は、SQLAlchemyの statement logging や、 ConnectionEvents.before_cursor_execute() などのカーソルイベントと完全に統合されます。パラメータのリストが別々のバッチに分割されている場合、 各INSERT文は個別にログに記録され、イベントハンドラに渡されます 。これは、複数のINSERT文の生成がログとイベントから隠されていた以前の1.xシリーズのSQLAlchemyでのpsycopg2のみの機能と比較して大きな変更です。ログ表示では、読みやすくするためにパラメータの長いリストが切り捨てられ、各文の特定のバッチも示されます。次の例は、このログの抜粋を示しています。

INSERT INTO a (data, x, y) VALUES (?, ?, ?), ... 795 characters truncated ...  (?, ?, ?), (?, ?, ?) RETURNING id
[generated in 0.00177s (insertmanyvalues) 1/10 (unordered)] ('d0', 0, 0, 'd1',  ...
INSERT INTO a (data, x, y) VALUES (?, ?, ?), ... 795 characters truncated ...  (?, ?, ?), (?, ?, ?) RETURNING id
[insertmanyvalues 2/10 (unordered)] ('d100', 100, 1000, 'd101', ...

...

INSERT INTO a (data, x, y) VALUES (?, ?, ?), ... 795 characters truncated ...  (?, ?, ?), (?, ?, ?) RETURNING id
[insertmanyvalues 10/10 (unordered)] ('d900', 900, 9000, 'd901', ...

non-batch mode が実行されると、ログはinsertmanyvaluesメッセージとともにこれを示します。

...

INSERT INTO a (data, x, y) VALUES (?, ?, ?) RETURNING id
[insertmanyvalues 67/78 (ordered; batch not supported)] ('d66', 66, 66)
INSERT INTO a (data, x, y) VALUES (?, ?, ?) RETURNING id
[insertmanyvalues 68/78 (ordered; batch not supported)] ('d67', 67, 67)
INSERT INTO a (data, x, y) VALUES (?, ?, ?) RETURNING id
[insertmanyvalues 69/78 (ordered; batch not supported)] ('d68', 68, 68)
INSERT INTO a (data, x, y) VALUES (?, ?, ?) RETURNING id
[insertmanyvalues 70/78 (ordered; batch not supported)] ('d69', 69, 69)

...

See also

Configuring Logging

Upsert Support

PostgreSQL、SQLite、MariaDBダイアレクトでは、バックエンド固有の”upsert”構文として _PostgreSQL.insert() 、 _SQLite.insert() 、 insert() が提供されています。これらはそれぞれ Insert 構文で、 on_conflict_do_update() や on_duplicate_key() などのメソッドが追加されています。これらの構文は、RETURNINGとともに使用された場合の”insertmanyvalues”動作もサポートしており、RETURNINGによる効率的なupsertが可能になっています。

Engine Disposal

Engine は接続プールを参照します。これは、通常の状況では、 Engine オブジェクトがまだメモリに存在している間は、開いているデータベース接続が存在することを意味します。 Engine がガベージコレクションされると、その接続プールはその Engine によって参照されなくなり、その接続がまだチェックアウトされていないと仮定すると、プールとその接続もガベージコレクションされ、実際のデータベース接続も閉じられます。しかし、そうでなければ、 Engine は、通常はデフォルトのプール実装である QueuePool を使用すると仮定して、開いているデータベース接続を保持します。

Engine は、通常、前もって確立され、アプリケーションの寿命を通じて維持される永続的なフィクスチャであることが意図されています。これは、接続ごとに作成され、配置されることを意図したものでは ありません 。代わりに、接続のプールと、使用中のデータベースとDBAPIに関する構成情報、およびデータベースごとのリソースのある程度の内部キャッシュの両方を維持するレジストリです。

しかし、多くの場合、 Engine が参照するすべての接続リソースが完全に閉じられることが望まれます。一般的に、このような場合にPythonのガベージコレクションに頼るのは良い考えではありません。代わりに、 Engine は Engine.dispose() メソッドを使って明示的に破棄することができます。これにより、エンジンの基礎となる接続プールが破棄され、空の新しい接続プールに置き換えられます。 Engine がこの時点で破棄されて使用されなくなれば、それが参照するすべての チェックインされた 接続も完全に閉じられます。

Engine.dispose() を呼び出す有効なユースケースは次のとおりです:

  • プログラムが、接続プールによって保持されている残りのチェックインされた接続を解放し、将来の操作のためにそのデータベースに接続されなくなることを予期している場合。

  • プログラムがマルチプロセッシングまたは fork() を使用し、 Engine オブジェクトが子プロセスにコピーされる場合、 Engine.dispose() を呼び出して、エンジンがそのフォークに対してローカルな新しいデータベース接続を作成するようにします。データベース接続は通常、プロセスの境界を越えて移動 しません 。この場合は、 Engine.dispose.close パラメータをFalseに設定して使用します。この使用例の背景については Using Connection Pools with Multiprocessing or os.fork() の節を参照してください。

  • テストスイートまたはマルチテナンシのシナリオで、多くのアドホックで短命な Engine オブジェクトが作成され、破棄される可能性がある場合。

チェックアウトされた 接続は、エンジンが破棄またはガベージコレクションされても 破棄されません 。なぜなら、これらの接続はアプリケーションによって他の場所で強く参照されているからです。ただし、 Engine.dispose() が呼び出された後、これらの接続はその Engine に関連付けられなくなります。これらの接続が閉じられると、現在は切り離された接続プールに戻されます。この接続プールは、それを参照するすべての接続も参照されなくなると、最終的にガベージコレクションされます。このプロセスを制御するのは簡単ではないので、 Engine.dispose() は、チェックアウトされたすべての接続がチェックインされた後、またはその他の方法で接続プールから関連付けが解除された後にのみ呼び出すことを強くお勧めします。

Engine オブジェクトが接続プーリングを使用することによって悪影響を受けるアプリケーションのための代替手段は、プーリングを完全に無効にすることです。これは通常、新しい接続を使用する際に性能にわずかな影響を与えるだけで、接続がチェックインされたときに完全に閉じられ、メモリに保持されないことを意味します。プーリングを無効にする方法のガイドラインについては Switching Pool Implementations を参照してください。

See also

Connection Pooling

Using Connection Pools with Multiprocessing or os.fork()

Working with Driver SQL and Raw DBAPI Connections

Connection.execute() の使用法の紹介では、 text() 構文を使用して、テキストSQL文がどのように呼び出されるかを説明しています。SQLAlchemyを使用する場合、Core式言語とORMの両方がSQLのテキスト表現を抽象化しているため、テキストSQLは実際には標準ではなく例外です。ただし、 text() 構文自体も、バインドされたパラメータがどのように渡されるかを標準化し、パラメータと結果セット行のデータ型付け動作をサポートするという点で、テキストSQLの抽象化を提供します。

Invoking SQL strings directly to the driver

text() 構文を使わずに、ドライバ( DBAPI と呼ばれます)に直接渡されるテキストSQLを呼び出したい場合には、 Connection.exec_driver_sql() メソッドを使うことができます:

with engine.connect() as conn:
    conn.exec_driver_sql("SET param='bar'")

New in version 1.4: Connection.exec_driver_sql() メソッドを追加しました。

Working with the DBAPI cursor directly

SQLAlchemyでは、ストアドプロシージャの呼び出しや複数の結果セットの処理など、いくつかの DBAPI 関数への汎用的なアクセス方法が提供されない場合があります。このような場合は、生のDBAPI接続を直接処理するのも同様に便利です。

生のDBAPI接続にアクセスする最も一般的な方法は、既に存在する Connection オブジェクトから直接取得することです。これは Connection.connection 属性を使用して存在します:

connection = engine.connect()
dbapi_conn = connection.connection

ここでのDBAPI接続は、実際には元の接続プールに関して”プロキシ”されていますが、これはほとんどの場合無視できる実装の詳細です。このDBAPI接続はまだ所有する Connection オブジェクトのスコープ内に含まれているので、トランザクション制御や Connection.close() メソッドの呼び出しなど、ほとんどの機能で Connection オブジェクトを利用するのが最善です。これらの操作がDBAPI接続で直接実行される場合、所有する Connection はこれらの状態の変化を認識しません。

所有する Connection によって維持されるDBAPI接続によって課される制限を克服するために、最初に Connection を調達しなくても、 Engine の Engine.raw_connection() メソッドを使用してDBAPI接続を利用することもできます:

dbapi_conn = engine.raw_connection()

このDBAPI接続は、以前の場合と同様に”プロキシ”された形式です。このプロキシの目的は明らかです。この接続の .close() メソッドを呼び出すと、DBAPI接続は通常実際には閉じられず、代わりにエンジンの接続プールに released されます:

dbapi_conn.close()

SQLAlchemyは将来、より多くのDBAPIユースケースに対して組み込みパターンを追加する可能性がありますが、これらのケースはほとんど必要とされない傾向があり、使用するDBAPIのタイプに大きく依存するため、収益は減少しています。したがって、いずれにしても、直接DBAPI呼び出しパターンは、必要とされるケースに対して常に存在します。

See also

How do I get at the raw DBAPI connection when using an Engine? - includes additional details about how the DBAPI connection is accessed as well as the “driver” connection when using asyncio drivers.

DBAPI接続を使用するためのいくつかのレシピを次に示します。

Calling Stored Procedures and User Defined Functions

SQLAlchemyでは、複数の方法でストアド・プロシージャおよびユーザー定義関数を呼び出すことができます。すべてのDBAPIには異なる方法があるため、特定の使用方法に関する詳細については、基礎となるDBAPIのドキュメントを参照する必要があります。次の例は仮定のものであり、基礎となるDBAPIでは機能しない場合があります。

特別な構文やパラメータに関係するストアドプロシージャや関数では、DBAPIレベルの callproc がDBAPIで使用される可能性があります。このパターンの例を次に示します:

connection = engine.raw_connection()
try:
    cursor_obj = connection.cursor()
    cursor_obj.callproc("my_procedure", ["x", "y", "z"])
    results = list(cursor_obj.fetchall())
    cursor_obj.close()
    connection.commit()
finally:
    connection.close()

Note

すべてのDBAPIが callproc を使用しているわけではなく、全体的な使用方法の詳細も異なります。上の例は、特定のDBAPI関数を使用した場合の外観を示したものにすぎません。

DBAPIに callproc 要件がない場合 または ストアドプロシージャまたはユーザ定義関数を、通常のSQLAlchemy接続の使用する場合、別のパターンで呼び出す必要がある場合があります。この使用パターンの例の1つは、このドキュメントの執筆時点で、PostgreSQLデータベース内のストアドプロシージャをpsycopg2 DBAPIで実行することです。これは通常の接続で呼び出す必要があります:

connection.execute("CALL my_procedure();")

前述の例は仮定のものです。基礎となるデータベースでは、このような状況での”CALL”または”SELECT”のサポートが保証されていません。また、キーワードは、ストアド・プロシージャまたはユーザー定義関数である関数によって異なる場合があります。このような状況で正しい構文およびパターンを判断するには、基礎となるDBAPIおよびデータベースのドキュメントを参照してください。

Multiple Result Sets

複数の結果セットのサポートは、 nextset メソッドを使用して、生のDBAPIカーソルから利用できます:

connection = engine.raw_connection()
try:
    cursor_obj = connection.cursor()
    cursor_obj.execute("select * from table1; select * from table2")
    results_one = cursor_obj.fetchall()
    cursor_obj.nextset()
    results_two = cursor_obj.fetchall()
    cursor_obj.close()
finally:
    connection.close()

Registering New Dialects

create_engine() 関数呼び出しは、setuptoolsのエントリポイントを使用して与えられた方言を見つけます。これらのエントリポイントは、setup.pyスクリプト内でサードパーティの方言に対して確立できます。たとえば、新しい方言”foodialect://””を作成するには、次の手順を実行します。

  1. foodialect というパッケージを作成します。

  2. パッケージには、ダイアレクトクラスを含むモジュールが必要です。これは通常、 sqlalchemy.engine.default.DefaultDialect のサブクラスです。この例では、それが FooDialect と呼ばれ、そのモジュールが「FooDialect.dialect」を介してアクセスされるとします。

  3. エントリポイントは、次のようにして setup.cfg に設定できます。

    [options.entry_points]
sqlalchemy.dialects =
    foodialect = foodialect.dialect:FooDialect

既存のSQLAlchemyでサポートされているデータベースに加えて、特定のDBAPIをサポートしているダイアレクトの場合は、データベース修飾を含めて名前を指定できます。例えば、 FooDialect が実際にMySQLダイアレクトである場合、エントリポイントは次のように設定できます:

[options.entry_points]
sqlalchemy.dialects
    mysql.foodialect = foodialect.dialect:FooDialect

上記のエントリポイントは create_engine("mysql+foodialect://") としてアクセスされます。

Registering Dialects In-Process

また、SQLAlchemyを使用すると、現在のプロセス内にダイアレクトを登録できるため、個別にインストールする必要がありません。次のように register() 関数を使用してください:

from sqlalchemy.dialects import registry


registry.register("mysql.foodialect", "myapp.dialect", "MyMySQLDialect")

上記は create_engine("mysql+foodialect://") に応答して、 myapp.dialect モジュールから MyMySQLDialect クラスをロードします。

Connection / Engine API

Object Name Description

Connection

Provides high-level functionality for a wrapped DB-API connection.

CreateEnginePlugin

A set of hooks intended to augment the construction of an Engine object based on entrypoint names in a URL.

Engine

Connects a Pool and Dialect together to provide a source of database connectivity and behavior.

ExceptionContext

Encapsulate information about an error condition in progress.

NestedTransaction

Represent a ‘nested’, or SAVEPOINT transaction.

RootTransaction

Represent the “root” transaction on a Connection.

Transaction

Represent a database transaction in progress.

TwoPhaseTransaction

Represent a two-phase transaction.
class sqlalchemy.engine.Connection

Provides high-level functionality for a wrapped DB-API connection.

The Connection object is procured by calling the Engine.connect() method of the Engine object, and provides services for execution of SQL statements as well as transaction control.

The Connection object is not thread-safe. While a Connection can be shared among threads using properly synchronized access, it is still possible that the underlying DBAPI connection may not support shared access between threads. Check the DBAPI documentation for details.

Members

__init__(), begin(), begin_nested(), begin_twophase(), close(), closed, commit(), connection, default_isolation_level, detach(), exec_driver_sql(), execute(), execution_options(), get_execution_options(), get_isolation_level(), get_nested_transaction(), get_transaction(), in_nested_transaction(), in_transaction(), info, invalidate(), invalidated, rollback(), scalar(), scalars(), schema_for_object()

The Connection object represents a single DBAPI connection checked out from the connection pool. In this state, the connection pool has no affect upon the connection, including its expiration or timeout state. For the connection pool to properly manage connections, connections should be returned to the connection pool (i.e. connection.close()) whenever the connection is not in use.

Class signature

class sqlalchemy.engine.Connection (sqlalchemy.engine.interfaces.ConnectionEventsTarget, sqlalchemy.inspection.Inspectable)

method sqlalchemy.engine.Connection.__init__(engine: Engine, connection: PoolProxiedConnection | None = None, _has_events: bool | None = None, _allow_revalidate: bool = True, _allow_autobegin: bool = True)

Construct a new Connection.

method sqlalchemy.engine.Connection.begin() → RootTransaction

Begin a transaction prior to autobegin occurring.

E.g.:

with engine.connect() as conn:
    with conn.begin() as trans:
        conn.execute(table.insert(), {"username": "sandy"})

The returned object is an instance of RootTransaction. This object represents the “scope” of the transaction, which completes when either the Transaction.rollback() or Transaction.commit() method is called; the object also works as a context manager as illustrated above.

The Connection.begin() method begins a transaction that normally will be begun in any case when the connection is first used to execute a statement. The reason this method might be used would be to invoke the ConnectionEvents.begin() event at a specific time, or to organize code within the scope of a connection checkout in terms of context managed blocks, such as:

with engine.connect() as conn:
    with conn.begin():
        conn.execute(...)
        conn.execute(...)

    with conn.begin():
        conn.execute(...)
        conn.execute(...)

The above code is not fundamentally any different in its behavior than the following code which does not use Connection.begin(); the below style is known as “commit as you go” style:

with engine.connect() as conn:
    conn.execute(...)
    conn.execute(...)
    conn.commit()

    conn.execute(...)
    conn.execute(...)
    conn.commit()

From a database point of view, the Connection.begin() method does not emit any SQL or change the state of the underlying DBAPI connection in any way; the Python DBAPI does not have any concept of explicit transaction begin.

See also

Working with Transactions and the DBAPI - in the SQLAlchemy Unified Tutorial

Connection.begin_nested() - use a SAVEPOINT

Connection.begin_twophase() - use a two phase /XID transaction

Engine.begin() - context manager available from Engine

method sqlalchemy.engine.Connection.begin_nested() → NestedTransaction

Begin a nested transaction (i.e. SAVEPOINT) and return a transaction handle that controls the scope of the SAVEPOINT.

E.g.:

with engine.begin() as connection:
    with connection.begin_nested():
        connection.execute(table.insert(), {"username": "sandy"})

The returned object is an instance of NestedTransaction, which includes transactional methods NestedTransaction.commit() and NestedTransaction.rollback(); for a nested transaction, these methods correspond to the operations “RELEASE SAVEPOINT <name>” and “ROLLBACK TO SAVEPOINT <name>”. The name of the savepoint is local to the NestedTransaction object and is generated automatically. Like any other Transaction, the NestedTransaction may be used as a context manager as illustrated above which will “release” or “rollback” corresponding to if the operation within the block were successful or raised an exception.

Nested transactions require SAVEPOINT support in the underlying database, else the behavior is undefined. SAVEPOINT is commonly used to run operations within a transaction that may fail, while continuing the outer transaction. E.g.:

from sqlalchemy import exc

with engine.begin() as connection:
    trans = connection.begin_nested()
    try:
        connection.execute(table.insert(), {"username": "sandy"})
        trans.commit()
    except exc.IntegrityError:  # catch for duplicate username
        trans.rollback()  # rollback to savepoint

    # outer transaction continues
    connection.execute( ... )

If Connection.begin_nested() is called without first calling Connection.begin() or Engine.begin(), the Connection object will “autobegin” the outer transaction first. This outer transaction may be committed using “commit-as-you-go” style, e.g.:

with engine.connect() as connection:  # begin() wasn't called

    with connection.begin_nested(): will auto-"begin()" first
        connection.execute( ... )
    # savepoint is released

    connection.execute( ... )

    # explicitly commit outer transaction
    connection.commit()

    # can continue working with connection here

Changed in version 2.0: Connection.begin_nested() will now participate in the connection “autobegin” behavior that is new as of 2.0 / “future” style connections in 1.4.

See also

Connection.begin()

Using SAVEPOINT - ORM support for SAVEPOINT

method sqlalchemy.engine.Connection.begin_twophase(xid: Any | None = None) → TwoPhaseTransaction

Begin a two-phase or XA transaction and return a transaction handle.

The returned object is an instance of TwoPhaseTransaction, which in addition to the methods provided by Transaction, also provides a TwoPhaseTransaction.prepare() method.

Parameters:

xid – the two phase transaction id. If not supplied, a random id will be generated.

See also

Connection.begin()

Connection.begin_twophase()

method sqlalchemy.engine.Connection.close() → None

Close this Connection.

This results in a release of the underlying database resources, that is, the DBAPI connection referenced internally. The DBAPI connection is typically restored back to the connection-holding Pool referenced by the Engine that produced this Connection. Any transactional state present on the DBAPI connection is also unconditionally released via the DBAPI connection’s rollback() method, regardless of any Transaction object that may be outstanding with regards to this Connection.

This has the effect of also calling Connection.rollback() if any transaction is in place.

After Connection.close() is called, the Connection is permanently in a closed state, and will allow no further operations.

attribute sqlalchemy.engine.Connection.closed

Return True if this connection is closed.

method sqlalchemy.engine.Connection.commit() → None

Commit the transaction that is currently in progress.

This method commits the current transaction if one has been started. If no transaction was started, the method has no effect, assuming the connection is in a non-invalidated state.

A transaction is begun on a Connection automatically whenever a statement is first executed, or when the Connection.begin() method is called.

Note

The Connection.commit() method only acts upon the primary database transaction that is linked to the Connection object. It does not operate upon a SAVEPOINT that would have been invoked from the Connection.begin_nested() method; for control of a SAVEPOINT, call NestedTransaction.commit() on the NestedTransaction that is returned by the Connection.begin_nested() method itself.

attribute sqlalchemy.engine.Connection.connection

The underlying DB-API connection managed by this Connection.

This is a SQLAlchemy connection-pool proxied connection which then has the attribute _ConnectionFairy.dbapi_connection that refers to the actual driver connection.

See also

Working with Driver SQL and Raw DBAPI Connections

attribute sqlalchemy.engine.Connection.default_isolation_level

The initial-connection time isolation level associated with the Dialect in use.

This value is independent of the Connection.execution_options.isolation_level and Engine.execution_options.isolation_level execution options, and is determined by the Dialect when the first connection is created, by performing a SQL query against the database for the current isolation level before any additional commands have been emitted.

Calling this accessor does not invoke any new SQL queries.

See also

Connection.get_isolation_level() - view current actual isolation level

create_engine.isolation_level - set per Engine isolation level

Connection.execution_options.isolation_level - set per Connection isolation level

method sqlalchemy.engine.Connection.detach() → None

Detach the underlying DB-API connection from its connection pool.

E.g.:

with engine.connect() as conn:
    conn.detach()
    conn.execute(text("SET search_path TO schema1, schema2"))

    # work with connection

# connection is fully closed (since we used "with:", can
# also call .close())

This Connection instance will remain usable. When closed (or exited from a context manager context as above), the DB-API connection will be literally closed and not returned to its originating pool.

This method can be used to insulate the rest of an application from a modified state on a connection (such as a transaction isolation level or similar).

method sqlalchemy.engine.Connection.exec_driver_sql(statement: str, parameters: _DBAPIAnyExecuteParams | None = None, execution_options: CoreExecuteOptionsParameter | None = None) → CursorResult[Any]

Executes a string SQL statement on the DBAPI cursor directly, without any SQL compilation steps.

This can be used to pass any string directly to the cursor.execute() method of the DBAPI in use.

Parameters:

  • statement – The statement str to be executed. Bound parameters must use the underlying DBAPI’s paramstyle, such as “qmark”, “pyformat”, “format”, etc.

  • parameters – represent bound parameter values to be used in the execution. The format is one of: a dictionary of named parameters, a tuple of positional parameters, or a list containing either dictionaries or tuples for multiple-execute support.

Returns:

a CursorResult.

E.g. multiple dictionaries:

conn.exec_driver_sql(
    "INSERT INTO table (id, value) VALUES (%(id)s, %(value)s)",
    [{"id":1, "value":"v1"}, {"id":2, "value":"v2"}]
)

Single dictionary:

conn.exec_driver_sql(
    "INSERT INTO table (id, value) VALUES (%(id)s, %(value)s)",
    dict(id=1, value="v1")
)

Single tuple:

conn.exec_driver_sql(
    "INSERT INTO table (id, value) VALUES (?, ?)",
    (1, 'v1')
)

Note

The Connection.exec_driver_sql() method does not participate in the ConnectionEvents.before_execute() and ConnectionEvents.after_execute() events. To intercept calls to Connection.exec_driver_sql(), use ConnectionEvents.before_cursor_execute() and ConnectionEvents.after_cursor_execute().

See also

PEP 249

method sqlalchemy.engine.Connection.execute(statement: Executable, parameters: _CoreAnyExecuteParams | None = None, *, execution_options: CoreExecuteOptionsParameter | None = None) → CursorResult[Any]

Executes a SQL statement construct and returns a CursorResult.

Parameters:

  • statement –

    The statement to be executed. This is always an object that is in both the ClauseElement and Executable hierarchies, including:

  • parameters – parameters which will be bound into the statement. This may be either a dictionary of parameter names to values, or a mutable sequence (e.g. a list) of dictionaries. When a list of dictionaries is passed, the underlying statement execution will make use of the DBAPI cursor.executemany() method. When a single dictionary is passed, the DBAPI cursor.execute() method will be used.

  • execution_options – optional dictionary of execution options, which will be associated with the statement execution. This dictionary can provide a subset of the options that are accepted by Connection.execution_options().

Returns:

a Result object.

method sqlalchemy.engine.Connection.execution_options(**opt: Any) → Connection

Set non-SQL options for the connection which take effect during execution.

This method modifies this Connection in-place; the return value is the same Connection object upon which the method is called. Note that this is in contrast to the behavior of the execution_options methods on other objects such as Engine.execution_options() and Executable.execution_options(). The rationale is that many such execution options necessarily modify the state of the base DBAPI connection in any case so there is no feasible means of keeping the effect of such an option localized to a “sub” connection.

Changed in version 2.0: The Connection.execution_options() method, in contrast to other objects with this method, modifies the connection in-place without creating copy of it.

As discussed elsewhere, the Connection.execution_options() method accepts any arbitrary parameters including user defined names. All parameters given are consumable in a number of ways including by using the Connection.get_execution_options() method. See the examples at Executable.execution_options() and Engine.execution_options().

The keywords that are currently recognized by SQLAlchemy itself include all those listed under Executable.execution_options(), as well as others that are specific to Connection.

Parameters:

See also

Engine.execution_options()

Executable.execution_options()

Connection.get_execution_options()

ORM Execution Options - documentation on all ORM-specific execution options

method sqlalchemy.engine.Connection.get_execution_options() → _ExecuteOptions

Get the non-SQL options which will take effect during execution.

New in version 1.3.

See also

Connection.execution_options()

method sqlalchemy.engine.Connection.get_isolation_level() → Literal['SERIALIZABLE', 'REPEATABLE READ', 'READ COMMITTED', 'READ UNCOMMITTED', 'AUTOCOMMIT']

Return the current actual isolation level that’s present on the database within the scope of this connection.

This attribute will perform a live SQL operation against the database in order to procure the current isolation level, so the value returned is the actual level on the underlying DBAPI connection regardless of how this state was set. This will be one of the four actual isolation modes READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. It will not include the AUTOCOMMIT isolation level setting. Third party dialects may also feature additional isolation level settings.

Note

This method will not report on the AUTOCOMMIT isolation level, which is a separate dbapi setting that’s independent of actual isolation level. When AUTOCOMMIT is in use, the database connection still has a “traditional” isolation mode in effect, that is typically one of the four values READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

Compare to the Connection.default_isolation_level accessor which returns the isolation level that is present on the database at initial connection time.

See also

Connection.default_isolation_level - view default level

create_engine.isolation_level - set per Engine isolation level

Connection.execution_options.isolation_level - set per Connection isolation level

method sqlalchemy.engine.Connection.get_nested_transaction() → NestedTransaction | None

Return the current nested transaction in progress, if any.

New in version 1.4.

method sqlalchemy.engine.Connection.get_transaction() → RootTransaction | None

Return the current root transaction in progress, if any.

New in version 1.4.

method sqlalchemy.engine.Connection.in_nested_transaction() → bool

Return True if a transaction is in progress.

method sqlalchemy.engine.Connection.in_transaction() → bool

Return True if a transaction is in progress.

attribute sqlalchemy.engine.Connection.info

Info dictionary associated with the underlying DBAPI connection referred to by this Connection, allowing user-defined data to be associated with the connection.

The data here will follow along with the DBAPI connection including after it is returned to the connection pool and used again in subsequent instances of Connection.

method sqlalchemy.engine.Connection.invalidate(exception: BaseException | None = None) → None

Invalidate the underlying DBAPI connection associated with this Connection.

An attempt will be made to close the underlying DBAPI connection immediately; however if this operation fails, the error is logged but not raised. The connection is then discarded whether or not close() succeeded.

Upon the next use (where “use” typically means using the Connection.execute() method or similar), this Connection will attempt to procure a new DBAPI connection using the services of the Pool as a source of connectivity (e.g. a “reconnection”).

If a transaction was in progress (e.g. the Connection.begin() method has been called) when Connection.invalidate() method is called, at the DBAPI level all state associated with this transaction is lost, as the DBAPI connection is closed. The Connection will not allow a reconnection to proceed until the Transaction object is ended, by calling the Transaction.rollback() method; until that point, any attempt at continuing to use the Connection will raise an InvalidRequestError. This is to prevent applications from accidentally continuing an ongoing transactional operations despite the fact that the transaction has been lost due to an invalidation.

The Connection.invalidate() method, just like auto-invalidation, will at the connection pool level invoke the PoolEvents.invalidate() event.

Parameters:

exception – an optional Exception instance that’s the reason for the invalidation. is passed along to event handlers and logging functions.

See also

More on Invalidation

attribute sqlalchemy.engine.Connection.invalidated

Return True if this connection was invalidated.

This does not indicate whether or not the connection was invalidated at the pool level, however

method sqlalchemy.engine.Connection.rollback() → None

Roll back the transaction that is currently in progress.

This method rolls back the current transaction if one has been started. If no transaction was started, the method has no effect. If a transaction was started and the connection is in an invalidated state, the transaction is cleared using this method.

A transaction is begun on a Connection automatically whenever a statement is first executed, or when the Connection.begin() method is called.

Note

The Connection.rollback() method only acts upon the primary database transaction that is linked to the Connection object. It does not operate upon a SAVEPOINT that would have been invoked from the Connection.begin_nested() method; for control of a SAVEPOINT, call NestedTransaction.rollback() on the NestedTransaction that is returned by the Connection.begin_nested() method itself.

method sqlalchemy.engine.Connection.scalar(statement: Executable, parameters: _CoreSingleExecuteParams | None = None, *, execution_options: CoreExecuteOptionsParameter | None = None) → Any

Executes a SQL statement construct and returns a scalar object.

This method is shorthand for invoking the Result.scalar() method after invoking the Connection.execute() method. Parameters are equivalent.

Returns:

a scalar Python value representing the first column of the first row returned.

method sqlalchemy.engine.Connection.scalars(statement: Executable, parameters: _CoreAnyExecuteParams | None = None, *, execution_options: CoreExecuteOptionsParameter | None = None) → ScalarResult[Any]

Executes and returns a scalar result set, which yields scalar values from the first column of each row.

This method is equivalent to calling Connection.execute() to receive a Result object, then invoking the Result.scalars() method to produce a ScalarResult instance.

Returns:

a ScalarResult

New in version 1.4.24.

method sqlalchemy.engine.Connection.schema_for_object(obj: HasSchemaAttr) → str | None

Return the schema name for the given schema item taking into account current schema translate map.

class sqlalchemy.engine.CreateEnginePlugin

A set of hooks intended to augment the construction of an Engine object based on entrypoint names in a URL.

The purpose of CreateEnginePlugin is to allow third-party systems to apply engine, pool and dialect level event listeners without the need for the target application to be modified; instead, the plugin names can be added to the database URL. Target applications for CreateEnginePlugin include:

  • connection and SQL performance tools, e.g. which use events to track number of checkouts and/or time spent with statements

  • connectivity plugins such as proxies

A rudimentary CreateEnginePlugin that attaches a logger to an Engine object might look like:

import logging

from sqlalchemy.engine import CreateEnginePlugin
from sqlalchemy import event

class LogCursorEventsPlugin(CreateEnginePlugin):
    def __init__(self, url, kwargs):
        # consume the parameter "log_cursor_logging_name" from the
        # URL query
        logging_name = url.query.get("log_cursor_logging_name", "log_cursor")

        self.log = logging.getLogger(logging_name)

    def update_url(self, url):
        "update the URL to one that no longer includes our parameters"
        return url.difference_update_query(["log_cursor_logging_name"])

    def engine_created(self, engine):
        "attach an event listener after the new Engine is constructed"
        event.listen(engine, "before_cursor_execute", self._log_event)


    def _log_event(
        self,
        conn,
        cursor,
        statement,
        parameters,
        context,
        executemany):

        self.log.info("Plugin logged cursor event: %s", statement)

Plugins are registered using entry points in a similar way as that of dialects:

entry_points={
    'sqlalchemy.plugins': [
        'log_cursor_plugin = myapp.plugins:LogCursorEventsPlugin'
    ]

A plugin that uses the above names would be invoked from a database URL as in:

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+pymysql://scott:tiger@localhost/test?"
    "plugin=log_cursor_plugin&log_cursor_logging_name=mylogger"
)

The plugin URL parameter supports multiple instances, so that a URL may specify multiple plugins; they are loaded in the order stated in the URL:

engine = create_engine(
  "mysql+pymysql://scott:tiger@localhost/test?"
  "plugin=plugin_one&plugin=plugin_twp&plugin=plugin_three")

The plugin names may also be passed directly to create_engine() using the create_engine.plugins argument:

engine = create_engine(
  "mysql+pymysql://scott:tiger@localhost/test",
  plugins=["myplugin"])

New in version 1.2.3: plugin names can also be specified to create_engine() as a list

A plugin may consume plugin-specific arguments from the URL object as well as the kwargs dictionary, which is the dictionary of arguments passed to the create_engine() call. “Consuming” these arguments includes that they must be removed when the plugin initializes, so that the arguments are not passed along to the Dialect constructor, where they will raise an ArgumentError because they are not known by the dialect.

As of version 1.4 of SQLAlchemy, arguments should continue to be consumed from the kwargs dictionary directly, by removing the values with a method such as dict.pop. Arguments from the URL object should be consumed by implementing the CreateEnginePlugin.update_url() method, returning a new copy of the URL with plugin-specific parameters removed:

class MyPlugin(CreateEnginePlugin):
    def __init__(self, url, kwargs):
        self.my_argument_one = url.query['my_argument_one']
        self.my_argument_two = url.query['my_argument_two']
        self.my_argument_three = kwargs.pop('my_argument_three', None)

    def update_url(self, url):
        return url.difference_update_query(
            ["my_argument_one", "my_argument_two"]
        )

Arguments like those illustrated above would be consumed from a create_engine() call such as:

from sqlalchemy import create_engine

engine = create_engine(
  "mysql+pymysql://scott:tiger@localhost/test?"
  "plugin=myplugin&my_argument_one=foo&my_argument_two=bar",
  my_argument_three='bat'
)

Changed in version 1.4: The URL object is now immutable; a CreateEnginePlugin that needs to alter the URL should implement the newly added CreateEnginePlugin.update_url() method, which is invoked after the plugin is constructed.

For migration, construct the plugin in the following way, checking for the existence of the CreateEnginePlugin.update_url() method to detect which version is running:

class MyPlugin(CreateEnginePlugin):
    def __init__(self, url, kwargs):
        if hasattr(CreateEnginePlugin, "update_url"):
            # detect the 1.4 API
            self.my_argument_one = url.query['my_argument_one']
            self.my_argument_two = url.query['my_argument_two']
        else:
            # detect the 1.3 and earlier API - mutate the
            # URL directly
            self.my_argument_one = url.query.pop('my_argument_one')
            self.my_argument_two = url.query.pop('my_argument_two')

        self.my_argument_three = kwargs.pop('my_argument_three', None)

    def update_url(self, url):
        # this method is only called in the 1.4 version
        return url.difference_update_query(
            ["my_argument_one", "my_argument_two"]
        )

See also

The URL object is now immutable - overview of the URL change which also includes notes regarding CreateEnginePlugin.

Members

__init__(), engine_created(), handle_dialect_kwargs(), handle_pool_kwargs(), update_url()

When the engine creation process completes and produces the Engine object, it is again passed to the plugin via the CreateEnginePlugin.engine_created() hook. In this hook, additional changes can be made to the engine, most typically involving setup of events (e.g. those defined in Core Events).

method sqlalchemy.engine.CreateEnginePlugin.__init__(url: URL, kwargs: Dict[str, Any])

Construct a new CreateEnginePlugin.

The plugin object is instantiated individually for each call to create_engine(). A single Engine will be passed to the CreateEnginePlugin.engine_created() method corresponding to this URL.

Parameters:
method sqlalchemy.engine.CreateEnginePlugin.engine_created(engine: Engine) → None

Receive the Engine object when it is fully constructed.

The plugin may make additional changes to the engine, such as registering engine or connection pool events.

method sqlalchemy.engine.CreateEnginePlugin.handle_dialect_kwargs(dialect_cls: Type[Dialect], dialect_args: Dict[str, Any]) → None

parse and modify dialect kwargs

method sqlalchemy.engine.CreateEnginePlugin.handle_pool_kwargs(pool_cls: Type[Pool], pool_args: Dict[str, Any]) → None

parse and modify pool kwargs

method sqlalchemy.engine.CreateEnginePlugin.update_url(url: URL) → URL

Update the URL.

A new URL should be returned. This method is typically used to consume configuration arguments from the URL which must be removed, as they will not be recognized by the dialect. The URL.difference_update_query() method is available to remove these arguments. See the docstring at CreateEnginePlugin for an example.

New in version 1.4.

class sqlalchemy.engine.Engine

Connects a Pool and Dialect together to provide a source of database connectivity and behavior.

An Engine object is instantiated publicly using the create_engine() function.

See also

Engine Configuration

Working with Engines and Connections

Members

begin(), clear_compiled_cache(), connect(), dispose(), driver, engine, execution_options(), get_execution_options(), name, raw_connection(), update_execution_options()

Class signature

class sqlalchemy.engine.Engine (sqlalchemy.engine.interfaces.ConnectionEventsTarget, sqlalchemy.log.Identified, sqlalchemy.inspection.Inspectable)

method sqlalchemy.engine.Engine.begin() → Iterator[Connection]

Return a context manager delivering a Connection with a Transaction established.

E.g.:

with engine.begin() as conn:
    conn.execute(
        text("insert into table (x, y, z) values (1, 2, 3)")
    )
    conn.execute(text("my_special_procedure(5)"))

Upon successful operation, the Transaction is committed. If an error is raised, the Transaction is rolled back.

See also

Engine.connect() - procure a Connection from an Engine.

Connection.begin() - start a Transaction for a particular Connection.

method sqlalchemy.engine.Engine.clear_compiled_cache() → None

Clear the compiled cache associated with the dialect.

This applies only to the built-in cache that is established via the create_engine.query_cache_size parameter. It will not impact any dictionary caches that were passed via the Connection.execution_options.compiled_cache parameter.

New in version 1.4.

method sqlalchemy.engine.Engine.connect() → Connection

Return a new Connection object.

The Connection acts as a Python context manager, so the typical use of this method looks like:

with engine.connect() as connection:
    connection.execute(text("insert into table values ('foo')"))
    connection.commit()

Where above, after the block is completed, the connection is “closed” and its underlying DBAPI resources are returned to the connection pool. This also has the effect of rolling back any transaction that was explicitly begun or was begun via autobegin, and will emit the ConnectionEvents.rollback() event if one was started and is still in progress.

See also

Engine.begin()

method sqlalchemy.engine.Engine.dispose(close: bool = True) → None

Dispose of the connection pool used by this Engine.

A new connection pool is created immediately after the old one has been disposed. The previous connection pool is disposed either actively, by closing out all currently checked-in connections in that pool, or passively, by losing references to it but otherwise not closing any connections. The latter strategy is more appropriate for an initializer in a forked Python process.

Parameters:

close –

if left at its default of True, has the effect of fully closing all currently checked in database connections. Connections that are still checked out will not be closed, however they will no longer be associated with this Engine, so when they are closed individually, eventually the Pool which they are associated with will be garbage collected and they will be closed out fully, if not already closed on checkin.

If set to False, the previous connection pool is de-referenced, and otherwise not touched in any way.

New in version 1.4.33: Added the Engine.dispose.close parameter to allow the replacement of a connection pool in a child process without interfering with the connections used by the parent process.

See also

Engine Disposal

Using Connection Pools with Multiprocessing or os.fork()

attribute sqlalchemy.engine.Engine.driver

Driver name of the Dialect in use by this Engine.

attribute sqlalchemy.engine.Engine.engine

Returns this Engine.

Used for legacy schemes that accept Connection / Engine objects within the same variable.

method sqlalchemy.engine.Engine.execution_options(**opt: Any) → OptionEngine

Return a new Engine that will provide Connection objects with the given execution options.

The returned Engine remains related to the original Engine in that it shares the same connection pool and other state:

  • The Pool used by the new Engine is the same instance. The Engine.dispose() method will replace the connection pool instance for the parent engine as well as this one.

  • Event listeners are “cascaded” - meaning, the new Engine inherits the events of the parent, and new events can be associated with the new Engine individually.

  • The logging configuration and logging_name is copied from the parent Engine.

The intent of the Engine.execution_options() method is to implement schemes where multiple Engine objects refer to the same connection pool, but are differentiated by options that affect some execution-level behavior for each engine. One such example is breaking into separate “reader” and “writer” Engine instances, where one Engine has a lower isolation level setting configured or is even transaction-disabled using “autocommit”. An example of this configuration is at Maintaining Multiple Isolation Levels for a Single Engine.

Another example is one that uses a custom option shard_id which is consumed by an event to change the current schema on a database connection:

from sqlalchemy import event
from sqlalchemy.engine import Engine

primary_engine = create_engine("mysql+mysqldb://")
shard1 = primary_engine.execution_options(shard_id="shard1")
shard2 = primary_engine.execution_options(shard_id="shard2")

shards = {"default": "base", "shard_1": "db1", "shard_2": "db2"}

@event.listens_for(Engine, "before_cursor_execute")
def _switch_shard(conn, cursor, stmt,
        params, context, executemany):
    shard_id = conn.get_execution_options().get('shard_id', "default")
    current_shard = conn.info.get("current_shard", None)

    if current_shard != shard_id:
        cursor.execute("use %s" % shards[shard_id])
        conn.info["current_shard"] = shard_id

The above recipe illustrates two Engine objects that will each serve as factories for Connection objects that have pre-established “shard_id” execution options present. A ConnectionEvents.before_cursor_execute() event handler then interprets this execution option to emit a MySQL use statement to switch databases before a statement execution, while at the same time keeping track of which database we’ve established using the Connection.info dictionary.

See also

Connection.execution_options() - update execution options on a Connection object.

Engine.update_execution_options() - update the execution options for a given Engine in place.

Engine.get_execution_options()

method sqlalchemy.engine.Engine.get_execution_options() → _ExecuteOptions

Get the non-SQL options which will take effect during execution.

See also

Engine.execution_options()

attribute sqlalchemy.engine.Engine.name

String name of the Dialect in use by this Engine.

method sqlalchemy.engine.Engine.raw_connection() → PoolProxiedConnection

Return a “raw” DBAPI connection from the connection pool.

The returned object is a proxied version of the DBAPI connection object used by the underlying driver in use. The object will have all the same behavior as the real DBAPI connection, except that its close() method will result in the connection being returned to the pool, rather than being closed for real.

This method provides direct DBAPI connection access for special situations when the API provided by Connection is not needed. When a Connection object is already present, the DBAPI connection is available using the Connection.connection accessor.

See also

Working with Driver SQL and Raw DBAPI Connections

method sqlalchemy.engine.Engine.update_execution_options(**opt: Any) → None

Update the default execution_options dictionary of this Engine.

The given keys/values in **opt are added to the default execution options that will be used for all connections. The initial contents of this dictionary can be sent via the execution_options parameter to create_engine().

See also

Connection.execution_options()

Engine.execution_options()

class sqlalchemy.engine.ExceptionContext

Encapsulate information about an error condition in progress.

Members

chained_exception, connection, cursor, dialect, engine, execution_context, invalidate_pool_on_disconnect, is_disconnect, is_pre_ping, original_exception, parameters, sqlalchemy_exception, statement

This object exists solely to be passed to the DialectEvents.handle_error() event, supporting an interface that can be extended without backwards-incompatibility.

attribute sqlalchemy.engine.ExceptionContext.chained_exception: BaseException | None

The exception that was returned by the previous handler in the exception chain, if any.

If present, this exception will be the one ultimately raised by SQLAlchemy unless a subsequent handler replaces it.

May be None.

attribute sqlalchemy.engine.ExceptionContext.connection: Connection | None

The Connection in use during the exception.

This member is present, except in the case of a failure when first connecting.

See also

ExceptionContext.engine

attribute sqlalchemy.engine.ExceptionContext.cursor: DBAPICursor | None

The DBAPI cursor object.

May be None.

attribute sqlalchemy.engine.ExceptionContext.dialect: Dialect

The Dialect in use.

This member is present for all invocations of the event hook.

New in version 2.0.

attribute sqlalchemy.engine.ExceptionContext.engine: Engine | None

The Engine in use during the exception.

This member is present in all cases except for when handling an error within the connection pool “pre-ping” process.

attribute sqlalchemy.engine.ExceptionContext.execution_context: ExecutionContext | None

The ExecutionContext corresponding to the execution operation in progress.

This is present for statement execution operations, but not for operations such as transaction begin/end. It also is not present when the exception was raised before the ExecutionContext could be constructed.

Note that the ExceptionContext.statement and ExceptionContext.parameters members may represent a different value than that of the ExecutionContext, potentially in the case where a ConnectionEvents.before_cursor_execute() event or similar modified the statement/parameters to be sent.

May be None.

attribute sqlalchemy.engine.ExceptionContext.invalidate_pool_on_disconnect: bool

Represent whether all connections in the pool should be invalidated when a “disconnect” condition is in effect.

Setting this flag to False within the scope of the DialectEvents.handle_error() event will have the effect such that the full collection of connections in the pool will not be invalidated during a disconnect; only the current connection that is the subject of the error will actually be invalidated.

The purpose of this flag is for custom disconnect-handling schemes where the invalidation of other connections in the pool is to be performed based on other conditions, or even on a per-connection basis.

attribute sqlalchemy.engine.ExceptionContext.is_disconnect: bool

Represent whether the exception as occurred represents a “disconnect” condition.

This flag will always be True or False within the scope of the DialectEvents.handle_error() handler.

SQLAlchemy will defer to this flag in order to determine whether or not the connection should be invalidated subsequently. That is, by assigning to this flag, a “disconnect” event which then results in a connection and pool invalidation can be invoked or prevented by changing this flag.

Note

The pool “pre_ping” handler enabled using the create_engine.pool_pre_ping parameter does not consult this event before deciding if the “ping” returned false, as opposed to receiving an unhandled error. For this use case, the legacy recipe based on engine_connect() may be used. A future API allow more comprehensive customization of the “disconnect” detection mechanism across all functions.

attribute sqlalchemy.engine.ExceptionContext.is_pre_ping: bool

Indicates if this error is occurring within the “pre-ping” step performed when create_engine.pool_pre_ping is set to True. In this mode, the ExceptionContext.engine attribute will be None. The dialect in use is accessible via the ExceptionContext.dialect attribute.

New in version 2.0.5.

attribute sqlalchemy.engine.ExceptionContext.original_exception: BaseException

The exception object which was caught.

This member is always present.

attribute sqlalchemy.engine.ExceptionContext.parameters: _DBAPIAnyExecuteParams | None

Parameter collection that was emitted directly to the DBAPI.

May be None.

attribute sqlalchemy.engine.ExceptionContext.sqlalchemy_exception: StatementError | None

The sqlalchemy.exc.StatementError which wraps the original, and will be raised if exception handling is not circumvented by the event.

May be None, as not all exception types are wrapped by SQLAlchemy. For DBAPI-level exceptions that subclass the dbapi’s Error class, this field will always be present.

attribute sqlalchemy.engine.ExceptionContext.statement: str | None

String SQL statement that was emitted directly to the DBAPI.

May be None.

class sqlalchemy.engine.NestedTransaction

Represent a ‘nested’, or SAVEPOINT transaction.

The NestedTransaction object is created by calling the Connection.begin_nested() method of Connection.

When using NestedTransaction, the semantics of “begin” / “commit” / “rollback” are as follows:

  • the “begin” operation corresponds to the “BEGIN SAVEPOINT” command, where the savepoint is given an explicit name that is part of the state of this object.

  • The NestedTransaction.commit() method corresponds to a “RELEASE SAVEPOINT” operation, using the savepoint identifier associated with this NestedTransaction.

  • The NestedTransaction.rollback() method corresponds to a “ROLLBACK TO SAVEPOINT” operation, using the savepoint identifier associated with this NestedTransaction.

The rationale for mimicking the semantics of an outer transaction in terms of savepoints so that code may deal with a “savepoint” transaction and an “outer” transaction in an agnostic way.

See also

Using SAVEPOINT - ORM version of the SAVEPOINT API.

Members

close(), commit(), rollback()

Class signature

class sqlalchemy.engine.NestedTransaction (sqlalchemy.engine.Transaction)

method sqlalchemy.engine.NestedTransaction.close() → None

inherited from the Transaction.close() method of Transaction

Close this Transaction.

If this transaction is the base transaction in a begin/commit nesting, the transaction will rollback(). Otherwise, the method returns.

This is used to cancel a Transaction without affecting the scope of an enclosing transaction.

method sqlalchemy.engine.NestedTransaction.commit() → None

inherited from the Transaction.commit() method of Transaction

Commit this Transaction.

The implementation of this may vary based on the type of transaction in use:

  • For a simple database transaction (e.g. RootTransaction), it corresponds to a COMMIT.

  • For a NestedTransaction, it corresponds to a “RELEASE SAVEPOINT” operation.

  • For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

method sqlalchemy.engine.NestedTransaction.rollback() → None

inherited from the Transaction.rollback() method of Transaction

Roll back this Transaction.

The implementation of this may vary based on the type of transaction in use:

  • For a simple database transaction (e.g. RootTransaction), it corresponds to a ROLLBACK.

  • For a NestedTransaction, it corresponds to a “ROLLBACK TO SAVEPOINT” operation.

  • For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

class sqlalchemy.engine.RootTransaction

Represent the “root” transaction on a Connection.

This corresponds to the current “BEGIN/COMMIT/ROLLBACK” that’s occurring for the Connection. The RootTransaction is created by calling upon the Connection.begin() method, and remains associated with the Connection throughout its active span. The current RootTransaction in use is accessible via the Connection.get_transaction method of Connection.

In 2.0 style use, the Connection also employs “autobegin” behavior that will create a new RootTransaction whenever a connection in a non-transactional state is used to emit commands on the DBAPI connection. The scope of the RootTransaction in 2.0 style use can be controlled using the Connection.commit() and Connection.rollback() methods.

Members

close(), commit(), rollback()

Class signature

class sqlalchemy.engine.RootTransaction (sqlalchemy.engine.Transaction)

method sqlalchemy.engine.RootTransaction.close() → None

inherited from the Transaction.close() method of Transaction

Close this Transaction.

If this transaction is the base transaction in a begin/commit nesting, the transaction will rollback(). Otherwise, the method returns.

This is used to cancel a Transaction without affecting the scope of an enclosing transaction.

method sqlalchemy.engine.RootTransaction.commit() → None

inherited from the Transaction.commit() method of Transaction

Commit this Transaction.

The implementation of this may vary based on the type of transaction in use:

  • For a simple database transaction (e.g. RootTransaction), it corresponds to a COMMIT.

  • For a NestedTransaction, it corresponds to a “RELEASE SAVEPOINT” operation.

  • For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

method sqlalchemy.engine.RootTransaction.rollback() → None

inherited from the Transaction.rollback() method of Transaction

Roll back this Transaction.

The implementation of this may vary based on the type of transaction in use:

  • For a simple database transaction (e.g. RootTransaction), it corresponds to a ROLLBACK.

  • For a NestedTransaction, it corresponds to a “ROLLBACK TO SAVEPOINT” operation.

  • For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

class sqlalchemy.engine.Transaction

Represent a database transaction in progress.

The Transaction object is procured by calling the Connection.begin() method of Connection:

from sqlalchemy import create_engine
engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")
connection = engine.connect()
trans = connection.begin()
connection.execute(text("insert into x (a, b) values (1, 2)"))
trans.commit()

The object provides rollback() and commit() methods in order to control transaction boundaries. It also implements a context manager interface so that the Python with statement can be used with the Connection.begin() method:

with connection.begin():
    connection.execute(text("insert into x (a, b) values (1, 2)"))

The Transaction object is not threadsafe.

Members

close(), commit(), rollback()

See also

Connection.begin()

Connection.begin_twophase()

Connection.begin_nested()

Class signature

class sqlalchemy.engine.Transaction (sqlalchemy.engine.util.TransactionalContext)

method sqlalchemy.engine.Transaction.close() → None

Close this Transaction.

If this transaction is the base transaction in a begin/commit nesting, the transaction will rollback(). Otherwise, the method returns.

This is used to cancel a Transaction without affecting the scope of an enclosing transaction.

method sqlalchemy.engine.Transaction.commit() → None

Commit this Transaction.

The implementation of this may vary based on the type of transaction in use:

  • For a simple database transaction (e.g. RootTransaction), it corresponds to a COMMIT.

  • For a NestedTransaction, it corresponds to a “RELEASE SAVEPOINT” operation.

  • For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

method sqlalchemy.engine.Transaction.rollback() → None

Roll back this Transaction.

The implementation of this may vary based on the type of transaction in use:

  • For a simple database transaction (e.g. RootTransaction), it corresponds to a ROLLBACK.

  • For a NestedTransaction, it corresponds to a “ROLLBACK TO SAVEPOINT” operation.

  • For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

class sqlalchemy.engine.TwoPhaseTransaction

Represent a two-phase transaction.

A new TwoPhaseTransaction object may be procured using the Connection.begin_twophase() method.

The interface is the same as that of Transaction with the addition of the prepare() method.

Members

close(), commit(), prepare(), rollback()

Class signature

class sqlalchemy.engine.TwoPhaseTransaction (sqlalchemy.engine.RootTransaction)

method sqlalchemy.engine.TwoPhaseTransaction.close() → None

inherited from the Transaction.close() method of Transaction

Close this Transaction.

If this transaction is the base transaction in a begin/commit nesting, the transaction will rollback(). Otherwise, the method returns.

This is used to cancel a Transaction without affecting the scope of an enclosing transaction.

method sqlalchemy.engine.TwoPhaseTransaction.commit() → None

inherited from the Transaction.commit() method of Transaction

Commit this Transaction.

The implementation of this may vary based on the type of transaction in use:

  • For a simple database transaction (e.g. RootTransaction), it corresponds to a COMMIT.

  • For a NestedTransaction, it corresponds to a “RELEASE SAVEPOINT” operation.

  • For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

method sqlalchemy.engine.TwoPhaseTransaction.prepare() → None

Prepare this TwoPhaseTransaction.

After a PREPARE, the transaction can be committed.

method sqlalchemy.engine.TwoPhaseTransaction.rollback() → None

inherited from the Transaction.rollback() method of Transaction

Roll back this Transaction.

The implementation of this may vary based on the type of transaction in use:

  • For a simple database transaction (e.g. RootTransaction), it corresponds to a ROLLBACK.

  • For a NestedTransaction, it corresponds to a “ROLLBACK TO SAVEPOINT” operation.

  • For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

Result Set API

Object Name Description

ChunkedIteratorResult

An IteratorResult that works from an iterator-producing callable.

CursorResult

A Result that is representing state from a DBAPI cursor.

FilterResult

A wrapper for a Result that returns objects other than Row objects, such as dictionaries or scalar objects.

FrozenResult

Represents a Result object in a “frozen” state suitable for caching.

IteratorResult

A Result that gets data from a Python iterator of Row objects or similar row-like data.

MappingResult

A wrapper for a Result that returns dictionary values rather than Row values.

MergedResult

A Result that is merged from any number of Result objects.

Result

Represent a set of database results.

Row

Represent a single result row.

RowMapping

A Mapping that maps column names and objects to Row values.

ScalarResult

A wrapper for a Result that returns scalar values rather than Row values.

TupleResult

A Result that’s typed as returning plain Python tuples instead of rows.
class sqlalchemy.engine.ChunkedIteratorResult

An IteratorResult that works from an iterator-producing callable.

The given chunks argument is a function that is given a number of rows to return in each chunk, or None for all rows. The function should then return an un-consumed iterator of lists, each list of the requested size.

The function can be called at any time again, in which case it should continue from the same result set but adjust the chunk size as given.

New in version 1.4.

Members

yield_per()

Class signature

class sqlalchemy.engine.ChunkedIteratorResult (sqlalchemy.engine.IteratorResult)

method sqlalchemy.engine.ChunkedIteratorResult.yield_per(num: int) → Self

Configure the row-fetching strategy to fetch num rows at a time.

This impacts the underlying behavior of the result when iterating over the result object, or otherwise making use of methods such as Result.fetchone() that return one row at a time. Data from the underlying cursor or other data source will be buffered up to this many rows in memory, and the buffered collection will then be yielded out one row at a time or as many rows are requested. Each time the buffer clears, it will be refreshed to this many rows or as many rows remain if fewer remain.

The Result.yield_per() method is generally used in conjunction with the Connection.execution_options.stream_results execution option, which will allow the database dialect in use to make use of a server side cursor, if the DBAPI supports a specific “server side cursor” mode separate from its default mode of operation.

Tip

Consider using the Connection.execution_options.yield_per execution option, which will simultaneously set Connection.execution_options.stream_results to ensure the use of server side cursors, as well as automatically invoke the Result.yield_per() method to establish a fixed row buffer size at once.

The Connection.execution_options.yield_per execution option is available for ORM operations, with Session-oriented use described at Fetching Large Result Sets with Yield Per. The Core-only version which works with Connection is new as of SQLAlchemy 1.4.40.

New in version 1.4.

Parameters:

num – number of rows to fetch each time the buffer is refilled. If set to a value below 1, fetches all rows for the next buffer.

See also

Using Server Side Cursors (a.k.a. stream results) - describes Core behavior for Result.yield_per()

Fetching Large Result Sets with Yield Per - in the ORM Querying Guide

class sqlalchemy.engine.CursorResult

A Result that is representing state from a DBAPI cursor.

Changed in version 1.4: The CursorResult` class replaces the previous ResultProxy interface. This classes are based on the Result calling API which provides an updated usage model and calling facade for SQLAlchemy Core and SQLAlchemy ORM.

Returns database rows via the Row class, which provides additional API features and behaviors on top of the raw data returned by the DBAPI. Through the use of filters such as the Result.scalars() method, other kinds of objects may also be returned.

See also

Using SELECT Statements - introductory material for accessing CursorResult and Row objects.

Members

all(), close(), columns(), fetchall(), fetchmany(), fetchone(), first(), freeze(), inserted_primary_key, inserted_primary_key_rows, is_insert, keys(), last_inserted_params(), last_updated_params(), lastrow_has_defaults(), lastrowid, mappings(), merge(), one(), one_or_none(), partitions(), postfetch_cols(), prefetch_cols(), returned_defaults, returned_defaults_rows, returns_rows, rowcount, scalar(), scalar_one(), scalar_one_or_none(), scalars(), splice_horizontally(), splice_vertically(), supports_sane_multi_rowcount(), supports_sane_rowcount(), t, tuples(), unique(), yield_per()

Class signature

class sqlalchemy.engine.CursorResult (sqlalchemy.engine.Result)

method sqlalchemy.engine.CursorResult.all() → Sequence[Row[_TP]]

inherited from the Result.all() method of Result

Return all rows in a sequence.

Closes the result set after invocation. Subsequent invocations will return an empty sequence.

New in version 1.4.

Returns:

a sequence of Row objects.

See also

Using Server Side Cursors (a.k.a. stream results) - How to stream a large result set without loading it completely in python.

method sqlalchemy.engine.CursorResult.close() → Any

Close this CursorResult.

This closes out the underlying DBAPI cursor corresponding to the statement execution, if one is still present. Note that the DBAPI cursor is automatically released when the CursorResult exhausts all available rows. CursorResult.close() is generally an optional method except in the case when discarding a CursorResult that still has additional rows pending for fetch.

After this method is called, it is no longer valid to call upon the fetch methods, which will raise a ResourceClosedError on subsequent use.

See also

Working with Engines and Connections

method sqlalchemy.engine.CursorResult.columns(*col_expressions: _KeyIndexType) → Self

inherited from the Result.columns() method of Result

Establish the columns that should be returned in each row.

This method may be used to limit the columns returned as well as to reorder them. The given list of expressions are normally a series of integers or string key names. They may also be appropriate ColumnElement objects which correspond to a given statement construct.

Changed in version 2.0: Due to a bug in 1.4, the Result.columns() method had an incorrect behavior where calling upon the method with just one index would cause the Result object to yield scalar values rather than Row objects. In version 2.0, this behavior has been corrected such that calling upon Result.columns() with a single index will produce a Result object that continues to yield Row objects, which include only a single column.

E.g.:

statement = select(table.c.x, table.c.y, table.c.z)
result = connection.execute(statement)

for z, y in result.columns('z', 'y'):
    # ...

Example of using the column objects from the statement itself:

for z, y in result.columns(
        statement.selected_columns.c.z,
        statement.selected_columns.c.y
):
    # ...

New in version 1.4.

Parameters:

*col_expressions – indicates columns to be returned. Elements may be integer row indexes, string column names, or appropriate ColumnElement objects corresponding to a select construct.

Returns:

this Result object with the modifications given.

method sqlalchemy.engine.CursorResult.fetchall() → Sequence[Row[_TP]]

inherited from the Result.fetchall() method of Result

A synonym for the Result.all() method.

method sqlalchemy.engine.CursorResult.fetchmany(size: int | None = None) → Sequence[Row[_TP]]

inherited from the Result.fetchmany() method of Result

Fetch many rows.

When all rows are exhausted, returns an empty sequence.

This method is provided for backwards compatibility with SQLAlchemy 1.x.x.

To fetch rows in groups, use the Result.partitions() method.

Returns:

a sequence of Row objects.

See also

Result.partitions()

method sqlalchemy.engine.CursorResult.fetchone() → Row[_TP] | None

inherited from the Result.fetchone() method of Result

Fetch one row.

When all rows are exhausted, returns None.

This method is provided for backwards compatibility with SQLAlchemy 1.x.x.

To fetch the first row of a result only, use the Result.first() method. To iterate through all rows, iterate the Result object directly.

Returns:

a Row object if no filters are applied, or None if no rows remain.

method sqlalchemy.engine.CursorResult.first() → Row[_TP] | None

inherited from the Result.first() method of Result

Fetch the first row or None if no row is present.

Closes the result set and discards remaining rows.

Note

This method returns one row, e.g. tuple, by default. To return exactly one single scalar value, that is, the first column of the first row, use the Result.scalar() method, or combine Result.scalars() and Result.first().

Additionally, in contrast to the behavior of the legacy ORM Query.first() method, no limit is applied to the SQL query which was invoked to produce this Result; for a DBAPI driver that buffers results in memory before yielding rows, all rows will be sent to the Python process and all but the first row will be discarded.

See also

ORM Query Unified with Core Select

Returns:

a Row object, or None if no rows remain.

See also

Result.scalar()

Result.one()

method sqlalchemy.engine.CursorResult.freeze() → FrozenResult[_TP]

inherited from the Result.freeze() method of Result

Return a callable object that will produce copies of this Result when invoked.

The callable object returned is an instance of FrozenResult.

This is used for result set caching. The method must be called on the result when it has been unconsumed, and calling the method will consume the result fully. When the FrozenResult is retrieved from a cache, it can be called any number of times where it will produce a new Result object each time against its stored set of rows.

See also

Re-Executing Statements - example usage within the ORM to implement a result-set cache.

attribute sqlalchemy.engine.CursorResult.inserted_primary_key

Return the primary key for the row just inserted.

The return value is a Row object representing a named tuple of primary key values in the order in which the primary key columns are configured in the source Table.

Changed in version 1.4.8: - the CursorResult.inserted_primary_key value is now a named tuple via the Row class, rather than a plain tuple.

This accessor only applies to single row insert() constructs which did not explicitly specify Insert.returning(). Support for multirow inserts, while not yet available for most backends, would be accessed using the CursorResult.inserted_primary_key_rows accessor.

Note that primary key columns which specify a server_default clause, or otherwise do not qualify as “autoincrement” columns (see the notes at Column), and were generated using the database-side default, will appear in this list as None unless the backend supports “returning” and the insert statement executed with the “implicit returning” enabled.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() construct.

attribute sqlalchemy.engine.CursorResult.inserted_primary_key_rows

Return the value of CursorResult.inserted_primary_key as a row contained within a list; some dialects may support a multiple row form as well.

Note

As indicated below, in current SQLAlchemy versions this accessor is only useful beyond what’s already supplied by CursorResult.inserted_primary_key when using the psycopg2 dialect. Future versions hope to generalize this feature to more dialects.

This accessor is added to support dialects that offer the feature that is currently implemented by the Psycopg2 Fast Execution Helpers feature, currently only the psycopg2 dialect, which provides for many rows to be INSERTed at once while still retaining the behavior of being able to return server-generated primary key values.

  • When using the psycopg2 dialect, or other dialects that may support “fast executemany” style inserts in upcoming releases : When invoking an INSERT statement while passing a list of rows as the second argument to Connection.execute(), this accessor will then provide a list of rows, where each row contains the primary key value for each row that was INSERTed.

  • When using all other dialects / backends that don’t yet support this feature: This accessor is only useful for single row INSERT statements, and returns the same information as that of the CursorResult.inserted_primary_key within a single-element list. When an INSERT statement is executed in conjunction with a list of rows to be INSERTed, the list will contain one row per row inserted in the statement, however it will contain None for any server-generated values.

Future releases of SQLAlchemy will further generalize the “fast execution helper” feature of psycopg2 to suit other dialects, thus allowing this accessor to be of more general use.

New in version 1.4.

See also

CursorResult.inserted_primary_key

attribute sqlalchemy.engine.CursorResult.is_insert

True if this CursorResult is the result of a executing an expression language compiled insert() construct.

When True, this implies that the inserted_primary_key attribute is accessible, assuming the statement did not include a user defined “returning” construct.

method sqlalchemy.engine.CursorResult.keys() → RMKeyView

inherited from the sqlalchemy.engine._WithKeys.keys method of sqlalchemy.engine._WithKeys

Return an iterable view which yields the string keys that would be represented by each Row.

The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.

The view also can be tested for key containment using the Python in operator, which will test both for the string keys represented in the view, as well as for alternate keys such as column objects.

Changed in version 1.4: a key view object is returned rather than a plain list.

method sqlalchemy.engine.CursorResult.last_inserted_params()

Return the collection of inserted parameters from this execution.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() construct.

method sqlalchemy.engine.CursorResult.last_updated_params()

Return the collection of updated parameters from this execution.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an update() construct.

method sqlalchemy.engine.CursorResult.lastrow_has_defaults()

Return lastrow_has_defaults() from the underlying ExecutionContext.

See ExecutionContext for details.

attribute sqlalchemy.engine.CursorResult.lastrowid

Return the ‘lastrowid’ accessor on the DBAPI cursor.

This is a DBAPI specific method and is only functional for those backends which support it, for statements where it is appropriate. It’s behavior is not consistent across backends.

Usage of this method is normally unnecessary when using insert() expression constructs; the CursorResult.inserted_primary_key attribute provides a tuple of primary key values for a newly inserted row, regardless of database backend.

method sqlalchemy.engine.CursorResult.mappings() → MappingResult

inherited from the Result.mappings() method of Result

Apply a mappings filter to returned rows, returning an instance of MappingResult.

When this filter is applied, fetching rows will return RowMapping objects instead of Row objects.

New in version 1.4.

Returns:

a new MappingResult filtering object referring to this Result object.

method sqlalchemy.engine.CursorResult.merge(*others: Result[Any]) → MergedResult[Any]

Merge this Result with other compatible result objects.

The object returned is an instance of MergedResult, which will be composed of iterators from the given result objects.

The new result will use the metadata from this result object. The subsequent result objects must be against an identical set of result / cursor metadata, otherwise the behavior is undefined.

method sqlalchemy.engine.CursorResult.one() → Row[_TP]

inherited from the Result.one() method of Result

Return exactly one row or raise an exception.

Raises NoResultFound if the result returns no rows, or MultipleResultsFound if multiple rows would be returned.

Note

This method returns one row, e.g. tuple, by default. To return exactly one single scalar value, that is, the first column of the first row, use the Result.scalar_one() method, or combine Result.scalars() and Result.one().

New in version 1.4.

Returns:

The first Row.

Raises:

MultipleResultsFound, NoResultFound

See also

Result.first()

Result.one_or_none()

Result.scalar_one()

method sqlalchemy.engine.CursorResult.one_or_none() → Row[_TP] | None

inherited from the Result.one_or_none() method of Result

Return at most one result or raise an exception.

Returns None if the result has no rows. Raises MultipleResultsFound if multiple rows are returned.

New in version 1.4.

Returns:

The first Row or None if no row is available.

Raises:

MultipleResultsFound

See also

Result.first()

Result.one()

method sqlalchemy.engine.CursorResult.partitions(size: int | None = None) → Iterator[Sequence[Row[_TP]]]

inherited from the Result.partitions() method of Result

Iterate through sub-lists of rows of the size given.

Each list will be of the size given, excluding the last list to be yielded, which may have a small number of rows. No empty lists will be yielded.

The result object is automatically closed when the iterator is fully consumed.

Note that the backend driver will usually buffer the entire result ahead of time unless the Connection.execution_options.stream_results execution option is used indicating that the driver should not pre-buffer results, if possible. Not all drivers support this option and the option is silently ignored for those who do not.

When using the ORM, the Result.partitions() method is typically more effective from a memory perspective when it is combined with use of the yield_per execution option, which instructs both the DBAPI driver to use server side cursors, if available, as well as instructs the ORM loading internals to only build a certain amount of ORM objects from a result at a time before yielding them out.

New in version 1.4.

Parameters:

size – indicate the maximum number of rows to be present in each list yielded. If None, makes use of the value set by the Result.yield_per(), method, if it were called, or the Connection.execution_options.yield_per execution option, which is equivalent in this regard. If yield_per weren’t set, it makes use of the Result.fetchmany() default, which may be backend specific and not well defined.

Returns:

iterator of lists

See also

Using Server Side Cursors (a.k.a. stream results)

Fetching Large Result Sets with Yield Per - in the ORM Querying Guide

method sqlalchemy.engine.CursorResult.postfetch_cols()

Return postfetch_cols() from the underlying ExecutionContext.

See ExecutionContext for details.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() or update() construct.

method sqlalchemy.engine.CursorResult.prefetch_cols()

Return prefetch_cols() from the underlying ExecutionContext.

See ExecutionContext for details.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() or update() construct.

attribute sqlalchemy.engine.CursorResult.returned_defaults

Return the values of default columns that were fetched using the ValuesBase.return_defaults() feature.

The value is an instance of Row, or None if ValuesBase.return_defaults() was not used or if the backend does not support RETURNING.

See also

ValuesBase.return_defaults()

attribute sqlalchemy.engine.CursorResult.returned_defaults_rows

Return a list of rows each containing the values of default columns that were fetched using the ValuesBase.return_defaults() feature.

The return value is a list of Row objects.

New in version 1.4.

attribute sqlalchemy.engine.CursorResult.returns_rows

True if this CursorResult returns zero or more rows.

I.e. if it is legal to call the methods CursorResult.fetchone(), CursorResult.fetchmany() CursorResult.fetchall().

Overall, the value of CursorResult.returns_rows should always be synonymous with whether or not the DBAPI cursor had a .description attribute, indicating the presence of result columns, noting that a cursor that returns zero rows still has a .description if a row-returning statement was emitted.

This attribute should be True for all results that are against SELECT statements, as well as for DML statements INSERT/UPDATE/DELETE that use RETURNING. For INSERT/UPDATE/DELETE statements that were not using RETURNING, the value will usually be False, however there are some dialect-specific exceptions to this, such as when using the MSSQL / pyodbc dialect a SELECT is emitted inline in order to retrieve an inserted primary key value.

attribute sqlalchemy.engine.CursorResult.rowcount

Return the ‘rowcount’ for this result.

The primary purpose of ‘rowcount’ is to report the number of rows matched by the WHERE criterion of an UPDATE or DELETE statement executed once (i.e. for a single parameter set), which may then be compared to the number of rows expected to be updated or deleted as a means of asserting data integrity.

This attribute is transferred from the cursor.rowcount attribute of the DBAPI before the cursor is closed, to support DBAPIs that don’t make this value available after cursor close. Some DBAPIs may offer meaningful values for other kinds of statements, such as INSERT and SELECT statements as well. In order to retrieve cursor.rowcount for these statements, set the Connection.execution_options.preserve_rowcount execution option to True, which will cause the cursor.rowcount value to be unconditionally memoized before any results are returned or the cursor is closed, regardless of statement type.

For cases where the DBAPI does not support rowcount for a particular kind of statement and/or execution, the returned value will be -1, which is delivered directly from the DBAPI and is part of PEP 249. All DBAPIs should support rowcount for single-parameter-set UPDATE and DELETE statements, however.

Note

Notes regarding CursorResult.rowcount:

  • This attribute returns the number of rows matched, which is not necessarily the same as the number of rows that were actually modified. For example, an UPDATE statement may have no net change on a given row if the SET values given are the same as those present in the row already. Such a row would be matched but not modified. On backends that feature both styles, such as MySQL, rowcount is configured to return the match count in all cases.

  • CursorResult.rowcount in the default case is only useful in conjunction with an UPDATE or DELETE statement, and only with a single set of parameters. For other kinds of statements, SQLAlchemy will not attempt to pre-memoize the value unless the Connection.execution_options.preserve_rowcount execution option is used. Note that contrary to PEP 249, many DBAPIs do not support rowcount values for statements that are not UPDATE or DELETE, particularly when rows are being returned which are not fully pre-buffered. DBAPIs that dont support rowcount for a particular kind of statement should return the value -1 for such statements.

  • CursorResult.rowcount may not be meaningful when executing a single statement with multiple parameter sets (i.e. an executemany). Most DBAPIs do not sum “rowcount” values across multiple parameter sets and will return -1 when accessed.

  • SQLAlchemy’s “Insert Many Values” Behavior for INSERT statements feature does support a correct population of CursorResult.rowcount when the Connection.execution_options.preserve_rowcount execution option is set to True.

  • Statements that use RETURNING may not support rowcount, returning a -1 value instead.

See also

Getting Affected Row Count from UPDATE, DELETE - in the SQLAlchemy Unified Tutorial

Connection.execution_options.preserve_rowcount

method sqlalchemy.engine.CursorResult.scalar() → Any

inherited from the Result.scalar() method of Result

Fetch the first column of the first row, and close the result set.

Returns None if there are no rows to fetch.

No validation is performed to test if additional rows remain.

After calling this method, the object is fully closed, e.g. the CursorResult.close() method will have been called.

Returns:

a Python scalar value, or None if no rows remain.

method sqlalchemy.engine.CursorResult.scalar_one() → Any

inherited from the Result.scalar_one() method of Result

Return exactly one scalar result or raise an exception.

This is equivalent to calling Result.scalars() and then Result.one().

See also

Result.one()

Result.scalars()

method sqlalchemy.engine.CursorResult.scalar_one_or_none() → Any | None

inherited from the Result.scalar_one_or_none() method of Result

Return exactly one scalar result or None.

This is equivalent to calling Result.scalars() and then Result.one_or_none().

See also

Result.one_or_none()

Result.scalars()

method sqlalchemy.engine.CursorResult.scalars(index: _KeyIndexType = 0) → ScalarResult[Any]

inherited from the Result.scalars() method of Result

Return a ScalarResult filtering object which will return single elements rather than Row objects.

E.g.:

>>> result = conn.execute(text("select int_id from table"))
>>> result.scalars().all()
[1, 2, 3]

When results are fetched from the ScalarResult filtering object, the single column-row that would be returned by the Result is instead returned as the column’s value.

New in version 1.4.

Parameters:

index – integer or row key indicating the column to be fetched from each row, defaults to 0 indicating the first column.

Returns:

a new ScalarResult filtering object referring to this Result object.

method sqlalchemy.engine.CursorResult.splice_horizontally(other)

Return a new CursorResult that “horizontally splices” together the rows of this CursorResult with that of another CursorResult.

Tip

This method is for the benefit of the SQLAlchemy ORM and is not intended for general use.

“horizontally splices” means that for each row in the first and second result sets, a new row that concatenates the two rows together is produced, which then becomes the new row. The incoming CursorResult must have the identical number of rows. It is typically expected that the two result sets come from the same sort order as well, as the result rows are spliced together based on their position in the result.

The expected use case here is so that multiple INSERT..RETURNING statements (which definitely need to be sorted) against different tables can produce a single result that looks like a JOIN of those two tables.

E.g.:

r1 = connection.execute(
    users.insert().returning(
        users.c.user_name,
        users.c.user_id,
        sort_by_parameter_order=True
    ),
    user_values
)

r2 = connection.execute(
    addresses.insert().returning(
        addresses.c.address_id,
        addresses.c.address,
        addresses.c.user_id,
        sort_by_parameter_order=True
    ),
    address_values
)

rows = r1.splice_horizontally(r2).all()
assert (
    rows ==
    [
        ("john", 1, 1, "foo@bar.com", 1),
        ("jack", 2, 2, "bar@bat.com", 2),
    ]
)

New in version 2.0.

See also

CursorResult.splice_vertically()

method sqlalchemy.engine.CursorResult.splice_vertically(other)

Return a new CursorResult that “vertically splices”, i.e. “extends”, the rows of this CursorResult with that of another CursorResult.

Tip

This method is for the benefit of the SQLAlchemy ORM and is not intended for general use.

“vertically splices” means the rows of the given result are appended to the rows of this cursor result. The incoming CursorResult must have rows that represent the identical list of columns in the identical order as they are in this CursorResult.

New in version 2.0.

See also

CursorResult.splice_horizontally()

method sqlalchemy.engine.CursorResult.supports_sane_multi_rowcount()

Return supports_sane_multi_rowcount from the dialect.

See CursorResult.rowcount for background.

method sqlalchemy.engine.CursorResult.supports_sane_rowcount()

Return supports_sane_rowcount from the dialect.

See CursorResult.rowcount for background.

attribute sqlalchemy.engine.CursorResult.t

inherited from the Result.t attribute of Result

Apply a “typed tuple” typing filter to returned rows.

The Result.t attribute is a synonym for calling the Result.tuples() method.

New in version 2.0.

method sqlalchemy.engine.CursorResult.tuples() → TupleResult[_TP]

inherited from the Result.tuples() method of Result

Apply a “typed tuple” typing filter to returned rows.

This method returns the same Result object at runtime, however annotates as returning a TupleResult object that will indicate to PEP 484 typing tools that plain typed Tuple instances are returned rather than rows. This allows tuple unpacking and __getitem__ access of Row objects to by typed, for those cases where the statement invoked itself included typing information.

New in version 2.0.

Returns:

the TupleResult type at typing time.

See also

Result.t - shorter synonym

Row._t - Row version

method sqlalchemy.engine.CursorResult.unique(strategy: Callable[[Any], Any] | None = None) → Self

inherited from the Result.unique() method of Result

Apply unique filtering to the objects returned by this Result.

When this filter is applied with no arguments, the rows or objects returned will filtered such that each row is returned uniquely. The algorithm used to determine this uniqueness is by default the Python hashing identity of the whole tuple. In some cases a specialized per-entity hashing scheme may be used, such as when using the ORM, a scheme is applied which works against the primary key identity of returned objects.

The unique filter is applied after all other filters, which means if the columns returned have been refined using a method such as the Result.columns() or Result.scalars() method, the uniquing is applied to only the column or columns returned. This occurs regardless of the order in which these methods have been called upon the Result object.

The unique filter also changes the calculus used for methods like Result.fetchmany() and Result.partitions(). When using Result.unique(), these methods will continue to yield the number of rows or objects requested, after uniquing has been applied. However, this necessarily impacts the buffering behavior of the underlying cursor or datasource, such that multiple underlying calls to cursor.fetchmany() may be necessary in order to accumulate enough objects in order to provide a unique collection of the requested size.

Parameters:

strategy – a callable that will be applied to rows or objects being iterated, which should return an object that represents the unique value of the row. A Python set() is used to store these identities. If not passed, a default uniqueness strategy is used which may have been assembled by the source of this Result object.

method sqlalchemy.engine.CursorResult.yield_per(num: int) → Self

Configure the row-fetching strategy to fetch num rows at a time.

This impacts the underlying behavior of the result when iterating over the result object, or otherwise making use of methods such as Result.fetchone() that return one row at a time. Data from the underlying cursor or other data source will be buffered up to this many rows in memory, and the buffered collection will then be yielded out one row at a time or as many rows are requested. Each time the buffer clears, it will be refreshed to this many rows or as many rows remain if fewer remain.

The Result.yield_per() method is generally used in conjunction with the Connection.execution_options.stream_results execution option, which will allow the database dialect in use to make use of a server side cursor, if the DBAPI supports a specific “server side cursor” mode separate from its default mode of operation.

Tip

Consider using the Connection.execution_options.yield_per execution option, which will simultaneously set Connection.execution_options.stream_results to ensure the use of server side cursors, as well as automatically invoke the Result.yield_per() method to establish a fixed row buffer size at once.

The Connection.execution_options.yield_per execution option is available for ORM operations, with Session-oriented use described at Fetching Large Result Sets with Yield Per. The Core-only version which works with Connection is new as of SQLAlchemy 1.4.40.

New in version 1.4.

Parameters:

num – number of rows to fetch each time the buffer is refilled. If set to a value below 1, fetches all rows for the next buffer.

See also

Using Server Side Cursors (a.k.a. stream results) - describes Core behavior for Result.yield_per()

Fetching Large Result Sets with Yield Per - in the ORM Querying Guide

class sqlalchemy.engine.FilterResult

A wrapper for a Result that returns objects other than Row objects, such as dictionaries or scalar objects.

FilterResult is the common base for additional result APIs including MappingResult, ScalarResult and AsyncResult.

Members

close(), closed, yield_per()

Class signature

class sqlalchemy.engine.FilterResult (sqlalchemy.engine.ResultInternal)

method sqlalchemy.engine.FilterResult.close() → None

Close this FilterResult.

New in version 1.4.43.

attribute sqlalchemy.engine.FilterResult.closed

Return True if the underlying Result reports closed

New in version 1.4.43.

method sqlalchemy.engine.FilterResult.yield_per(num: int) → Self

Configure the row-fetching strategy to fetch num rows at a time.

The FilterResult.yield_per() method is a pass through to the Result.yield_per() method. See that method’s documentation for usage notes.

New in version 1.4.40: - added FilterResult.yield_per() so that the method is available on all result set implementations

See also

Using Server Side Cursors (a.k.a. stream results) - describes Core behavior for Result.yield_per()

Fetching Large Result Sets with Yield Per - in the ORM Querying Guide

class sqlalchemy.engine.FrozenResult

Represents a Result object in a “frozen” state suitable for caching.

The FrozenResult object is returned from the Result.freeze() method of any Result object.

A new iterable Result object is generated from a fixed set of data each time the FrozenResult is invoked as a callable:

result = connection.execute(query)

frozen = result.freeze()

unfrozen_result_one = frozen()

for row in unfrozen_result_one:
    print(row)

unfrozen_result_two = frozen()
rows = unfrozen_result_two.all()

# ... etc

New in version 1.4.

See also

Re-Executing Statements - example usage within the ORM to implement a result-set cache.

merge_frozen_result() - ORM function to merge a frozen result back into a Session.

Class signature

class sqlalchemy.engine.FrozenResult (typing.Generic)

class sqlalchemy.engine.IteratorResult

A Result that gets data from a Python iterator of Row objects or similar row-like data.

New in version 1.4.

Members

closed

Class signature

class sqlalchemy.engine.IteratorResult (sqlalchemy.engine.Result)

attribute sqlalchemy.engine.IteratorResult.closed

Return True if this IteratorResult has been closed

New in version 1.4.43.

class sqlalchemy.engine.MergedResult

A Result that is merged from any number of Result objects.

Returned by the Result.merge() method.

New in version 1.4.

Class signature

class sqlalchemy.engine.MergedResult (sqlalchemy.engine.IteratorResult)

class sqlalchemy.engine.Result

Represent a set of database results.

New in version 1.4: The Result object provides a completely updated usage model and calling facade for SQLAlchemy Core and SQLAlchemy ORM. In Core, it forms the basis of the CursorResult object which replaces the previous ResultProxy interface. When using the ORM, a higher level object called ChunkedIteratorResult is normally used.

Note

In SQLAlchemy 1.4 and above, this object is used for ORM results returned by Session.execute(), which can yield instances of ORM mapped objects either individually or within tuple-like rows. Note that the Result object does not deduplicate instances or rows automatically as is the case with the legacy Query object. For in-Python de-duplication of instances or rows, use the Result.unique() modifier method.

See also

Fetching Rows - in the SQLAlchemy Unified Tutorial

Members

all(), close(), closed, columns(), fetchall(), fetchmany(), fetchone(), first(), freeze(), keys(), mappings(), merge(), one(), one_or_none(), partitions(), scalar(), scalar_one(), scalar_one_or_none(), scalars(), t, tuples(), unique(), yield_per()

Class signature

class sqlalchemy.engine.Result (sqlalchemy.engine._WithKeys, sqlalchemy.engine.ResultInternal)

method sqlalchemy.engine.Result.all() → Sequence[Row[_TP]]

Return all rows in a sequence.

Closes the result set after invocation. Subsequent invocations will return an empty sequence.

New in version 1.4.

Returns:

a sequence of Row objects.

See also

Using Server Side Cursors (a.k.a. stream results) - How to stream a large result set without loading it completely in python.

method sqlalchemy.engine.Result.close() → None

close this Result.

The behavior of this method is implementation specific, and is not implemented by default. The method should generally end the resources in use by the result object and also cause any subsequent iteration or row fetching to raise ResourceClosedError.

New in version 1.4.27: - .close() was previously not generally available for all Result classes, instead only being available on the CursorResult returned for Core statement executions. As most other result objects, namely the ones used by the ORM, are proxying a CursorResult in any case, this allows the underlying cursor result to be closed from the outside facade for the case when the ORM query is using the yield_per execution option where it does not immediately exhaust and autoclose the database cursor.

attribute sqlalchemy.engine.Result.closed

return True if this Result reports .closed

New in version 1.4.43.

method sqlalchemy.engine.Result.columns(*col_expressions: _KeyIndexType) → Self

Establish the columns that should be returned in each row.

This method may be used to limit the columns returned as well as to reorder them. The given list of expressions are normally a series of integers or string key names. They may also be appropriate ColumnElement objects which correspond to a given statement construct.

Changed in version 2.0: Due to a bug in 1.4, the Result.columns() method had an incorrect behavior where calling upon the method with just one index would cause the Result object to yield scalar values rather than Row objects. In version 2.0, this behavior has been corrected such that calling upon Result.columns() with a single index will produce a Result object that continues to yield Row objects, which include only a single column.

E.g.:

statement = select(table.c.x, table.c.y, table.c.z)
result = connection.execute(statement)

for z, y in result.columns('z', 'y'):
    # ...

Example of using the column objects from the statement itself:

for z, y in result.columns(
        statement.selected_columns.c.z,
        statement.selected_columns.c.y
):
    # ...

New in version 1.4.

Parameters:

*col_expressions – indicates columns to be returned. Elements may be integer row indexes, string column names, or appropriate ColumnElement objects corresponding to a select construct.

Returns:

this Result object with the modifications given.

method sqlalchemy.engine.Result.fetchall() → Sequence[Row[_TP]]

A synonym for the Result.all() method.

method sqlalchemy.engine.Result.fetchmany(size: int | None = None) → Sequence[Row[_TP]]

Fetch many rows.

When all rows are exhausted, returns an empty sequence.

This method is provided for backwards compatibility with SQLAlchemy 1.x.x.

To fetch rows in groups, use the Result.partitions() method.

Returns:

a sequence of Row objects.

See also

Result.partitions()

method sqlalchemy.engine.Result.fetchone() → Row[_TP] | None

Fetch one row.

When all rows are exhausted, returns None.

This method is provided for backwards compatibility with SQLAlchemy 1.x.x.

To fetch the first row of a result only, use the Result.first() method. To iterate through all rows, iterate the Result object directly.

Returns:

a Row object if no filters are applied, or None if no rows remain.

method sqlalchemy.engine.Result.first() → Row[_TP] | None

Fetch the first row or None if no row is present.

Closes the result set and discards remaining rows.

Note

This method returns one row, e.g. tuple, by default. To return exactly one single scalar value, that is, the first column of the first row, use the Result.scalar() method, or combine Result.scalars() and Result.first().

Additionally, in contrast to the behavior of the legacy ORM Query.first() method, no limit is applied to the SQL query which was invoked to produce this Result; for a DBAPI driver that buffers results in memory before yielding rows, all rows will be sent to the Python process and all but the first row will be discarded.

See also

ORM Query Unified with Core Select

Returns:

a Row object, or None if no rows remain.

See also

Result.scalar()

Result.one()

method sqlalchemy.engine.Result.freeze() → FrozenResult[_TP]

Return a callable object that will produce copies of this Result when invoked.

The callable object returned is an instance of FrozenResult.

This is used for result set caching. The method must be called on the result when it has been unconsumed, and calling the method will consume the result fully. When the FrozenResult is retrieved from a cache, it can be called any number of times where it will produce a new Result object each time against its stored set of rows.

See also

Re-Executing Statements - example usage within the ORM to implement a result-set cache.

method sqlalchemy.engine.Result.keys() → RMKeyView

inherited from the sqlalchemy.engine._WithKeys.keys method of sqlalchemy.engine._WithKeys

Return an iterable view which yields the string keys that would be represented by each Row.

The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.

The view also can be tested for key containment using the Python in operator, which will test both for the string keys represented in the view, as well as for alternate keys such as column objects.

Changed in version 1.4: a key view object is returned rather than a plain list.

method sqlalchemy.engine.Result.mappings() → MappingResult

Apply a mappings filter to returned rows, returning an instance of MappingResult.

When this filter is applied, fetching rows will return RowMapping objects instead of Row objects.

New in version 1.4.

Returns:

a new MappingResult filtering object referring to this Result object.

method sqlalchemy.engine.Result.merge(*others: Result[Any]) → MergedResult[_TP]

Merge this Result with other compatible result objects.

The object returned is an instance of MergedResult, which will be composed of iterators from the given result objects.

The new result will use the metadata from this result object. The subsequent result objects must be against an identical set of result / cursor metadata, otherwise the behavior is undefined.

method sqlalchemy.engine.Result.one() → Row[_TP]

Return exactly one row or raise an exception.

Raises NoResultFound if the result returns no rows, or MultipleResultsFound if multiple rows would be returned.

Note

This method returns one row, e.g. tuple, by default. To return exactly one single scalar value, that is, the first column of the first row, use the Result.scalar_one() method, or combine Result.scalars() and Result.one().

New in version 1.4.

Returns:

The first Row.

Raises:

MultipleResultsFound, NoResultFound

See also

Result.first()

Result.one_or_none()

Result.scalar_one()

method sqlalchemy.engine.Result.one_or_none() → Row[_TP] | None

Return at most one result or raise an exception.

Returns None if the result has no rows. Raises MultipleResultsFound if multiple rows are returned.

New in version 1.4.

Returns:

The first Row or None if no row is available.

Raises:

MultipleResultsFound

See also

Result.first()

Result.one()

method sqlalchemy.engine.Result.partitions(size: int | None = None) → Iterator[Sequence[Row[_TP]]]

Iterate through sub-lists of rows of the size given.

Each list will be of the size given, excluding the last list to be yielded, which may have a small number of rows. No empty lists will be yielded.

The result object is automatically closed when the iterator is fully consumed.

Note that the backend driver will usually buffer the entire result ahead of time unless the Connection.execution_options.stream_results execution option is used indicating that the driver should not pre-buffer results, if possible. Not all drivers support this option and the option is silently ignored for those who do not.

When using the ORM, the Result.partitions() method is typically more effective from a memory perspective when it is combined with use of the yield_per execution option, which instructs both the DBAPI driver to use server side cursors, if available, as well as instructs the ORM loading internals to only build a certain amount of ORM objects from a result at a time before yielding them out.

New in version 1.4.

Parameters:

size – indicate the maximum number of rows to be present in each list yielded. If None, makes use of the value set by the Result.yield_per(), method, if it were called, or the Connection.execution_options.yield_per execution option, which is equivalent in this regard. If yield_per weren’t set, it makes use of the Result.fetchmany() default, which may be backend specific and not well defined.

Returns:

iterator of lists

See also

Using Server Side Cursors (a.k.a. stream results)

Fetching Large Result Sets with Yield Per - in the ORM Querying Guide

method sqlalchemy.engine.Result.scalar() → Any

Fetch the first column of the first row, and close the result set.

Returns None if there are no rows to fetch.

No validation is performed to test if additional rows remain.

After calling this method, the object is fully closed, e.g. the CursorResult.close() method will have been called.

Returns:

a Python scalar value, or None if no rows remain.

method sqlalchemy.engine.Result.scalar_one() → Any

Return exactly one scalar result or raise an exception.

This is equivalent to calling Result.scalars() and then Result.one().

See also

Result.one()

Result.scalars()

method sqlalchemy.engine.Result.scalar_one_or_none() → Any | None

Return exactly one scalar result or None.

This is equivalent to calling Result.scalars() and then Result.one_or_none().

See also

Result.one_or_none()

Result.scalars()

method sqlalchemy.engine.Result.scalars(index: _KeyIndexType = 0) → ScalarResult[Any]

Return a ScalarResult filtering object which will return single elements rather than Row objects.

E.g.:

>>> result = conn.execute(text("select int_id from table"))
>>> result.scalars().all()
[1, 2, 3]

When results are fetched from the ScalarResult filtering object, the single column-row that would be returned by the Result is instead returned as the column’s value.

New in version 1.4.

Parameters:

index – integer or row key indicating the column to be fetched from each row, defaults to 0 indicating the first column.

Returns:

a new ScalarResult filtering object referring to this Result object.

attribute sqlalchemy.engine.Result.t

Apply a “typed tuple” typing filter to returned rows.

The Result.t attribute is a synonym for calling the Result.tuples() method.

New in version 2.0.

method sqlalchemy.engine.Result.tuples() → TupleResult[_TP]

Apply a “typed tuple” typing filter to returned rows.

This method returns the same Result object at runtime, however annotates as returning a TupleResult object that will indicate to PEP 484 typing tools that plain typed Tuple instances are returned rather than rows. This allows tuple unpacking and __getitem__ access of Row objects to by typed, for those cases where the statement invoked itself included typing information.

New in version 2.0.

Returns:

the TupleResult type at typing time.

See also

Result.t - shorter synonym

Row._t - Row version

method sqlalchemy.engine.Result.unique(strategy: Callable[[Any], Any] | None = None) → Self

Apply unique filtering to the objects returned by this Result.

When this filter is applied with no arguments, the rows or objects returned will filtered such that each row is returned uniquely. The algorithm used to determine this uniqueness is by default the Python hashing identity of the whole tuple. In some cases a specialized per-entity hashing scheme may be used, such as when using the ORM, a scheme is applied which works against the primary key identity of returned objects.

The unique filter is applied after all other filters, which means if the columns returned have been refined using a method such as the Result.columns() or Result.scalars() method, the uniquing is applied to only the column or columns returned. This occurs regardless of the order in which these methods have been called upon the Result object.

The unique filter also changes the calculus used for methods like Result.fetchmany() and Result.partitions(). When using Result.unique(), these methods will continue to yield the number of rows or objects requested, after uniquing has been applied. However, this necessarily impacts the buffering behavior of the underlying cursor or datasource, such that multiple underlying calls to cursor.fetchmany() may be necessary in order to accumulate enough objects in order to provide a unique collection of the requested size.

Parameters:

strategy – a callable that will be applied to rows or objects being iterated, which should return an object that represents the unique value of the row. A Python set() is used to store these identities. If not passed, a default uniqueness strategy is used which may have been assembled by the source of this Result object.

method sqlalchemy.engine.Result.yield_per(num: int) → Self

Configure the row-fetching strategy to fetch num rows at a time.

This impacts the underlying behavior of the result when iterating over the result object, or otherwise making use of methods such as Result.fetchone() that return one row at a time. Data from the underlying cursor or other data source will be buffered up to this many rows in memory, and the buffered collection will then be yielded out one row at a time or as many rows are requested. Each time the buffer clears, it will be refreshed to this many rows or as many rows remain if fewer remain.

The Result.yield_per() method is generally used in conjunction with the Connection.execution_options.stream_results execution option, which will allow the database dialect in use to make use of a server side cursor, if the DBAPI supports a specific “server side cursor” mode separate from its default mode of operation.

Tip

Consider using the Connection.execution_options.yield_per execution option, which will simultaneously set Connection.execution_options.stream_results to ensure the use of server side cursors, as well as automatically invoke the Result.yield_per() method to establish a fixed row buffer size at once.

The Connection.execution_options.yield_per execution option is available for ORM operations, with Session-oriented use described at Fetching Large Result Sets with Yield Per. The Core-only version which works with Connection is new as of SQLAlchemy 1.4.40.

New in version 1.4.

Parameters:

num – number of rows to fetch each time the buffer is refilled. If set to a value below 1, fetches all rows for the next buffer.

See also

Using Server Side Cursors (a.k.a. stream results) - describes Core behavior for Result.yield_per()

Fetching Large Result Sets with Yield Per - in the ORM Querying Guide

class sqlalchemy.engine.ScalarResult

A wrapper for a Result that returns scalar values rather than Row values.

The ScalarResult object is acquired by calling the Result.scalars() method.

A special limitation of ScalarResult is that it has no fetchone() method; since the semantics of fetchone() are that the None value indicates no more results, this is not compatible with ScalarResult since there is no way to distinguish between None as a row value versus None as an indicator. Use next(result) to receive values individually.

Members

all(), close(), closed, fetchall(), fetchmany(), first(), one(), one_or_none(), partitions(), unique(), yield_per()

Class signature

class sqlalchemy.engine.ScalarResult (sqlalchemy.engine.FilterResult)

method sqlalchemy.engine.ScalarResult.all() → Sequence[_R]

Return all scalar values in a sequence.

Equivalent to Result.all() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.close() → None

inherited from the FilterResult.close() method of FilterResult

Close this FilterResult.

New in version 1.4.43.

attribute sqlalchemy.engine.ScalarResult.closed

inherited from the FilterResult.closed attribute of FilterResult

Return True if the underlying Result reports closed

New in version 1.4.43.

method sqlalchemy.engine.ScalarResult.fetchall() → Sequence[_R]

A synonym for the ScalarResult.all() method.

method sqlalchemy.engine.ScalarResult.fetchmany(size: int | None = None) → Sequence[_R]

Fetch many objects.

Equivalent to Result.fetchmany() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.first() → _R | None

Fetch the first object or None if no object is present.

Equivalent to Result.first() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.one() → _R

Return exactly one object or raise an exception.

Equivalent to Result.one() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.one_or_none() → _R | None

Return at most one object or raise an exception.

Equivalent to Result.one_or_none() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.partitions(size: int | None = None) → Iterator[Sequence[_R]]

Iterate through sub-lists of elements of the size given.

Equivalent to Result.partitions() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.unique(strategy: Callable[[Any], Any] | None = None) → Self

Apply unique filtering to the objects returned by this ScalarResult.

See Result.unique() for usage details.

method sqlalchemy.engine.ScalarResult.yield_per(num: int) → Self

inherited from the FilterResult.yield_per() method of FilterResult

Configure the row-fetching strategy to fetch num rows at a time.

The FilterResult.yield_per() method is a pass through to the Result.yield_per() method. See that method’s documentation for usage notes.

New in version 1.4.40: - added FilterResult.yield_per() so that the method is available on all result set implementations

See also

Using Server Side Cursors (a.k.a. stream results) - describes Core behavior for Result.yield_per()

Fetching Large Result Sets with Yield Per - in the ORM Querying Guide

class sqlalchemy.engine.MappingResult

A wrapper for a Result that returns dictionary values rather than Row values.

The MappingResult object is acquired by calling the Result.mappings() method.

Members

all(), close(), closed, columns(), fetchall(), fetchmany(), fetchone(), first(), keys(), one(), one_or_none(), partitions(), unique(), yield_per()

Class signature

class sqlalchemy.engine.MappingResult (sqlalchemy.engine._WithKeys, sqlalchemy.engine.FilterResult)

method sqlalchemy.engine.MappingResult.all() → Sequence[RowMapping]

Return all scalar values in a sequence.

Equivalent to Result.all() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.close() → None

inherited from the FilterResult.close() method of FilterResult

Close this FilterResult.

New in version 1.4.43.

attribute sqlalchemy.engine.MappingResult.closed

inherited from the FilterResult.closed attribute of FilterResult

Return True if the underlying Result reports closed

New in version 1.4.43.

method sqlalchemy.engine.MappingResult.columns(*col_expressions: _KeyIndexType) → Self

Establish the columns that should be returned in each row.

method sqlalchemy.engine.MappingResult.fetchall() → Sequence[RowMapping]

A synonym for the MappingResult.all() method.

method sqlalchemy.engine.MappingResult.fetchmany(size: int | None = None) → Sequence[RowMapping]

Fetch many objects.

Equivalent to Result.fetchmany() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.fetchone() → RowMapping | None

Fetch one object.

Equivalent to Result.fetchone() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.first() → RowMapping | None

Fetch the first object or None if no object is present.

Equivalent to Result.first() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.keys() → RMKeyView

inherited from the sqlalchemy.engine._WithKeys.keys method of sqlalchemy.engine._WithKeys

Return an iterable view which yields the string keys that would be represented by each Row.

The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.

The view also can be tested for key containment using the Python in operator, which will test both for the string keys represented in the view, as well as for alternate keys such as column objects.

Changed in version 1.4: a key view object is returned rather than a plain list.

method sqlalchemy.engine.MappingResult.one() → RowMapping

Return exactly one object or raise an exception.

Equivalent to Result.one() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.one_or_none() → RowMapping | None

Return at most one object or raise an exception.

Equivalent to Result.one_or_none() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.partitions(size: int | None = None) → Iterator[Sequence[RowMapping]]

Iterate through sub-lists of elements of the size given.

Equivalent to Result.partitions() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.unique(strategy: Callable[[Any], Any] | None = None) → Self

Apply unique filtering to the objects returned by this MappingResult.

See Result.unique() for usage details.

method sqlalchemy.engine.MappingResult.yield_per(num: int) → Self

inherited from the FilterResult.yield_per() method of FilterResult

Configure the row-fetching strategy to fetch num rows at a time.

The FilterResult.yield_per() method is a pass through to the Result.yield_per() method. See that method’s documentation for usage notes.

New in version 1.4.40: - added FilterResult.yield_per() so that the method is available on all result set implementations

See also

Using Server Side Cursors (a.k.a. stream results) - describes Core behavior for Result.yield_per()

Fetching Large Result Sets with Yield Per - in the ORM Querying Guide

class sqlalchemy.engine.Row

Represent a single result row.

The Row object represents a row of a database result. It is typically associated in the 1.x series of SQLAlchemy with the CursorResult object, however is also used by the ORM for tuple-like results as of SQLAlchemy 1.4.

The Row object seeks to act as much like a Python named tuple as possible. For mapping (i.e. dictionary) behavior on a row, such as testing for containment of keys, refer to the Row._mapping attribute.

See also

Using SELECT Statements - includes examples of selecting rows from SELECT statements.

Changed in version 1.4: Renamed RowProxy to Row. Row is no longer a “proxy” object in that it contains the final form of data within it, and now acts mostly like a named tuple. Mapping-like functionality is moved to the Row._mapping attribute. See RowProxy is no longer a “proxy”; is now called Row and behaves like an enhanced named tuple for background on this change.

Members

_asdict(), _fields, _mapping, _t, _tuple(), count, index, t, tuple()

Class signature

class sqlalchemy.engine.Row (sqlalchemy.engine._py_row.BaseRow, collections.abc.Sequence, typing.Generic)

method sqlalchemy.engine.Row._asdict() → Dict[str, Any]

Return a new dict which maps field names to their corresponding values.

This method is analogous to the Python named tuple ._asdict() method, and works by applying the dict() constructor to the Row._mapping attribute.

New in version 1.4.

See also

Row._mapping

attribute sqlalchemy.engine.Row._fields

Return a tuple of string keys as represented by this Row.

The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.

This attribute is analogous to the Python named tuple ._fields attribute.

New in version 1.4.

See also

Row._mapping

attribute sqlalchemy.engine.Row._mapping

Return a RowMapping for this Row.

This object provides a consistent Python mapping (i.e. dictionary) interface for the data contained within the row. The Row by itself behaves like a named tuple.

See also

Row._fields

New in version 1.4.

attribute sqlalchemy.engine.Row._t

A synonym for Row._tuple().

New in version 2.0.19: - The Row._t attribute supersedes the previous Row.t attribute, which is now underscored to avoid name conflicts with column names in the same way as other named-tuple methods on Row.

See also

Result.t

method sqlalchemy.engine.Row._tuple() → _TP

Return a ‘tuple’ form of this Row.

At runtime, this method returns “self”; the Row object is already a named tuple. However, at the typing level, if this Row is typed, the “tuple” return type will be a PEP 484 Tuple datatype that contains typing information about individual elements, supporting typed unpacking and attribute access.

New in version 2.0.19: - The Row._tuple() method supersedes the previous Row.tuple() method, which is now underscored to avoid name conflicts with column names in the same way as other named-tuple methods on Row.

See also

Row._t - shorthand attribute notation

Result.tuples()

attribute sqlalchemy.engine.Row.count
attribute sqlalchemy.engine.Row.index
attribute sqlalchemy.engine.Row.t

A synonym for Row._tuple().

Deprecated since version 2.0.19: The Row.t attribute is deprecated in favor of Row._t; all Row methods and library-level attributes are intended to be underscored to avoid name conflicts. Please use Row._t.

New in version 2.0.

method sqlalchemy.engine.Row.tuple() → _TP

Return a ‘tuple’ form of this Row.

Deprecated since version 2.0.19: The Row.tuple() method is deprecated in favor of Row._tuple(); all Row methods and library-level attributes are intended to be underscored to avoid name conflicts. Please use Row._tuple().

New in version 2.0.

class sqlalchemy.engine.RowMapping

A Mapping that maps column names and objects to Row values.

The RowMapping is available from a Row via the Row._mapping attribute, as well as from the iterable interface provided by the MappingResult object returned by the Result.mappings() method.

RowMapping supplies Python mapping (i.e. dictionary) access to the contents of the row. This includes support for testing of containment of specific keys (string column names or objects), as well as iteration of keys, values, and items:

for row in result:
    if 'a' in row._mapping:
        print("Column 'a': %s" % row._mapping['a'])

    print("Column b: %s" % row._mapping[table.c.b])

New in version 1.4: The RowMapping object replaces the mapping-like access previously provided by a database result row, which now seeks to behave mostly like a named tuple.

Members

items(), keys(), values()

Class signature

class sqlalchemy.engine.RowMapping (sqlalchemy.engine._py_row.BaseRow, collections.abc.Mapping, typing.Generic)

method sqlalchemy.engine.RowMapping.items() → ROMappingItemsView

Return a view of key/value tuples for the elements in the underlying Row.

method sqlalchemy.engine.RowMapping.keys() → RMKeyView

Return a view of ‘keys’ for string column names represented by the underlying Row.

method sqlalchemy.engine.RowMapping.values() → ROMappingKeysValuesView

Return a view of values for the values represented in the underlying Row.

class sqlalchemy.engine.TupleResult

A Result that’s typed as returning plain Python tuples instead of rows.

Since Row acts like a tuple in every way already, this class is a typing only class, regular Result is still used at runtime.

Class signature

class sqlalchemy.engine.TupleResult (sqlalchemy.engine.FilterResult, sqlalchemy.util.langhelpers.TypingOnly)