sqlalchemyのテーブル定義tips #Python

tips

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

バージョンは0.9.4です

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

__table_args__で指定出来る

__table_args__ = {'mysql_engine': 'InnoDB'}

ユニーク制約

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

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

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

__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

# 参照する側
# 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側で実行しておく必要がある。

# -*- 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