St_Hakky’s blog

Data Science / Human Resources / Web Applicationについて書きます

SQLAlchemyを使っていて勉強したこととか便利なTipsとかをまとめた

Python

こんにちは。SQLAlchemyを使っていてわかったこととか勉強になったことをまとめました。

◯環境構築

以下の記事にまとめました。

st-hakky.hatenablog.com

◯DatabaseのURLの指定方法

Databaseに接続するためにURLを指定する必要があるのですが、そのURLの指定方法は以下のとおりです。

<dialect>+<driver>://<username>:<password>@<host>:<port>/<database>?charset=<charset_type>

ちなみに実際に書くときは<>は外して書きます。また「?」以降は無くても動作します。

各要素についての説明は以下のとおりです。

要素 意味
dialect mysqlとかpostgresqlとかを指定します。ここはDBの種類を指定するイメージ
driver DBに接続するためのドライバーの指定をします。pymysqlとか
username DBに接続することができるユーザー名
password DBに接続するためのパスワード
host ホスト名。localhostとかIPで指定します
port ポート番号
database 接続するデータベース名
charset_type DBに接続する際の文字コード。utf8とかですね

実際の具体例としては、例えばMySQLに接続する際は以下のような感じで接続できます。

mysql+mysqldb://scott:tiger@localhost/foo

他の例についてはこちらの公式ドキュメントで挙げられているので、参考になります。

◯Connectiong : create_engine

上記のような形で指定できるURLを用いてデータベースに接続するためのEngineのインスタンスを生成します。

from sqlalchemy import create_engine
engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')

create_engineでは、引数にいろんな指定をすることができて、例えば文字コードの指定(encoding)やデバッグ用に出力を吐かせたり(echo)など、そういうことができます。具体例は以下の通り。

from sqlalchemy import create_engine
engine = create_engine("mysql://scott:tiger@hostname/dbname",
                            encoding='latin1', echo=True)
■参考文献

◯Declare a Mapping

pythonでテーブルを記述したclassとデータベースをMappingするための処理が用意されています。以下のようにして記述し、ここで生成したBaseのインスタンスを用いてclassを作成することで実現できます。

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class User(Base):
  __tablename__ = "users"
  #...略...

◯定義したTableのclassを見る方法

以下のコマンドでアクセスできる。

User.__table__

◯Sessionの作成

データベースに対するORMの操作はSessionを通じて行われます。

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
engine = create_engine("mysql://scott:tiger@hostname/dbname",
                            encoding='latin1', echo=True)
Session = sessionmaker(bind=engine)

もし上記のようにcreate_engineを事前に行っていなかった場合は、以下のようにできます。

# Sessionのオブジェクトを作る
Session = sessionmaker()

# create_engineをした段階で、Sessionに代入する
Session.configure(bind=engine)

◯挿入・更新・削除・読み込み

ここまででテーブルの定義も一応でき、sessionもできたので、ここでテーブルのデータへの操作を行うことを考える。ここでは、こちらのTutorialで使われている例を参考にした。

■今回の例でつかうテーブル

挿入などの例を見るために使用するテーブルは以下のとおりである。

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(12))

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
                                self.name, self.fullname, self.password)
■挿入：insert
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)  # ここではまだDBに登録されていない
session.commit()  # ここで登録の処理が実行される

addした段階ではまだDBに登録する処理は行われていない。しかし、addまでをした段階で「name="ed"」の条件のもとqueryをなげると、結果が正常に返ってくる。

■更新：update

queryなどで取ってきたデータの情報を更新する処理。sessionに登録されているクラスの情報を更新して、commitすると情報が更新される。

# ここはqueryで取ってきたデータでもよい。
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)

# パスワードの変更
ed_user.password = 'f8s7ccs'

# パスワードの更新
session.commit()
■削除：delete

これは簡単。ふつうにqueryで投げた結果に対して、sessionからdeleteメソッドを使用すればいいです、

session.delete(user)
■読み込み：select

queryはsessionのquery()メソッドで実現される。

query_result = session.query(User).all()

ちなみに、通常のSQLのqueryみたく、条件によるフィルター(filter())やソート(order_by())などもできる。これについては、以下のサイトに沢山記載されている。


