Using INSERT Statements

バルク操作にORMを使う場合と同様にCoreを使う場合、SQL INSERT文は insert() 関数を使って直接生成されます-この関数は Insert の新しいインスタンスを生成します。これはSQLのINSERT文を表し、新しいデータをテーブルに追加します。

ORM Readers -

このセクションでは、テーブルに新しい行を追加するために個々のSQL INSERT文を生成するコアの方法について詳しく説明します。ORMを使用する場合、私たちは通常、 unit of work と呼ばれるこの上にある別のツールを使用します。これは、一度に多くのINSERT文の生成を自動化します。しかし、コアがデータの作成と操作をどのように処理するかを理解することは、ORMがそれを実行している場合でも非常に有用です。さらに、ORMは Bulk / Multi Row INSERT, upsert, UPDATE and DELETE と呼ばれる機能を使用してINSERTを直接使用することをサポートしています。

通常の作業単位パターンを使用してORMで行を挿入する方法に直接スキップするには、 Inserting Rows using the ORM Unit of Work pattern を参照してください。

The insert() SQL Expression Construct

Insert の簡単な例で、対象となるテーブルとVALUES句を同時に示します:

>>> from sqlalchemy import insert
>>> stmt = insert(user_table).values(name="spongebob", fullname="Spongebob Squarepants")

上の stmt 変数は Insert のインスタンスです。ほとんどのSQL式は、生成されているものの一般的な形式を見る手段として、その場で文字列化することができます:

>>> print(stmt)

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)

文字列化された形式は、文のデータベース固有の文字列SQL表現を含むオブジェクトの Compiled 形式を生成することによって作成されます。このオブジェクトは、 ClauseElement.compile() メソッドを使用して直接取得できます。:

>>> compiled = stmt.compile()

私たちの Insert 構文は、以前に Sending Parameters で説明した「パラメータ化された」構文の例です。 name と fullname bound parameters を見るために、これらも Compiled 構文から利用できます:

