<reponame>saymoniphal/fswd-p4-item-catalog<filename>models.py
#!/bin/bash/env python3

from sqlalchemy import Column, Integer, String, Sequence, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.orm.session import object_session

Base = declarative_base()


class User(Base):
    """Represents 'users' table in the database with attributes:
    user_id: the id number of the user (generated by the DB)
    name: name of the user
    email: email address of the user
    """

    # table name in the database
    __tablename__ = 'users'

    # attributes which corespond to each column in the table
    user_id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50), nullable=False, unique=True)
    email = Column(String(50), nullable=False, unique=True)

    @staticmethod
    def create(session, username, email):
        """Create User object and save to the database. Returns the id number
        of the newly added user
        """
        try:
            return session.query(User).filter_by(email=email).one()
        except NoResultFound:
            pass
        newUser = User(name=username, email=email)
        session.add(newUser)
        return newUser

    @staticmethod
    def getByName(session, username):
        return session.query(User).filter_by(name=username).one()


class Category(Base):
    """Class represents table 'category' in the database which has attributes:
    name: name of the category
    description: description in text of the category
    category_id: the id number of the category
                (incrementally assigned by the database)
    user_id: the id number of the user created the category (foreign key)
    """

    # table name in the database
    __tablename__ = 'categories'

    # attributes which represents each column in the table
    category_id = Column(Integer, Sequence('category_id_seq'),
                         primary_key=True)
    name = Column(String(50), nullable=False, unique=True)
    description = Column(String(255))
    user_id = Column(Integer, ForeignKey('users.user_id'))
    user = relationship(User, backref=backref('categories'))

    @property
    def num_items(self):
        return object_session(self).query(Item).with_parent(self).count()

    @property
    def serialize(self):
        """Return object data in easily serialize format"""
        return {
            'name': self.name,
            'description': self.description,
            'user_id': self.user_id,
            'category_id': self.category_id,
            'items': [item.serialize for item in self.items]
        }

    @staticmethod
    def create(session, name, user, description=None):
        """Adds the category to the database and returns its <id number>"""
        c = Category(name=name, user=user, description=description)
        session.add(c)
        return c

    @staticmethod
    def getById(session, category_id):
        c = session.query(Category).filter_by(category_id=category_id).one()
        return c

    @staticmethod
    def all(session):
        return session.query(Category).all()

    @staticmethod
    def delete(session, category_id):
        c = Category.getById(session, category_id)
        if c is not None:
            return session.delete(c)


class Item(Base):
    """Class represents table 'item' in the database which has attributes:
    name: name of the item, it should not be empty
    description: description in text of the item
    item_id: the id number of the item (incrementally assigned by the database)
    category_id: the id number of the category (refer to categories)
    """

    # table name in the database
    __tablename__ = 'items'

    # attributes which represents each column in the table
    item_id = Column(Integer, Sequence('item_id_seq'), primary_key=True)
    name = Column(String(50), nullable=False)
    description = Column(String(255))
    category_id = Column(Integer, ForeignKey('categories.category_id'))
    category = relationship(Category,
                            backref=backref('items',
                                            cascade="save-update, merge, delete"))

    @property
    def serialize(self):
        """Return object data in easily serialize format"""
        return {
                 'name': self.name,
                 'description': self.description,
                 'item_id': self.item_id,
                 'category_id': self.category_id
               }

    @staticmethod
    def create(session, name, category, description=None):
        """Adds the item entry to the database and returns its <id number>"""
        item = Item(name=name, category=category,
                    description=description)
        session.add(item)
        return item

    @staticmethod
    def getById(session, item_id):
        item = session.query(Item).filter_by(item_id=item_id).one()
        return item

    @staticmethod
    def all(session):
        return session.query(Item).all()

    @staticmethod
    def delete(session, item_id):
        item = Item.getById(session, item_id)
        if item is not None:
            return session.delete(item)


def connect_db(db_url):
    """Connect to the database and create the database schema"""

    # create an engine connecting the database
    engine = create_engine(db_url, echo=False)
    return sessionmaker(bind=engine)()

#!/bin/bash/env python3

from sqlalchemy import Column, Integer, String, Sequence, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.orm.session import object_session

Base = declarative_base()


