InvMan/libdb.py
2020-10-05 19:22:24 -05:00

109 lines
2.6 KiB
Python

#!/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))