【Python ORM】 sqlalchemyの使い方これだけ！

実行環境

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)

beyondDays

IT系の技術関連、資産運用・お得情報に関する記事を投稿しています。

【Python ORM】 sqlalchemyの使い方これだけ！

実行環境

概要

事前準備

SQLAlchemyとは

事前準備1. sessionの作成

事前準備2. モデルの作成

事前準備3. テーブルの作成

SELECT

SELECT（カラム指定）

WHERE

IN

BETWEEN

ORDER BY

DISTINCT

COUNT

LIMIT

JOIN

INSERT

UPDATE

DELETE

TIPS1. 実行されるSQLの確認

TIPS2. 更新日時を自動で更新させる

コメントを残すコメントをキャンセル

実行環境

概要

事前準備

SQLAlchemyとは

事前準備1. sessionの作成

事前準備2. モデルの作成

事前準備3. テーブルの作成

SELECT

SELECT（カラム指定）

WHERE

IN

BETWEEN

ORDER BY

DISTINCT

COUNT

LIMIT

JOIN

INSERT

UPDATE

DELETE

TIPS1. 実行されるSQLの確認

TIPS2. 更新日時を自動で更新させる

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル