SQLAlchemy ORMの基本的な使い方

# このQiitaの目的

SQLAlchemyを使うとPythonからSQLを直接操作できます。
SQLAlchemyにはcoreとORMがあるのですが、このQiitaでは**ORMのみ扱います**。
日本語のドキュメントが少なかったので、以下の[書籍](https://www.amazon.co.jp/gp/product/149191646X/ref=as_li_qf_asin_il_tl?ie=UTF8&tag=hideakitamai-22&creative=1211&linkCode=as2&creativeASIN=149191646X&linkId=4e49792c666b854b1f424efc05423415)や[公式ドキュメント](https://docs.sqlalchemy.org/en/latest/)で調べたことをまとめておきます。内容は順次加筆していこうと思います。
**[Essential SQLAlchemy - Amazon](https://www.amazon.co.jp/gp/product/149191646X/ref=as_li_qf_asin_il_tl?ie=UTF8&tag=hideakitamai-22&creative=1211&linkCode=as2&creativeASIN=149191646X&linkId=4e49792c666b854b1f424efc05423415)**

#環境
mysqlで`sampledatabase`という名前の空のデータベースをあらかじめ作成しsqlalchemyでいじっていきます。
mysqlのバージョン`8.0.13`
Pythonのバージョンは`3.7.2`
実行環境はPyCharmの`2018.3.4`
SQLAlchemyのバージョンは`1.13.11`

# SQLAlchemyとは / メリット / デメリット

SQLAlchemyとはPythonのモジュールで、`session.query(Person.id,Person.name).first()`のように書くことでSQLを直接操作できます。

SQLAlchemyではSQLをクラスとして扱えるので、オブジェクト指向で書けるのがメリットです。
一方で日本語の文献が多くないのはデメリットです。

# テーブルの作成

まずインストールします。

```Python:
pip install sqlalchemy
```

次にengine(SQLサーバーに接続)とbaseとsessionを作成します。

```Python:staffdata.py
import pymysql
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql:///sampledatabase")

Session = sessionmaker(bind=engine)

session = Session()
```
このsessionは今後ほぼ全ての操作で使用します。engineでは今回mysqlを使いました。そしてテーブルの作成。

```Python:staffdata.py
from sqlalchemy import (Table, Column, Integer, String)
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Staff(Base):
    __tablename__ = "staffs"
    staff_id = Column(Integer(), primary_key=True)
    staff_name = Column(String(20), index=True)
    staff_age = Column(Integer())
    staff_section = Column(String(20))
    staff_post = Column(String(20))

```

今回はmysqlを使用しましたがsqlite等も使えます。

## 対応データベース

なお、対応データベースは以下(2020年2月11日時点)。

- MySQL
- SQLite
- PostgreSQL
- Oracle
- Mycrosoft SQL Server
- Firebird
- Sybase

# データのinsert
データのinsertはsession.add()で行います。

```Python:insertdata.py
from staffdata import *

Yamada = Staff(staff_id=1, staff_name="Yamada Taro", staff_age=30, staff_section="Marketing", staff_post="Manager")
session.add(Yamada)
session.commit()
```

注意しなければならないのは、インスタンスを**addした後は`session.commit()`しないとデータが保存されません**。
この段階でYamadaのデータがstaffsテーブルにinsertされたので、

```
print(Yamada.staff_id)
```

と実行すると

```
1
```

と出力されます。複数データをinsertする場合はaddを繰り返すより以下の方が高速です。

```Python:insertdata.py
Yamada2 = Staff(staff_id=2, staff_name="Yamada Hanako", staff_age=25, staff_section="Legal", staff_post="Partner")
Sato = Staff(staff_id=3, staff_name="Sato Taro", staff_age=40, staff_section="Legal", staff_post="Manager")

session.bulk_save_objects([Yamada2, Sato])
session.commit()
```

bulk_save_objectsを使うと複数のデータをまとめてinsertでき、それぞれaddするよりはるかに高速です。ただ、bulk_save_objectによるinsertは速度を優先しているため、primary keyの使用やforeign key(複数のtableで同じデータを使用するときにリンクできる)の併用は適さないとされています。以下公式ドキュメント。

>If you are inserting multiple records and don't need access to relationships or the inserted primary key, use bulk_save_objects or its related methods.

primary keyやforeign keyを併用したい場合は大人しく1つ1つaddしましょう。その際`session.commit()`するより`session.flush()`の方が早いようです。仕組みはよくわかりません。。先ほどのコードでいうと

```Python
session.bulk_save_objects([Yamada2, Sato])
session.commit()
```

の部分を

```Python
session.add(Yamada2)
session.add(Sato)
session.flush()
```

に変えます。

# Query
`query()`メソッドを使うとデータをクエリ（選択）できます。以下のコードで先ほど作成したstaffsテーブルの全データを選択、表示できます。

```Python
all_staffs = session.query(Staff).all()
print(all_staffs)
```

この`all()`メソッドは全データを選択することを表し、他にも`first()`メソッドや`one()`メソッド、`scalar()`メソッドがあります。データをひとつだけ取り出したい場合は通常`first()`メソッドを使用します。`one()`メソッドだとそもそも入力するデータが1つでないとエラーが出ます。

## 一部のcolumnのみクエリする
`query()`メソッドの引数でcolumnの名前を指定することで、一部のcolumnのみクエリすることができます。

```Python
print(session.query(Staff.staff_name, Staff.staff_section))
```

# Order
`order_by()`メソッドを使うとデータを並び替えることができます。先ほどのデータを年齢で並び替える場合は、

```Python
for staff in session.query(Staff).order_by(Staff.staff_age):
    print("{} - {}".format(staff.staff_age, staff.staff_name))
```

##降順にorder
`desc()`を使います。

```Python
from sqlalchemy import desc
for staff in session.query(Staff).order_by(desc(Staff.staff_age)):
    print("{} - {}".format(staff.staff_age, staff.staff_name))
```

##orderしてlimit
`limit()`を使うと指定した数だけ取り出せます。order_byと組み合わせて若手の2人を取り出します。

```Python
limit = session.query(Staff).order_by(Staff.staff_age).limit(2)
print([result.staff_name for result in limit])
```

#func（sum,count）
funcを使うと`sum()`や`count()`など簡単な統計量をだせます。

```Python
from sqlalchemy import func
```

##sum
まず合計を出す`sum()`

```Python
age_sum = session.query(func.sum(Staff.staff_age)).scalar()
```

##count
次にデータの数をカウントする`count()`

```Python
name_count = session.query(func.count(Staff.staff_name)).first()
```

#label
`label()`を使うとcolumnの名前を変更できます。先ほど紹介したsumやcountでは新しい「count」などの名前のcolumnが生成されそこにデータが入ってしまうので、labelを併用することは特に有効です。「staff_section」を「staff_location」にリネームしてみます。リネームできたかの確認には`key()`メソッドが有用です。

```Python
rename = session.query(Staff.staff_section.label("staff_location"))
print(rename.key())
# [u'staff_location']
```

#filter
最も使用頻度が高い`filter()`と`filter_by()`。カラム内のデータをフィルタリングできます。

```Python
filtered = session.query(Staff).filter(Staff.staff_name == "Sato Taro").first()
filtered2 = session.query(Staff).filter_by(Staff.staff_name="Sato Taro").first()
```

filteredとfiltered2では同じ結果が出力されます（違いがよくわかりません）。もし`Sato Taro`の`staff_id`のみを出力したい場合は`query(Staff)`を`query(Staff.staff_id)`に変えます。また、カラムの文字列を一部だけfilterすることもできます。その場合は

```Python
filtered3 = session.query(Staff).filter(Staff.staff_name.like("%Sato%").first()
```

とします。そうすると`Sato`を含む行が全て取得できます。

##複数の条件をand,orで繋いでfilter
`filter()`で2つ以上の条件を繋ぐ方法は2通りあります。最もわかりやすいのはこの方法です。

```Python
query = session.query(Staff).filter(
    Staff.staff_age >= 30,
    Staff.staff_post ==　"Manager"
).all()
```

and(連言)であることを明確にしたい場合は

```Python
from sqlalchemy import and_, or_, not_
query = session.query(Staff).filter(
    and_(
        Staff.staff_age >= 30,
        Staff.staff_post ==　"Manager"
    )
).all()
```

と書くこともできます。or(選言)である場合、or_を使います。

```Python
from sqlalchemy import and_, or_
query = session.query(Staff).filter(
    or_(
        Staff.staff_age.between(35,45),
        Staff.staff_post.contains("Manager")
    )
).all()
```

`between()`は「2つの引数の間」、`contains()`は「文字列を含んでいる」という条件を効果的にfilterできます。

#Update
データのupdateは以下のように行えます。

```Python
query = session.query(Staff)
staff_update = query.filter(Staff.staff_name == "Yamada Taro")
staff_update.staff_age = staff_update.staff_age + 100
session.commit()
```

最後にsession.commit()しないとデータが上書きされないので注意が必要です。
updateは`update()`を使って行うこともできます。この場合、　`session.commit()`は必要ありません。

```Python
query = session.query(Staff)
query = query.filter(Staff.staff_name == "Yamada Taro").query.update({Staff.staff_age: Staff.staff_age + 100})
```

#Delete
データの削除は以下のように行えます。

```Python
query = session.query(Staff).filter(Staff.staff_name == "Yamada Taro")
target_data = query.one()
session.delete(target_data)
session.commit()
```

あるいは

```Python
query = session.query(Staff)
query = query.filter(Staff.staff_name == "Yamada Taro")
query.delete()
```

です。`session`からdeleteする場合は`commit()`する必要があります。忘れがちです。

#レファレンス
[公式ドキュメント](https://docs.sqlalchemy.org/en/latest/)
[Essential SQLAlchemy - Amazon](https://www.amazon.co.jp/gp/product/149191646X/ref=as_li_qf_asin_il_tl?ie=UTF8&tag=hideakitamai-22&creative=1211&linkCode=as2&creativeASIN=149191646X&linkId=4e49792c666b854b1f424efc05423415)