sqlalchemyのテーブル定義tips

2014-05-28

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

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

from: https://qiita.com/petitviolet/items/e03c67794c4e335b6706