Sqlalchemy-quick-guide

提供：Dev Guides
移動先:案内、検索

SQLAlchemy-はじめに

SQLAlchemyは一般的なSQLツールキットであり、オブジェクトリレーショナルマッパー*です。 *Python で記述されており、アプリケーション開発者にSQLのフルパワーと柔軟性を提供します。 これは、MITライセンスの下でリリースされた*オープンソース*および*クロスプラットフォームソフトウェア*です。

SQLAlchemyは、オブジェクトリレーショナルマッパー（ORM）で有名です。これを使用すると、クラスをデータベースにマッピングでき、オブジェクトモデルとデータベーススキーマを最初から完全に切り離した方法で開発できます。

SQLデータベースのサイズとパフォーマンスが重要になり始めると、オブジェクトコレクションのように動作しなくなります。 一方、オブジェクトコレクションの抽象化が重要になると、テーブルや行のように動作しなくなります。 SQLAlchemyは、これらの原則の両方に対応することを目的としています。

このため、他の多くのORMで使用されているアクティブなレコードパターンではなく、*データマッパーパターン（Hibernateなど）を採用しています。 データベースとSQLは、SQLAlchemyを使用して異なる視点で表示されます。

Michael BayerはSQLAlchemyの原作者です。 最初のバージョンは2006年2月にリリースされました。 最新バージョンの番号は1.2.7で、2018年4月にリリースされました。

ORMとは何ですか？

ORM（オブジェクトリレーショナルマッピング）は、オブジェクト指向プログラミング言語の互換性のない型システム間でデータを変換するためのプログラミング手法です。 通常、Pythonなどのオブジェクト指向（OO）言語で使用される型システムには、非スカラー型が含まれています。 これらは、整数や文字列などのプリミティブ型として表現できません。 したがって、OOプログラマーは、スカラーデータのオブジェクトを変換してバックエンドデータベースとやり取りする必要があります。 ただし、Oracle、MySQLなど、ほとんどのデータベース製品のデータ型はプライマリです。

ORMシステムでは、各クラスは基礎となるデータベースのテーブルにマップします。 面倒なデータベースインターフェースコードを自分で記述する代わりに、ORMがこれらの問題を処理し、システムのロジックのプログラミングに集中できます。

SQLAlchemy-環境のセットアップ

SQLAlchemyを使用するために必要な環境設定について説明します。

SQLAlchemyをインストールするには、2.7以上のPythonのバージョンが必要です。 最も簡単なインストール方法は、Pythonパッケージマネージャー pip を使用することです。 このユーティリティは、Pythonの標準配布にバンドルされています。 

pip install sqlalchemy

上記のコマンドを使用して、https://pypi.org/project/SQLAlchemy/[python.org]からSQLAlchemyの*最新リリースバージョン*をダウンロードし、システムにインストールできます。

Pythonのanacondaディストリビューションの場合、SQLAlchemyは以下のコマンドを使用して* conda端末*からインストールできます- 

conda install -c anaconda sqlalchemy

以下のソースコードからSQLAlchemyをインストールすることも可能です- 

python setup.py install

SQLAlchemyは、特定のデータベース用に構築されたDBAPI実装で動作するように設計されています。 方言システムを使用して、さまざまなタイプのDBAPI実装およびデータベースと通信します。 すべての方言では、適切なDBAPIドライバーがインストールされている必要があります。

以下が含まれる方言です-

  • 火の鳥
  • Microsoft SQL Server
  • MySQL
  • オラクル
  • PostgreSQL
  • SQLite
  • Sybase

SQLAlchemyが適切にインストールされているかどうかを確認し、そのバージョンを知るには、Pythonプロンプトで次のコマンドを入力します- 

>>> import sqlalchemy
>>>sqlalchemy.__version__
'1.2.7'

SQLAlchemyコア–式言語

SQLAlchemyコアには、* SQLレンダリングエンジン、DBAPI統合、トランザクション統合*、および*スキーマ記述サービス*が含まれています。 SQLAlchemyコアは*スキーマ中心の使用法*パラダイムを提供するSQL Expression Languageを使用しますが、SQLAlchemy ORMは*ドメイン中心の使用法*です。

SQL Expression Languageは、Pythonコンストラクトを使用してリレーショナルデータベースの構造と式を表すシステムを提供します。 それは意見なしでリレーショナルデータベースの原始的な構造を直接表現するシステムを提示します。これは、それ自体が表現言語の応用的な使用の例である高レベルで抽象的な使用パターンを提示するORMとは対照的です。

式言語は、SQLAlchemyのコアコンポーネントの1つです。 プログラマーは、PythonコードでSQLステートメントを指定し、より複雑なクエリで直接使用できます。 式言語はバックエンドから独立しており、生のSQLのあらゆる側面を包括的にカバーします。 SQLAlchemyの他のコンポーネントよりも生のSQLに近いです。

Expression Languageは、リレーショナルデータベースのプリミティブな構造を直接表します。 ORMはExpression言語の最上位に基づいているため、一般的なPythonデータベースアプリケーションでは両方の使用が重複している場合があります。 アプリケーションは式言語のみを使用できますが、アプリケーションの概念を個々のデータベースクエリに変換する独自のシステムを定義する必要があります。

式言語のステートメントは、SQLAlchemyエンジンによって対応する生のSQLクエリに変換されます。 ここで、エンジンを作成し、その助けを借りてさまざまなSQLクエリを実行する方法を学習します。

SQLAlchemyコア-データベースへの接続

前の章で、SQLAlchemyの式言語について説明しました。 次に、データベースへの接続に関連する手順に進みましょう。

エンジンクラスは、 PoolとDialect を接続して、データベースのソース connectivity and behavior を提供します。 Engineクラスのオブジェクトは、* create_engine（）*関数を使用してインスタンス化されます。

create_engine（）関数は、データベースを1つの引数として受け取ります。 データベースをどこにでも定義する必要はありません。 標準の呼び出しフォームは、最初の定位置引数としてURLを送信する必要があります。通常は、データベースの方言と接続の引数を示す文字列です。 以下のコードを使用して、データベースを作成できます。 

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///college.db', echo = True)
  • MySQLデータベース*の場合、以下のコマンドを使用します-
engine = create_engine("mysql://user:pwd@localhost/college",echo = True)

接続に使用される DB-API を具体的に述べるために、* URL文字列*は次のような形式を取ります- 

dialect[+driver]://user:password@host/dbname

たとえば、MySQLで* PyMySQLドライバーを使用している場合は、次のコマンドを使用します- 

mysql+pymysql://<username>:<password>@<host>/<dbname>
  • echoフラグ*は、SQLAlchemyロギングをセットアップするためのショートカットです。これは、Pythonの標準ロギングモジュールを介して実行されます。 後続の章では、生成されたすべてのSQLについて学習します。 詳細出力を非表示にするには、echo属性を None に設定します。 create_engine（）関数の他の引数は、方言固有のものである場合があります。

create_engine（）関数は* Engineオブジェクト*を返します。 Engineクラスのいくつかの重要なメソッドは次のとおりです-

Sr.No. Method & Description
1

connect()

接続オブジェクトを返します

2

execute()

SQLステートメント構成を実行します

3

begin()

トランザクションが確立された接続を配信するコンテキストマネージャを返します。 操作が成功すると、トランザクションがコミットされます。それ以外の場合は、ロールバックされます

4

dispose()

エンジンが使用した接続プールを破棄します

5

driver()

エンジンが使用している方言のドライバー名

6

table_names()

データベースで利用可能なすべてのテーブル名のリストを返します

7

transaction()

トランザクション境界内で指定された関数を実行します

SQLAlchemyコア-テーブルの作成

次に、テーブル作成関数の使用方法について説明します。

SQL Expression Languageは、テーブル列に対して式を作成します。 SQLAlchemy Columnオブジェクトは、データベーステーブルの column を表し、 Table は Tableobject で表されます。 メタデータには、テーブルの定義と、インデックス、ビュー、トリガーなどの関連オブジェクトが含まれます。

したがって、SQLAlchemyメタデータのMetaDataクラスのオブジェクトは、Tableオブジェクトとそれに関連付けられたスキーマ構造のコレクションです。 Tableオブジェクトのコレクションと、エンジンまたは接続へのオプションのバインディングを保持します。 

from sqlalchemy import MetaData
meta = MetaData()

MetaDataクラスのコンストラクターは、デフォルトでは None であるバインドおよびスキーマパラメーターを持つことができます。

次に、通常のSQL CREATE TABLEステートメントに似た* Tableコンストラクト*を使用して、上記のメタデータカタログ内ですべてのテーブルを定義します。

Tableクラスのオブジェクトは、データベース内の対応するテーブルを表します。 コンストラクタは、次のパラメータを取ります-

Name Name of the table
Metadata MetaData object that will hold this table
Column(s) One or more objects of column class

列オブジェクトは、*データベース表*の*列*を表します。 コンストラクターは、名前、タイプ、primary_key、autoincrement、その他の制約などの他のパラメーターを取ります。

SQLAlchemyは、Pythonデータを、定義されている可能な限り最適な汎用列データ型に一致させます。 いくつかの一般的なデータ型は-

  • BigInteger
  • ブール値
  • Date
  • 日付時刻
  • 浮く
  • 整数
  • 数値
  • SmallInteger
  • ひも
  • Text
  • Time

大学のデータベースで*学生のテーブル*を作成するには、次のスニペットを使用します- 

from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

create_all（）関数は、エンジンオブジェクトを使用してすべての定義済みテーブルオブジェクトを作成し、情報をメタデータに保存します。 

meta.create_all(engine)

以下に完全なコードを示します。これにより、studentsテーブルを含むSQLiteデータベースcollege.dbが作成されます。 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)
meta.create_all(engine)

create_engine（）関数のecho属性が True に設定されているため、コンソールはテーブル作成用の実際のSQLクエリを次のように表示します- 

CREATE TABLE students (
   id INTEGER NOT NULL,
   name VARCHAR,
   lastname VARCHAR,
   PRIMARY KEY (id)
)

college.dbは、現在の作業ディレクトリに作成されます。 Studentsテーブルが作成されているかどうかを確認するには、 SQLiteStudio などのSQLite GUIツールを使用してデータベースを開くことができます。

以下の画像は、データベースに作成された学生テーブルを示しています-

学生表

SQLAlchemyコア-SQL式

この章では、SQL式とその機能について簡単に説明します。

SQL式は、ターゲットテーブルオブジェクトに関連する対応するメソッドを使用して構築されます。 たとえば、INSERTステートメントは、次のようにinsert（）メソッドを実行することにより作成されます- 

ins = students.insert()

上記のメソッドの結果は、* str（）*関数を使用して検証できる挿入オブジェクトです。 以下のコードは、学生ID、名前、姓などの詳細を挿入します。 

'INSERT INTO students (id, name, lastname) VALUES (:id, :name, :lastname)'
  • values（）*メソッドを使用してオブジェクトを挿入することにより、特定のフィールドに値を挿入することができます。 同じためのコードを以下に示します-
