tips
--Specify database engine --Unique constraints on multiple columns --Foreign key constraints --Do not allow nulls
The version is 0.9.4
Can be specified with __table_args__
__table_args__ = {'mysql_engine': 'InnoDB'}
Easy for a single column, but to put a unique constraint on multiple columns
Need to use sqlalchemy.schema.UniqueConstraint
#Single column
name = Column("name", String(255), unique=True)
#Multiple columns
__table_args__ = (UniqueConstraint("personid", "address", name="unique_idx_personid_address"))
#name does not have to be specified
In addition, it seems that it is necessary to enclose ʻUnique Constraint in
() `to combine with the above InnoDB specification.
__table_args__ = (
(UniqueConstraint('personid', 'address', name='unique_idx_personid_address')),
{'mysql_engine': 'InnoDB'})
Use sqlalchemy.ForeignKey
for foreign key constraints
Supports ʻON UPDATE and ʻON DELETE
Specify with sqlalchemy.orm.relationship
on the referenced side as well
If backref
is specified, it will be a reference from both directions.
Reference: http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html
#Referrer
#Table name in Foreign Key.Specify the column name
personid = Column('personid', Integer(unsigned=True), \
ForeignKey('person.id',onupdate='CASCADE', ondelete='CASCADE'))
#Referenced side
#Relationship with the same indentation as the column definition(Table class name(Not a table name))
address = relationship("Address")
# address = relationship("Address", backref="person")
NULL
Just write nullable = [True or False]
in the column definition
Use sqlalchemy.dialects.mysql.INTEGER
and set ʻINTEGER (unsigned = True) I don't know anything other than
mysql, but it seems that it is supported because there are other RDMS such as ʻoracle
and sqlite
in sqlalchemy.dialects
.
It seems that you can use it just by copying and playing with it a little
Supports mysql
In addition, create database [db_name] default charset utf8;
needs to be executed on the mysql side.
# -*- 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)
When this is executed, the following table will be created.
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