SQLAlchemy Core Sample with relationship and polymorphic

sample.py

# -*- coding: utf-8 -*-
from sqlalchemy import create_engine, func
from sqlalchemy import Table, Column, Integer, String, DateTime, MetaData, ForeignKey
from sqlalchemy.pool import NullPool
from sqlalchemy.orm import mapper, sessionmaker, relationship


params = {'user': 'admin',
          'password': 'password',
          'host': 'localhost',
          'db_name': 'test'}
engine = create_engine(
    'mysql://{user}:{password}@{host}/{db_name}?charset=utf8'.format(**params),
    poolclass=NullPool)
Session = sessionmaker(bind=engine)
session = Session()


metadata = MetaData()

"""
create table food_types (
  id         int unsigned not null auto_increment primary key,
  name       varchar(255) not null default '',
  created_at datetime default null,
  updated_at datetime default null
) engine=innodb;
insert into food_types (id, name, created_at, updated_at) values
(1, 'tsumami', now(), now()),
(2, 'oyatsu', now(), now())
;

create table foods (
  id         int unsigned not null auto_increment primary key,
  type_id    int unsigned not null,
  name       varchar(255) not null default '',
  created_at datetime default null,
  updated_at datetime default null
) engine=innodb;
insert into foods (id, type_id, name, created_at, updated_at) values
(1, 1, 'toriwasa', now(), now()),
(2, 1, 'hinepon', now(), now()),
(3, 2, 'icecreem', now(), now())
;

create table alcholes (
  id         int unsigned not null auto_increment primary key,
  food_id    int unsigned not null,
  name       varchar(255) not null default '',
  created_at datetime default null,
  updated_at datetime default null
) engine=innodb;
insert into alcholes (id, food_id, name, created_at, updated_at) values
(1, 1, 'sake', now(), now()),
(2, 1, 'white wine', now(), now())
;

create table non_alcholes (
  id         int unsigned not null auto_increment primary key,
  food_id    int unsigned not null,
  name       varchar(255) not null default '',
  created_at datetime default null,
  updated_at datetime default null
) engine=innodb;
insert into non_alcholes (id, food_id, name, created_at, updated_at) values
(1, 3, 'coffee', now(), now())
;
"""

food_types = Table(
    'food_types', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('created_at', DateTime, default=func.now()),
    Column('updated_at', DateTime, onupdate=func.now()),
)

foods = Table(
    'foods', metadata,
    Column('id', Integer, primary_key=True),
    Column('type_id', None, ForeignKey('food_types.id')),
    Column('name', String),
    Column('created_at', DateTime, default=func.now()),
    Column('updated_at', DateTime, onupdate=func.now()),
)


class FoodType(object):
    def __repr__(self):
        return '{}{}'.format((self.id, self.name), super().__repr__())


class Food(object):
    def __new__(cls, type_id=None, *args, **kwargs):
        """
        Foodを直接instanceする時用
        """
        if type_id == 1:
            return super().__new__(Tsumami)
        if type_id == 2:
            return super().__new__(Oyatsu)
        return super().__new__(cls, *args, **kwargs)

    def __init__(self, **kwargs):
        """
        Foodを直接instanceする時用
        """
        for (k, v) in kwargs.items():
            setattr(self, k, v)

    def __repr__(self):
        return '{}{}'.format((self.id, self.name), super().__repr__())


class Tsumami(Food):
    pass


class Oyatsu(Food):
    pass


mapper(FoodType, food_types)
food_mapper = mapper(Food, foods,
    polymorphic_on=foods.c.type_id,
    properties={
        'type': relationship(FoodType),
    })
mapper(Tsumami, foods, inherits=food_mapper,
    polymorphic_identity=1,
    )
mapper(Oyatsu, foods, inherits=food_mapper,
    polymorphic_identity=2,
    )

for food in session.query(Food):
    print(food)
    print(food.type)

print(Food(type_id=1, name='yakitori'))