InvMan/libdb.py

#!/usr/bin/env python3
"""
libdb

Library for interacting with InvMan DB using SQLAlchemy
"""

from configparser import ConfigParser
from sqlalchemy import create_engine, Column
from sqlalchemy import String, Text, Date
from sqlalchemy import BigInteger, Integer, SmallInteger, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


def load_config():
    """Load PostgreSQL config from postgres.ini"""
    filename = "postgres.ini"
    section = "postgresql"
    parser = ConfigParser()
    parser.read(filename)

    config = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            config[param[0]] = param[1]
    else:
        raise Exception(
            'Section {0} not found in the {1} file'.format(section, filename))
    return config


cfg = load_config()

engine = create_engine(f"postgresql://{cfg['user']}:{cfg['pass']}@{cfg['host']}/{cfg['database']}",
                       echo='debug')
Base = declarative_base()
Session = sessionmaker()
Session.configure(bind=engine)


class Location(Base):
    """location table"""
    __tablename__ = 'location'

    name = Column(String(length=32), primary_key=True)
    description = Column(Text)


class ProductQuantity(Base):
    """product_quantity table"""
    __tablename__ = 'product_quantity'

    product_upc = Column(String(length=32), primary_key=True)
    name = Column(Text)
    quantity = Column(BigInteger)
    location = Column(String(length=32), primary_key=True)


class Product(Base):
    """product table"""
    __tablename__ = 'product'

    upc = Column(String(length=32), primary_key=True)
    brand = Column(String(length=63))
    name = Column(Text)
    description = Column(Text)
    size = Column(Float)
    sizeunit = Column(String(length=32))


class Brand(Base):
    """brand table"""
    __tablename__ = 'brand'

    name = Column(String(length=63), primary_key=True)
    description = Column(Text)


class Unit(Base):
    """unit table"""
    __tablename__ = 'unit'

    name = Column(String(length=32), primary_key=True)
    description = Column(Text)


class Purchase(Base):
    """purchase table"""
    __tablename__ = 'purchase'

    id = Column(Integer, primary_key=True)
    product_upc = Column(String(length=32))
    quantity = Column(SmallInteger)
    date = Column(Date)
    location = Column(String(length=32))


class Use(Base):
    """use table"""
    __tablename__ = 'use'

    id = Column(Integer, primary_key=True)
    product_upc = Column(String(length=32))
    quantity = Column(SmallInteger)
    date = Column(Date)
    location = Column(String(length=32))