database.py

import os
import datetime

import flask_sqlalchemy

import cocktail_site
import cocktail_site.utilities as utils

cocktail_site.app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db/drinks.db'
db = flask_sqlalchemy.SQLAlchemy(cocktail_site.app)

# noinspection PyUnresolvedReferences
def init_db(delete=False, backup_name=None):
    if delete:
        if os.path.exists('db/drinks.db'):
            if backup_name is None:
                backup_name = 'db/backups/drinks_{0}.db'.format(
                    datetime.datetime.today().strftime('%Y%m%d-%H%M%S'))
            os.rename('db/drinks.db', backup_name)
        import cocktail_site.models
        db.create_all()

models.py

from sqlalchemy import Integer, Column, String, ForeignKey, orm
from cocktail_site.database import db
from sqlalchemy.ext.associationproxy import association_proxy


class Drink(db.Model):
    __tablename__ = 'drink'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False, unique=True)
    ingredients = association_proxy('_drink_ingredients', 'ingredient', creator=lambda x: DrinkIngredient(*x))

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return 'Drink({name})'.format_map(self.__dict__)


class Ingredient(db.Model):
    __tablename__ = 'ingredient'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False, unique=True)
    type = Column(String(50))
    drinks = association_proxy('_drink_ingredients', 'drink')

    def __init__(self, name, type):
        self.name = name
        self.type = type

    def __repr__(self):
        return 'Ingredient({name}, {type})'.format_map(self.__dict__)


class DrinkIngredient(db.Model):
    __tablename__ = 'drink_ingredient'
    drink_id = Column(Integer, ForeignKey(Drink.id), primary_key=True)
    ingredient_id = Column(Integer, ForeignKey(Ingredient.id), primary_key=True)
    quantity = Column(String(50))

    drink = orm.relationship(Drink, backref='_drink_ingredients')
    ingredient = orm.relationship(Ingredient, backref='_drink_ingredients')

    def __init__(self, drink, ingredient, quantity):
        self.drink = drink
        self.ingredient = ingredient
        self.quantity = quantity