>>> ins = users.insert().values(name = 'Karan')
>>> str(ins)
'INSERT INTO users (name) VALUES (:name)'

Pythonコンソールに表示されるSQLには、実際の値（この場合は「Karan」）は表示されません。 代わりに、SQLALchemyは、コンパイルされた形式のステートメントで表示されるバインドパラメーターを生成します。 

ins.compile().params
{'name': 'Karan'}

同様に、* update（）、delete（）、および select（）*などのメソッドは、それぞれUPDATE、DELETE、およびSELECT式を作成します。 それらについては後の章で説明します。

SQLAlchemyコア-式の実行

前の章で、SQL式を学習しました。 この章では、これらの式の実行について説明します。

結果のSQL式を実行するには、以下のコードに示すように、アクティブにチェックアウトされたDBAPI接続リソースを表す接続オブジェクトを取得してから、式オブジェクトを*フィードする必要があります。 

conn = engine.connect()

次のinsert（）オブジェクトは、execute（）メソッドに使用できます- 

ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
result = conn.execute(ins)

コンソールは以下のようにSQL式の実行の結果を示しています- 

INSERT INTO students (name, lastname) VALUES (?, ?)
('Ravi', 'Kapoor')
COMMIT

以下は、SQLAlchemyのコアテクニックを使用したINSERTクエリの実行を示すスニペット全体です- 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

ins = students.insert()
ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
conn = engine.connect()
result = conn.execute(ins)

以下のスクリーンショットに示すように、SQLite Studioを使用してデータベースを開くことにより、結果を確認できます-

SQLite Studio

結果変数は、https://docs.sqlalchemy.org/en/latest/core/connectionsl#sqlalchemy.engine.ResultProxy [ResultProxy] object として知られています。 DBAPIカーソルオブジェクトに似ています。 以下に示すように、 ResultProxy.inserted_primary_key を使用して、ステートメントから生成された主キー値に関する情報を取得できます- 

result.inserted_primary_key
[1]

DBAPIのexecute many（）メソッドを使用して多くの挿入を発行するために、挿入する個別のパラメーターセットを含む辞書のリストを送信できます。 

conn.execute(students.insert(), [
   {'name':'Rajiv', 'lastname' : 'Khanna'},
   {'name':'Komal','lastname' : 'Bhandari'},
   {'name':'Abdul','lastname' : 'Sattar'},
   {'name':'Priya','lastname' : 'Rajhans'},
])

これは、次の図に示すように、テーブルのデータビューに反映されます-

テーブルデータビュー

SQLAlchemyコア-行の選択

この章では、テーブルオブジェクトの行を選択する概念について説明します。

テーブルオブジェクトのselect（）メソッドを使用すると、SELECT式を*構築できます。 

s = students.select()

selectオブジェクトは、以下に示すように、str（s）関数による *SELECTクエリに変換されます- 

'SELECT students.id, students.name, students.lastname FROM students'

以下のコードに示すように、接続オブジェクトのexecute（）メソッドのパラメーターとしてこのselectオブジェクトを使用できます- 

result = conn.execute(s)

上記のステートメントが実行されると、Pythonシェルは同等のSQL式に従ってエコーします- 

SELECT students.id, students.name, students.lastname
FROM students

結果の変数は、DBAPIのカーソルと同等です。* fetchone（）メソッド*を使用してレコードを取得できます。 

row = result.fetchone()

テーブルで選択されたすべての行は、以下に示すように* forループ*で印刷することができます- 

for row in result:
   print (row)

学生テーブルからすべての行を印刷する完全なコードを以下に示します- 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

s = students.select()
conn = engine.connect()
result = conn.execute(s)

for row in result:
   print (row)

Pythonシェルに表示される出力は次のとおりです- 

