Edited at

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