SQLAlchemyでのテーブル定義

#はじめに
ここでは，SQLAlchemyを用いてテーブル定義を行うときの方法についてまとめる．
**以下，dialect(DBの種類)はmysqlとする．**
#Charset指定
` __table_args__ `の`mysql_charset`で指定することができる．
例えば，charsetをutf8mb4に指定したければ，

```python
class User(Base):
    __table_args__=({"mysql_charset": "utf8mb4"})
```
とすればよい．
ちなみに，utf8と指定すると，以下のようにWarningが吐かれる．

```python
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以前を使い，下のように書くとエラーが生じる．

```python
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となる．
以下のように書けばよい．

```python
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に指定したければ，

```python
class User(Base):
    __table_args__=({"mysql_engine": "InnoDB"})
```
とすればよい．
#Data Types
SQLAlchemyのGeneric Typesとしては，`from sqlalchemy.types import hogehoge`でimportして使うことができる．
例えば，以下のようなものがある．

```python
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)](https://docs.sqlalchemy.org/en/13/core/type_basics.html#generic-types)

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

```python
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)](https://docs.sqlalchemy.org/en/13/dialects/mysql.html#mysql-data-types
))
#Primary Key
Columnの中で`primary_key=True`とすればよい．

```python
class User(Base):
    id=Column(Integer, primary_key=True) # idはPrimary Key
```
複合Primary Keyにしたければ，両方に`primary_key=True`とすればよい．

```python
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`とすればよい．

```python
class User(Base):
    id=Column(Integer, autoincrement=True) # idはAuto Increment
```
#Default値
Columnの中で`server_default=hogehoge`とすればよい．
注意点としては，server_defaultには，stringかtext()を渡さなければならない．

```python
from sqlalchemy.dialects.mysql import TINYINT as Tinyint

class User(Base):
    bool=Column(Tinyint(1), server_default="1") # 渡すのはstring
```
渡したものがstringであれば，single quotesに変わり，text()であればquoteなしに変わる．

```python
x=Column(String, server_default="val")
x STRING DEFAULT 'val' #single quotesに変わる
```
```python
y=Column(DateTime, server_default=text("NOW()")
y DATETIME DEFAULT NOW() # quoteなし
```
[公式ドキュメント(server_default)](https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Column.params.server_default
)

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

```python
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)](https://docs.sqlalchemy.org/en/13/core/functions.html#sqlalchemy.sql.functions.current_timestamp
)

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

```python
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?
](https://stackoverflow.com/questions/6992321/how-to-set-default-on-update-current-timestamp-in-mysql-with-sqlalchemy)
#Null制約
Columnの中で`nullable=False`とすればよい．

```python
class User(Base):
    a=Column(Integer, nullable=False) # aはintでnot null
```
#Unique制約
単一カラムにUnique制約をつけるのであれば，Columnの中で`unique=True`とすればよい．

```python
class User(Base):
    a=Column(Integer, unique=True) # aはintでunique
```
複数カラムにUnique制約をつけるのであれば，`sqlalchemy.schema.UniqueConstraint`を`__table_args__`内に書かなければならない．

```python
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}")`

```python
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)](https://docs.sqlalchemy.org/en/13/core/constraints.html#sqlalchemy.schema.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とする．

```python
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を張ると，以下のようにしてそれぞれのクラスのインスタンスを呼び出すことができる．

```python

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先が呼び出されたときに読み込まれる．

```python
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先を呼び出すために，別々に発行される．

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

###lazy="joined" ("False"でも同じ)
relation元が呼び出されたときに，relation先も呼び出される．
lazy="immediate"との違いは，クエリの発行回数である．joinして呼び出されるため，クエリの発行回数は1回ですむ．
[公式ドキュメント(relationship)](https://docs.sqlalchemy.org/en/13/orm/relationship_api.html#sqlalchemy.orm.relationship.params.lazy)

#参考文献
この記事は以下の情報を参考にして執筆しました．
・[公式ドキュメント](https://www.sqlalchemy.org/)
・[sqlalchemyのテーブル定義tips](https://qiita.com/petitviolet/items/e03c67794c4e335b6706)
・[SQLAlchemyのリレーションにおけるメソッドやパラメータについてのメモ](https://poyo.hatenablog.jp/entry/2017/01/08/212227)
・[Python の O/Rマッパー SQLAlchemy を使ったリレーショナルマッピング基本 4 パターン](https://momijiame.tumblr.com/post/27327972441/python-%E3%81%AE-or%E3%83%9E%E3%83%83%E3%83%91%E3%83%BC-sqlalchemy-%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%9F%E3%83%AA%E3%83%AC%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%8A%E3%83%AB%E3%83%9E%E3%83%83%E3%83%94%E3%83%B3%E3%82%B0%E5%9F%BA%E6%9C%AC-4)
・[How to set DEFAULT ON UPDATE CURRENT_TIMESTAMP in mysql with sqlalchemy?
](https://stackoverflow.com/questions/6992321/how-to-set-default-on-update-current-timestamp-in-mysql-with-sqlalchemy)