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とします。

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のセッションの生成方法の詳細については以下の記事にまとめてあります。

https://lonely-solitary.hateblo.jp/entry/2020/01/22/214434

autoflushオプションについて

autoflushをFalseにすると、InsertやUpdateなどの更新系処理をしてもsession.commit()するまでは実行されません。

autoflush=Trueにすると、session.addなどをしたタイミングで自動的にInsertやUpdate文などが走ります。

モデルを作成 = テーブル設計

いよいよテーブル設計（テーブル定義）として、モデルを作成していきます。

試しに作るものとして、ユーザー情報を格納するためのusersテーブル、モデル名はUserとしましょう。

setting.pyからimportでBaseとEngineの変数を呼び出します。

user.py

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文を実行する例を作っていきます。

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のユーザーのみに絞って取得する例です。

sql

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テーブルにある取得するレコードの数を限定する例です。

sql

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が複数に一致するレコードを絞る例です。

sql

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は遅くなるので、使いすぎない方が良いです。

http://jetglass.hatenablog.jp/entry/2015/05/20/185336

ORDER BY

usersテーブルの全てのレコードのcreated_atが昇順になるように取得する例です。

sql

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つにまとめるための例です。

sql

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を内部結合する例です。

sql

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()

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join

LEFT JOIN

usersテーブルのusers.idと、user_socialsテーブルのuser_socials.usre_idを内外部結合する例です。

sql

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()

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.outerjoin

UNION

tagsテーブルとgenresテーブルの実行結果を統合して表示する例です。ちなみに重複している部分は削除されます。

sql

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()

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.union

UNION ALL

tagsテーブルとgenresテーブルの実行結果を統合して表示する例です。上記のUNIONとの違いは重複している部分も表示されます。

sql

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()

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.union_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のログが記録されています。

sql_log.py

from setting import session
from user import User

user_name = session.query(User)
sql_statement = user_name.statement

print(sql_statement)