(1, 'Ravi', 'Kapoor')
(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')

SELECTクエリのWHERE句は、* Select.where（）*を使用して適用できます。 たとえば、ID> 2の行を表示する場合 

s = students.select().where(students.c.id>2)
result = conn.execute(s)

for row in result:
   print (row)

ここで、 c属性はcolumn のエイリアスです。 次の出力がシェルに表示されます- 

(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')

ここで、sqlalchemy.sqlモジュールのselect（）関数でもselectオブジェクトを取得できることに注意する必要があります。 select（）関数には、引数としてテーブルオブジェクトが必要です。 

from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)

SQLAlchemyコア-テキストSQLの使用

SQLAlchemyでは、文字列を使用できます。これは、SQLが既知であり、動的な機能をサポートするステートメントの必要性が低い場合に使用します。 text（）コンストラクトは、ほとんど変更されずにデータベースに渡されるテキストステートメントを作成するために使用されます。

以下のコードに示すように、テキストのSQL文字列を直接表す新しい TextClause を構築します- 

from sqlalchemy import text
t = text("SELECT * FROM students")
result = connection.execute(t)

プレーン文字列よりも* text（）*が提供する利点は-

  • バインドパラメータのバックエンドニュートラルサポート
  • ステートメントごとの実行オプション *結果列のタイピング動作

text（）関数には、名前付きコロン形式のBoundパラメーターが必要です。 データベースバックエンドに関係なく一貫しています。 パラメーターの値を送信するには、追加の引数としてそれらをexecute（）メソッドに渡します。

次の例では、テキストSQLでバインドされたパラメータを使用します- 

from sqlalchemy.sql import text
s = text("select students.name, students.lastname from students where students.name between :x and :y")
conn.execute(s, x = 'A', y = 'L').fetchall()

text（）関数は次のようにSQL式を構築します- 

select students.name, students.lastname from students where students.name between ? and ?

x = 'A’およびy = 'L’の値がパラメーターとして渡されます。 結果は、「A」と「L」の間の名前を持つ行のリストです- 

[('Komal', 'Bhandari'), ('Abdul', 'Sattar')]

text（）コンストラクトは、TextClause.bindparams（）メソッドを使用して事前に確立されたバインド値をサポートします。 パラメータは、次のように明示的に入力することもできます- 

stmt = text("SELECT* FROM students WHERE students.name BETWEEN :x AND :y")

stmt = stmt.bindparams(
   bindparam("x", type_= String),
   bindparam("y", type_= String)
)

result = conn.execute(stmt, {"x": "A", "y": "L"})

The text() function also be produces fragments of SQL within a select() object that
accepts text() objects as an arguments. The “geometry” of the statement is provided by
select() construct , and the textual content by text() construct. We can build a statement
without the need to refer to any pre-established Table metadata.

from sqlalchemy.sql import select
s = select([text("students.name, students.lastname from students")]).where(text("students.name between :x and :y"))
conn.execute(s, x = 'A', y = 'L').fetchall()

また、* and _（）*関数を使用して、text（）関数を使用して作成されたWHERE句で複数の条件を組み合わせることができます。 

from sqlalchemy import and_
from sqlalchemy.sql import select
s = select([text("* from students")]) \
.where(
   and_(
      text("students.name between :x and :y"),
      text("students.id>2")
   )
)
conn.execute(s, x = 'A', y = 'L').fetchall()

上記のコードは、IDが2より大きい「A」と「L」の間の名前を持つ行をフェッチします。 コードの出力は以下のとおりです- 

[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar')]

SQLAlchemyコア-エイリアスの使用

SQLのエイリアスは、テーブルまたはSELECTステートメントの「名前を変更した」バージョンに対応します。これは、「SELECT * FROM table1 AS a」と言うたびに発生します。 ASはテーブルの新しい名前を作成します。 エイリアスを使用すると、テーブルまたはサブクエリを一意の名前で参照できます。

テーブルの場合、これにより、同じテーブルにFROM句で複数回名前を付けることができます。 ステートメントによって表される列の親名を提供し、この名前に関連してそれらを参照できるようにします。

SQLAlchemyでは、* From Clause.alias（）*メソッドを使用して任意のTable、select（）コンストラクト、またはその他の選択可能なオブジェクトをエイリアスに変換できます。これにより、Aliasコンストラクトが生成されます。 sqlalchemy.sqlモジュールのalias（）関数は、ASキーワードを使用してSQLステートメント内のテーブルまたはサブセレクトに通常適用されるように、エイリアスを表します。 

from sqlalchemy.sql import alias
st = students.alias("a")

このエイリアスをselect（）構造で使用して、studentsテーブルを参照できるようになりました- 

s = select([st]).where(st.c.id>2)

これは、次のようにSQL式に変換されます- 

SELECT a.id, a.name, a.lastname FROM students AS a WHERE a.id > 2

接続オブジェクトのexecute（）メソッドを使用して、このSQLクエリを実行できるようになりました。 完全なコードは次のとおりです- 

from sqlalchemy.sql import alias, select
st = students.alias("a")
s = select([st]).where(st.c.id > 2)
conn.execute(s).fetchall()

上記のコード行が実行されると、次の出力が生成されます- 

[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar'), (5, 'Priya', 'Rajhans')]

UPDATE式の使用

ターゲットテーブルオブジェクトの* update（）*メソッドは、同等のUPDATE SQL式を構築します。 

table.update().where(conditions).values(SET expressions)

結果の更新オブジェクトの* values（）*メソッドは、UPDATEのSET条件を指定するために使用されます。 Noneのままにすると、SET条件は、ステートメントの実行またはコンパイル中にステートメントに渡されるパラメーターから決定されます。

where句は、UPDATEステートメントのWHERE条件を記述するオプション式です。

次のコードスニペットは、学生テーブルの「姓」列の値を「カンナ」から「カプーア」に変更します- 

stmt = students.update().where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')

stmtオブジェクトはに変換する更新オブジェクトです- 

'UPDATE students SET lastname = :lastname WHERE students.lastname = :lastname_1'
  • execute（）メソッドが呼び出されると、バインドされたパラメーター *lastname_1 が置換されます。 完全な更新コードは以下のとおりです-
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students',
   meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

conn = engine.connect()
stmt=students.update().where(students.c.lastname=='Khanna').values(lastname='Kapoor')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

上記のコードは、次の出力を表示します。2行目には、指定されたスクリーンショットのように更新操作の効果が示されています- 

[
   (1, 'Ravi', 'Kapoor'),
   (2, 'Rajiv', 'Kapoor'),
   (3, 'Komal', 'Bhandari'),
   (4, 'Abdul', 'Sattar'),
   (5, 'Priya', 'Rajhans')
]

更新操作

以下に示すように、sqlalchemy.sql.expressionモジュールで* update（）*関数を使用しても、同様の機能を実現できることに注意してください- 

from sqlalchemy.sql.expression import update
stmt = update(students).where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')

DELETE式の使用

前の章で、 Update 式の機能を理解しました。 次に学習する表現は*削除*です。

削除操作は、次のステートメントに示されているように、ターゲットテーブルオブジェクトでdelete（）メソッドを実行することで実現できます- 

stmt = students.delete()

学生テーブルの場合、上記のコード行は次のようにSQL式を構築します- 

'DELETE FROM students'

ただし、これにより、studentsテーブルのすべての行が削除されます。 通常、DELETEクエリはWHERE句で指定された論理式に関連付けられます。 次のステートメントは、パラメータを示しています- 

stmt = students.delete().where(students.c.id > 2)

結果のSQL式には、実行時にステートメントが実行されるときに置換されるバインドされたパラメーターがあります。 

'DELETE FROM students WHERE students.id > :id_1'

次のコード例は、姓が「Khanna」である学生テーブルからこれらの行を削除します- 

from sqlalchemy.sql.expression import update
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

conn = engine.connect()
stmt = students.delete().where(students.c.lastname == 'Khanna')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

結果を確認するには、SQLiteStudioの学生テーブルのデータビューを更新します。

SQLAlchemyコア-複数のテーブルの使用

RDBMSの重要な機能の1つは、テーブル間の関係を確立することです。 SELECT、UPDATE、DELETEなどのSQL操作は、関連するテーブルで実行できます。 このセクションでは、SQLAlchemyを使用したこれらの操作について説明します。

この目的のために、SQLiteデータベース（college.db）に2つのテーブルが作成されます。 Studentsテーブルの構造は、前のセクションで示したものと同じです。一方、住所表には st_id 列があり、これは外部キー制約を使用して、students表の* id列にマップされます。

次のコードはcollege.dbに2つのテーブルを作成します- 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('sqlite:///college.db', echo=True)
meta = MetaData()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

addresses = Table(
   'addresses', meta,
   Column('id', Integer, primary_key = True),
   Column('st_id', Integer, ForeignKey('students.id')),
   Column('postal_add', String),
   Column('email_add', String))

meta.create_all(engine)

上記のコードは、以下のように学生と住所のテーブルのCREATE TABLEクエリに変換されます- 

CREATE TABLE students (
   id INTEGER NOT NULL,
   name VARCHAR,
   lastname VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE addresses (
   id INTEGER NOT NULL,
   st_id INTEGER,
   postal_add VARCHAR,
   email_add VARCHAR,
   PRIMARY KEY (id),
   FOREIGN KEY(st_id) REFERENCES students (id)
)

次のスクリーンショットは、上記のコードを非常に明確に示しています-

テーブルクエリの作成

アドレステーブルクエリ

これらのテーブルには、テーブルオブジェクトの* insert（）メソッド*を実行することでデータが入力されます。 Studentsテーブルに5行を挿入するには、以下に示すコードを使用できます- 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

conn = engine.connect()
students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

conn.execute(students.insert(), [
   {'name':'Ravi', 'lastname':'Kapoor'},
   {'name':'Rajiv', 'lastname' : 'Khanna'},
   {'name':'Komal','lastname' : 'Bhandari'},
   {'name':'Abdul','lastname' : 'Sattar'},
   {'name':'Priya','lastname' : 'Rajhans'},
])
  • 行*は、次のコードの助けを借りてアドレス表に追加されます-
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

addresses = Table(
   'addresses', meta,
   Column('id', Integer, primary_key = True),
   Column('st_id', Integer),
   Column('postal_add', String),
   Column('email_add', String)
)

conn.execute(addresses.insert(), [
   {'st_id':1, 'postal_add':'Shivajinagar Pune', 'email_add':'ravi@gmail.com'},
   {'st_id':1, 'postal_add':'ChurchGate Mumbai', 'email_add':'kapoor@gmail.com'},
   {'st_id':3, 'postal_add':'Jubilee Hills Hyderabad', 'email_add':'komal@gmail.com'},
   {'st_id':5, 'postal_add':'MG Road Bangaluru', 'email_add':'as@yahoo.com'},
   {'st_id':2, 'postal_add':'Cannought Place new Delhi', 'email_add':'admin@khanna.com'},
])

アドレステーブルのst_id列は、studentsテーブルのid列を参照することに注意してください。 これで、このリレーションを使用して両方のテーブルからデータを取得できます。 アドレス表のst_idに対応するstudents表から name と lastname を取得します。 

from sqlalchemy.sql import select
s = select([students, addresses]).where(students.c.id == addresses.c.st_id)
result = conn.execute(s)

for row in result:
   print (row)

選択オブジェクトは、共通の関係にある2つのテーブルを結合する次のSQL式に効果的に変換されます- 

SELECT students.id,
   students.name,
   students.lastname,
   addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM students, addresses
WHERE students.id = addresses.st_id

これは、次のように両方のテーブルから対応するデータを抽出する出力を生成します- 

(1, 'Ravi', 'Kapoor', 1, 1, 'Shivajinagar Pune', 'ravi@gmail.com')
(1, 'Ravi', 'Kapoor', 2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')
(3, 'Komal', 'Bhandari', 3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')
(5, 'Priya', 'Rajhans', 4, 5, 'MG Road Bangaluru', 'as@yahoo.com')
(2, 'Rajiv', 'Khanna', 5, 2, 'Cannought Place new Delhi', 'admin@khanna.com')

複数のテーブル更新の使用

前の章では、複数のテーブルの使用方法について説明しました。 したがって、この章ではさらに一歩進んで、*複数のテーブルの更新*を学びます。

SQLAlchemyのテーブルオブジェクトを使用すると、update（）メソッドのWHERE句で複数のテーブルを指定できます。 PostgreSQLおよびMicrosoft SQL Serverは、複数のテーブルを参照するUPDATEステートメントをサポートしています。 これは、一度に1つのテーブルを更新する*“ UPDATE FROM” *構文を実装します。 ただし、追加のテーブルは、WHERE句の追加の「FROM」句で直接参照できます。 次のコード行は、*複数のテーブル更新*の概念を明確に説明しています。 

stmt = students.update().\
values({
   students.c.name:'xyz',
   addresses.c.email_add:'abc@xyz.com'
}).\
where(students.c.id == addresses.c.id)

更新オブジェクトは、次のUPDATEクエリと同等です- 

UPDATE students
SET email_add = :addresses_email_add, name = :name
FROM addresses
WHERE students.id = addresses.id

MySQLの方言に関する限り、複数のテーブルは、以下に示すようにカンマで区切られた単一のUPDATEステートメントに埋め込むことができます- 

stmt = students.update().\
   values(name = 'xyz').\
   where(students.c.id == addresses.c.id)

次のコードは、結果のUPDATEクエリを示しています- 

'UPDATE students SET name = :name
FROM addresses
WHERE students.id = addresses.id'

ただし、SQLiteダイアレクトはUPDATE内の複数テーブルの基準をサポートしておらず、次のエラーを示しています- 

NotImplementedError: This backend does not support multiple-table criteria within UPDATE

パラメータ順の更新

生SQLのUPDATEクエリにはSET句があります。 元のTableオブジェクトで指定された列の順序を使用するupdate（）コンストラクトによってレンダリングされます。 したがって、特定の列を持つ特定のUPDATEステートメントは、毎回同じようにレンダリングされます。 パラメーター自体はPython辞書キーとしてUpdate.values（）メソッドに渡されるため、他の固定された順序は利用できません。

場合によっては、SET句でレンダリングされるパラメーターの順序が重要です。 MySQLでは、カラム値の更新は他のカラム値の更新に基づいています。

次のステートメントの結果- 

UPDATE table1 SET x = y + 10, y = 20

とは異なる結果になります- 

UPDATE table1 SET y = 20, x = y + 10

MySQLのSET句は、行ごとではなく、値ごとに評価されます。 この目的のために、 preserve_parameter_order が使用されます。 2タプルのPythonリストは* Update.values（）*メソッドへの引数として与えられます- 

stmt = table1.update(preserve_parameter_order = True).\
   values([(table1.c.y, 20), (table1.c.x, table1.c.y + 10)])

Listオブジェクトは辞書に似ていますが、順序付けられている点が異なります。 これにより、「y」列のSET句が最初にレンダリングされ、次に「x」列のSET句がレンダリングされます。

SQLAlchemyコア-複数のテーブルの削除

この章では、複数テーブルの更新機能を使用するのと同様の、複数テーブルの削除式について説明します。

多くのDBMS方言で、DELETEステートメントのWHERE句で複数のテーブルを参照できます。 PGおよびMySQLの場合、「DELETE USING」構文が使用されます。 SQL Serverの場合、「DELETE FROM」式を使用すると、複数のテーブルが参照されます。 SQLAlchemy * delete（）*コンストラクトは、次のようにWHERE句で複数のテーブルを指定することにより、これらのモードの両方を暗黙的にサポートします- 

stmt = users.delete().\
   where(users.c.id == addresses.c.id).\
   where(addresses.c.email_address.startswith('xyz%'))
conn.execute(stmt)

PostgreSQLのバックエンドでは、上記のステートメントの結果のSQLは次のようにレンダリングされます- 

DELETE FROM users USING addresses
WHERE users.id = addresses.id
AND (addresses.email_address LIKE %(email_address_1)s || '%%')

この動作をサポートしていないデータベースでこのメソッドを使用すると、コンパイラはNotImplementedErrorを発生させます。

SQLAlchemyコア-結合の使用

この章では、SQLAlchemyで結合を使用する方法を学習します。

結合の効果は、select（）コンストラクトの* columns句*または* where句*のいずれかに2つのテーブルを配置するだけで実現されます。 ここで、join（）およびouterjoin（）メソッドを使用します。

join（）メソッドは、あるテーブルオブジェクトから別のテーブルオブジェクトへの結合オブジェクトを返します。 

join(right, onclause = None, isouter = False, full = False)

上記のコードに記載されているパラメータの機能は次のとおりです-

  • right -結合の右側。これは任意のTableオブジェクトです
  • onclause -結合のON句を表すSQL式。 Noneのままにすると、外部キー関係に基づいて2つのテーブルを結合しようとします
  • isouter -Trueの場合、JOINではなくLEFT OUTER JOINをレンダリングします
  • full -Trueの場合、LEFT OUTER JOINではなくFULL OUTER JOINをレンダリングします

たとえば、join（）メソッドを次に使用すると、外部キーに基づいて自動的に結合されます。 

>>> print(students.join(addresses))

これは、次のSQL式と同等です- 

students JOIN addresses ON students.id = addresses.st_id

次のように参加基準を明示的に言及できます- 

j = students.join(addresses, students.c.id == addresses.c.st_id)

次のようにこの結合を使用して以下の選択構造を構築する場合- 

stmt = select([students]).select_from(j)

これは、次のSQL式になります- 

SELECT students.id, students.name, students.lastname
FROM students JOIN addresses ON students.id = addresses.st_id

エンジンを表す接続を使用してこのステートメントを実行すると、選択した列に属するデータが表示されます。 完全なコードは次のとおりです- 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()
conn = engine.connect()
students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

addresses = Table(
   'addresses', meta,
   Column('id', Integer, primary_key = True),
   Column('st_id', Integer,ForeignKey('students.id')),
   Column('postal_add', String),
   Column('email_add', String)
)

from sqlalchemy import join
from sqlalchemy.sql import select
j = students.join(addresses, students.c.id == addresses.c.st_id)
stmt = select([students]).select_from(j)
result = conn.execute(stmt)
result.fetchall()

以下は、上記のコードの出力です- 

[
   (1, 'Ravi', 'Kapoor'),
   (1, 'Ravi', 'Kapoor'),
   (3, 'Komal', 'Bhandari'),
   (5, 'Priya', 'Rajhans'),
   (2, 'Rajiv', 'Khanna')
]

SQLAlchemyコア-接続詞の使用

接続詞は、SQL式のWHERE句で使用される関係演算子を実装するSQLAlchemyモジュールの関数です。 演算子AND、OR、NOTなどは、2つの個別の論理式を組み合わせた複合式を形成するために使用されます。 SELECTステートメントでANDを使用する簡単な例は次のとおりです- 

SELECT * from EMPLOYEE WHERE salary>10000 AND age>30

SQLAlchemy関数and （）、or（）およびnot_（）は、それぞれAND、ORおよびNOT演算子を実装します。

and_（）関数

ANDで結合された式の結合を生成します。 より良い理解のために例を以下に示します- 

from sqlalchemy import and_

print(
   and_(
      students.c.name == 'Ravi',
      students.c.id <3
   )
)

これは次のように変換されます- 

students.name = :name_1 AND students.id < :id_1

学生テーブルのselect（）構造でand_（）を使用するには、次のコード行を使用します- 

stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))

次の性質のSELECT文が構築されます- 

SELECT students.id,
   students.name,
   students.lastname
FROM students
WHERE students.name = :name_1 AND students.id < :id_1

上記のSELECTクエリの出力を表示する完全なコードは次のとおりです- 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

from sqlalchemy import and_, or_
stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))
result = conn.execute(stmt)
print (result.fetchall())

学生テーブルに前の例で使用したデータが入力されていると仮定して、次の行が選択されます- 

[(1, 'Ravi', 'Kapoor')]

or_（）関数

ORで結合された式の結合を生成します。 or_（）を使用して、上記の例のstmtオブジェクトを次のものに置き換えます。 

stmt = select([students]).where(or_(students.c.name == 'Ravi', students.c.id <3))

これは、次のSELECTクエリと実質的に同等です- 

SELECT students.id,
   students.name,
   students.lastname
FROM students
WHERE students.name = :name_1
OR students.id < :id_1

あなたが置換を行い、上記のコードを実行すると、結果はOR条件に該当する2行になります- 

[(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Khanna')]

asc（）関数

昇順のORDER BY句を生成します。 関数は、関数をパラメーターとして適用する列を受け取ります。 

from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))

ステートメントは、次のSQL式を実装します- 

SELECT students.id,
   students.name,
   students.lastname
FROM students
ORDER BY students.name ASC

次のコードは、学生テーブルのすべてのレコードを名前列の昇順でリストします- 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))
result = conn.execute(stmt)

