mcve.py

"""SQLAlchemy MCVE."""
from sqlalchemy import create_engine, Column, Integer, String, Table, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine(
    'mysql+pymysql://*******:*******@localhost/sqlalchemy_mcve')
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()


sites_categories_assoc = Table(
    'sites_categories_assoc', Base.metadata,
    Column('site_id', ForeignKey('sites.id'), primary_key=True),
    Column('category_id', ForeignKey('categories.id'), primary_key=True))


categories_products_assoc = Table(
    'categories_products_assoc', Base.metadata,
    Column('category_id', ForeignKey('categories.id'), primary_key=True),
    Column('product_id', ForeignKey('products.id'), primary_key=True))


sites_chosen_products = Table(
    'sites_chosen_products', Base.metadata,
    Column('site_id', ForeignKey('sites.id'), primary_key=True),
    Column('product_id', ForeignKey('products.id'), primary_key=True))


class Site(Base):
    """Sample Table."""

    __tablename__ = 'sites'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    categories = relationship('Category',
                              secondary=sites_categories_assoc,
                              backref='sites')
    chosen_products = relationship('Product',
                                   secondary=sites_chosen_products,
                                   backref='sites')

    def __repr__(self):
        return f'<{self.__class__.__name__}: {self.id}>'


class Category(Base):
    """Sample Table."""

    __tablename__ = 'categories'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    products = relationship('Product',
                            secondary=categories_products_assoc,
                            backref='categories')

    def __repr__(self):
        return f'<{self.__class__.__name__}: {self.id}>'


class Product(Base):
    """Sample Table."""

    __tablename__ = 'products'

    id = Column(Integer, primary_key=True)
    title = Column(String(32), nullable=False)

    def __repr__(self):
        return f'<{self.__class__.__name__}: {self.id}>'