class User(Base):
    """Represents 'users' table in the database with attributes:
    user_id: the id number of the user (generated by the DB)
    name: name of the user
    email: email address of the user
    """

    # table name in the database
    __tablename__ = 'users'

    # attributes which corespond to each column in the table
    user_id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50), nullable=False, unique=True)
    email = Column(String(50), nullable=False, unique=True)

    @staticmethod
    def create(session, username, email):
        """Create User object and save to the database. Returns the id number
        of the newly added user
        """
        try:
            return session.query(User).filter_by(email=email).one()
        except NoResultFound:
            pass
        newUser = User(name=username, email=email)
        session.add(newUser)
        return newUser

    @staticmethod
    def getByName(session, username):
        return session.query(User).filter_by(name=username).one()


class Category(Base):
    """Class represents table 'category' in the database which has attributes:
    name: name of the category
    description: description in text of the category
    category_id: the id number of the category
                (incrementally assigned by the database)
    user_id: the id number of the user created the category (foreign key)
    """

    # table name in the database
    __tablename__ = 'categories'

    # attributes which represents each column in the table
    category_id = Column(Integer, Sequence('category_id_seq'),
                         primary_key=True)
    name = Column(String(50), nullable=False, unique=True)
    description = Column(String(255))
    user_id = Column(Integer, ForeignKey('users.user_id'))
    user = relationship(User, backref=backref('categories'))

    @property
    def num_items(self):
        return object_session(self).query(Item).with_parent(self).count()

    @property
    def serialize(self):
        """Return object data in easily serialize format"""
        return {
            'name': self.name,
            'description': self.description,
            'user_id': self.user_id,
            'category_id': self.category_id,
            'items': [item.serialize for item in self.items]
        }

    @staticmethod
    def create(session, name, user, description=None):
        """Adds the category to the database and returns its <id number>"""
        c = Category(name=name, user=user, description=description)
        session.add(c)
        return c

    @staticmethod
    def getById(session, category_id):
        c = session.query(Category).filter_by(category_id=category_id).one()
        return c

    @staticmethod
    def all(session):
        return session.query(Category).all()

    @staticmethod
    def delete(session, category_id):
        c = Category.getById(session, category_id)
        if c is not None:
            return session.delete(c)


class Item(Base):
    """Class represents table 'item' in the database which has attributes:
    name: name of the item, it should not be empty
    description: description in text of the item
    item_id: the id number of the item (incrementally assigned by the database)
    category_id: the id number of the category (refer to categories)
    """

    # table name in the database
    __tablename__ = 'items'

    # attributes which represents each column in the table
    item_id = Column(Integer, Sequence('item_id_seq'), primary_key=True)
    name = Column(String(50), nullable=False)
    description = Column(String(255))
    category_id = Column(Integer, ForeignKey('categories.category_id'))
    category = relationship(Category,
                            backref=backref('items',
                                            cascade="save-update, merge, delete"))

    @property
    def serialize(self):
        """Return object data in easily serialize format"""
        return {
                 'name': self.name,
                 'description': self.description,
                 'item_id': self.item_id,
                 'category_id': self.category_id
               }

    @staticmethod
    def create(session, name, category, description=None):
        """Adds the item entry to the database and returns its <id number>"""
        item = Item(name=name, category=category,
                    description=description)
        session.add(item)
        return item

    @staticmethod
    def getById(session, item_id):
        item = session.query(Item).filter_by(item_id=item_id).one()
        return item

    @staticmethod
    def all(session):
        return session.query(Item).all()

    @staticmethod
    def delete(session, item_id):
        item = Item.getById(session, item_id)
        if item is not None:
            return session.delete(item)


def connect_db(db_url):
    """Connect to the database and create the database schema"""

    # create an engine connecting the database
    engine = create_engine(db_url, echo=False)
    return sessionmaker(bind=engine)()
#!/bin/bash/env python3 Represents 'users' table in the database with attributes:     user_id: the id number of the user (generated by the DB)     name: name of the user     email: email address of the user # table name in the database # attributes which corespond to each column in the table Create User object and save to the database. Returns the id number         of the newly added user Class represents table 'category' in the database which has attributes:     name: name of the category     description: description in text of the category     category_id: the id number of the category                 (incrementally assigned by the database)     user_id: the id number of the user created the category (foreign key) # table name in the database # attributes which represents each column in the table Return object data in easily serialize format Adds the category to the database and returns its <id number> Class represents table 'item' in the database which has attributes:     name: name of the item, it should not be empty     description: description in text of the item     item_id: the id number of the item (incrementally assigned by the database)     category_id: the id number of the category (refer to categories) # table name in the database # attributes which represents each column in the table Return object data in easily serialize format Adds the item entry to the database and returns its <id number> Connect to the database and create the database schema # create an engine connecting the database