for row in result:
   print (row)

上記のコードは次の出力を生成します- 

(4, 'Abdul', 'Sattar')
(3, 'Komal', 'Bhandari')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')

desc（）関数

同様に、desc（）関数は次のように降順のORDER BY句を生成します- 

from sqlalchemy import desc
stmt = select([students]).order_by(desc(students.c.lastname))

同等のSQL式は- 

SELECT students.id,
   students.name,
   students.lastname
FROM students
ORDER BY students.lastname DESC

そして、上記のコード行の出力は- 

(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')
(3, 'Komal', 'Bhandari')

between（）関数

BETWEEN述語句を生成します。 これは通常、特定の列の値が範囲内にあるかどうかを検証するために使用されます。 たとえば、次のコードは、id列が2〜4の行を選択します- 

from sqlalchemy import between
stmt = select([students]).where(between(students.c.id,2,4))
print (stmt)

結果のSQL式は次のようになります- 

SELECT students.id,
   students.name,
   students.lastname
FROM students
WHERE students.id
BETWEEN :id_1 AND :id_2

そして、結果は次のようになります- 

(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')

SQLAlchemyコア-関数の使用

この章では、SQLAlchemyで使用される重要な関数の一部について説明します。

標準SQLは、ほとんどの方言で実装されている多くの関数を推奨しています。 渡された引数に基づいて単一の値を返します。 一部のSQL関数は引数として列を取りますが、一部は汎用です。 * SQLAlchemy APIのfuncキーワードは、これらの関数を生成するために使用されます*。

SQLでは、now（）は汎用関数です。 次のステートメントは、funcを使用してnow（）関数をレンダリングします- 

from sqlalchemy.sql import func
result = conn.execute(select([func.now()]))
print (result.fetchone())

上記のコードのサンプル結果は以下のようになります- 

(datetime.datetime(2018, 6, 16, 6, 4, 40),)

一方、テーブルから選択された行の数を返すcount（）関数は、以下のfuncの使用によってレンダリングされます- 

from sqlalchemy.sql import func
result = conn.execute(select([func.count(students.c.id)]))
print (result.fetchone())

上記のコードから、studentsテーブルの行数のカウントがフェッチされます。

いくつかの組み込みSQL関数は、次のデータでEmployeeテーブルを使用して示されます-

ID Name Marks
1 Kamal 56
2 Fernandez 85
3 Sunil 62
4 Bhaskar 76

max（）関数は、SQLAlchemyのfuncを使用することで実装されます。これにより、85個の合計最大マークが取得されます- 

from sqlalchemy.sql import func
result = conn.execute(select([func.max(employee.c.marks)]))
print (result.fetchone())

同様に、56、最小マークを返すmin（）関数は、次のコードによってレンダリングされます- 

from sqlalchemy.sql import func
result = conn.execute(select([func.min(employee.c.marks)]))
print (result.fetchone())

したがって、AVG（）関数は、以下のコードを使用して実装することもできます- 

from sqlalchemy.sql import func
result = conn.execute(select([func.avg(employee.c.marks)]))
print (result.fetchone())

Functions are normally used in the columns clause of a select statement.
They can also be given label as well as a type. A label to function allows the result
to be targeted in a result row based on a string name, and a type is required when
you need result-set processing to occur.from sqlalchemy.sql import func

result = conn.execute(select([func.max(students.c.lastname).label('Name')]))

print (result.fetchone())

SQLAlchemyコア-セット操作の使用

前の章では、max（）、min（）、count（）などのさまざまな関数について学びました。ここでは、集合演算とその使用法について学びます。

UNIONやINTERSECTなどの集合演算は、標準SQLとそのほとんどの方言でサポートされています。 SQLAlchemyは、次の機能の助けを借りてそれらを実装します-

連合（）

2つ以上のSELECTステートメントの結果を組み合わせながら、UNIONは結果セットから重複を削除します。 列とデータ型の数は両方のテーブルで同じでなければなりません。

union（）関数は、複数のテーブルからCompoundSelectオブジェクトを返します。 次の例は、その使用を示しています- 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()
conn = engine.connect()
addresses = Table(
   'addresses', meta,
   Column('id', Integer, primary_key = True),
   Column('st_id', Integer),
   Column('postal_add', String),
   Column('email_add', String)
)

u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))))

result = conn.execute(u)
result.fetchall()

ユニオン構造は、次のSQL式に変換されます- 

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

アドレス表から、次の行はユニオン操作を表します- 

[
   (1, 1, 'Shivajinagar Pune', 'ravi@gmail.com'),
   (2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com'),
   (3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com'),
   (4, 5, 'MG Road Bangaluru', 'as@yahoo.com')
]

union_all（）

UNION ALL操作では、重複を削除できず、結果セット内のデータをソートできません。 たとえば、上記のクエリでは、効果を確認するためにUNIONがUNION ALLに置き換えられています。 

u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))

対応するSQL式は次のとおりです- 

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION ALL SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

except_（）

SQL EXCEPT 句/演算子は、2つのSELECTステートメントを結合し、2番目のSELECTステートメントによって返されない最初のSELECTステートメントから行を返すために使用されます。 except_（）関数は、EXCEPT句を含むSELECT式を生成します。

次の例では、except_（）関数は、email_addフィールドに「gmail.com」が含まれる住所テーブルのレコードのみを返しますが、postal_addフィールドの一部として「Pune」が含まれるレコードは除外します。 

u = except_(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))

上記のコードの結果は、次のSQL式です- 

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? EXCEPT SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

アドレステーブルに以前の例で使用されたデータが含まれていると仮定すると、次の出力が表示されます- 

