def load_db(host, user, psswd, db, table, cols, port=5432):
_db = Pygres({
'SQL_HOST': host,
'SQL_PORT': port,
'SQL_USER': user,
'SQL_PASSWORD': psswd,
'SQL_DB': db
})
df = pd.read_sql("SELECT {} FROM {}".format(cols, table), _db.conn)
_db.close()
return df
def connect_database(type_='pygres'):
db_host = os.getenv("SQL_HOST")
db_port = os.getenv("SQL_PORT")
db_name = os.getenv("SQL_DB")
db_user = os.getenv("SQL_USER")
db_password = os.getenv("SQL_PASSWORD")
if os.getenv('ENV', '').lower() == 'dev' and 'dev' not in db_name:
db_name += '_dev'
if type_.lower() == 'pygres':
return Pygres({
"SQL_HOST": db_host,
"SQL_PORT": db_port,
"SQL_DB": db_name,
"SQL_USER": db_user,
"SQL_PASSWORD": db_password
})
if type_.lower() == 'sqlalchemy':
return create_engine(
'postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}'
.format(db_user=db_user,
db_password=db_password,
db_host=db_host,
db_port=db_port,
db_name=db_name))
else:
return False
def connect_psql():
return Pygres(
dict(SQL_HOST=SQL_HOST,
SQL_DB=SQL_DB,
SQL_USER=SQL_USER,
SQL_PASSWORD=SQL_PASSWORD,
SQL_PORT=SQL_PORT))
def connect_psql_identity():
return Pygres(
dict(
SQL_HOST="identity.byprice.db", #"0.0.0.0", #
SQL_DB="identity",
SQL_USER="******",
SQL_PASSWORD=os.getenv("IDENTITY_PASSWORD", "postgres"),
SQL_PORT="5432"))
def connect_psql_items():
return Pygres(
dict(
SQL_HOST="items.byprice.db", # "0.0.0.0", #
SQL_DB="items",
SQL_USER="******",
SQL_PASSWORD=os.getenv("ITEMS_PASSWORD", "postgres"),
SQL_PORT="5432"))
def connectdb(self, config):
"""Connects to the specific database."""
return Pygres(dict(
SQL_HOST=config['POSTGRESQL_HOST'],
SQL_DB=config['POSTGRESQL_DB'],
SQL_USER=config['POSTGRESQL_USER'],
SQL_PASSWORD=config['POSTGRESQL_PASSWORD'],
SQL_PORT=config['POSTGRESQL_PORT'],
))
def get_id_categories():
print("Getting id categories")
db = Pygres(
{
"SQL_HOST": os.getenv("SQL_HOST"),
"SQL_PORT": os.getenv("SQL_PORT"),
"SQL_DB": os.getenv("SQL_DB"),
"SQL_USER": os.getenv("SQL_USER"),
"SQL_PASSWORD": os.getenv("SQL_PASSWORD")
}
)
df = pd.read_sql("""SELECT id_category, name as category_name FROM category where source='byprice'""", db.conn)
db.close()
categories = {}
for index, row in df.iterrows():
categories[row.category_name] = row.id_category
return categories
def connectdb():
"""Connects to the specific database."""
logger.debug("Trying to connect to")
logger.debug(str([SQL_HOST, config.SQL_DB, SQL_USER, SQL_PORT]))
return Pygres(
dict(
SQL_HOST=SQL_HOST,
SQL_DB=config.SQL_DB,
SQL_USER=SQL_USER,
SQL_PASSWORD=SQL_PASSWORD,
SQL_PORT=SQL_PORT,
))
def create_categories_in_db():
print("Creating categories in db")
db_name = os.getenv("SQL_DB")
if os.getenv('ENV', '').lower() == 'dev' and 'dev' not in os.getenv("SQL_DB", ''):
db_name += '_dev'
db = Pygres(
{
"SQL_HOST": os.getenv("SQL_HOST"),
"SQL_PORT": os.getenv("SQL_PORT"),
"SQL_DB": db_name,
"SQL_USER": os.getenv("SQL_USER"),
"SQL_PASSWORD": os.getenv("SQL_PASSWORD")
}
)
bp_farma = pd.read_sql("select * from category where source='byprice_farma'", db.conn)
bp_all = pd.read_sql("select id_category from category where source='byprice'", db.conn)
bp_source = pd.read_sql("select * from source where key='byprice_farma'", db.conn)
if bp_source.empty or bp_farma.empty or bp_all.empty:
category = db.model('category', 'id_category')
if bp_source.empty:
source = db.model('source', 'key')
source.key = 'byprice_farma'
source.name = 'ByPrice Farma'
source.logo = 'byprice.png'
source.type = 'retailer'
source.retailer = 1
source.hierarchy = 2
source.save()
if bp_farma.empty and len(bp_all) < 20:
for index, row in bp_all.iterrows():
category.id_category = int(row.id_category)
category.source = 'byprice_farma'
category.save()
for name, attrs in categories_json.items():
category.name = name
category.source = 'byprice'
category.key = to_key(name)
category.save()
id_parent = category.last_id
for subcat in attrs.get('subcats'):
name_subcat = list(subcat.keys())[0]
category.name = name_subcat
category.source = 'byprice'
category.key = to_key(name_subcat)
category.id_parent = id_parent
category.save()
db.close()
def dropdb():
''' Drops the testing database '''
if not config.TESTING:
return None
db_drop = Pygres(dict(SQL_DB='postgres',
SQL_USER=SQL_USER,
SQL_PASSWORD=SQL_PASSWORD,
SQL_HOST=SQL_HOST,
SQL_PORT=SQL_PORT),
autocommit=True)
db_drop.query('drop database {}'.format(config.SQL_DB))
logger.info("Database dropped")
db_drop.close()
#-*- coding: utf-8 -*-
from pygres import Pygres
from datetime import datetime
try:
# Create Database
db_init = Pygres(dict(SQL_DB='postgres',
SQL_USER='******',
SQL_PASSWORD='******',
SQL_HOST='127.0.0.1',
SQL_PORT='5432'),
autocommit=True)
db_init.query('create database pygres_test')
db_init.close()
except:
pass
# Connect to database
config = dict(SQL_DB='pygres_test',
SQL_USER='******',
SQL_PASSWORD='******',
SQL_HOST='127.0.0.1',
SQL_PORT='5432')
db = Pygres(config)
try:
db.query("create extension pgcrypto")
except:
pass
# Clean DB
db.rollback()
def initdb():
''' Initialize the db '''
# Define schema
_schema = 'schema.sql' if not TESTING else 'schema.sql'
try:
db_init = Pygres(dict(SQL_DB='postgres',
SQL_USER=SQL_USER,
SQL_PASSWORD=SQL_PASSWORD,
SQL_HOST=SQL_HOST,
SQL_PORT=SQL_PORT),
autocommit=True)
try:
db_init.query('create database {}'.format(config.SQL_DB))
logger.info("Created DB!")
except:
raise Exception("Db was already initialized!")
db_init.close()
del db_init
# insert the tables
db_init = Pygres(
dict(
SQL_HOST=SQL_HOST,
SQL_DB=config.SQL_DB,
SQL_USER=SQL_USER,
SQL_PASSWORD=SQL_PASSWORD,
SQL_PORT=SQL_PORT,
))
with open(BASE_DIR + '/' + _schema, 'r') as f:
try:
db_init.query(f.read())
logger.info("Schema generated in DB!")
except Exception as e:
raise Exception(str(e))
db_init.close()
except Exception as e:
logger.info(e)
ingredient[ingredient.retailer == 'byprice'],
on='id_ingredient',
how='left')
print('Filled ingredients:', len(complete_ingred))
# Verify Clss and Attr in BYprice
_clss = load_db(SQL_HOST, SQL_USER, SQL_PASSWORD, CATALOGUE_DB, 'clss',
'*')
_attr = load_db(SQL_HOST, SQL_USER, SQL_PASSWORD, CATALOGUE_DB, 'attr',
'*')
# If no Ingredient clss existant
if 'ingredient' not in _clss[_clss.source == 'byprice']['key'].tolist():
_db = Pygres({
'SQL_HOST': SQL_HOST,
'SQL_PORT': SQL_PORT,
'SQL_USER': SQL_USER,
'SQL_PASSWORD': SQL_PASSWORD,
'SQL_DB': SQL_DB
})
_ingclss = _db.model('clss', 'id_clss')
_ingclss.name = 'Ingredient'
_ingclss.name_es = 'Ingrediente'
_ingclss.description = 'Ingrediente'
_ingclss.key = 'ingredient'
_ingclss.source = 'byprice'
_ingclss.save()
bp_ing_clss = _ingclss.last_id
print('Created Ingredient ByPrice Class')
_db.close()
else:
bp_ing_clss = _clss[(_clss.key == 'ingredient')