【PythonのORM】SQLAlchemyで基本的なSQLクエリまとめ

![sqlalchemy.jpg](https://qiita-image-store.s3.amazonaws.com/0/89992/f77bfe60-13e8-3056-09e2-35420d7f6f6c.jpeg)
SQLAlchemy1.2の基本的なクエリをまとめました！
python初心者以上向けの記事です。

改めて読み返してみると、直した方が良さそうな箇所や、この機能書かないんかいってのがあるので修正予定です。

## 記述内容
ORMについて、動かしてみるところまで、select, limit, orderby, distinct, join, leftjoin, in, insert, update, delete, union, unionAll, sql吐き出し, sqlを直接実行について
また、[flask-sqlalchemy](http://flask.pocoo.org/docs/1.0/patterns/sqlalchemy/)では実行を試していません。

## [SQLAlchemy](https://www.sqlalchemy.org/)とは
pythonの[ORM](https://ja.wikipedia.org/wiki/%E3%82%AA%E3%83%96%E3%82%B8%E3%82%A7%E3%82%AF%E3%83%88%E9%96%A2%E4%BF%82%E3%83%9E%E3%83%83%E3%83%94%E3%83%B3%E3%82%B0)モジュール。
session.query(User).all()
このようにSQL操作ができる。

## ORM
SQLをクラスとして扱えるようにしたもの。
使用する理由は、
・[SQLインジェクション対策](https://ja.wikipedia.org/wiki/SQL%E3%82%A4%E3%83%B3%E3%82%B8%E3%82%A7%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3)がサポートされる。
・クラスなので、SQLを[オブジェクト指向](https://ja.wikipedia.org/wiki/%E3%82%AA%E3%83%96%E3%82%B8%E3%82%A7%E3%82%AF%E3%83%88%E6%8C%87%E5%90%91)で書ける。
・引数に変数を入れるため、文字列の結合などが必要ないので短く書ける。

導入の注意点は、
・ちょっとしたSQLを実行する場合は、ORMを使わない方がコード量が短い。
・英語の文献が多いので学習コストが必要。
・慣れないうちはブラックボックスのコード。

## ブラックボックスだから
「セキュリティ」や「実行速度」の面から考えると、[SQLAlchemyの内部コード](https://github.com/zzzeek/sqlalchemy)は読む必要あり。
どういうSQLが吐き出されてるかチェックすべき。
コメントで、どういうSQLが吐き出されてるか書いておいても良いと思う。
気づかないうちに超重いSQLを叩いてたりするから注意。  

# install

```bash
pip install sqlalchemy
```

# まずsessionの作成(setting.py)
ファイルを実際に作成して説明します！
「DB接続を定義」と「session変数の作成」をします。
ファイル名 setting.py

```python3:setting.py

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

# mysqlのDBの設定
DATABASE = 'mysql://%s:%s@%s/%s?charset=utf8' % (
    "user_name",
    "password",
    "host_ip",
    "db_name",
)
ENGINE = create_engine(
    DATABASE,
    encoding = "utf-8",
    echo=True # Trueだと実行のたびにSQLが出力される
)

# Sessionの作成
session = scoped_session(
  # ORM実行時の設定。自動コミットするか、自動反映するなど。
　　　　sessionmaker(
　　　　　　　　autocommit = False,
　　　　　　　　autoflush = False,
　　　　　　　　bind = ENGINE
　　　　)
)

# modelで使用する
Base = declarative_base()
Base.query = session.query_property()
```

# モデルの作成(user.py)
DBにテーブルに対応するモデルを作成します！
setting.pyからimportで「BaseとENGINE」変数を呼び出している。
ファイル名user.py

```python3:user.py
import sys
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, DateTime
from setting import Base
from setting import ENGINE

class User(Base):
    """
    ユーザモデル
    """
    __tablename__ = 'users'
    id = Column('id', Integer, primary_key = True)
    name = Column('name', String(200))
    age = Column('age', Integer)
    email = Column('email', String(100))

def main(args):
    """
    メイン関数
    """
    Base.metadata.create_all(bind=ENGINE)

if __name__ == "__main__":
    main(sys.argv)
```

# テーブルの作成
上のスクリプトを実行するとテーブルが作成されます。
「Base.metadata.create_all(bind=ENGINE)」は、importしているモデル全てに対して、テーブルが存在しなかったら作成するという関数です。

```bash
python user.py
```

# 試しに実行
上で定義したsession.pyとuser.pyをimportします。
このファイルを実行すると「Userモデルのnameカラム」に「太郎」を追加され、
その後ユーザの一覧を表示しているので、実行結果には「太郎」と表示されます！
ファイル名main.py

```python3:main.py
# セッション変数の取得
from setting import session
# Userモデルの取得
from user import *

# DBにレコードの追加
user = User()
user.name = '太郎'
session.add(user)  
session.commit()

# Userテーブルのnameカラムをすべて取得
users = session.query(User).all()
for user in users:
    print(user.name)
```

# SELECT（カラムを指定）
Userテーブルの指定したカラムの全レコードを取得。
下の例では、「User.name, User.email」のカラムのみをList型で取得する。
返り値：[User, ]

```python
users = session.query(User.name, User.email).all()
```

```python
# 取り出し例
for user in users:
    print(user.name)
```

# WHERE
Userテーブルのnameカラムが"name"のユーザ全てをList型で取得する。
返り値：[User, ]
※ pythonで処理の途中で改行する時には「\」を使う

```python
users = session.query(User).\
    filter(User.name=="name").\
    all()
```

```python
# 取り出し例
for user in users:
    print(user.name, user.age)
```

# LIMIT
Userテーブルから10人分をList型で取得する。
all()をつけないと、query型で取得してしまうため、
呼び出すごとにqueryが発行されて重くなる。
返り値：[User, ]
<pre>
<code>user_name = session.query(User).\
    limit(10).\
    all()
</code>
</pre>


# IN
Userテーブルでnamesに含まれるユーザ名をList型で取得する。
返り値：[User, ]

```
names = ['taro', 'jiro', 'ichiro']
brothers = session.query(User).\
    filter(User.name.in_(names)).\
    all()
```

INは遅くなるので下の記事参考
http://jetglass.hatenablog.jp/entry/2015/05/20/185336

# ORDER BY
Userテーブルのユーザ全てをcreated_atが昇順にList型で取得する。
返り値：[User, ]

```
from sqlalchemy import desc

users = session.query(User).\
    order_by(desc(User.created_at)).\
    all()
```


# Distinct
パターン1
<pre>
<code>from sqlalchemy import distinct

user_name = session.query(User).\
    distinct(User.name).\
    all()
</code>
</pre>

パターン2
<pre>
<code>from sqlalchemy import distinct

user_name = session.query(distinct(User.name)).\
    all()
</code>
</pre>


#JOIN
User.idと、UserSocial.user_idで内部結合し、
ユーザ全てをList型で取得する。
user_nameはこの形式で取得される。
返り値：[(User, UserSocial)]
<pre>
<code>user_name = session.query(User, UserSocial).\
    join(UserSocial, User.id==UserSocial.user_id).\
    all()
</code>
</pre>
*公式リファレンス*
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join

# LEFT JOIN
User.idと、UserSocial.user_idで外部結合し、
ユーザ全てをList型で取得する。
user_nameはこの形式で取得される。
返り値：[(User, UserSocial)]
<pre>
<code>user_name = session.query(User, UserSocial).\
    outerjoin(UserSocial, User.id==UserSocial.user_id).\
    all()
</code>
</pre>
*公式リファレンス*
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.outerjoin


# UNION
TagとGenreを重複なしで結合するunionをし、List型で取得する。
返り値：[Tag,]
<pre>
<code>tag_genre = session.query(Tag).\
    union(session.query(Genre)).\
    all()
</code>
</pre>
*公式リファレンス*
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.union


# UNION　ALL
TagとGenreを重複ありで結合するunion allをし、List型で取得する。
返り値：[Tag,]
<pre>
<code>tag_genre = session.query(Tag).\
    union_all(session.query(Genre)).\
    all()
</code>
</pre>
*公式リファレンス*
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.union_all
 
# 吐き出されるSQLの取得
<pre>
<code>user_name = session.query(User)
sql_statement = user_name.statement
print(sql_statement)
</code>
</pre>

# INSERT
User.nameが'太郎'のユーザを追加する。
session.commit()でクエリ実行
<pre>
<code>user_name = User()
user_name.name = '太郎'
session.add(user_name)  
session.commit()
</code>
</pre>

# UPDATE
User.idが1のユーザの名前を更新する。
user_name = User
session.commit()でクエリ実行
<pre>
<code>user_name = session.query(User).\
    filter(User.id==1).\
    first()
user_name.name = '太郎'
session.commit()
</code>
</pre>

# DELETE
User.idが1のユーザを全て削除する。
session.commit()でクエリ実行
<pre>
<code>session.query(User).\
    filter(User.id==1).\
    delete()
session.commit()
</code>
</pre>

# SQLを直接実行
sqlインジェクションを対策しなくて良い場合、
や自前で実装している場合、
直にSQLを叩くのもありです。

```
user_id　= 1
sql = "select name from users where id = %s" % (user_id)
res = session.execute(sql)

# 取り出し例
for v in res:
   print(v.name)
```

# 注意
.all()をつけないと、リスト型で取得できません。
下のコードではループのたびにクエリが発行されます。

※ 昔実行した時は、all()つけないと速度が遅くなった気がしてたが、コメントでご指摘いただいた通り、all()つけなくても良いのかも。
なので、両方試してみて欲しいです。  
２年ぐらい前に書いたやつなので、手が空いてる時に全体的に修正しようと思います。

```
# allが付いていない
users = session.query(User).limit(100)

# 毎回クエリ発行
for user in users:
    print(user.name)
```


## sqlalchemyドキュメント
http://docs.sqlalchemy.org/en/latest/

## SqlAlchemy-migrate
https://braitom.hatenablog.com/entry/2014/12/03/102451

## 内包表記
https://qiita.com/y__sama/items/a2c458de97c4aa5a98e7