[(2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com'),
   (3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')]

intersection（）

INTERSECT演算子を使用して、SQLは両方のSELECTステートメントからの共通行を表示します。 intersection（）関数はこの振る舞いを実装します。

次の例では、2つのSELECT構造体は、intersect（）関数のパラメーターです。 1つはemail_add列の一部として「gmail.com」を含む行を返し、もう1つはpostal_add列の一部として「Pune」を含む行を返します。 結果は、両方の結果セットの共通行になります。 

u = intersect(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))

実際には、これは次のSQL文と同等です- 

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? INTERSECT SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

2つのバインドされたパラメーター「％gmail.com」と「％Pune」は、以下に示すように、アドレステーブルの元のデータから単一の行を生成します- 

[(1, 1, 'Shivajinagar Pune', 'ravi@gmail.com')]

SQLAlchemy ORM-マッピングの宣言

SQLAlchemyのオブジェクトリレーショナルマッパーAPIの主な目的は、ユーザー定義のPythonクラスをデータベーステーブルに関連付け、それらのクラスのオブジェクトを対応するテーブルの行に関連付けることです。 オブジェクトと行の状態の変化は、互いに同期的に一致します。 SQLAlchemyを使用すると、ユーザー定義のクラスと定義された関係の観点からデータベースクエリを表現できます。

ORMは、SQL式言語の上に構築されます。 これは、高レベルで抽象化された使用パターンです。 実際、ORMは式言語の適用された使用法です。

成功したアプリケーションは、オブジェクトリレーショナルマッパーのみを使用して構築できますが、ORMを使用して構築したアプリケーションでは、特定のデータベースとのやり取りが必要な場合に、式言語を直接使用できます。

マッピングを宣言する

まず、create_engine（）関数を呼び出してエンジンオブジェクトを設定し、その後でSQL操作を実行するために使用します。 この関数には2つの引数があります。1つはデータベースの名前で、もう1つはTrueに設定された場合のエコーパラメーターであり、アクティビティログを生成します。 存在しない場合、データベースが作成されます。 次の例では、SQLiteデータベースが作成されます。 

from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)

Engine.execute（）やEngine.connect（）などのメソッドが呼び出されると、エンジンはデータベースへの実際のDBAPI接続を確立します。 次に、エンジンを直接使用しないSQLORMを発行するために使用されます。代わりに、ORMによって舞台裏で使用されます。

ORMの場合、構成プロセスは、データベーステーブルを記述してから、それらのテーブルにマッピングされるクラスを定義することから始まります。 SQLAlchemyでは、これら2つのタスクは一緒に実行されます。 これは、宣言システムを使用して行われます。作成されたクラスには、マップ先の実際のデータベーステーブルを記述するディレクティブが含まれます。

基本クラスは、宣言システムのクラスとマップされたテーブルのカタログを保存します。 これは、宣言ベースクラスと呼ばれます。 通常、一般的にインポートされるモジュールには、このベースのインスタンスが1つだけあります。 declarative_base（）関数は、基本クラスを作成するために使用されます。 この関数は、sqlalchemy.ext.declarativeモジュールで定義されています。 

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

基底クラスが宣言されると、マッピングされたクラスをいくつでも定義できます。 次のコードは、顧客のクラスを定義します。 マッピングされるテーブルと、その中の列の名前とデータ型が含まれます。 

class Customers(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

Declarativeのクラスには、 tablename 属性と、主キーの一部である少なくとも1つの Column が必要です。 宣言は、すべての Column オブジェクトを descriptors として知られる特別なPythonアクセサーに置き換えます。 このプロセスはインストルメンテーションと呼ばれ、SQLコンテキストでテーブルを参照する手段を提供し、データベースから列の値を永続化してロードできるようにします。

通常のPythonクラスのようなこのマッピングされたクラスには、要件に従って属性とメソッドがあります。

宣言システムのクラスに関する情報は、テーブルメタデータと呼ばれます。 SQLAlchemyは、Tableオブジェクトを使用して、宣言によって作成された特定のテーブルのこの情報を表します。 Tableオブジェクトは仕様に従って作成され、Mapperオブジェクトを作成することでクラスに関連付けられます。 このマッパーオブジェクトは直接使用されませんが、マップされたクラスとテーブルの間のインターフェイスとして内部的に使用されます。

各Tableオブジェクトは、MetaDataと呼ばれるより大きなコレクションのメンバーであり、このオブジェクトは、宣言ベースクラスの .metadata 属性を使用して使用できます。 * MetaData.create_all（）*メソッドは、データベース接続のソースとしてエンジンを渡します。 まだ作成されていないすべてのテーブルに対して、データベースに対してCREATE TABLEステートメントを発行します。 

Base.metadata.create_all(engine)

データベースとテーブルを作成し、Pythonクラスをマップするための完全なスクリプトを以下に示します- 

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   id = Column(Integer, primary_key=True)

   name = Column(String)
   address = Column(String)
   email = Column(String)
Base.metadata.create_all(engine)

実行されると、Pythonコンソールは実行されている次のSQL式をエコーし​​ます- 

CREATE TABLE customers (
   id INTEGER NOT NULL,
   name VARCHAR,
   address VARCHAR,
   email VARCHAR,
   PRIMARY KEY (id)
)

SQLiteStudioグラフィックツールを使用してSales.dbを開くと、上記の構造を持つ顧客テーブルが表示されます。

顧客テーブル

SQLAlchemy ORM-セッションの作成

データベースと対話するには、そのハンドルを取得する必要があります。 セッションオブジェクトは、データベースへのハンドルです。 セッションクラスは、sessionmaker（）を使用して定義されます。これは、以前に作成されたエンジンオブジェクトにバインドされる構成可能なセッションファクトリメソッドです。 

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)

セッションオブジェクトは、次のようにデフォルトのコンストラクタを使用して設定されます- 

session = Session()

セッションクラスの頻繁に必要なメソッドの一部を以下に示します-

Sr.No. Method & Description
1

begin()

このセッションでトランザクションを開始します

2

add()

セッションにオブジェクトを配置します。 その状態は、次のフラッシュ操作でデータベースに保持されます

3

add_all()

オブジェクトのコレクションをセッションに追加します

4

commit()

すべてのアイテムと進行中のトランザクションをフラッシュします

5

delete()

トランザクションを削除済みとしてマークします

6

execute()

SQL式を実行します

7

expire()

インスタンスの属性を期限切れとしてマークします

8

flush()

すべてのオブジェクト変更をデータベースにフラッシュします

9

invalidate()

接続無効化を使用してセッションを閉じます

10

rollback()

進行中の現在のトランザクションをロールバックします

11

close()

すべてのアイテムをクリアし、進行中のトランザクションを終了することにより、現在のセッションを閉じます

SQLAlchemy ORM-オブジェクトの追加

SQLAlchemy ORMの前の章で、マッピングを宣言してセッションを作成する方法を学びました。 この章では、テーブルにオブジェクトを追加する方法を学びます。

customersテーブルにマッピングされたCustomerクラスを宣言しました。 このクラスのオブジェクトを宣言し、セッションオブジェクトのadd（）メソッドで永続的にテーブルに追加する必要があります。 

c1 = Sales(name = 'Ravi Kumar', address = 'Station Road Nanded', email = 'ravi@gmail.com')
session.add(c1)

このトランザクションは、commit（）メソッドを使用してフラッシュされるまで保留されていることに注意してください。 

session.commit()

以下は、顧客テーブルにレコードを追加するための完全なスクリプトです- 

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key=True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

c1 = Customers(name = 'Ravi Kumar', address = 'Station Road Nanded', email = 'ravi@gmail.com')

session.add(c1)
session.commit()

複数のレコードを追加するには、セッションクラスの* add_all（）*メソッドを使用できます。 

session.add_all([
   Customers(name = 'Komal Pande', address = 'Koti, Hyderabad', email = 'komal@gmail.com'),
   Customers(name = 'Rajender Nath', address = 'Sector 40, Gurgaon', email = 'nath@gmail.com'),
   Customers(name = 'S.M.Krishna', address = 'Budhwar Peth, Pune', email = 'smk@gmail.com')]
)

session.commit()

SQLiteStudioのテーブルビューは、レコードが顧客テーブルに永続的に追加されることを示しています。 次の画像は結果を示しています-

顧客テーブルレコードの追加

SQLAlchemy ORM-クエリの使用

SQLAlchemy ORMによって生成されたすべてのSELECTステートメントは、Queryオブジェクトによって構築されます。 それは生成インターフェースを提供します。したがって、連続した呼び出しは、新しいQueryオブジェクト、それに関連付けられた追加の基準とオプションを持つ前者のコピーを返します。

クエリオブジェクトは、最初に次のようにセッションのquery（）メソッドを使用して生成されます- 

q = session.query(mapped class)

次の文も上記の文と同等です- 

q = Query(mappedClass, session)

クエリオブジェクトには、オブジェクトのリストの形式で結果セットを返すall（）メソッドがあります。 顧客テーブルで実行する場合- 

result = session.query(Customers).all()

このステートメントは、次のSQL式と実質的に同等です- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers

結果オブジェクトは、以下のForループを使用してトラバースし、基礎となる顧客テーブルのすべてのレコードを取得できます。 これは、Customersテーブルのすべてのレコードを表示する完全なコードです- 

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   id = Column(Integer, primary_key =  True)
   name = Column(String)

   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).all()

for row in result:
   print ("Name: ",row.name, "Address:",row.address, "Email:",row.email)

Pythonコンソールには、以下のようにレコードのリストが表示されます- 

Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
Name: Komal Pande Address: Koti, Hyderabad Email: komal@gmail.com
Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com

Queryオブジェクトには、次の便利なメソッドもあります-

Sr.No. Method & Description
1

add_columns()

返される結果列のリストに1つ以上の列式を追加します。

2

add_entity()

返される結果列のリストにマッピングされたエンティティを追加します。

3

count()

このクエリが返す行数を返します。

4

delete()

一括削除クエリを実行します。 このクエリと一致した行をデータベースから削除します。

5

distinct()

DISTINCT句をクエリに適用し、新しく生成されたクエリを返します。

6

filter()

SQL式を使用して、指定されたフィルタリング基準をこのクエリのコピーに適用します。

7

first()

このクエリの最初の結果を返します。結果に行が含まれていない場合はNoneを返します。

8

get()

指定された主キー識別子に基づいてインスタンスを返し、所有するセッションのIDマップへの直接アクセスを提供します。

9

group_by()

1つ以上のGROUP BY基準をクエリに適用し、新しく生成されたクエリを返します

10

join()

このQueryオブジェクトの基準に対してSQL JOINを作成し、生成的に適用して、新しく生成されたQueryを返します。

11

one()

正確に1つの結果を返すか、例外を発生させます。

12

order_by()

クエリに1つ以上のORDER BY基準を適用し、新しく生成されたクエリを返します。

13

update()

一括更新クエリを実行し、データベース内のこのクエリに一致する行を更新します。

SQLAlchemy ORM-オブジェクトの更新

この章では、目的の値でテーブルを変更または更新する方法を説明します。

オブジェクトの特定の属性のデータを変更するには、それに新しい値を割り当て、変更をコミットして変更を永続化する必要があります。

ID = 2のCustomersテーブルで、主キー識別子を持つテーブルからオブジェクトを取得しましょう。 次のようにセッションのget（）メソッドを使用できます- 

x = session.query(Customers).get(2)

以下のコードで選択したオブジェクトの内容を表示できます- 

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

私たちの顧客テーブルから、次の出力が表示されるはずです- 

Name: Komal Pande Address: Koti, Hyderabad Email: komal@gmail.com

次に、以下に示すように新しい値を割り当てることにより、アドレスフィールドを更新する必要があります- 

x.address = 'Banjara Hills Secunderabad'
session.commit()

変更はデータベースに永続的に反映されます。 次のように first（）method を使用して、テーブルの最初の行に対応するオブジェクトを取得します- 

x = session.query(Customers).first()

これは、次のSQL式を実行します- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
LIMIT ? OFFSET ?

バインドされたパラメーターは、それぞれLIMIT = 1およびOFFSET = 0になり、最初の行が選択されることを意味します。 

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

さて、最初の行を表示する上記のコードの出力は次のとおりです- 

Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com

今名前属性を変更し、以下のコードを使用して内容を表示します- 

x.name = 'Ravi Shrivastava'
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

上記のコードの出力は- 

Name: Ravi Shrivastava Address: Station Road Nanded Email: ravi@gmail.com

変更が表示されても、コミットされません。 以下のコードで rollback（）method を使用すると、以前の永続的な位置を保持できます。 

session.rollback()

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

最初のレコードの元の内容が表示されます。

一括更新の場合、Queryオブジェクトのupdate（）メソッドを使用します。 各行の名前に「Mr.」というプレフィックスを付けてみましょう（ID = 2を除く）。 対応するupdate（）ステートメントは次のとおりです- 

session.query(Customers).filter(Customers.id! = 2).
update({Customers.name:"Mr."+Customers.name}, synchronize_session = False)
  • update（）メソッドには、次の2つのパラメーターが必要です-*
  • キーが更新される属性であり、値が属性の新しいコンテンツであるキー値の辞書。
  • セッションの属性を更新する戦略を示すsynchronize_session属性。 有効な値はfalseです。セッションを同期しない場合、フェッチ：更新の前に選択クエリを実行して、更新クエリと一致するオブジェクトを見つけます。そして評価：セッション内のオブジェクトの基準を評価します。

テーブルの4行のうち3行には「Mr.」というプレフィックスが付いていますが、変更はコミットされないため、SQLiteStudioのテーブルビューには反映されません。 セッションをコミットするときにのみ更新されます。

SQLAlchemy ORM-フィルターの適用

この章では、フィルターを適用する方法と、特定のフィルター操作とそのコードについて説明します。

