Help us understand the problem. What is going on with this article?

sqlalchemyのテーブル定義tips

More than 5 years have passed since last update.

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away