petitviolet blog

    sqlalchemyのテーブル定義tips

    2014-05-28

    QiitaPythonsqlalchemy

    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 UPDATEON 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.dialectsoraclesqliteなど、他の 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

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