初めに
こんにちは。株式会社エヌ・エフ・ラボラトリーズ(以下NFLabs.) 学生インターンの滝上です。 弊社では事業の１つとしてセキュリティ人材を育成する教育用コンテンツの作成を行なっており、私は普段このプロジェクトのバックエンド領域の開発に携わっています。最近では、sqlクエリのO/Rマッパー(ORM)による書き換えを行なっています。
それに関連して、今回はSQLAlchemyを使ってDB上のデータを操作する方法を解説したいと思います。
注意点
本記事では接続先のDBにMySQLを使用しています。また、既存のDB、テーブルに対する操作に焦点を当てて解説をしています。より包括的な情報は公式のチュートリアルをご覧ください。
インターンについて
では早速SQLAlchemyについて解説...といきたいところですが、その前にNFLabs.でのインターンを普段どのように行っているか紹介したいと思います。
私は現在大学４年であり、授業がある時期には1日3-4時間程度の勤務を週2-4回、長期休みには9:00-17:00で週2回程度の勤務をしています。
基本的に業務はフルリモートで行っており、朝から出勤する日はデイリースクラムで今日やるタスクの確認をした後、困ったことがあればslackやgitlabで適宜質問をしつつ開発するという流れになっています。午後からの出勤等でデイリースクラムに出られない日は最初にメンターの方と15分程度ミーティングをし、その日やるタスクの確認・整理をして業務に入るという感じになります。最初からフルリモートだと中々コミュニケーションが取りづらいかなと心配していたんですが、出勤すればデイリースクラムで毎回顔を合わせますし、社員の方がみなさん優しいのでその点で困る事はありませんでした(最近はランチ会などもあったりします)。質問に関してもフランクにslackで質問が飛び交っているので、インターンだからと気後れする事なく質問することができています。
また、上司の方から1on1で度々「学業を最優先にしてください」と言われていて、卒論で忙しい時期やテスト期間に出勤の量を減らすor無くすということが気兼ねなくできるのも嬉しいポイントです。総じてインターンとしてはとても良い環境で働かせてもらっていると思います。
ORM導入の背景
インターンの紹介も済んだので、ついにSQLAlchemyについて解説...といきたいところですが、その前にまず、なぜ今回ORMを使うことになったのかについて簡単にご説明したいと思います。 これまでDB操作についてはpymysqlを使って、コードにクエリを直接書いていました。しかしテーブル間のリレーションが複雑になったことで、以下のような長いクエリが段々多くなってきました(実際のコードではなく例です)。
sql = (
"SELECT users.uer_id, users.user_name, groups.group_id, regions.region_id,"
+ " profiles.profile_id, profiles.profile, ips.ip"
+ " FROM users AS users"
+ " LEFT OUTER JOIN groups AS groups ON users.fk_group_id = groups.group_id"
+ " LEFT OUTER JOIN regions AS regions ON groups.fk_region_id = regions.region_id"
+ " LEFT OUTER JOIN profiles AS profiles ON users.fk_profile_id = profiles.profile_id"
+ " LEFT OUTER JOIN ips AS ips ON users.fk_ip_id = ips.ip_id"
+ " WHERE users.user_id = %(user_id)s
)
これにより、可読性が低くメンテナンスがしづらいと感じる機会が多くなりました。また、クエリを文字列で書かなければならないためlinter等も効かず、pythonコード上での修正がしづらいこともデメリットとなっていました。そこで、ORMを導入してDBをオブジェクトとして扱い、クエリを関数を使って書くことで可読性が上がり、バグや脆弱性を埋め込む可能性が低くなることを期待してORMを導入することになりました。
SQLAlchemyとは
SQLAlchemyはpythonのORMの１つで、ソースコード、公式ドキュメントはMIT LicenseでOSSとして配布されています。 今回のプロジェクトではSQLAlchemyを使ってDB処理の書き換えを行なっていくことにしました。
インストール方法
SQLAlchemyのインストールはpipで行えます。
pip install sqlalchemy
DBとの接続方法
SQLAlchemyを使用するためには、まずEngineインスタンスを作成する必要があります。Engineはcreate_engine関数を使って以下のように作成します。
from sqlalchemy import create_engine
import config
user = config.DB_USER
password = config.PASSWORD
host = config.HOST
dbname = config.DATABASE
mysql_url = f"mysql+pymysql://{user}:{password}@{host}/{dbname}"
engine = create_engine(mysql_url)
create_engineでは接続先のURL(mysql_urlの部分)を指定することでEngineインスタンスを作成します。その他の引数についてはデフォルトの設定のままで十分ですが、
engine = create_engine(mysql_url, echo=True)
のように設定することで標準出力にログを吐き出すことができるようになり、デバッグに便利です。本番環境ではしっかりFalseにすることを忘れないようにしましょう。
ここで作成したengineを利用してDBへのコネクションの作成、DB操作、結果の取得等を行います。
既存のテーブル情報の取得
ここでは以下のようなテーブルが既にDBに存在している状態を考えます。(公式サイトより引用)
CREATE TABLE user (
id INTEGER NOT NULL,
name VARCHAR(30) NOT NULL,
fullname VARCHAR,
PRIMARY KEY (id)
)
...
CREATE TABLE address (
id INTEGER NOT NULL,
email_address VARCHAR NOT NULL,
user_id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES user_account (id)
)
この時、以下のようにautomapを使うことでpythonのクラスにDBのテーブルをマッピングさせることができます。
from sqlalchemy.ext.automap import automap_base Base = automap_base() Base.prepare(engine) user = Base.classes.user address = Base.classes.address
この時、スキーマやリレーションシップといった情報も自動的に取得してくれます。また、automapを使ってクラスを作成すると、テーブルスキーマの変更を行なった時にこれに追従して対応するクラスの更新も行なってくれます。declarative_base()を使ったテーブルインスタンスの作成が一般的ですが、個人的には既にテーブルが存在する場合にはautomapを使ったテーブルインスタンスの作成をお勧めします。
セッション作成
SQLAlchemyはSessionインスタンスを使ってDBとのコネクション、トランザクション等の管理を行い、Sessionクラスのコンストラクタを呼ぶことで生成します。
session = scoped_session(
sessionmaker(
autocommit = False,
autoflush = True,
bind = engine
)
)
autocommitはSQLAlchemyが自動的にcommitをする機能です。ver.1.4からこの機能は公式に非推奨になっているため、Falseにしておくのが良いと思います(デフォルトでFalseになっています)。autoflushはInsertやUpdateなどの更新処理のクエリが読み込まれた場合、自動的にクエリを発行してくれる機能であり、Falseにしているとsession.commit()されるまでクエリが発行されなくなリます。bindではセッションを作成する接続先のDBを指定します。
CRUD処理のORM記法
実はSQLAlchemyにはORMとSQL Expression Languageという2つの記法があります。SQL Expression LanguageはORMより生のSQLに近い表記で記述することができる為、より柔軟にクエリを記述することができます。ただ、本記事ではORM記法に絞って解説します。
select
テーブルからデータを取り出すselect処理を行うにはsession.queryを使います。
res = session.query(users)
条件を指定したい(sqlのwhere文を使いたい)場合はfilter()を使って条件を指定します。
target_user_name = "taro" res = session.query(users).filter(users.name==target_user_name).first()
filter()の後にあるfirst()は検索結果の１番目のレコードのみ取得する関数であり、その他の指定方法として - all: 全ての検索結果を取得 - one: 検索結果を一つだけ取り出す。検索結果が０件、または2以上であった場合にエラーを返す - scalar: 検索結果の１番目のレコードの、１番最初の要素だけ取り出す - ...etc
等があります。詳しくは公式サイトをご覧ください。 複数テーブルから情報を取りたい場合に使うjoin文に関してはjoin()があり、leftouterjoinを行いたい場合はouterjoin()を使うことができます。これを使ってORM導入の背景で例としてあげたクエリ
sql = (
"SELECT users.uer_id, users.user_name, groups.group_id, regions.region_id,"
+ " profiles.profile_id, profiles.profile, ips.ip"
+ " FROM users AS users"
+ " LEFT OUTER JOIN groups AS groups ON users.fk_group_id = groups.group_id"
+ " LEFT OUTER JOIN regions AS regions ON groups.fk_region_id = regions.region_id"
+ " LEFT OUTER JOIN profiles AS profiles ON users.fk_profile_id = profiles.profile_id"
+ " LEFT OUTER JOIN ips AS ips ON users.fk_ip_id = ips.ip_id"
+ " WHERE users.user_id = %(user_id)s
)
を書き換えると以下のようになります。
sql = session.query(users.uer_id, users.user_name, groups.group_id, regions.region_id, \
profiles.profile_id, profiles.profile, ips.ip) \
.outerjoin(groups, users.fk_group_id == groups.group_id) \
.outerjoin(environments, groups.fk_region_id == regions.region_id) \
.outerjoin(statuses, users.fk_profile_id == profiles.profile_id) \
.outerjoin(users.fk_ip_id = ips.ip_id) \
.filter(users.user_id==user_id)
...どうでしょう？多少は読みやすくなりましたでしょうか？outerjoinが関数になったことでエディタ上では見やすくなった他に、joinの条件式にlinterが働くようになったのでバグの混入を防ぎやすくなったかなと思います。
insert,delete,update
ここからは業務で詳しく扱ったわけではないため、紹介程度にとどめます。
session.add()を使うことで、レコードを追加することができます。
target_record = tables(name = "taro", address="sample@samplemail.com") session.add(target_record) session.commit()
レコードを追加したなら次は消去したくなると思いますが、session.queryで読み出したオブジェクトをdelete()するとレコードの消去を行えます。
del_records = session.query(table).filter(table.name=="taro")
for del_record in del_records:
session.delete(del_record)
session.commit()
検索条件にヒットしたものを一括で消去することも可能です。
session.query(table).filter(table.name=="taro").delete() session.commit()
レコードの情報を更新したい場合には対象のレコード情報を取得し、変更したい要素に変更後の内容を代入してsession.commit()すれば良いです。
record = session.query(table).filter(table.name=="taro").first() record.name = "jiro" session.commit()
終わりに
今回はSQLAlchemyを使ったDB処理の書き方について紹介しました。 n番煎じのような記事の内容ですが、学んだ知識の確認・インターンの業務内容の紹介のために記事を書かせていただきました。 この記事がご参考になれば幸いです。
