def delete(self, kwargs=None): """ Delete method for Producer company table :return: """ conn, cur = get_conn_cur() producer_delete_template = "DELETE FROM \"ProducerCompany\" WHERE id=%s;" id = None if kwargs: if 'id' not in kwargs.keys(): if self.fields['id'] is not None: id = self.fields['id'] else: id = int(kwargs['id']) if id: try: cur.execute(producer_delete_template, (id, )) conn.commit() except psycopg2.DatabaseError as e: conn.rollback() raise e finally: for name in self.fields.keys(): self.fields[name] = None close(conn, cur)
def update(self, kwargs): """ UPDATE method for JewelryShop table :param kwargs: :return: values of model fields """ conn, cur = get_conn_cur() jewelry_shop_update_template = "UPDATE \"JewelryShop\" SET address=%s, license_copy=%s, schedule_day=%s, schedule_time=%s WHERE id=%s;" try: for name, value in kwargs.items(): if name in self.FIELDS.keys(): kwargs[name] = self.FIELDS[name](value) except ValueError as e: raise e try: cur.execute(jewelry_shop_update_template, (kwargs["address"], kwargs["license_copy"], kwargs["schedule_day"], kwargs["schedule_time"], kwargs["id"])) conn.commit() except psycopg2.DatabaseError as e: conn.rollback() raise e finally: for name in self.fields.keys(): self.fields[name] = kwargs[name] close(conn, cur)
def update(self, kwargs): """ UPDATE method for ItemComposition table :param kwargs: :return: values of model fields """ conn, cur = get_conn_cur() item_composition_update_template = "UPDATE \"ItemComposition\" SET material=%s, weight=%s WHERE id=%s;" try: for name, value in kwargs.items(): if name in self.FIELDS.keys(): kwargs[name] = self.FIELDS[name](value) except ValueError as e: raise e try: cur.execute(item_composition_update_template, (kwargs["material"], kwargs["weight"], kwargs["id"])) conn.commit() except psycopg2.DatabaseError as e: conn.rollback() raise e finally: for name in self.fields.keys(): self.fields[name] = kwargs[name] close(conn, cur)
def get_jewelry_items(self, kwargs=None): conn, cur = get_conn_cur() jewelry_item_getitems_template = "SELECT {} FROM \"JewelryItem\"" producers = [] cols = None order_by = None order_template = " ORDER BY {}" if kwargs: if 'cols' in kwargs.keys(): cols = kwargs['cols'] else: cols = '*' if kwargs['order']: order_by = kwargs['order'] jewelry_item_getitems_template += order_template.format( order_by) if not cols: cols = '*' if not order_by: order_by = 'id' jewelry_item_getitems_template = jewelry_item_getitems_template.format( cols) try: cur.execute(jewelry_item_getitems_template, (order_by, )) producers_db = cur.fetchall() prod = ProducerCompany() item_comp = ItemComposition() for row in producers_db: new_dict = dict(row) prod_dict = dict( prod.get_producer( kwargs={'id': new_dict['company_producer']})) it_comp_dict = dict( item_comp.get_item_composition( kwargs={'id': new_dict['item_composition']})) new_dict['company_producer'] = prod_dict new_dict['item_composition'] = it_comp_dict producers.append(new_dict) except psycopg2.DatabaseError as e: # conn.rollback() raise e finally: close(conn, cur) return producers
def insert(self, kwargs): """ INSERT method for Producer company table :param kwargs: :return: values of model fields """ conn, cur = get_conn_cur() producer_insert_template = "INSERT INTO \"ProducerCompany\" (company_name, region) VALUES (%s, %s) RETURNING id;" id = None regions_id = None try: for name, value in kwargs.items(): if name in self.FIELDS.keys(): if name is not 'region': kwargs[name] = self.FIELDS[name](value) except ValueError as e: raise e if kwargs: if 'region' in kwargs.keys(): if isinstance(kwargs['region'], int): regions_id = kwargs['region'] try: r = Region() if not regions_id: r.save(kwargs=kwargs['region']) regions_id = r.fields['id'] kwargs['region'].update({'id': regions_id}) elif not r.get_region(kwargs={'id': regions_id}).get('id'): raise IndexError('No elements with id={}'.format(regions_id)) cur.execute(producer_insert_template, ( kwargs["company_name"], regions_id, )) id = cur.fetchone() conn.commit() # self.fields.up pass except psycopg2.DatabaseError as e: conn.rollback() raise e finally: for name in self.fields.keys(): if name is not 'id': self.fields[name] = kwargs[name] else: self.fields[name] = id['id'] close(conn, cur)
def get_producers(self, kwargs=None): conn, cur = get_conn_cur() producer_getitems_template = "SELECT {} FROM \"ProducerCompany\"" producers = [] cols = None order_by = None order_template = " ORDER BY {}" if kwargs: if 'cols' in kwargs.keys(): cols = kwargs['cols'] else: cols = '*' if kwargs['order']: order_by = kwargs['order'] producer_getitems_template += order_template.format(order_by) if not cols: cols = '*' if not order_by: order_by = 'id' producer_getitems_template = producer_getitems_template.format(cols) try: cur.execute(producer_getitems_template, (order_by, )) producers_db = cur.fetchall() r = Region() for row in producers_db: new_dict = dict(row) reg_dict = dict( r.get_region(kwargs={'id': new_dict['region']})) new_dict['region'] = reg_dict producers.append(new_dict) except psycopg2.DatabaseError as e: # conn.rollback() raise e finally: close(conn, cur) return producers
def get_jewelry_item(self, kwargs): conn, cur = get_conn_cur() cols = ','.join(self.FIELDS.keys()) jewelry_item_getitem_template = "SELECT {} FROM \"JewelryItem\" WHERE id=%s;".format( cols) id = None jewelry_item = None if kwargs: if 'id' not in kwargs.keys(): if self.fields['id'] is not None: id = self.fields['id'] else: id = int(kwargs['id']) if id: try: cur.execute(jewelry_item_getitem_template, (id, )) jewelry_item = cur.fetchone() prod = ProducerCompany() item_comp = ItemComposition() producer = prod.get_producer( kwargs={'id': jewelry_item['company_producer']}) item_composition = item_comp.get_item_composition( kwargs={'id': jewelry_item['item_composition']}) jewelry_item['company_producer'] = producer jewelry_item['item_composition'] = item_composition except psycopg2.DatabaseError as e: # conn.rollback() raise e finally: for name in self.fields.keys(): self.fields[name] = None close(conn, cur) if jewelry_item: for name in self.FIELDS.keys(): self.fields[name] = jewelry_item[name] else: for name in self.FIELDS.keys(): self.fields[name] = None return self.fields
def update(self, kwargs): """ UPDATE method for Producer company table :param kwargs: :return: values of model fields """ conn, cur = get_conn_cur() region_update_template = "UPDATE \"ProducerCompany\" SET company_name=%s, region=%s WHERE id=%s;" regions_id = None try: for name, value in kwargs.items(): if name in self.FIELDS.keys(): kwargs[name] = self.FIELDS[name](value) except ValueError as e: raise e if kwargs: if 'region' in kwargs.keys(): if isinstance(kwargs['region'], int): regions_id = kwargs['region'] try: r = Region() if not regions_id: r.save(kwargs=kwargs['region']) regions_id = r.fields['id'] elif not r.get_region(kwargs={'id': regions_id}).get('id'): raise IndexError('No elements with id={}'.format(regions_id)) cur.execute(region_update_template, (kwargs["company_name"], regions_id, kwargs['id'])) conn.commit() except psycopg2.DatabaseError as e: conn.rollback() raise e finally: for name in self.fields.keys(): self.fields[name] = kwargs[name] close(conn, cur)
def get_jewelry_shops(self, kwargs=None): conn, cur = get_conn_cur() jewelry_shops_getitems_template = "SELECT {} FROM \"ItemComposition\"" jewelry_shops = [] cols = None order_by = None order_template = " ORDER BY {}" if kwargs: if 'cols' in kwargs.keys(): cols = kwargs['cols'] else: cols = '*' if kwargs['order']: order_by = kwargs['order'] jewelry_shops_getitems_template += order_template.format( order_by) if not cols: cols = '*' if not order_by: order_by = 'id' jewelry_shops_getitems_template = jewelry_shops_getitems_template.format( cols) try: cur.execute(jewelry_shops_getitems_template, (order_by, )) jewelry_shops = cur.fetchall() except psycopg2.DatabaseError as e: # conn.rollback() raise e finally: close(conn, cur) return jewelry_shops
def get_producer(self, kwargs): conn, cur = get_conn_cur() cols = ','.join(self.FIELDS.keys()) producer_getitem_template = "SELECT {} FROM \"ProducerCompany\" WHERE id=%s;".format( cols) id = None producer_item = None if kwargs: if 'id' not in kwargs.keys(): if self.fields['id'] is not None: id = self.fields['id'] else: id = int(kwargs['id']) if id: try: cur.execute(producer_getitem_template, (id, )) producer_item = cur.fetchone() r = Region() region = r.get_region(kwargs={'id': producer_item['region']}) producer_item['region'] = region except psycopg2.DatabaseError as e: # conn.rollback() raise e finally: for name in self.fields.keys(): self.fields[name] = None close(conn, cur) if producer_item: for name in self.FIELDS.keys(): self.fields[name] = producer_item[name] else: for name in self.FIELDS.keys(): self.fields[name] = None return self.fields
def insert(self, kwargs): """ INSERT method for Region table :param kwargs: :return: values of model fields """ conn, cur = get_conn_cur() region_insert_template = "INSERT INTO \"Region\" (region_number, region_name) VALUES (%s, %s) RETURNING id;" id = None try: for name, value in kwargs.items(): if name in self.FIELDS.keys(): kwargs[name] = self.FIELDS[name](value) except ValueError as e: raise e try: cur.execute(region_insert_template, (kwargs["region_number"], kwargs["region_name"])) id = cur.fetchone() conn.commit() # self.fields.up pass except psycopg2.DatabaseError as e: conn.rollback() raise e finally: for name in self.fields.keys(): if name is not 'id': self.fields[name] = kwargs[name] else: self.fields[name] = id['id'] close(conn, cur)
def get_jewelry_shop(self, kwargs): conn, cur = get_conn_cur() cols = ','.join(self.FIELDS.keys()) jewelry_shop_getitem_template = "SELECT {} FROM \"JewelryShop\" WHERE id=%s;".format( cols) id = None jewelry_shop = None if kwargs: if 'id' not in kwargs.keys(): if self.fields['id'] is not None: id = self.fields['id'] else: id = int(kwargs['id']) if id: try: cur.execute(jewelry_shop_getitem_template, (id, )) jewelry_shop = cur.fetchone() except psycopg2.DatabaseError as e: # conn.rollback() raise e finally: for name in self.fields.keys(): self.fields[name] = None close(conn, cur) if jewelry_shop or len(jewelry_shop) != 0: for name in self.FIELDS.keys(): self.fields[name] = jewelry_shop[name] else: for name in self.FIELDS.keys(): self.fields[name] = None return self.fields
def insert(self, kwargs): """ INSERT method for JewelryShop table :param kwargs: :return: values of model fields """ conn, cur = get_conn_cur() jewelry_shop_insert_template = "INSERT INTO \"JewelryShop\" (address, license_copy, schedule_day, schedule_time) VALUES (%s, %s, %s, %s) RETURNING id;" id = None try: for name, value in kwargs.items(): if name in self.FIELDS.keys(): kwargs[name] = self.FIELDS[name](value) except ValueError as e: raise e try: cur.execute(jewelry_shop_insert_template, (kwargs["address"], kwargs["license_copy"], kwargs["schedule_day"], kwargs["schedule_time"])) id = cur.fetchone() conn.commit() # self.fields.up pass except psycopg2.DatabaseError as e: conn.rollback() raise e finally: for name in self.fields.keys(): if name is not 'id': self.fields[name] = kwargs[name] else: self.fields[name] = id['id'] close(conn, cur)
def get_item_composition(self, kwargs): conn, cur = get_conn_cur() cols = ','.join(self.FIELDS.keys()) item_composition_getitem_template = "SELECT {} FROM \"ItemComposition\" WHERE id=%s;".format( cols) id = None item_composition = None if kwargs: if 'id' not in kwargs.keys(): if self.fields['id'] is not None: id = self.fields['id'] else: id = int(kwargs['id']) if id: try: cur.execute(item_composition_getitem_template, (id, )) item_composition = cur.fetchone() except psycopg2.DatabaseError as e: # conn.rollback() raise e finally: for name in self.fields.keys(): self.fields[name] = None close(conn, cur) if item_composition or len(item_composition) != 0: for name in self.FIELDS.keys(): self.fields[name] = item_composition[name] else: for name in self.FIELDS.keys(): self.fields[name] = None return self.fields
def update(self, kwargs): """ UPDATE method for Jewelry Item table :param kwargs: :return: values of model fields """ conn, cur = get_conn_cur() jewelry_item_update_template = "UPDATE \"JewelryItem\" SET item_type=%s, item_weight=%s, item_probe=%s, \ company_producer=%s, item_arrive_date=%s, item_cost=%s, item_composition=%s WHERE id=%s;" company_producer_id = None item_composition_id = None try: for name, value in kwargs.items(): if name in self.FIELDS.keys(): kwargs[name] = self.FIELDS[name](value) except ValueError as e: raise e if kwargs: if 'company_producer' in kwargs.keys(): if isinstance(kwargs['company_producer'], int): company_producer_id = kwargs['company_producer'] if 'item_composition' in kwargs.keys(): if isinstance(kwargs['item_composition'], int): item_composition_id = kwargs['item_composition'] try: prod = ProducerCompany() item_comp = ItemComposition() if not company_producer_id: prod.save(kwargs=kwargs['company_producer']) company_producer_id = prod.fields['id'] elif not prod.get_producer(kwargs={ 'id': company_producer_id }).get('id'): raise IndexError( 'No elements with id={}'.format(company_producer_id)) if not item_composition_id: item_comp.save(kwargs=kwargs['item_composition']) item_composition_id = item_comp.fields['id'] elif not item_comp.get_item_composition(kwargs={ 'id': item_composition_id }).get('id'): raise IndexError( 'No elements with id={}'.format(item_composition_id)) cur.execute(jewelry_item_update_template, (kwargs['item_type'], kwargs['item_weight'], kwargs['item_probe'], company_producer_id, kwargs['item_arrive_date'], kwargs['item_cost'], item_composition_id, kwargs['id'])) conn.commit() except psycopg2.DatabaseError as e: conn.rollback() raise e finally: for name in self.fields.keys(): self.fields[name] = kwargs[name] close(conn, cur)
def insert(self, kwargs): """ INSERT method for Jewelry item table :param kwargs: :return: values of model fields """ conn, cur = get_conn_cur() jewelry_item_insert_template = "INSERT INTO \"JewelryItem\" (item_type, item_weight, item_probe," \ " company_producer, item_arrive_date, item_cost, item_composition)" \ " VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING id;" id = None company_producer_id = None item_composition_id = None try: for name, value in kwargs.items(): if name in self.FIELDS.keys(): if name not in ['company_producer', 'item_composition']: kwargs[name] = self.FIELDS[name](value) except ValueError as e: raise e if kwargs: if 'company_producer' in kwargs.keys(): if isinstance(kwargs['company_producer'], int): company_producer_id = kwargs['company_producer'] if 'item_composition' in kwargs.keys(): if isinstance(kwargs['item_composition'], int): item_composition_id = kwargs['item_composition'] try: prod = ProducerCompany() item_comp = ItemComposition() if not company_producer_id: prod.save(kwargs=kwargs['company_producer']) company_producer_id = prod.fields['id'] kwargs['company_producer'].update({'id': company_producer_id}) elif not prod.get_producer(kwargs={ 'id': company_producer_id }).get('id'): raise IndexError( 'No elements with id={}'.format(company_producer_id)) if not item_composition_id: item_comp.save(kwargs=kwargs['item_composition']) item_composition_id = item_comp.fields['id'] kwargs['item_composition'].update({'id': item_composition_id}) elif not item_comp.get_item_composition(kwargs={ 'id': item_composition_id }).get('id'): raise IndexError( 'No elements with id={}'.format(item_composition_id)) cur.execute(jewelry_item_insert_template, (kwargs['item_type'], kwargs['item_weight'], kwargs['item_probe'], company_producer_id, kwargs['item_arrive_date'], kwargs['item_cost'], item_composition_id)) id = cur.fetchone() conn.commit() # self.fields.up pass except psycopg2.DatabaseError as e: conn.rollback() raise e finally: for name in self.fields.keys(): if name is not 'id': self.fields[name] = kwargs[name] else: self.fields[name] = id['id'] close(conn, cur)