def fetch_gear_by_id(self, gear_id):
try:
sql = "SELECT * from gear WHERE id = %s;"
self.logger.debug('Executing SQL: {0}'.format(sql))
with self.db_conn.cursor() as cursor:
cursor.execute(sql, [gear_id])
record = cursor.fetchone()
if record:
gear = Gear(name=record[1],
description=record[2],
thumb_url=record[3],
amazon_link=record[4],
image_url=record[5],
manufacturer=record[6],
type_id=record[7],
features=record[8])
return gear
else:
return None
except (db_utils.psycopg2.DatabaseError, Exception) as err:
self.logger.exception(
'An error occurred during fetch by id operation on gear table!'
)
log_utils.close_logger(self.logger)
db_utils.close_db_connection(self.db_conn)
raise err
def delete_gear(self, gear_id):
try:
sql = "DELETE FROM gear WHERE id = %s;"
self.logger.debug('Executing SQL: {0}'.format(sql))
with self.db_conn.cursor() as cursor:
cursor.execute(sql, [gear_id])
self.db_conn.commit()
except (db_utils.psycopg2.DatabaseError, Exception) as err:
self.logger.exception(
'An error occurred during delete operation on gear table!')
log_utils.close_logger(self.logger)
db_utils.close_db_connection(self.db_conn)
raise err
def insert_geartype_retrieved(self, gear_type_retrieved):
try:
ret_id = None
sql = "INSERT INTO gear_type_retrieved " \
"(amazon_node_id) " \
"VALUES (%s) RETURNING amazon_node_id;"
self.logger.debug('Executing SQL: {0}'.format(sql))
with self.db_conn.cursor() as cursor:
cursor.execute(sql, [gear_type_retrieved.amazon_node_id])
ret_id = cursor.fetchone()[0]
self.db_conn.commit()
return ret_id
except (db_utils.psycopg2.DatabaseError, Exception) as db_err:
self.logger.exception(
'An error occurred during insert operation on gear_type_retrieved table!'
)
log_utils.close_logger(self.logger)
db_utils.close_db_connection(self.db_conn)
raise db_err
def fetch_geartype_retrieved_by_id(self, node_id):
try:
sql = "SELECT * FROM gear_type_retrieved WHERE amazon_node_id = %s;"
self.logger.debug('Executing SQL: {0}'.format(sql))
with self.db_conn.cursor() as cursor:
cursor.execute(sql, [node_id])
row = cursor.fetchone()
if row:
# Create GearTypeRetrieved instance from fetched row values
gear_type_retrieved = GearTypeRetrieved(amazon_node_id=row[0])
return gear_type_retrieved
else:
return None
except (db_utils.psycopg2.DatabaseError, Exception) as db_err:
self.logger.exception(
'An error occurred during fetch operation on gear_type_retrieved table!'
)
log_utils.close_logger(self.logger)
db_utils.close_db_connection(self.db_conn)
raise db_err
def fetch_geartype_by_name(self, name):
try:
sql = "SELECT * FROM gear_type WHERE cat_name = %s;"
self.logger.debug('Executing SQL: {0}'.format(sql))
with self.db_conn.cursor() as cursor:
cursor.execute(sql, [name])
row = cursor.fetchone()
if row:
gear_type = GearType(amazon_node_id=row[0],
name=row[1],
parent_node_id=row[2],
is_leaf_node=row[3])
return gear_type
else:
return None
except (db_utils.psycopg2.DatabaseError, Exception) as err:
self.logger.exception(
'An error occurred during fetch by name operation on gear_type table!'
)
log_utils.close_logger(self.logger)
db_utils.close_db_connection(self.db_conn)
raise err
def test_open_connection(self):
# Test open connection to bot database
db_version = None
bot_db_props = self.db_configs['bot_db_props']
bot_db_conn = db_utils.open_db_connection(db_configs=bot_db_props)
with bot_db_conn.cursor() as cursor:
cursor.execute('SELECT version()')
db_version = cursor.fetchone()
self.assertIsNotNone(db_version)
print db_version
# Test open connection to app database
db_version = None
app_db_props = self.db_configs['app_db_props']
app_db_conn = db_utils.open_db_connection(db_configs=app_db_props)
with app_db_conn.cursor() as cursor:
cursor.execute('SELECT version()')
db_version = cursor.fetchone()
self.assertIsNotNone(db_version)
print db_version
# Close database connections
db_utils.close_db_connection(bot_db_conn)
db_utils.close_db_connection(app_db_conn)
def insert_geartype(self, gear_type):
try:
sql = "INSERT INTO gear_type " \
"(amazon_node_id, cat_name, parent_node_id, is_leaf_node) " \
"VALUES (%s, %s, %s, %s) RETURNING amazon_node_id;"
self.logger.debug('Executing SQL: {0}'.format(sql))
with self.db_conn.cursor() as cursor:
cursor.execute(
sql, (gear_type.amazon_node_id, gear_type.name,
gear_type.parent_node_id, gear_type.is_leaf_node))
rec = cursor.fetchone()
if rec:
self.db_conn.commit()
return rec[0]
else:
return None
except (db_utils.psycopg2.DatabaseError, Exception) as err:
self.logger.exception(
'An error occurred during insert operation on gear_type table!'
)
log_utils.close_logger(self.logger)
db_utils.close_db_connection(self.db_conn)
raise err
def save_gear(self, gear):
try:
sql = "INSERT INTO gear (name, description, thumb_url, amazon_link, " \
"image_url, manufacturer, type_id, features) " \
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;"
self.logger.debug('Executing SQL: {0}'.format(sql))
with self.db_conn.cursor() as cursor:
cursor.execute(
sql, (gear.name, gear.description, gear.thumb_url,
gear.amazon_link, gear.image_url, gear.manufacturer,
gear.type_id, gear.features))
rec = cursor.fetchone()
if rec:
self.db_conn.commit()
return rec[0]
else:
return None
except (db_utils.psycopg2.DatabaseError, Exception) as err:
self.logger.exception(
'An error occurred during save operation on gear table!')
log_utils.close_logger(self.logger)
db_utils.close_db_connection(self.db_conn)
raise err
def __del__(self):
db_utils.close_db_connection(self.db_conn)
