LoginSignup
89
81

More than 3 years have passed since last update.

@arkuchy

SQLAlchemyでのテーブル定義

Last updated at Posted at 2020-02-03

はじめに

ここでは，SQLAlchemyを用いてテーブル定義を行うときの方法についてまとめる．
以下，dialect(DBの種類)はmysqlとする．

Charset指定

__table_args__のmysql_charsetで指定することができる．
例えば，charsetをutf8mb4に指定したければ，

class User(Base):
    __table_args__=({"mysql_charset": "utf8mb4"})

とすればよい．
ちなみに，utf8と指定すると，以下のようにWarningが吐かれる．

Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")

ROW_FORMAT=DYNAMIC

インデックスの最大キー長は3072byteであるが，mysqlのversionが5.6以前の場合，デフォルトでは767byteとなっている．したがって，5.6以前を使い，下のように書くとエラーが生じる．

class User(Base):
    __table_args__=({"mysql_charset": "utf8mb4"})
    a=Column(String(255), unique=True)

なぜなら，utf8mb4を指定しているので，255*4=1020(byte)となり，767byteを超えてしまうからである．
(ちなみに，utf8mb3の場合は，255*3=765(byte)で大丈夫)
そこで，ROW_FORMAT=DYNAMICとすることで，インデックスの最大キー長が3072byteとなる．
以下のように書けばよい．

class User(Base):
    __table_args__=({"mysql_charset": "utf8mb4", "mysql_row_format": "DYNAMIC"})
    a=Column(String(255), unique=True)

5.7以降ではデフォルトで，ROW_FORMAT=DYNAMICとなっている．

DBエンジン指定

__table_args__のmysql_engineで指定することができる．
例えば，DBエンジンをInnoDBに指定したければ，

class User(Base):
    __table_args__=({"mysql_engine": "InnoDB"})

とすればよい．

Data Types

SQLAlchemyのGeneric Typesとしては，from sqlalchemy.types import hogehogeでimportして使うことができる．
例えば，以下のようなものがある．

from sqlalchemy.types import Float
from sqlalchemy.types import Integer
from sqlalchemy.types import String
from sqlalchemy.types import DateTime

これらのGeneric Typesは，使用するDBの種類に応じて適切なData Typesを，CREATE TABLEするときに，SQLAlchemyが選んでくれる．
公式ドキュメント(Generic Types)

しかし，例えばunsigned intやtiny int，timestamp型などはGeneric Typesにはないので，そのようなときは，from sqlalchemy.dialects.mysql import fugafugaとすればよい．

from sqlalchemy.dialects.mysql import INTEGER as Integer
from sqlalchemy.dialects.mysql import TINYINT as Tinyint
from sqlalchemy.dialects.mysql import TIMESTAMP as Timestamp

class User(Base):
    a=Column(Integer(unsigned=True)) # aはunsigned int
    b=Column(Tinyint(1)) # bはtinyint(1)
    c=Column(Timestamp) # cはtimestamp

(公式ドキュメント(MySQL Data Types))

Primary Key

Columnの中でprimary_key=Trueとすればよい．

class User(Base):
    id=Column(Integer, primary_key=True) # idはPrimary Key

複合Primary Keyにしたければ，両方にprimary_key=Trueとすればよい．

class User(Base):
    # id_1とid_2が複合Primary Key
    id_1=Column(Integer, primary_key=True)
    id_2=Column(Integer, primary_key=True)

Auto Increment

Columnの中でautoincrement=Trueとすればよい．

class User(Base):
    id=Column(Integer, autoincrement=True) # idはAuto Increment

Default値

Columnの中でserver_default=hogehogeとすればよい．
注意点としては，server_defaultには，stringかtext()を渡さなければならない．

from sqlalchemy.dialects.mysql import TINYINT as Tinyint

class User(Base):
    bool=Column(Tinyint(1), server_default="1") # 渡すのはstring

渡したものがstringであれば，single quotesに変わり，text()であればquoteなしに変わる．

x=Column(String, server_default="val")
x STRING DEFAULT 'val' #single quotesに変わる