Queryオブジェクトで表される結果セットは、filter（）メソッドを使用して特定の基準に従うことができます。 フィルタ方法の一般的な使用法は次のとおりです- 

session.query(class).filter(criteria)

次の例では、CustomersテーブルのSELECTクエリによって取得された結果セットは、条件（ID> 2）によってフィルター処理されます- 

result = session.query(Customers).filter(Customers.id>2)

このステートメントは、次のSQL式に変換されます- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.id > ?

バインドされたパラメーター（？）は2として指定されるため、ID column> 2の行のみが表示されます。 完全なコードは以下のとおりです- 

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)

   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).filter(Customers.id>2)

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Pythonコンソールに表示される出力は次のとおりです- 

ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com

SQLAlchemy ORM-フィルター演算子

次に、それぞれのコードと出力を使用してフィルター操作を学習します。

等しい

使用される通常の演算子は==であり、同等性をチェックする基準を適用します。 

result = session.query(Customers).filter(Customers.id == 2)

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

SQLAlchemyは次のSQL式を送信します- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.id = ?

上記のコードの出力は次のとおりです- 

ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: komal@gmail.com

等しくない

等しくないために使用される演算子は！=であり、等しくない基準を提供します。 

result = session.query(Customers).filter(Customers.id! = 2)

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

結果のSQL式は- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.id != ?

上記のコード行の出力は次のとおりです- 

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com

Like

like（）メソッド自体は、SELECT式のWHERE句のLIKE基準を生成します。 

result = session.query(Customers).filter(Customers.name.like('Ra%'))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

上記のSQLAlchemyコードは、次のSQL式と同等です- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.name LIKE ?

そして、上記のコードの出力は- 

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com

IN

この演算子は、列の値がリスト内のアイテムのコレクションに属しているかどうかを確認します。 in_（）メソッドによって提供されます。 

result = session.query(Customers).filter(Customers.id.in_([1,3]))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

ここで、SQLiteエンジンによって評価されるSQL式は次のようになります- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.id IN (?, ?)

上記のコードの出力は次のとおりです- 

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com

AND

このコンジャンクションは、フィルターに複数のコンマ区切り基準を入れるか、以下に示すようにand_（）メソッドを使用して生成されます- 

result = session.query(Customers).filter(Customers.id>2, Customers.name.like('Ra%'))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)
from sqlalchemy import and_
result = session.query(Customers).filter(and_(Customers.id>2, Customers.name.like('Ra%')))

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

上記のアプローチの両方は、同様のSQL式をもたらします- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.id > ? AND customers.name LIKE ?

上記のコード行の出力は- 

ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com

OR

この論理積は、* or_（）メソッド*によって実装されます。 

from sqlalchemy import or_
result = session.query(Customers).filter(or_(Customers.id>2, Customers.name.like('Ra%')))

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

その結果、SQLiteエンジンは以下の同等のSQL式を取得します- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.id > ? OR customers.name LIKE ?

上記のコードの出力は次のとおりです- 

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com

リストとスカラーを返す

すぐにSQLを発行し、ロードされたデータベースの結果を含む値を返すQueryオブジェクトのメソッドがいくつかあります。

リストとスカラーを返す簡単な概要は次のとおりです-

すべて（）

リストを返します。 以下に示すのは、all（）関数のコード行です。 

session.query(Customers).all()

Pythonコンソールには、次のSQL式が表示されます- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers

最初（）

1の制限を適用し、最初の結果をスカラーとして返します。 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
LIMIT ? OFFSET ?

LIMITのバインドパラメーターは1で、OFFSETのバインドパラメーターは0です。

1（）

このコマンドはすべての行を完全にフェッチし、結果にオブジェクトIDまたは複合行が1つだけ存在しない場合、エラーが発生します。 

session.query(Customers).one()

複数の行が見つかりました- 

MultipleResultsFound: Multiple rows were found for one()

行が見つかりませんでした- 

NoResultFound: No row was found for one()

one（）メソッドは、「アイテムが見つかりません」と「複数のアイテムが見つかりました」を別々に処理することを想定しているシステムに役立ちます。

スカラー（）

それはone（）メソッドを呼び出し、成功すると次のように行の最初の列を返します- 

session.query(Customers).filter(Customers.id == 3).scalar()

これは、次のSQL文を生成します- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.id = ?

SQLAlchemy ORM-テキストSQL

以前、text（）関数を使用したテキストSQLは、SQLAlchemyのコア式言語の観点から説明されていました。 次に、ORMの観点から説明します。

リテラル文字列は、text（）構造で使用を指定することにより、Queryオブジェクトで柔軟に使用できます。 ほとんどの適用可能なメソッドはそれを受け入れます。 たとえば、filter（）およびorder_by（）。

以下の例では、filter（）メソッドは文字列「id <3」をWHERE id <3に変換します 

from sqlalchemy import text
for cust in session.query(Customers).filter(text("id<3")):
   print(cust.name)

生成された生のSQL式は、以下に示すコードを使用してWHERE句へのフィルタの変換を示しています- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE id<3

Customersテーブルのサンプルデータから、2つの行が選択され、名前列が次のように印刷されます- 

Ravi Kumar
Komal Pande

文字列ベースのSQLでバインドパラメータを指定するには、コロンを使用し、値を指定するには、params（）メソッドを使用します。 

cust = session.query(Customers).filter(text("id = :value")).params(value = 1).one()

Pythonコンソールに表示される有効なSQLは以下のようになります- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE id = ?

完全に文字列ベースのステートメントを使用するには、完全なステートメントを表すtext（）コンストラクトをfrom_statement（）に渡すことができます。 

session.query(Customers).from_statement(text("SELECT *FROM customers")).all()

上記のコードの結果は、以下に示すように基本的なSELECTステートメントになります- 

SELECT* FROM customers

明らかに、顧客テーブルのすべてのレコードが選択されます。

text（）構造により、テキストSQLをコアまたはORMにマップされた列式に位置的にリンクできます。 これは、列式を位置引数としてTextClause.columns（）メソッドに渡すことで実現できます。 

stmt = text("SELECT name, id, name, address, email FROM customers")
stmt = stmt.columns(Customers.id, Customers.name)
session.query(Customers.id, Customers.name).from_statement(stmt).all()

SQLiteエンジンが上記のコードによって生成された次の式を実行しても、すべての行のID列と名前列が選択されますtext（）メソッドのすべての列を示します- 

SELECT name, id, name, address, email FROM customers

SQLAlchemy ORM-関係の構築

このセッションでは、データベース内の既存のテーブルに関連する別のテーブルの作成について説明します。 得意先テーブルには、得意先のマスタデータが含まれています。 ここで、顧客に属する請求書をいくつでも持つことができる請求書テーブルを作成する必要があります。 これは、1対多の関係の場合です。

宣言を使用して、以下に示すように、マップされたクラスである請求書とともにこのテーブルを定義します- 

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship

class Customer(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

class Invoice(Base):
   __tablename__ = 'invoices'

   id = Column(Integer, primary_key = True)
   custid = Column(Integer, ForeignKey('customers.id'))
   invno = Column(Integer)
   amount = Column(Integer)
   customer = relationship("Customer", back_populates = "invoices")

Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")
Base.metadata.create_all(engine)

これにより、CREATE TABLEクエリが以下のようにSQLiteエンジンに送信されます- 

CREATE TABLE invoices (
   id INTEGER NOT NULL,
   custid INTEGER,
   invno INTEGER,
   amount INTEGER,
   PRIMARY KEY (id),
   FOREIGN KEY(custid) REFERENCES customers (id)
)

SQLiteStudioツールを使用して、sales.dbに新しいテーブルが作成されていることを確認できます。

Sales.db新しいテーブル

請求書クラスは、custid属性にForeignKeyコンストラクトを適用します。 このディレクティブは、この列の値を、customersテーブルのid列に存在する値に制限する必要があることを示します。 これはリレーショナルデータベースのコア機能であり、接続されていないテーブルのコレクションを豊かな重複関係に変換する「接着剤」です。

relationship（）として知られる2番目のディレクティブは、属性Invoice.customerを使用してInvoiceクラスをCustomerクラスにリンクする必要があることをORMに伝えます。 relationship（）は、2つのテーブル間の外部キー関係を使用して、このリンケージの性質を判断し、多対1であると判断します。

追加のrelationship（）ディレクティブは、Customer.invoices属性の下のCustomerマップクラスに配置されます。 パラメーターrelationship.back_populatesは、補完的な属性名を参照するために割り当てられます。これにより、各relationship（）は、逆に表現された同じ関係についてインテリジェントな決定を行うことができます。 一方では、Invoices.customerはInvoicesインスタンスを参照し、他方では、Customer.invoicesはCustomerインスタンスのリストを参照します。

関係関数は、SQLAlchemy ORMパッケージの関係APIの一部です。 2つのマップされたクラス間の関係を提供します。 これは、親子関係または関連テーブルの関係に対応します。

以下は、見つかった基本的な関係パターンです-

1対多

1対多の関係は、子テーブルの外部キーを使用して親を参照します。 次に、子で表されるアイテムのコレクションを参照するように、親でrelationship（）が指定されます。 relationship.back_populatesパラメーターは、「逆」側が多対1の1対多の双方向関係を確立するために使用されます。

多対一

一方、多対1の関係では、親テーブルに外部キーを配置して子を参照します。 relationship（）は親で宣言され、新しいスカラー保持属性が作成されます。 ここでも、relationship.back_populatesパラメーターがBidirectionalbehaviourに使用されます。

一対一

一対一の関係は、本質的に本質的に双方向の関係です。 uselistフラグは、関係の「多」側のコレクションではなく、スカラー属性の配置を示します。 1対多を1対1タイプの関係に変換するには、uselistパラメーターをfalseに設定します。

多対多

多対多の関係は、外部キーで属性を定義することにより、2つのクラスに関連する関連付けテーブルを追加することによって確立されます。 それは、relationship（）の2次引数によって示されます。 通常、Tableは宣言的な基本クラスに関連付けられたMetaDataオブジェクトを使用するため、ForeignKeyディレクティブはリンクするリモートテーブルを見つけることができます。 各relationship（）のrelationship.back_populatesパラメーターは、双方向の関係を確立します。 関係の両側にはコレクションが含まれます。

関連オブジェクトの使用

この章では、SQLAlchemy ORMの関連オブジェクトに焦点を当てます。

これで、Customerオブジェクトを作成すると、空の請求書コレクションがPythonリストの形式で表示されます。 

c1 = Customer(name = "Gopal Krishna", address = "Bank Street Hydarebad", email = "gk@gmail.com")

c1.invoicesのinvoices属性は空のリストになります。 リスト内のアイテムを次のように割り当てることができます- 

c1.invoices = [Invoice(invno = 10, amount = 15000), Invoice(invno = 14, amount = 3850)]

次のようにSessionオブジェクトを使用してデータベースにこのオブジェクトをコミットしましょう- 

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
session.add(c1)
session.commit()

これにより、顧客と請求書のテーブルに対するINSERTクエリが自動的に生成されます- 

INSERT INTO customers (name, address, email) VALUES (?, ?, ?)
('Gopal Krishna', 'Bank Street Hydarebad', 'gk@gmail.com')
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 10, 15000)
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 14, 3850)

