SQLAlchemyでテーブル設計とORMの操作
SQLAlchemyとは
SQLAlchemy
は、PythonのORMの1つで、Pythonでは一番有名なORMでもあります。ORMとしては、SQLインジェクション対策が標準でサポートされています。ただのORMとしてではなく、テーブル設計を行うのにも非常に便利です。
ここではSQLAlchemy
の使い方について紹介します。DBはPostgresqlやMySQLではなく、簡易的なはSQLiteを使用します。
ORMとは
Object-Relational Mapping
の略です。
オブジェクト志向言語のクラスとRDBとのマッピングを行ってくれるのがORMです。SQLをオブジェクト指向で書けるようになります。
ORMの利点をまとめると主に以下の2つの点があります。
異なるDBの違いを吸収する
MySQLやPostgresqlといったデータベースの種類によらず、同じソースコードで操作できます。
複数のデーターベースを併用する場合、データベースを変更する場合にも、コードの書き換えの必要がなくなります。
SQLを書く必要がない
MySQL、PostgreSQL
などのRDBを操作するにはSQLを使う必要がありますが、SQLAlchemyのようなORM使うことでSQL
を直接記述することなしにデータベースをPythonic
に操作できます。
インストール
まずはSQLAlchemy
を以下のコマンドでインストールします。
$ pip install sqlalchemy
設定ファイルの作成
SQLAlchemy
としての基本的な設定ファイルを作成していきます。
設定する内容としては、データベース接続を定義
したり、実際にPythonコードとRDBを仲介するためのセッション(Session)
の作成も行っていきます。
設定ファイルの名前はsetting.py
とします。
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
# 接続先DBの設定
DATABASE = 'sqlite:///db.sqlite3'
# Engine の作成
Engine = create_engine(
DATABASE,
encoding="utf-8",
echo=False
)
Base = declarative_base()
# Sessionの作成
session = Session(
autocommit = False,
autoflush = True,
bind = Engine
)
# modelで使用する
Base = declarative_base()
Base.query = session.query_property()
create_engineのechoの設定
上記では、create_engine
の中にあるecho
がFalse
になっていますが、True
にすると実行する度にSQL
が出力されるようになります。
ENGINE = create_engine(
DATABASE,
encoding = "utf-8",
# echo=Trueだと実行のたびにSQLが出力される
echo=True
)
DATABASEの設定
create_engine
の第一引数に入れるDATABASE
は、接続する先のデータベースの情報を入れます。
上記ではSQlite
を入れていますが、MySQL
やPostgresql
などといったデータベースを使用する際はユーザー名やパスワード、ホスト名やポートといった接続先情報をちゃんと指定しなければいけません。
from setting import create_engine
DATABASE = "{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?charset={charset_type}"
engine = create_engine(DATABASE)
DATABASE
に入るそれぞれの要素は下記のとおりです。
要素 | 説明 |
---|---|
dialect | DBの種類を指定。(sqlite, mysql, postgresql, oracle, mssql) |
driver | DBに接続するためのドライバーの指定。("default" DBAPIになる) |
username | DBに接続することができるユーザ名を指定 |
password | DBに接続するためのパスワードを指定 |
host | ホスト名を指定。(localhost, IPアドレス) |
port | ポート番号を指定。(指定しなければ、defaultのポート番号) |
database | 接続するデータベース名を指定 |
charset_type | 文字コードを指定(utf8とか) |
セッションの設定手段
SQLAlchemy
はセッションを介してクエリを実行します。
セッションは、PythonコードとRDBを仲介するものと上記で説明しました。つまりはPythonコードとRDBを紐付けるようなものとも言えます。
セッションの作成方法はいくつかあります。
session = Session(
autocommit = False,
autoflush = True,
bind = Engine
)
settings.py
では上記のSession
を使う方法で指定しましたが、scoped_session
とsessionmaker
でやる方法が一番オススメだそうです。
from sqlalchemy.orm import scoped_session, sessionmaker
# Sessionの作成
session = scoped_session(
sessionmaker(
autocommit = False,
autoflush = False,
bind = ENGINE
)
)
SQLAlchemy
のセッションの生成方法の詳細については以下の記事にまとめてあります。
autoflushオプションについて
autoflush
をFalseにすると、InsertやUpdateなどの更新系処理をしてもsession.commit()
するまでは実行されません。
autoflush=True
にすると、session.add
などをしたタイミングで自動的にInsertやUpdate文などが走ります。
モデルを作成 = テーブル設計
いよいよテーブル設計(テーブル定義)として、モデルを作成していきます。
試しに作るものとして、ユーザー情報を格納するためのusers
テーブル、モデル名はUser
としましょう。
setting.py
からimportでBase
とEngine
の変数を呼び出します。
from sqlalchemy import Column, Integer, String, Float, DateTime
from setting import Engine
from setting import Base
class User(Base):
"""
ユーザモデル
"""
__tablename__ = 'users'
__table_args__ = {
'comment': 'ユーザー情報のマスターテーブル'
}
id = Column('id', Integer, primary_key=True, autoincrement=True)
name = Column('name', String(200))
age = Column('age', Integer)
email = Column('email', String(100))
if __name__ == "__main__":
Base.metadata.create_all(bind=ENGINE)
今回作成したテーブル定義は以下のようになっています。
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
user_id | int(11) | NO | PRI | NULL | auto_increment |
first_name | varchar(255) | YES | NULL | ||
last_name | varchar(255) | YES | NULL | ||
age | int(11) | YES | NULL |
カラムに指定するオプション
上記のUser
モデルで設定した4つのカラムを定義しています。
Column
にはいくつかのオプションを指定しており、それぞれについて解説します。
primary_key
primary_key=True
とすることで、そのカラムを主キーとして扱います。User
モデルではidカラムにそれを割り当てています。
id = Column('id', Integer, primary_key = True, autoincrement=True)
auto_increment
auto_increment=True
とすることで、そのカラムはレコードが追加される毎に連番がわりふられていきます。User
モデルではidカラムにそれを割り当てています。
id = Column('id', Integer, primary_key = True, autoincrement=True)
SQLAlchemyのテーブル定義のオプションや詳細に関しては以下の記事で別途まとめました。
テーブルを作成
上記のuser.py
を実行すると、Base.metadata.create_all(bind=ENGINE)
が実行されます。これでテーブルが存在しなければ自動で作成されます。
作成されるテーブルの対象としては、Base
クラスを継承しているモデルであれば、テーブル郡が一括して作成されます。
metadataとは
Base.metadata
というコードが出てきており、metadata
という言葉が出てきました。
metadata
とは、データベースの様々な情報を保持しているオブジェクトのことです。このmetadata
を使うと、既存のデータベースからテーブル定義をもってきたりすることもできます。
ORMの基本操作
次にいよいよORMの基本的な操作をしていきます。
ここでは試しにusersテーブルに複数のレコードを追加
して、その後にusersテーブルのレコードの一覧
を表示します。
それでは、上記で定義したsetting.py
とuser.py
をimportします。
# セッション変数の取得
from setting import session
# Userモデルの取得
from user import User
# DBにレコードの追加
user = User()
user.name = '太郎'
session.add(user)
user = User()
user.name = '一郎'
session.add(user)
session.commit()
# usersテーブルのレコードを全て取得する
users = session.query(User).all()
for user in users:
print(user.name)
上記を実行すると、users
テーブルに名前が太郎
と一郎
という2つのレコードを追加し、usersテーブルの全てのレコード
を取り出して名前を表示します。
基本的なCRUD処理
次に基本的なCRUD処理の方法について紹介していきます。
- Create(新規作成)
- Read(読み取り)
- Update(更新)
- Destroy(削除)
Create (Insert)
データを追加する処理になります。
モデルにデータを入力し、sessionに対して追加します。
追加したデータをcommit
でデータベースへ反映します。(最後にsessionを閉じ処理も行っています。)
from setting import session
from user import User
user = User()
user.name = '太郎'
session.add(user)
session.commit()
session.close()
Read (Select)
データを参照する処理になります。
session.query
を使って、参照したいデータを指定します。
ここでは.all()
で全てのデータを取り出しています。
from setting import session
from user import User
session.query(User).all()
session.close()
Update (Update)
データを更新を行う処理になります。
session.query(User).first()
で一番最初のレコードを取得し、そのレコードのnama
カラムを変更して更新する例です。
更新したデータをcommit
でデータベースへ反映します。
from setting import session
from user import User
user = session.query(User).first()
user.name = '次郎'
session.commit()
session.close()
Delete (Delete)
データを削除する処理になります。
削除したいレコードをquery
を使って、取得します。ここでは一番最初のレコードを取得しています。
delete
を使って取得したレコードを削除して、commit
でデータベースに反映します。
from setting import session
from user import User
user = session.query(User).first()
session.delete(user)
session.commit()
session.close()
SQLAlchemyでの条件式
SQL文でのwhere
やjoin
といったものは、SQLAlchemy
ではどのように書くのかを解説していきます。
- SELECT
- WHERE
- LIMIT
- IN
- ORDER BY
- DISTINCT
- JOIN
- OUTER JOIN
- UNION
- UNION ALL
SELECT
users
テーブルの指定したカラムの全レコードを取得します。
こちらのSQL文を実行する例を作っていきます。
SELECT name, email FROM users;
User.name, User.email
のカラムのみに絞るように、.query
に指定します。
from setting import session
from user import User
users = session.query(User.name, User.email).all()
for user in users:
print(user.name)
WHERE
users
テーブルにあるnameカラムがsample-name
のユーザーのみに絞って取得する例です。
SELECT * FROM users WHERE name = 'sample-name';
where
の内容は.filter
に書き出していきます。
from setting import session
from user import User
users = session.query(User).filter(User.name == "sample-name").all()
for user in users:
print(user.name, user.age)
LIMIT
users
テーブルにある取得するレコードの数を限定する例です。
SELECT * FROM users LIMIT 10;
.limit
というメソッドを使用します。
all()
をつけないと、query型で取得してしまい、呼び出すごとにqueryが発行されて重くなります。必ずall()
をつけてください。
from setting import session
from user import User
users = session.query(User).limit(10).all()
IN
users
テーブルのnameが複数に一致するレコードを絞る例です。
SELECT * FROM users WHERE name IN("taro", "jiro", "ichiro");
Userでユーザ名を含んでいるnames
という変数にfilter
メソッドの中で、{モデル名}.{カラム名}.in_({リスト型変数名})
を指定します。
from setting import session
from user import User
names = ['taro', 'jiro', 'ichiro']
users = session.query(User).filter(User.name.in_(names)).all()
INは遅くなるので、使いすぎない方が良いです。
ORDER BY
users
テーブルの全てのレコードのcreated_at
が昇順
になるように取得する例です。
SELECT * FROM users ORDER BY users.created_at DESC;
desc
をimportして、order_by
メソッドの中に埋め込みます。
from setting import session
from user import User
from sqlalchemy import desc
users = session.query(User).order_by(desc(User.created_at)).all()
DISTINCT
users
テーブルの重複レコードを1つにまとめるための例です。
SELECT DISTINCT name from users;
distinct
をimportして、.query
の中に混ぜるか、queryメソッドの後に.distinct
を付けるかのどちらかになります。
from setting import session
from user import User
from sqlalchemy import distinct
user_name = session.query(User).distinct(User.name).all()
# 別な書き方
user_name = session.query(distinct(User.name)).all()
JOIN (INNER JOIN)
users
テーブルのusers.id
と、user_socials
テーブルのuser_socials.usre_id
を内部結合する例です。
SELECT * from users inner join user_socials on users.id = user_socials.id;
query
の後にjoin
メソッドを付けて、join
の中に条件式を入れます。
from setting import session
from user import User, UserSocial
user_name = session.query(User, UserSocial)\
.join(UserSocial, User.id == UserSocial.user_id)\
.all()
LEFT JOIN
users
テーブルのusers.id
と、user_socials
テーブルのuser_socials.usre_id
を内外部結合する例です。
SELECT * from users letf outer join user_socials on users.id = user_socials.id;
query
の後にouterjoin
メソッドを付けて、outerjoin
の中に条件式を入れます。
from setting import session
from user import User, UserSocial
user_name = session.query(User, UserSocial).\
outerjoin(UserSocial, User.id==UserSocial.user_id).\
all()
UNION
tags
テーブルとgenres
テーブルの実行結果を統合して表示する例です。ちなみに重複している部分は削除されます。
SELECT * FROM tags
UNION
SELECT * FROM genres;
session.query(Genre)
をuniton
の中に入れることで、実行結果を統合します。
from setting import session
from user import User
tag_genre = session.query(Tag).\
union(session.query(Genre)).\
all()
UNION ALL
tags
テーブルとgenres
テーブルの実行結果を統合して表示する例です。上記のUNION
との違いは重複している部分も表示されます。
SELECT * FROM tags
UNION ALL
SELECT * FROM genres;
session.query(Genre)
をunion_all
の中に入れることで、実行結果を統合します。
from setting import session
from user import User
tag_genre = session.query(Tag).\
union_all(session.query(Genre)).\
all()
直接SQL文を実行
ORMが生成したSQLが処理の負担になり、パフォーマンスの低下を招くこともあります。
そのため、直接SQLを入力して実行することもできます。
以下のようにengine.execute
で実行できます。
from setting import Engine
# 直接SQLを実行
result = Engine.execute("select * from users")
for row in result:
print(row)
吐き出されるSQLを取得
ORMを実行した際の実際に生成されたSQLのログを取得することもできます。
以下のようにQuery
のstatementにSQLのログが記録されています。
from setting import session
from user import User
user_name = session.query(User)
sql_statement = user_name.statement
print(sql_statement)
Discussion