また、いつも混乱するのだが、「.all()」メソッドはlist形式で、「.first()」メソッドはqueryの結果の最初の値をscalarで取ってくる。これは良く勘違いするので、注意。queryの結果については、各カラムの値を、属性(例えばnameなど)で指定することで、参照することができる。

また、queryの結果を辞書式にする方法は、以下の通り。

# queryを投げてデータを取得。.all()なので、結果はlistで取得される。
users = session.query(User.id, User.user_name).all()

# 辞書式に変換
dict_result = [user._asdict() for user in users]

queryを投げる時に取得するカラム名を指定することで、辞書式にする「_asdict()」メソッドが使えます。

◯sessionの管理

sessionは使ったらきちんと閉じないといけないのですが、これがどうにも忘れがちになるので、なんかうまいこと管理する方法がないかなーと探していたところ、sessionをwithで管理する方法を実現しているコードがありました。以下の記事で紹介されています。

Python の with 文で SQLAlchemy のセッションを管理する | CUBE SUGAR STORAGE

これはめちゃめちゃ便利でした。

◯データベースエンジンの指定

InnoDBを指定したいときとかは、以下のような感じでできる。

__table_args__ = {'mysql_engine': 'InnoDB'}

これをテーブルの定義の際にいれればおけ。

◯UNIQUE制約

UNIQUE制約を書ける時は、普通にテーブル定義の際に、Columnの中で指定をすればいい。また複数のカラムに対してUNIQUE制約を書けたい場合も、以下のようにすることで実現できる。コードはこちらのものを参考にした。

class Example(Base):
    __tablename__ = "example"
    __table_args__ = UniqueConstraint('col2', 'col3', name='uix_1')


    # per-column anonymous unique constraint
    col1 = Column('col1', Integer, unique=True)

    col2 = Column('col2', Integer)
    col3 = Column('col3', Integer)
    
    def __init__(self...)
    # ...略

ちなみに、InnoDBと組み合わせる際には、()でくくる必要がある。

__table_args__ = (UniqueConstraint('col2', 'col3', name='uix_1'), {'mysql_engine': 'InnoDB'})
■参考サイト

◯外部キー

sqlalchemyの外部キーに関する記事を見ていたら外部キー制約をつけない方法もあるみたいなのをみて、なぬーんってなったけど一旦無視する笑。

■SQLiteをつかう場合

あと、私は今SQLiteは使っていないですが、SQLiteでは外部キー制約が無効になっているらしく、それを解決する必要があるみたいですね。

■CASCADEでUPDATE/DELETEを行う

[工事中]

■参考文献

◯sqlalchemyでEnumを使う

sqlalchemyを使っていた時、「デフォルトでEnumってないんかーい」ってまずなりました笑。別に文字列で指定したりとかして値の入力に制限を設ければいいんだろうけどなんかムズムズするので、Enumをつかう方法を結局頑張って探すことにしました。

Python3.4からは標準でEnum型を扱う方法が導入されており、これを使ってsqlalchemyからEnumを使えるように自作します。

私が改めて書く必要がないくらい以下の記事にまとまっていますので、これにて終了とします笑

qiita.com

Enumついでに書いておきますと、python3.6からEnumに自動で値をつけるautoが実装されたので、値の種類が多くなっても楽勝です。使い方としては、以下のような感じ。

from enum import IntEnum, auto

class Field(IntEnum):

   OrderNo = 1
   CustomerNo = auto()
   PurchaseDate = auto()

こうすると、一つ前のメンバにプラス１した値が自動で代入されます。便利ですね。

■参考文献

Python の with 文で SQLAlchemy のセッションを管理する | CUBE SUGAR STORAGE

◯NULLに関する制約

NULLに関する制約はColumnの引数で行うことができる。

Column("column_name", nullable=False)

デフォルトはTrueで、NULLの値を許可している。FalseでNULLの値を許可しないようにできる。

◯デーブル定義のテンプレ

こちらの記事にある通り、一つテーブル定義のテンプレを作って置いて、それを使い回せばかなり色々楽になりそう。よくやる処理は決まっているし笑。

■参考文献

◯その他

以下は探している途中で見つけた面白い記事。