jobs python

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

 

実行環境

python：3.9.2
SQLAlchemy ：1.4.15

概要

  1. SQLAlchemyのインストール
  2. 設定ファイル、モデルファイル、テーブルの準備
  3. 各クエリの実行

事前準備

まずは、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)

 

コメントを残す

メールアドレスが公開されることはありません。

CAPTCHA