[PYTHON] sqlalchemy table definition tips

tips

--Specify database engine --Unique constraints on multiple columns --Foreign key constraints --Do not allow nulls

unsigned int(MySQL) --Add at any time?

The version is 0.9.4

Database engine specification

Can be specified with __table_args__

__table_args__ = {'mysql_engine': 'InnoDB'}

Unique constraint

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'})

Foreign key constraint

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

Unsigned int in MySQL

Use sqlalchemy.dialects.mysql.INTEGER and set ʻINTEGER (unsigned = True) I don't know anything other thanmysql, but it seems that it is supported because there are other RDMS such as ʻoracle and sqlite in sqlalchemy.dialects.

Table definition template?

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> 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