>>> compiled.params
{'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}

Executing the Statement

この文を呼び出すことで、 user_table に行をINSERTすることができます。INSERT SQLおよびバンドルされたパラメータは、SQLロギングで確認できます:

>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     conn.commit()

BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('spongebob', 'Spongebob Squarepants')
COMMIT

上記の単純な形式では、INSERT文は行を返しません。また、単一の行のみが挿入された場合、通常、その行のINSERT中に生成された列レベルのデフォルト値(最も一般的には整数の主キー値)に関する情報を返す機能が含まれます。上記の場合、SQLiteデータベースの最初の行は通常、最初の整数の主キー値に対して ``1` を返します。これは CursorResult.inserted_primary_key アクセッサを使って取得できます。:

>>> result.inserted_primary_key
(1,)

Tip

CursorResult.inserted_primary_key は、プライマリキーが複数の列を含む可能性があるので、タプルを返します。これは primary key として知られています。 CursorResult.inserted_primary_key は、単に”cursor.lastrowid”のような値ではなく、挿入されたばかりのレコードの完全なプライマリキーを常に含むことを意図しており、”autoincrement”が使用されたかどうかにかかわらず入力されることも意図しています。したがって、完全な複合主キーを表現するにはタプルになります。

Changed in version 1.4.8: CursorResult.inserted_primary_key によって返されるタプルは、 Row オブジェクトとして返されることによって実現される名前付きタプルになりました。

INSERT usually generates the “values” clause automatically

上の例では、 Insert.values() メソッドを使用して、SQL INSERT文のVALUES句を明示的に作成しています。実際に Insert.values() を使用せずに”空の”文を出力すると、テーブル内のすべての列に対してINSERTが行われます:

>>> print(insert(user_table))

INSERT INTO user_account (id, name, fullname) VALUES (:id, :name, :fullname)

Insert.values() が呼び出されていない Insert 構文を取得し、出力するのではなく実行すると、 Connection.execute() メソッドに渡したパラメータに基づいて文が文字列にコンパイルされ、渡されたパラメータに関連する列のみが含まれます。これは実際には、 Insert を使用して、明示的なVALUES句を入力せずに行を挿入する通常の方法です。次の例は、2列のINSERT文がパラメータのリストとともに同時に実行される様子を示しています。

>>> with engine.connect() as conn:
...     result = conn.execute(
...         insert(user_table),
...         [
...             {"name": "sandy", "fullname": "Sandy Cheeks"},
...             {"name": "patrick", "fullname": "Patrick Star"},
...         ],
...     )
...     conn.commit()

BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] [('sandy', 'Sandy Cheeks'), ('patrick', 'Patrick Star')]
COMMIT

上記の実行は、 Sending Multiple Parameters で最初に説明されている「executemany」形式を特徴としていますが、 text() 構文を使用する場合とは異なり、SQLを記述する必要はありませんでした。辞書または辞書のリストを Insert 構文と共に Connection.execute() メソッドに渡すことで、 Connection は、渡される列名が Insert 構文のVALUES句で自動的に表現されることを保証します。

Deep Alchemy

こんにちは、 Deep Alchemy の第1版へようこそ。左側の人は The Alchemist として知られていますが、あなたは彼らが 魔法使いではない ことに気づくでしょう。なぜなら、とがった帽子が上に突き出ていないからです。Alchemistは、一般的に より高度で扱いにくい こと、さらに 通常は必要とされない ことを説明するためにやってきますが、どんな理由があっても、彼らはSQLAlchemyができることを知っておくべきだと感じています。

この版では、 address_table にも興味深いデータを入れることを目標に、 Insert.values() メソッドを明示的に使用しながら、同時にパラメータから生成された追加のVALUESを含める方法を示す、より高度な例を以下に示します。 scalar subquery は、次のセクションで紹介する select() 構文を使用して構築され、サブクエリで使用されるパラメータは、 bindparam() 構文を使用して確立された明示的にバインドされたパラメータ名を使用して設定されます。

これは、 user_table 操作からアプリケーションに主キー識別子をフェッチしなくても関連する行を追加できるようにするための、少し**深い**錬金術です。ほとんどの錬金術師は、このような処理を行うORMを使用します。

>>> from sqlalchemy import select, bindparam
>>> scalar_subq = (
...     select(user_table.c.id)
...     .where(user_table.c.name == bindparam("username"))
...     .scalar_subquery()
... )

>>> with engine.connect() as conn:
...     result = conn.execute(
...         insert(address_table).values(user_id=scalar_subq),
...         [
...             {
...                 "username": "spongebob",
...                 "email_address": "spongebob@sqlalchemy.org",
...             },
...             {"username": "sandy", "email_address": "sandy@sqlalchemy.org"},
...             {"username": "sandy", "email_address": "sandy@squirrelpower.org"},
...         ],
...     )
...     conn.commit()

BEGIN (implicit)
INSERT INTO address (user_id, email_address) VALUES ((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?)
[...] [('spongebob', 'spongebob@sqlalchemy.org'), ('sandy', 'sandy@sqlalchemy.org'),
('sandy', 'sandy@squirrelpower.org')]
COMMIT

これにより、次のセクションで使用する興味深いデータがテーブルに追加されました。

Tip

引数なしで Insert.values() を指定すると、明示的な値をまったく含まずにテーブルの”デフォルト”だけを挿入する真の”空の”INSERTが生成されます。すべてのデータベースバックエンドがこれをサポートしているわけではありませんが、SQLiteが生成するものは次のとおりです。:

>>> print(insert(user_table).values().compile(engine))

{printsql}INSERT INTO user_account DEFAULT VALUES

INSERT…RETURNING

サポートされているバックエンドのRETURNING句は、最後に挿入されたプライマリ・キーの値とサーバのデフォルト値を取得するために自動的に使用されます。ただし、RETURNING句は Insert.returning() メソッドを使って明示的に指定することもできます。この場合、文の実行時に返される Result オブジェクトにはフェッチ可能な行があります:

>>> insert_stmt = insert(address_table).returning(
...     address_table.c.id, address_table.c.email_address
... )
>>> print(insert_stmt)

INSERT INTO address (id, user_id, email_address)
VALUES (:id, :user_id, :email_address)
RETURNING address.id, address.email_address

INSERT…FROM SELECT で説明されている例を基にした以下の例のように、 Insert.from_select() と組み合わせることもできます:

>>> select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
>>> insert_stmt = insert(address_table).from_select(
...     ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt.returning(address_table.c.id, address_table.c.email_address))

INSERT INTO address (user_id, email_address)
SELECT user_account.id, user_account.name || :name_1 AS anon_1
FROM user_account RETURNING address.id, address.email_address

Tip

RETURNING機能は、このチュートリアルの後半で紹介するUPDATE文とDELETE文でもサポートされています。

INSERT文の場合、RETURNING機能は単一行の文と、複数行を一度にINSERTする文の両方に使用できます。RETURNINGでの複数行のINSERTのサポートは方言固有ですが、RETURNINGをサポートするSQLAlchemyに含まれるすべての方言でサポートされています。この機能の背景については “Insert Many Values” Behavior for INSERT statements を参照してください。

See also

RETURNINGの有無に関わらず、バルクインサートもORMでサポートされています。参考資料として ORM Bulk INSERT Statements を参照してください。

INSERT…FROM SELECT

Insert のあまり使用されていない機能ですが、ここでは完全を期すために、 Insert 構文は、 Insert.from_select() メソッドを使用してSELECTから直接行を取得するINSERTを構成することができます。このメソッドは、実際のINSERTの対象となる列名のリストとともに、次のセクションで説明する select() 構文を受け入れます。次の例では、 user_account テーブルの行から派生した行が address テーブルに追加され、各ユーザーに aol.com のフリーメールアドレスが与えられます。:

>>> select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
>>> insert_stmt = insert(address_table).from_select(
...     ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt)

INSERT INTO address (user_id, email_address)
SELECT user_account.id, user_account.name || :name_1 AS anon_1
FROM user_account

この構文は、実際にクライアントからデータをフェッチして再送信することなく、データベースの他の部分から新しいロー・セットに直接データをコピーする場合に使用します。

See also

Insert - SQL式APIドキュメント内