y=Column(DateTime, server_default=text("NOW()")
y DATETIME DEFAULT NOW() # quoteなし

公式ドキュメント(server_default)

Default値をcurrent_timestampにしたければ，from sqlalchemy.sql.functions import current_timestampとし，server_default=current_timestamp()とすればよい．

from sqlalchemy.dialects.mysql import TIMESTAMP as Timestamp
from sqlalchemy.sql.functions import current_timestamp

class User(Base):
    created_at=Column(Timestamp, server_default=current_timestamp())

公式ドキュメント(current_timestamp)

Update値

Columnの中でserver_onupdate=hogehogeとしても反映されない．
そこで，DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPとしたければ，server_defaultを用いて，

from sqlalchemy.dialects.mysql import TIMESTAMP as Timestamp
from sqlalchemy.sql.expression import text

class User(Base):
    created_at=Column(Timestamp, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

とすればよい．
How to set DEFAULT ON UPDATE CURRENT_TIMESTAMP in mysql with sqlalchemy?

Null制約

Columnの中でnullable=Falseとすればよい．

class User(Base):
    a=Column(Integer, nullable=False) # aはintでnot null

Unique制約

単一カラムにUnique制約をつけるのであれば，Columnの中でunique=Trueとすればよい．

class User(Base):
    a=Column(Integer, unique=True) # aはintでunique

複数カラムにUnique制約をつけるのであれば，sqlalchemy.schema.UniqueConstraintを__table_args__内に書かなければならない．

from sqlalchemy.schema import UniqueConstraint

class User(Base):
    #(a, b)でunique
    __table_args__=(UniqueConstraint("a", "b", name="uq_user_00"))
    a=Column(Integer)
    b=Column(Integer)

もちろん，単一カラムにUnique制約をつけるときでも，sqlalchemy.schema.UniqueConstraintを使うことができる．

Foreign Key制約

使い方

Columnの中で，sqlalchemy.schema.ForeignKeyを設定すればよい．
基本は，sqlalchemy.schema.ForeignKey("{table_name}.{column_name}", name="{foreign_key_constraint_name}")

from sqlalchemy.schema import ForeignKey

class User(Base):
    __tablename__="users"
    id=Column(Integer, nullable=False, autoincrement=True, primary_key=True)
    name=Column(String(255), nullable=False)

class TestResult(Base):
    __tablename__="test_results"
    id=Column(Integer, nullable=False, autoincrement=True, primary_key=True)
    value=Column(Integer, nullable=False)
    user_id=Column(Integer, ForeignKey("users.id", name="fk_test_results_00", nullable=False)) # usersテーブルのidがForeignKey

また，OnUpdateやOnDeleteなどを設定したければ，sqlalchemy.schema.ForeignKey()の中に，onupdate="CASCADE"やondelete="CASCADE"を追加すればよい．
公式ドキュメント(ForeignKey.params.column)
注意点としては，Column内に書くオプションのうち，DataTypeやForeignKeyなどは前に，"="で渡すものは後ろに書かなければならない．

relation

relationを張ることで，明示的にjoinをしなくても，ForeignKeyを追うことができる．
relationの張り方は，カラム定義と同じようにhoge=sqlalchemy.orm.relationship("{クラス名}", uselist={True or False})とすればよい．
uselistは，1つのデータに対して，結びつくデータが複数ある場合にTrue, 1つしかない場合にFalseとする．
また，以下では，User:TestResult = one:manyとする．

from sqlalchemy.schema import ForeignKey
from sqlalchemy.orm import relationship

class User(Base):
    __tablename__="users"
    id=Column(Integer, nullable=False, autoincrement=True, primary_key=True)
    name=Column(String(255), nullable=False)
    test_result=relationship("TestResult", uselist=True) # UserからTestResultにrelationを張る, 結びつくデータは複数

class TestResult(Base):
    __tablename__="test_results"
    id=Column(Integer, nullable=False, autoincrement=True, primary_key=True)
    value=Column(Integer, nullable=False)
    user_id=Column(Integer, ForeignKey("users.id", name="fk_test_results_00", nullable=False)) # usersテーブルのidがForeignKey
    user=relationship("User") # TestResultからUserにrelationを張る, 結びつくデータは1つ

このようにrelationを張ると，以下のようにしてそれぞれのクラスのインスタンスを呼び出すことができる．


test_result_1=session.query(TestResult).filter(TestResult.id==1).one() # test_resultsテーブルにおいて，idが1のものを取り出す
user_of_test_result_1=test_result_1.user #test_result_1のuser_idと一致するidを持ったUserクラスのインスタンスが取り出せる．
# user_of_test_result_1=User

user_1=session.query(User).filter(User.id==1).one() # usersテーブルにおいて，idが1のものを取り出す
test_results_of_user_1=user_1.test_result # user_1のidと一致するuser_idを持ったTestResultクラスのインスタンスのリストが取り出せる　
# test_results_of_user_1=[TestResult, TestResult, ...]

オブションの中の1つとして，lazyというものがあり，このパラメータによって，relationが張られた先のテーブルが読み込まれるタイミングを設定することができる．
デフォルトでは，呼び出されたときに読み込まれる．

lazy="select" ("True"でも同じ)

デフォルトと同じ．relation先が呼び出されたときに読み込まれる．

test_result_1=session.query(TestResult).filter(TestResult.id==1).one() # ここではまだ，Userが読み込まれない．
user_of_test_result_1=test_result_1.user # ここでUserが読み込まれる

lazy="immediate"

relation元が呼び出されたときに，relation先も呼び出される．
クエリはrelation元とrelation先を呼び出すために，別々に発行される．

test_result_1=session.query(TestResult).filter(TestResult.id==1).one() # ここでUserも読み込まれる

lazy="joined" ("False"でも同じ)

relation元が呼び出されたときに，relation先も呼び出される．
lazy="immediate"との違いは，クエリの発行回数である．joinして呼び出されるため，クエリの発行回数は1回ですむ．
公式ドキュメント(relationship)

参考文献

この記事は以下の情報を参考にして執筆しました．
・公式ドキュメント
・sqlalchemyのテーブル定義tips
・SQLAlchemyのリレーションにおけるメソッドやパラメータについてのメモ
・Python の O/Rマッパー SQLAlchemy を使ったリレーショナルマッピング基本 4 パターン
・How to set DEFAULT ON UPDATE CURRENT_TIMESTAMP in mysql with sqlalchemy?

89
81
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
89
81