Python
sqlalchemy
Python3
ORM
RE:CODEDay 3

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

sqlalchemy.jpg
SQLAlchemy1.2の基本的なクエリをまとめました!
python初心者以上向けの記事です。

記述内容

ORMについて、動かしてみるところまで、select, limit, orderby, distinct, join, leftjoin, in, insert, update, delete, union, unionAll, sql吐き出し, sqlを直接実行について

SQLAlchemyとは

pythonのORMモジュール。
session.query(User).all()
このようにSQL操作ができる。

ORM

SQLをクラスとして扱えるようにしたもの。
使用する理由は、
SQLインジェクション対策がサポートされる。
・クラスなので、SQLをオブジェクト指向で書ける。
・引数に変数を入れるため、文字列の結合などが必要ないので短く書ける。

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

ブラックボックスだから

セキュリティの面から考えると、SQLAlchemyの内部コードは読む必要あり。
どういうSQLが吐き出されてるかチェックすべき。
コメントで、どういうSQLが吐き出されてるか書いておくといいと思う。
気づかないうちに超重いSQLを叩いてたりするから注意。

install

pip install sqlalchemy

まずsessionの作成(setting.py)

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

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

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しているモデル全てに対して、テーブルが存在しなかったら作成するという関数です。

python user.py

試しに実行

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

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, ]

users = session.query(User.name, User.email).all()
# 取り出し例
for user in users:
    print(user.name)

WHERE

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

users = session.query(User).\
    filter(User.name=="tomo").\
    all()
# 取り出し例
for user in users:
    print(user.name, user.age)

LIMIT

Userテーブルから10人分をList型で取得する。
all()をつけないと、query型で取得してしまうため、
呼び出すごとにqueryが発行されて重くなる。
返り値:[User, ]

user_tomo = session.query(User).\
    limit(10).\
    all()

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

from sqlalchemy import distinct

user_tomo = session.query(User).\
    distinct(User.name).\
    all()

パターン2

from sqlalchemy import distinct

user_tomo = session.query(distinct(User.name)).\
    all()

JOIN

User.idと、UserSocial.user_idで内部結合し、
ユーザ全てをList型で取得する。
user_tomoはこの形式で取得される。
返り値:[(User, UserSocial)]

user_tomo = session.query(User, UserSocial).\
    join(UserSocial, User.id==UserSocial.user_id).\
    all()


公式リファレンス
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join

LEFT JOIN

User.idと、UserSocial.user_idで外部結合し、
ユーザ全てをList型で取得する。
user_tomoはこの形式で取得される。
返り値:[(User, UserSocial)]

user_tomo = session.query(User, UserSocial).\
    outerjoin(UserSocial, User.id==UserSocial.user_id).\
    all()


公式リファレンス
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.outerjoin

UNION

TagとGenreを重複なしで結合するunionをし、List型で取得する。
返り値:[Tag,]

tag_genre = session.query(Tag).\
    union(session.query(Genre)).\
    all()


公式リファレンス
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.union

UNION ALL

TagとGenreを重複ありで結合するunion allをし、List型で取得する。
返り値:[Tag,]

tag_genre = session.query(Tag).\
    union_all(session.query(Genre)).\
    all()


公式リファレンス
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.union_all

吐き出されるSQLの取得

user_tomo = session.query(User)
sql_statement = user_tomo.statement
print(sql_statement)

INSERT

User.nameが'とも太郎'のユーザを追加する。
session.commit()でクエリ実行

user_tomo = User()
user_tomo.name = 'とも太郎'
session.add(user_tomo)
session.commit()

UPDATE

User.idが1のユーザの名前を更新する。
user_tomo = User
session.commit()でクエリ実行

user_tomo = session.query(User).\
    filter(User.id==1).\
    first()
user_tomo.name = 'とも太郎'
session.commit()

DELETE

User.idが1のユーザを全て削除する。
session.commit()でクエリ実行

session.query(User).\
    filter(User.id==1).\
    .delete()
session.commit()

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が付いていない
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