diner.py

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


# In Python, unlike many languages, it is possible to create new classes at
# runtime.  Here we call declarative_base, which creates and returns a base
# class that we can use for objects that we want SQLAlchemy to persist in a
# database.  We then store that class in variable named Persisted; effectively,
# we make a new class called Persisted.
Persisted = declarative_base()  # pylint: disable=invalid-name


class Menu(Persisted):
    __tablename__ = 'menus'
    menu_id = Column(Integer, primary_key=True)  # primary keys default to auto_increment
    name = Column(String(256), nullable=False)  # nullable is the default


class Item(Persisted):
    __tablename__ = 'items'
    item_id = Column(Integer, primary_key=True)
    # a foreign-key constraint restricts the allowable integers; cascade ties an item's lifetime to its menu
    menu_id = Column(Integer, ForeignKey('menus.menu_id', ondelete='CASCADE'), nullable=False)
    name = Column(String(256), nullable=False)
    price = Column(Integer, nullable=False)


class Order(Persisted):
    __tablename__ = 'orders'
    order_id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime)


class OrderItem(Persisted):
    __tablename__ = 'order_items'
    order_id = Column(Integer, ForeignKey('orders.order_id', ondelete='CASCADE'), primary_key=True)
    item_id = Column(Integer, ForeignKey('items.item_id', ondelete='CASCADE'), primary_key=True)
    amount = Column(Integer)


class DinerDatabase(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)  # an engine is like an endpoint, something to connect to
        self.Session = sessionmaker()  # create a class for connections to that endpoint / pylint: disable=invalid-name
        self.Session.configure(bind=self.engine)  # associate the class with the endpoint

    def ensure_tables_exist(self):
        Persisted.metadata.create_all(self.engine)  # create tables for all subclasses of Persisted

    def create_session(self):
        return self.Session()  # create a new session, which is like a connection to the database