diet.py

from sqlalchemy import create_engine, Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship


Persisted = declarative_base()  # pylint: disable=invalid-name


class User(Persisted):
    __tablename__ = 'users'
    user_id = Column(Integer, primary_key=True)
    name = Column(String(256), nullable=False)
    meals = relationship('Meal', uselist=True, back_populates='user')


class Meal(Persisted):
    __tablename__ = 'meals'
    meal_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.user_id', ondelete='CASCADE'), nullable=False)
    timestamp = Column(DateTime, nullable=False)
    user = relationship('User', back_populates='meals')
    meal_food_types = relationship('MealFoodType', uselist=True, back_populates='meal')
    food_types = relationship('FoodType', uselist=True, secondary='meal_food_types', viewonly=True)


class FoodType(Persisted):
    __tablename__ = 'food_types'
    food_type_id = Column(Integer, primary_key=True)
    name = Column(String(256), nullable=False)
    meal_food_types = relationship('MealFoodType', uselist=True, back_populates='food_type')
    meals = relationship('Meal', uselist=True, secondary='meal_food_types', viewonly=True)


class MealFoodType(Persisted):
    __tablename__ = 'meal_food_types'
    meal_id = Column(Integer, ForeignKey('meals.meal_id', ondelete='CASCADE'), primary_key=True)
    food_type_id = Column(Integer, ForeignKey('food_types.food_type_id', ondelete='CASCADE'), primary_key=True)
    amount = Column(Integer, nullable=False)
    meal = relationship('Meal', back_populates='meal_food_types')
    food_type = relationship('FoodType', back_populates='meal_food_types')


class DietDatabase(object):
    @staticmethod
    def construct_mysql_url(authority, port, database, username, password):
        return f'mysql+mysqlconnector://{username}:{password}@{authority}:{port}/{database}'

    @staticmethod
    def construct_in_memory_url():
        return 'sqlite:///'

    def __init__(self, url):
        self.engine = create_engine(url)
        self.Session = sessionmaker()  # pylint: disable=invalid-name
        self.Session.configure(bind=self.engine)

    def ensure_tables_exist(self):
        Persisted.metadata.create_all(self.engine)

    def create_session(self):
        return self.Session()