SQLiteStudioのテーブルビューで顧客テーブルと請求書テーブルの内容を見てみましょう-

顧客テーブルビュー

請求書テーブル

あなたは、以下のコマンドを使用して、コンストラクタ自体に請求書のマッピングされた属性を提供することにより、顧客オブジェクトを構築することができます- 

c2 = [
   Customer(
      name = "Govind Pant",
      address = "Gulmandi Aurangabad",
      email = "gpant@gmail.com",
      invoices = [Invoice(invno = 3, amount = 10000),
      Invoice(invno = 4, amount = 5000)]
   )
]

または以下に示すようにセッションオブジェクトのadd_all（）関数を使用して追加されるオブジェクトのリスト- 

rows = [
   Customer(
      name = "Govind Kala",
      address = "Gulmandi Aurangabad",
      email = "kala@gmail.com",
      invoices = [Invoice(invno = 7, amount = 12000), Invoice(invno = 8, amount = 18500)]),

   Customer(
      name = "Abdul Rahman",
      address = "Rohtak",
      email = "abdulr@gmail.com",
      invoices = [Invoice(invno = 9, amount = 15000),
      Invoice(invno = 11, amount = 6000)
   ])
]

session.add_all(rows)
session.commit()

SQLAlchemy ORM-結合の使用

2つのテーブルができたので、両方のテーブルに同時にクエリを作成する方法を確認します。 CustomerとInvoiceの間の単純な暗黙的な結合を構築するために、Query.filter（）を使用して、関連する列を一緒に同等化できます。 以下では、このメソッドを使用して、CustomerおよびInvoiceエンティティを一度に読み込みます- 

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

for c, i in session.query(Customer, Invoice).filter(Customer.id == Invoice.custid).all():
   print ("ID: {} Name: {} Invoice No: {} Amount: {}".format(c.id,c.name, i.invno, i.amount))

SQLAlchemyによって発行されたSQL式は次のとおりです- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email, invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM customers, invoices
WHERE customers.id = invoices.custid

そして、上記のコード行の結果は次のとおりです- 

ID: 2 Name: Gopal Krishna Invoice No: 10 Amount: 15000
ID: 2 Name: Gopal Krishna Invoice No: 14 Amount: 3850
ID: 3 Name: Govind Pant Invoice No: 3 Amount: 10000
ID: 3 Name: Govind Pant Invoice No: 4 Amount: 5000
ID: 4 Name: Govind Kala Invoice No: 7 Amount: 12000
ID: 4 Name: Govind Kala Invoice No: 8 Amount: 8500
ID: 5 Name: Abdul Rahman Invoice No: 9 Amount: 15000
ID: 5 Name: Abdul Rahman Invoice No: 11 Amount: 6000

実際のSQL JOIN構文は、次のようにQuery.join（）メソッドを使用して簡単に達成されます- 

session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()

結合のためのSQL式がコンソールに表示されます- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers JOIN invoices ON customers.id = invoices.custid
WHERE invoices.amount = ?

forループを使用して結果を反復処理できます- 

result = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500)
for row in result:
   for inv in row.invoices:
      print (row.id, row.name, inv.invno, inv.amount)

バインドパラメータとして8500を使用すると、次の出力が表示されます- 

4 Govind Kala 8 8500

Query.join（）は、これらのテーブル間に外部キーが1つしかないため、これらのテーブルを結合する方法を知っています。 外部キーが存在しない場合、またはより多くの外部キーが存在しない場合、Query.join（）は、次の形式のいずれかが使用された場合により良く機能します-

query.join(Invoice, id == Address.custid) explicit condition
query.join(Customer.invoices) specify relationship from left to right
query.join(Invoice, Customer.invoices) same, with explicit target
query.join('invoices') same, using a string

同様に、outerjoin（）関数を使用して、左外部結合を実現できます。 

query.outerjoin(Customer.invoices)

subquery（）メソッドは、エイリアス内に埋め込まれたSELECTステートメントを表すSQL式を生成します。 

from sqlalchemy.sql import func

stmt = session.query(
   Invoice.custid, func.count('*').label('invoice_count')
).group_by(Invoice.custid).subquery()

stmtオブジェクトには、次のようなSQL文が含まれます- 

SELECT invoices.custid, count(:count_1) AS invoice_count FROM invoices GROUP BY invoices.custid

ステートメントを作成すると、Tableコンストラクトのように動作します。 ステートメントの列は、以下のコードに示すようにcと呼ばれる属性を介してアクセス可能です- 

for u, count in session.query(Customer, stmt.c.invoice_count).outerjoin(stmt, Customer.id == stmt.c.custid).order_by(Customer.id):
   print(u.name, count)

上記のforループは、次のように請求書の名前ごとのカウントを表示します- 

Arjun Pandit None
Gopal Krishna 2
Govind Pant 2
Govind Kala 2
Abdul Rahman 2

一般的な関係演算子

この章では、関係を構築する演算子について説明します。

eq（）

上記の演算子は、多対1の「等しい」比較です。 この演算子のコード行は次のとおりです- 

s = session.query(Customer).filter(Invoice.invno.__eq__(12))

上記のコード行の同等のSQLクエリは- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers, invoices
WHERE invoices.invno = ?

ne（）

この演算子は、多対1の「等しくない」比較です。 この演算子のコード行は次のとおりです- 

s = session.query(Customer).filter(Invoice.custid.__ne__(2))

上記のコード行の同等のSQLクエリを以下に示します- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers, invoices
WHERE invoices.custid != ?

contains（）

この演算子は1対多のコレクションに使用され、以下に示すのはcontains（）のコードです- 

s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5]))

上記のコード行の同等のSQLクエリは- 

SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE (invoices.invno LIKE '%' + ? || '%')

どれか（）

any（）演算子は、以下に示すようにコレクションに使用されます- 

s = session.query(Customer).filter(Customer.invoices.any(Invoice.invno==11))

上記のコード行の同等のSQLクエリを以下に示します- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE EXISTS (
   SELECT 1
   FROM invoices
   WHERE customers.id = invoices.custid
   AND invoices.invno = ?)

has（）

この演算子は、次のようにスカラー参照に使用されます- 

s = session.query(Invoice).filter(Invoice.customer.has(name = 'Arjun Pandit'))

上記のコード行の同等のSQLクエリは- 

SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE EXISTS (
   SELECT 1
   FROM customers
   WHERE customers.id = invoices.custid
   AND customers.name = ?)

SQLAlchemy ORM-Eager Loading

負荷が大きいと、クエリの数が減ります。 SQLAlchemyは、クエリに追加の指示を与えるクエリオプションを介して呼び出される積極的な読み込み関数を提供します。 これらのオプションは、Query.options（）メソッドを介してさまざまな属性をロードする方法を決定します。

サブクエリロード

Customer.invoicesを熱心にロードする必要があります。 orm.subqueryload（）オプションは、ロードしたばかりの結果に関連付けられたコレクションを完全にロードする2番目のSELECTステートメントを提供します。 「subquery」という名前により、SELECTステートメントは、再利用されたクエリを介して直接構築され、関連テーブルに対するSELECTへのサブクエリとして埋め込まれます。 

from sqlalchemy.orm import subqueryload
c1 = session.query(Customer).options(subqueryload(Customer.invoices)).filter_by(name = 'Govind Pant').one()

これは、次の2つのSQL式になります- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.name = ?
('Govind Pant',)

SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount, anon_1.customers_id
AS anon_1_customers_id
FROM (
   SELECT customers.id
   AS customers_id
   FROM customers
   WHERE customers.name = ?)

AS anon_1
JOIN invoices
ON anon_1.customers_id = invoices.custid
ORDER BY anon_1.customers_id, invoices.id 2018-06-25 18:24:47,479
INFO sqlalchemy.engine.base.Engine ('Govind Pant',)

2つのテーブルからデータにアクセスするには、以下のプログラムを使用することができます- 

print (c1.name, c1.address, c1.email)

for x in c1.invoices:
   print ("Invoice no : {}, Amount : {}".format(x.invno, x.amount))

上記のプログラムの出力は次のとおりです- 

Govind Pant Gulmandi Aurangabad gpant@gmail.com
Invoice no : 3, Amount : 10000
Invoice no : 4, Amount : 5000

結合荷重

他の関数はorm.joinedload（）と呼ばれます。 これにより、LEFT OUTER JOINが発行されます。 リードオブジェクトと関連オブジェクトまたはコレクションが1ステップでロードされます。 

from sqlalchemy.orm import joinedload
c1 = session.query(Customer).options(joinedload(Customer.invoices)).filter_by(name='Govind Pant').one()

これは、上記と同じ出力を与える次の式を出力します- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email, invoices_1.id
AS invoices_1_id, invoices_1.custid
AS invoices_1_custid, invoices_1.invno
AS invoices_1_invno, invoices_1.amount
AS invoices_1_amount

FROM customers
LEFT OUTER JOIN invoices
AS invoices_1
ON customers.id = invoices_1.custid

WHERE customers.name = ? ORDER BY invoices_1.id
('Govind Pant',)

OUTER JOINは2行になりましたが、Customerのインスタンスを1つ返します。 これは、Queryが、返されたエンティティにオブジェクトIDに基づいて「一意」戦略を適用するためです。 クエリ結果に影響を与えることなく、結合されたイーガーロードを適用できます。

関連クエリのロードにはsubqueryload（）の方が適していますが、joinedload（）は多対1の関係に適しています。

SQLAlchemy ORM-関連オブジェクトの削除

単一のテーブルで削除操作を実行するのは簡単です。 必要なのは、マップされたクラスのオブジェクトをセッションから削除し、アクションをコミットすることだけです。 ただし、複数の関連するテーブルでの削除操作は少し面倒です。

sales.dbデータベースでは、CustomerクラスとInvoiceクラスは、1対多タイプの関係でcustomerおよびinvoiceテーブルにマップされます。 Customerオブジェクトを削除して結果を確認します。

