sqlalchemyのテーブル定義tips


## tips

- データベースエンジンの指定
- 複数カラムに対するユニーク制約
- 外部キー制約
- NULLを許さない
- unsigned int(MySQL)
- 随時追加？

バージョンは0.9.4です

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

`__table_args__`で指定出来る

```python
__table_args__ = {'mysql_engine': 'InnoDB'}
```


### ユニーク制約

単一カラムへは簡単だが、複数カラムにユニーク制約を貼るには
`sqlalchemy.schema.UniqueConstraint`を使用する必要がある

```python
# 単一カラム
name = Column("name", String(255), unique=True)
# 複数カラム
__table_args__ = (UniqueConstraint("personid", "address", name="unique_idx_personid_address"))
# nameは指定しなくも良い
```

なお、上のInnoDB指定と組み合わせるには`UniqueConstraint`を`()`で囲む必要があるらしい

```python
__table_args__ = (
            (UniqueConstraint('personid', 'address', name='unique_idx_personid_address')),
            {'mysql_engine': 'InnoDB'})
```

### 外部キー制約

外部キー制約には`sqlalchemy.ForeignKey`を使用する
`ON UPDATE`や`ON DELETE`にも対応できる
参照される側にも`sqlalchemy.orm.relationship`で指定する
`backref`を指定すれば両方向からの参照となる
参考： http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html

```python
# 参照する側
# ForeignKeyにテーブル名.カラム名を指定する
personid = Column('personid', Integer(unsigned=True), \
                  ForeignKey('person.id',onupdate='CASCADE', ondelete='CASCADE'))
# 参照される側
# カラム定義と同じインデントでrelationship(テーブルクラス名(テーブル名ではない))
address = relationship("Address")
# address = relationship("Address", backref="person")
```

### NULL

カラム定義に`nullable=[True or False]`を書くだけ

### MySQLでunsined int

`sqlalchemy.dialects.mysql.INTEGER`を用いて`INTEGER(unsigned=True)`とすれば良い
`mysql`以外は知らないが、`sqlalchemy.dialects`に`oracle`や`sqlite`など、他のRDMSもあるので対応していると思われる

## テーブル定義テンプレ？

コピペして多少弄るだけで使えそう
mysqlに対応
なお、`create database [db_name] default charset utf8;`はmysql側で実行しておく必要がある。

```python
# -*- encoding:utf-8 -*-

from sqlalchemy import (Column, String, Text, ForeignKey, \
                create_engine, MetaData, DECIMAL, DATETIME, exc, event, Index)
from sqlalchemy.schema import UniqueConstraint
from sqlalchemy.orm import (sessionmaker, relationship, scoped_session)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import INTEGER as Integer
from datetime import datetime

engine = create_engine('mysql://{user}:{passwd}@{host}/{db}'\
        .format(user=user, passwd=passwd, host=host, db=db_name),\
        encoding='utf-8', echo=False)

Session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))

metadata = MetaData(engine)
Base = declarative_base()


class Person(Base):
    __tablename__ = 'person'
    __table_args__ = {'mysql_engine': 'InnoDB'}
    id = Column('id', Integer(unsigned=True), primary_key=True, autoincrement=True)
    name = Column('name', String(255), index=True, unique=True)
    age = Column('age', Integer)
    created = Column('created', DATETIME, default=datetime.now, nullable=False)
    modified = Column('modified', DATETIME, default=datetime.now, nullable=False)
    address = relationship('Address')

    def __init__(self, name, age):
        self.name = name
        self.age = age
        now = datetime.now()
        self.created = now
        self.modified = now


class Address(Base):
    __tablename__ = 'address'
    __table_args__ = (
            (UniqueConstraint('personid', 'address', name='unique_idx_personid_address')),
            {'mysql_engine': 'InnoDB'})
    id = Column('id', Integer, primary_key=True, autoincrement=True)
    personid = Column('personid', Integer(unsigned=True), ForeignKey('person.id',
        onupdate='CASCADE', ondelete='CASCADE'))
    address = Column('address', String(255), nullable=False)
    created = Column('created', DATETIME, default=datetime.now, nullable=False)
    modified = Column('modified', DATETIME, default=datetime.now, nullable=False)

    def __init__(self, personid, address):
        self.personid = personid
        self.address = address
        now = datetime.now()
        self.created = now
        self.modified = now

if __name__ == "__main__":
    # create table
    Base.metadata.create_all(engine)
```

これを実行すると以下の様なテーブルが作成される。

>
mysql> desc person;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255)     | YES  | UNI | NULL    |                |
| age      | int(11)          | YES  |     | NULL    |                |
| created  | datetime         | NO   |     | NULL    |                |
| modified | datetime         | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
>
mysql> desc address;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11)          | NO   | PRI | NULL    | auto_increment |
| personid | int(10) unsigned | YES  | MUL | NULL    |                |
| address  | varchar(255)     | NO   |     | NULL    |                |
| created  | datetime         | NO   |     | NULL    |                |
| modified | datetime         | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
>
mysql> show index from person \G;
*************************** 1. row ***************************
       Table: person
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: person
  Non_unique: 0
    Key_name: ix_person_name
Seq_in_index: 1
 Column_name: name
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
2 rows in set (0.00 sec)
>
ERROR:
No query specified
>
mysql> show index from address \G;
*************************** 1. row ***************************
       Table: address
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: address
  Non_unique: 0
    Key_name: unique_idx_personid_address
Seq_in_index: 1
 Column_name: personid
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: address
  Non_unique: 0
    Key_name: unique_idx_personid_address
Seq_in_index: 2
 Column_name: address
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
3 rows in set (0.00 sec)
>
ERROR:
No query specified