Contents
実行環境
python:3.9.2
SQLAlchemy :1.4.15
概要
- SQLAlchemyのインストール
- 設定ファイル、モデルファイル、テーブルの準備
- 各クエリの実行
事前準備
まずは、sqlalchemyのインストールです。以下のコマンドでインストールできます。
pip install sqlalchemy
SQLAlchemyとは
pythonのsqlツールキットであり、pythonのORMapperです。
SQLを手書きするのではなく、以下のようにオブジェクト操作のようにテーブルデータの取得・更新などの操作ができるようになります。
session.query(Account).first()
事前準備1. sessionの作成
まず、設定ファイルを作成します。
pythonの各ファイルからDBにアクセスするためのsession変数の作成や、DB定義の設定ファイルです。
import os
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
DATABASE = os.getenv('DB')
USER = os.getenv('DB_USER')
PASSWORD = os.getenv('DB_PASS')
HOST = os.getenv('DB_HOST')
PORT = os.getenv('DB_PORT')
DB_NAME = os.getenv('DB_NAME')
CONNECT_STR = '{}://{}:{}@{}:{}/{}'.format(DATABASE, USER, PASSWORD, HOST, PORT, DB_NAME)
ENGINE = create_engine(
CONNECT_STR,
encoding = "utf-8",
echo=False
)
# Sessionの作成
# ORM実行時の設定。自動コミットするか、自動反映するなど。
session = scoped_session(
sessionmaker(
autocommit = False,
autoflush = False,
bind = ENGINE
)
)
Base = declarative_base()
Base.query = session.query_property()
事前準備2. モデルの作成
次のモデルを作成します。こちらは実際のテーブルとpython オブジェクトの紐付けのようなイメージです。
モデルを元にテーブルを作成することもできますし、すでにテーブルが存在する場合は、テーブル定義に合わせて記載する必要があります。
import sys
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, DateTime
from datetime import datetime
from common.db.setting import Base
from common.db.setting import ENGINE
class Account(Base):
"""
アカウントモデル
"""
__tablename__ = 'account'
account_id = Column('account_id', String(36), primary_key = True)
account_name = Column('account_name', String(36), nullable=False)
created_at = Column('created_at', DateTime, default=datetime.now(), nullable=False)
updated_at = Column('updated_at', DateTime, default=datetime.now(), onupdate=datetime.now(), nullable=False)
def main(args):
"""
メイン関数
"""
Base.metadata.create_all(bind=ENGINE)
if __name__ == "__main__":
main(sys.argv)
事前準備3. テーブルの作成
上記で作成したファイルを実行するとテーブルを作成することができます。
すでに既存テーブルがある場合は、不要な手順です。
python account.py
SELECT
Accountテーブルのデータを全て取得。
accounts = session.query(Account).all()
for account in accounts:
print(account.account_name)
実行されるSQL
SELECT account.account_id AS account_account_id, account.account_name AS account_account_name, account.created_at AS account_created_at, account.updated_at AS account_updated_at
FROM account
SELECT(カラム指定)
カラムを指定してデータを取得
account_names = session.query(Account.account_name).all()
for name in account_names:
print(name)
実行されるSQL
SELECT account.account_name AS account_account_name
FROM account
WHERE
filter関数を使って取得するデータを絞ることができます。
session.query(Account).filter(Account.account_name == 'テスト1').all()
実行されるSQL
SELECT account.account_name AS account_account_name
FROM account
WHERE account.account_name = %(account_name_1)s
IN
inメソッドに条件の配列を渡すことで、条件にあったデータを取得することができます。
names = ['テスト1', 'テスト2']
accounts = session.query(Account.account_name)\
.filter(Account.account_name.in_(names))\
.all()
実行されるSQL
SELECT account.account_name AS account_account_name
FROM account
WHERE account.account_name IN (%(account_name_1_1)s, %(account_name_1_2)s)
BETWEEN
betweenメソッドに、fromとtoの値を指定して条件にあったデータを取得することができます。
accounts = session.query(Account.account_name)\
.filter(Account.created_at.between('2021-04-01 00:00:00', '2021-04-01 00:00:00'))\
.all()
実行されるSQL
SELECT account.account_name AS account_account_name
FROM account
WHERE account.created_at BETWEEN %(created_at_1)s AND %(created_at_2)s
ORDER BY
order byは「desc」と「asc」のimportが必要です。
from sqlalchemy import desc, asc
accounts = session.query(Account.account_name)\
.order_by(desc(Account.created_at))\
.all()
accounts = session.query(Account.account_name)\
.order_by(asc(Account.created_at))\
.all()
実行されるSQL(DESC)
SELECT account.account_name AS account_account_name
FROM account
WHERE account.created_at BETWEEN %(created_at_1)s AND %(created_at_2)s ORDER BY account.created_at DESC
実行されるSQL(ASC)
SELECT account.account_name AS account_account_name
FROM account
WHERE account.created_at BETWEEN %(created_at_1)s AND %(created_at_2)s ORDER BY account.created_at ASC
DISTINCT
distinctも「distinct」のimportが必要です。また、実行されるSQLが微妙に異なります。
from sqlalchemy import distinct
account_name = session.query(Account).\
distinct(Account.account_name).\
all()
account_name = session.query(distinct(Account.account_name)).\
all()
実行されるSQL(上)
SELECT DISTINCT ON (account.account_name) account.account_id AS account_account_id, account.account_name AS account_account_name, account.created_at AS account_created_at, account.updated_at AS account_updated_at
FROM account
実行されるSQL(下)
SELECT DISTINCT account.account_name
FROM account
COUNT
count = session.query(Account)\
.count()
実行されるSQL
SELECT count(*) AS count_1
FROM (SELECT account.account_id AS account_account_id, account.account_name AS account_account_name, account.created_at AS account_created_at, account.updated_at AS account_updated_at
FROM account) AS anon_1
LIMIT
session.query(Account)\
.limit(2)\
.all()
実行されるSQL
SELECT account.account_id AS account_account_id, account.account_name AS account_account_name, account.created_at AS account_created_at, account.updated_at AS account_updated_at
FROM account
LIMIT %(param_1)s
JOIN
session.query(Account, Trade)\
.join(Trade, Account.account_id==Trade.account_id)\
.all()
実行されるSQL
SELECT account.account_id AS account_account_id, account.account_name AS account_account_name, account.created_at AS account_created_at, account.updated_at AS account_updated_at, trade.trade_id AS trade_trade_id, trade.account_id AS trade_account_id, trade.created_at AS trade_created_at, trade.updated_at AS trade_updated_at
FROM account
JOIN trade
ON account.account_id = trade.account_id
INSERT
account = Account()
account.account_id = 'ca773c0b-6f85-eedb-38ed-da04a634f21e'
account.account_name = 'テスト3'
session.add(account)
session.commit()
実行されるSQL
INSERT INTO account (account_id, account_name, created_at, updated_at) VALUES (%(account_id)s, %(account_name)s, %(created_at)s, %(updated_at)s)
2021-06-24 00:18:18,692 INFO sqlalchemy.engine.Engine [generated in 0.00042s] {'account_id': 'ca773c0b-6f85-eedb-38ed-da04a634f21e', 'account_name': 'テスト3', 'created_at': datetime.datetime(2021, 6, 24, 0, 18, 18, 599123), 'updated_at': datetime.datetime(2021, 6, 24, 0, 18, 18, 599175)}
UPDATE
account = session.query(Account).filter(Account.account_name == 'テスト3').first()
account.account_name = 'テスト4'
session.commit()
実行されるSQL
UPDATE account
SET account_name=%(account_name)s, updated_at=%(updated_at)s
WHERE account.account_id = %(account_account_id)s
DELETE
account = session.query(Account).filter(Account.account_name == 'テスト4').first()
session.delete(account)
session.commit()
実行されるSQL
DELETE FROM account WHERE account.account_id = %(account_id)s
TIPS1. 実行されるSQLの確認
上記で記載のsetting.pyのechoをTrueに変更すると標準出力にSQLが出力されます。
ENGINE = create_engine(
CONNECT_STR,
encoding = "utf-8",
echo=False # Trueにすると標準出力にSQLが出力される
)
TIPS2. 更新日時を自動で更新させる
更新日時をカラムに作っている場合は、自動で更新してほしいですよね。以下のようにモデルのupdate_atにonpudate=datetime.now()を指定すると自動更新させることができます。
updated_at = Column('updated_at', DateTime, default=datetime.now(), onupdate=datetime.now(), nullable=False)