クイックリファレンスとして、以下は顧客および請求書クラスの定義です- 

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship
class Customer(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

class Invoice(Base):
   __tablename__ = 'invoices'

   id = Column(Integer, primary_key = True)
   custid = Column(Integer, ForeignKey('customers.id'))
   invno = Column(Integer)
   amount = Column(Integer)
   customer = relationship("Customer", back_populates = "invoices")

Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")

セッションをセットアップし、以下のプログラムを使用してプライマリIDでクエリすることにより、Customerオブジェクトを取得します- 

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
x = session.query(Customer).get(2)

サンプルテーブルでは、x.nameはたまたま 'Gopal Krishna’です。 このxをセッションから削除し、この名前の出現をカウントしましょう。 

session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()

結果のSQL式は0を返します。 

SELECT count(*)
AS count_1
FROM (
   SELECT customers.id
   AS customers_id, customers.name
   AS customers_name, customers.address
   AS customers_address, customers.email
   AS customers_email
   FROM customers
   WHERE customers.name = ?)
AS anon_1('Gopal Krishna',) 0

ただし、xの関連するInvoiceオブジェクトはまだ存在しています。 次のコードで確認できます- 

session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()

ここで、10と14は、顧客のGopal Krishnaに属する請求書番号です。 上記のクエリの結果は2です。つまり、関連オブジェクトは削除されていません。 

SELECT count(*)
AS count_1
FROM (
   SELECT invoices.id
   AS invoices_id, invoices.custid
   AS invoices_custid, invoices.invno
   AS invoices_invno, invoices.amount
   AS invoices_amount
   FROM invoices
   WHERE invoices.invno IN (?, ?))
AS anon_1(10, 14) 2

これは、SQLAlchemyがカスケードの削除を想定していないためです。削除するコマンドを与える必要があります。

動作を変更するには、User.addresses関係のカスケードオプションを構成します。 進行中のセッションを閉じ、新しいdeclarative_base（）を使用してUserクラスを再宣言し、カスケード構成を含むアドレス関係を追加しましょう。

リレーションシップ関数のカスケード属性は、セッション操作を親から子に「カスケード」する方法を決定するカスケード規則のコンマ区切りリストです。 デフォルトでは、Falseです。つまり、「save-update、merge」です。

利用可能なカスケードは次のとおりです-

  • 更新を保存
  • マージ
  • 抹消
  • 削除する
  • 削除孤児
  • リフレッシュ期限

多くの場合、使用されるオプションは「all、delete-orphan」であり、関連するオブジェクトはすべての場合に親オブジェクトと一緒に続き、関連付けが解除されると削除されることを示します。

したがって、再宣言された顧客クラスは以下に示されています- 

class Customer(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)
   invoices = relationship(
      "Invoice",
      order_by = Invoice.id,
      back_populates = "customer",
      cascade = "all,
      delete, delete-orphan"
   )

以下のプログラムを使用してGopal Krishna名を持つ顧客を削除し、関連する請求書オブジェクトの数を確認しましょう- 

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
x = session.query(Customer).get(2)
session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()
session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()

カウントは現在、上記のスクリプトによって発行された次のSQLで0です- 

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.id = ?
(2,)
SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE ? = invoices.custid
ORDER BY invoices.id (2,)
DELETE FROM invoices
WHERE invoices.id = ? ((1,), (2,))
DELETE FROM customers
WHERE customers.id = ? (2,)
SELECT count(*)
AS count_1
FROM (
   SELECT customers.id
   AS customers_id, customers.name
   AS customers_name, customers.address
   AS customers_address, customers.email
   AS customers_email
   FROM customers
   WHERE customers.name = ?)
AS anon_1('Gopal Krishna',)
SELECT count(*)
AS count_1
FROM (
   SELECT invoices.id
   AS invoices_id, invoices.custid
   AS invoices_custid, invoices.invno
   AS invoices_invno, invoices.amount
   AS invoices_amount
   FROM invoices
   WHERE invoices.invno IN (?, ?))
AS anon_1(10, 14)
0

多対多の関係

2つのテーブル間の多対多の関係*は、2つの外部キー（各テーブルの主キーから1つ）を持つように関連付けテーブルを追加することで実現されます さらに、2つのテーブルにマッピングするクラスには、relationship（）関数の2次属性として割り当てられた他の関連付けテーブルのオブジェクトのコレクションを持つ属性があります。

この目的のために、部門と従業員という2つのテーブルを持つSQLiteデータベース（mycollege.db）を作成します。 ここでは、従業員は複数の部門の一部であり、部門には複数の従業員がいると想定しています。 これは、多対多の関係を構成します。

部門および従業員テーブルにマッピングされる従業員および部門クラスの定義は次のとおりです- 

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///mycollege.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship

class Department(Base):
   __tablename__ = 'department'
   id = Column(Integer, primary_key = True)
   name = Column(String)
   employees = relationship('Employee', secondary = 'link')

class Employee(Base):
   __tablename__ = 'employee'
   id = Column(Integer, primary_key = True)
   name = Column(String)
   departments = relationship(Department,secondary='link')

ここで、Linkクラスを定義します。 リンクテーブルにリンクされ、department_idおよびemployee_id属性が含まれています。それぞれ、departmentおよびemployeeテーブルのプライマリキーを参照しています。 

class Link(Base):
   __tablename__ = 'link'
   department_id = Column(
      Integer,
      ForeignKey('department.id'),
      primary_key = True)

employee_id = Column(
   Integer,
   ForeignKey('employee.id'),
   primary_key = True)

ここで、DepartmentクラスにはEmployeeクラスに関連するemployees属性があることに注意する必要があります。 関係関数の2次属性には、値としてリンクが割り当てられます。

同様に、Employeeクラスには、Departmentクラスに関連するdepartments属性があります。 関係関数の2次属性には、値としてリンクが割り当てられます。

これらの3つのテーブルはすべて、次のステートメントが実行されるときに作成されます- 

Base.metadata.create_all(engine)

Pythonコンソールは、次のCREATE TABLEクエリを出力します- 

CREATE TABLE department (
   id INTEGER NOT NULL,
   name VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE employee (
   id INTEGER NOT NULL,
   name VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE link (
   department_id INTEGER NOT NULL,
   employee_id INTEGER NOT NULL,
   PRIMARY KEY (department_id, employee_id),
   FOREIGN KEY(department_id) REFERENCES department (id),
   FOREIGN KEY(employee_id) REFERENCES employee (id)
)

これを確認するには、以下のスクリーンショットに示すように、SQLiteStudioを使用してmycollege.dbを開きます-

部門テーブル

従業員テーブル

リンクテーブル

次に、以下に示すように、Departmentクラスの3つのオブジェクトとEmployeeクラスの3つのオブジェクトを作成します- 

d1 = Department(name = "Accounts")
d2 = Department(name = "Sales")
d3 = Department(name = "Marketing")

e1 = Employee(name = "John")
e2 = Employee(name = "Tony")
e3 = Employee(name = "Graham")

各テーブルには、append（）メソッドを持つコレクション属性があります。 EmployeeオブジェクトをDepartmentオブジェクトのEmployeesコレクションに追加できます。 同様に、Employeeオブジェクトのdepartmentsコレクション属性にDepartmentオブジェクトを追加できます。 

e1.departments.append(d1)
e2.departments.append(d3)
d1.employees.append(e3)
d2.employees.append(e2)
d3.employees.append(e1)
e3.departments.append(d2)

私たちが今しなければならないのは、セッションオブジェクトを設定し、それにすべてのオブジェクトを追加し、以下に示すように変更をコミットすることです- 

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
session.add(e1)
session.add(e2)
session.add(d1)
session.add(d2)
session.add(d3)
session.add(e3)
session.commit()

次のSQLステートメントがPythonコンソールに出力されます- 

INSERT INTO department (name) VALUES (?) ('Accounts',)
INSERT INTO department (name) VALUES (?) ('Sales',)
INSERT INTO department (name) VALUES (?) ('Marketing',)
INSERT INTO employee (name) VALUES (?) ('John',)
INSERT INTO employee (name) VALUES (?) ('Graham',)
INSERT INTO employee (name) VALUES (?) ('Tony',)
INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 2), (3, 1), (2, 3))
INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 1), (2, 2), (3, 3))

上記の操作の効果を確認するには、SQLiteStudioを使用して、部門、従業員、およびリンクテーブルのデータを表示します-

部門テーブルデータ

従業員テーブルデータ

リンクテーブルデータ

データを表示するには、次のクエリステートメントを実行します- 

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

for x in session.query( Department, Employee).filter(Link.department_id == Department.id,
   Link.employee_id == Employee.id).order_by(Link.department_id).all():
   print ("Department: {} Name: {}".format(x.Department.name, x.Employee.name))

この例で入力されたデータに従って、出力は以下のように表示されます- 

Department: Accounts Name: John
Department: Accounts Name: Graham
Department: Sales Name: Graham
Department: Sales Name: Tony
Department: Marketing Name: John
Department: Marketing Name: Tony

SQLAlchemy-方言

SQLAlchemyは方言のシステムを使用して、さまざまなタイプのデータベースと通信します。 各データベースには、対応するDBAPIラッパーがあります。 すべての方言では、適切なDBAPIドライバーがインストールされている必要があります。

次の方言がSQLAlchemy APIに含まれています-

  • 火の鳥
  • Microsoft SQL Server
  • MySQL
  • オラクル
  • PostgreSQL
  • SQL
  • Sybase

URLに基​​づくエンジンオブジェクトは、create_engine（）関数によって生成されます。 これらのURLには、ユーザー名、パスワード、ホスト名、データベース名を含めることができます。 追加の構成用のオプションのキーワード引数があります。 ファイルパスが受け入れられる場合もあれば、「データソース名」が「ホスト」と「データベース」の部分を置き換える場合もあります。 データベースURLの典型的な形式は次のとおりです- 

dialect+driver://username:password@host:port/database

PostgreSQL

PostgreSQLダイアレクトは、デフォルトのDBAPIとして psycopg2 を使用します。 pg8000は、以下に示すように、純粋なPythonの代替としても使用できます。 

# default
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')

# psycopg2
engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')

# pg8000
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')

MySQL

MySQLダイアレクトは、デフォルトのDBAPIとして mysql-python を使用します。 次のようなMySQL-connector-pythonなど、多くのMySQL DBAPIが利用可能です- 

# default
engine = create_engine('mysql://scott:tiger@localhost/foo')

# mysql-python
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

# MySQL-connector-python
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')

オラクル

Oracleの方言は、次のようにデフォルトのDBAPIとして cx_oracle を使用します- 

engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')

Microsoft SQL Server

SQL Serverの方言は、デフォルトのDBAPIとして pyodbc を使用します。 pymssqlも利用できます。 

# pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

# pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')

SQLite

SQLiteは、デフォルトでPython組み込みモジュール sqlite3 を使用して、ファイルベースのデータベースに接続します。 SQLiteはローカルファイルに接続するため、URL形式はわずかに異なります。 URLの「ファイル」部分は、データベースのファイル名です。 相対ファイルパスの場合、以下に示すように3つのスラッシュが必要です- 

engine = create_engine('sqlite:///foo.db')

そして、絶対ファイルパスの場合、3つのスラッシュの後に絶対パスが続きます- 

engine = create_engine('sqlite:///C:\\path\\to\\foo.db')

SQLite：memory：databaseを使用するには、以下に示すように空のURLを指定します- 

engine = create_engine('sqlite://')

結論

このチュートリアルの最初の部分では、式言語を使用してSQLステートメントを実行する方法を学びました。 式言語は、PythonコードにSQLコンストラクトを埋め込みます。 後半では、SQLAlchemyのオブジェクトリレーションマッピング機能について説明しました。 ORM APIは、SQLテーブルをPythonクラスにマップします。

「https://www.finddevguides.com/index.php?title=Sqlalchemy-quick-guide&oldid=79393」から取得
Powered by MediaWiki