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)