def __save_content(self, param_id, content): for item in content: source_id = item['source']['id'] source_name = item['source']['name'] author = str(item['author']).replace("'", "''") title = str(item['title']).replace("'", "''") description = str(item['description']).replace("'", "''") url = urllib.parse.quote(item['url'].encode('UTF-8'), safe='') url_to_image = item['urlToImage'] published_at = item['publishedAt'] query = "insert into public.search_content values (default, %s, \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\');" % ( param_id, source_id, source_name, author, title, description, url, url_to_image, published_at) #TODO insert statement above fails silently by default. add error handling. with CursorFromConnectionFromPool() as cursor: cursor.execute(query)
def load_from_db_by_organizer_id(cls, organizer_id): with CursorFromConnectionFromPool() as cursor: cursor.execute('SELECT * FROM events WHERE organizer_id=%s', (organizer_id, )) event_data = cursor.fetchone() #but we really need to fetch more than one in the future! Not just the first one. if event_data: return cls( event_description=event_data[1], event_date=event_data[2], organizer_id=[3], event_footprint=event_data[4], id=event_data[0], min_participants=event_data[5], participants_count=event_data[6], )
def returnthis(cls, isbn): with CursorFromConnectionFromPool() as cursor: cursor.execute( "select copy_no from public.books where isbn= %s and issued_by=1", (isbn, )) strx = cursor.fetchone() strx = str(strx) strx = strx.replace('(', '') strx = strx.replace(')', '') strx = strx.replace(',', '') strx = int(strx) cursor.execute( "update public.books set issued_by=0 where isbn=%s and copy_no=%s", (isbn, strx))
def detail_complete(cls, order_detail_id): with CursorFromConnectionFromPool() as cursor: cursor.execute( 'select * from order_details where order_detail_id = %s', (order_detail_id, )) user_data = cursor.fetchone() # if user_data: order_detail = OrderDetail(order_id=user_data[1], smpl_no=user_data[2], operation=user_data[3], ms_width=user_data[4], ms_length=user_data[5], cut_width=user_data[6], cut_length=user_data[7], processing_wt=float(user_data[8]), numbers=user_data[9], fg_yes_no=user_data[10], no_per_packet=user_data[11], no_of_packets=user_data[12], packing=user_data[13], remarks=user_data[14], status=user_data[15], stage_no=user_data[16], tolerance=user_data[17], lamination=user_data[18], wt_per_pkt=user_data[19], internal_dia=user_data[20]) cursor.execute( "select weight from current_stock where smpl_no = %s and width = %s and length = %s", (order_detail.smpl_no, order_detail.cut_width, order_detail.cut_length)) user_data1 = cursor.fetchone() processed_wt = 0 #numbers = 0 if user_data1: processed_wt = float(user_data1[0]) # numbers = Decimal(user_data1[1]) # check_weight = 0.98 * float(order_detail.processing_wt) if processed_wt > (0.98 * float(order_detail.processing_wt)): order_detail.status = "Completed" order_detail.modify_detail(order_detail_id) OrderDetail.check_stage_complete(order_detail.order_id, order_detail.stage_no)
def save_to_db(self): # connection = psycopg2.connect(user='******', # password='******', # database='learning', # host='localhost') # with connection.cursor() as cursor: # cursor.execute('INSERT INTO users(email, first_name, last_name) VALUES (%s, %s, %s)', # (self.email, self.first_name, self.last_name)) # # connection.commit() # connection.close() # with可以省掉commit和close步骤 with CursorFromConnectionFromPool() as cursor: cursor.execute( 'INSERT INTO users(email, first_name, last_name) VALUES (%s, %s, %s)', (self.email, self.first_name, self.last_name))
def load_from_db(cls, smpl_no): dispatch_detail_lst = [] with CursorFromConnectionFromPool() as cursor: cursor.execute("select * from dispatch_detail where smpl_no = %s", (smpl_no, )) user_data = cursor.fetchall() for detail in user_data: dispatch_detail = DispatchDetail(int(detail[1]), detail[2], float(detail[3]), float(detail[4]), float(detail[5]), int(detail[6]), float(detail[7]), detail[8], detail[9]) dispatch_detail_lst.append(dispatch_detail) return dispatch_detail_lst
def load_dinner_dises(cls, id_day, restaurant_type): with CursorFromConnectionFromPool() as cursor: cursor.execute( 'SELECT dish.id, dish.id_day, dish.category, dish.name, dish.description, dish.picture, ' 'wine.id AS wine_id, wine.name AS wine_name, restaurant.name AS restaurant_name, ' 'meal.name AS meal_name, restaurant.restaurant_type FROM dish ' 'LEFT JOIN wine ON dish.recomended_wine = wine.id ' 'LEFT JOIN meal ON dish.id_meal = meal.id ' 'LEFT JOIN restaurant ON meal.id_restaurant = restaurant.id ' 'WHERE (dish.id_day = 0 OR dish.id_day = %s) ' 'AND meal.name = \'dinner\' AND restaurant.restaurant_type=%s ORDER BY dish.id', (id_day, restaurant_type)) dishes = [] all_data = cursor.fetchall() for data in all_data: dishes.append(vars(cls(*data))) return dishes
def change_wt(cls, smpl_no, width, length, processed_wt, actual_no_of_pieces, sign): with CursorFromConnectionFromPool() as cursor: cursor.execute( "select weight, numbers, unit, cs_id from current_stock where smpl_no = %s and width = %s " "and length = %s ", (smpl_no, width, length)) user_data = cursor.fetchone() if user_data: weight = Decimal(user_data[0]) numbers = Decimal(user_data[1]) cs_id = user_data[3] if sign == "minus": new_weight = weight - Decimal(processed_wt) new_weight = round(new_weight, 3) if numbers > 1: new_numbers = numbers - Decimal(actual_no_of_pieces) else: new_numbers = numbers if sign == "plus": new_weight = weight + Decimal(processed_wt) new_weight = round(new_weight, 3) if numbers > 1: new_numbers = numbers + Decimal(actual_no_of_pieces) else: new_numbers = numbers if new_weight < 0.15: #OrderDetail.complete_processing_on_del(smpl_no, width, length) #CurrentStock.delete_record(cs_id) cursor.execute( "delete from current_stock where smpl_no = %s and width = %s and length = %s", (smpl_no, width, length)) # This is done when the RM is over but for some reason the order could not be completed # This could when the RM is thickness is more or wrong calc of material or processing mistake/change return "complete" else: cursor.execute( "update current_stock set weight = %s, numbers = %s where smpl_no = %s and width = %s" " and length = %s", (new_weight, new_numbers, smpl_no, width, length)) return "continue" else: return "insert"
def get_daily_report(cls, dispatch_id): dispatch_detail_lst = [] with CursorFromConnectionFromPool() as cursor: cursor.execute( "select * from dispatch_detail where dispatch_id = %s", (dispatch_id, )) user_data = cursor.fetchall() for detail in user_data: dispatch_detail = DispatchDetail(int(detail[1]), detail[2], float(detail[3]), float(detail[4]), float(detail[5]), int(detail[6]), float(detail[7]), detail[8], detail[9]) dispatch_detail_lst.append(dispatch_detail) return dispatch_detail_lst
def load_length_from_db(self): # Connecting to database with CursorFromConnectionFromPool() as cursor: cursor.execute("SELECT spatial_length, pk FROM merged_ways \ WHERE connector = 0 AND transport = 'metro'") metro_data = cursor.fetchall( ) # Stores the result of the query in the metro_data variable self.spatial_length = [] # Makes sure the list is empty self.id = [] # Makes sure the list is empty if metro_data: # None is equivalent to false in boolean expressions for i in range(len(metro_data) ): # Iterating through all of the metro data self.spatial_length.append( metro_data[i][0] ) # Removing the tuples that comes along with the DB queries self.id.append( metro_data[i][1] ) # Removing the tuples that comes along with the DB queries return None
def hello_world(): with CursorFromConnectionFromPool() as cursor: cursor.execute( "SELECT name,author FROM public.books WHERE suggest = (SELECT MIN(suggest) FROM public.books) and issued_by=0;") data = cursor.fetchone() strx = str(data) strx = strx.replace('[', '') strx = strx.replace(']', '') strx = strx.replace('(', '') strx = strx.replace(')', '') strx = strx.replace('"', '') strx = strx.replace("'", "") strx = strx.replace(",", '|') x = strx.split("|") name = x[0] author = x[1] return render_template("home.html", name=name, author=author)
def smpl_lst_by_operation(cls, status, operation): with CursorFromConnectionFromPool() as cursor: if operation == 'Mini_Slitting': cursor.execute( "select distinct on (smpl_no, ms_width) * from order_details where status = %s and operation like %s", (status, operation + '%')) if operation == 'Reshearing' or operation == "Narrow_CTL": cursor.execute( "select distinct on (smpl_no, ms_width, ms_length) * from order_details where status = %s and operation like %s", (status, operation + '%')) else: cursor.execute( "select distinct on (smpl_no) * from order_details where status = %s and operation like %s", (status, operation + '%')) user_data = cursor.fetchall() order_detail_lst = [] if user_data: for lst in user_data: order_detail = OrderDetail(order_id=lst[1], smpl_no=lst[2], operation=lst[3], ms_width=lst[4], ms_length=lst[5], cut_width=lst[6], cut_length=lst[7], processing_wt=float(lst[8]), numbers=lst[9], fg_yes_no=lst[10], no_per_packet=lst[11], no_of_packets=lst[12], packing=lst[13], remarks=lst[14], status=lst[15], stage_no=lst[16], tolerance=lst[17], lamination=lst[18], wt_per_pkt=lst[19], internal_dia=lst[20]) order_detail_lst.append(order_detail) return order_detail_lst
def load_length_from_db(self): # Connecting to database, and loading train spatial lengths and the id belonging to it with CursorFromConnectionFromPool() as cursor: cursor.execute("SELECT spatial_length, pk FROM merged_ways \ WHERE connector = 0 AND transport = 'train'") train_data = cursor.fetchall( ) # Stores the result of the query in the train_data variable self.spatial_length = [ ] # Creates a spatial length list and make sure the list is empty self.id = [] # Creates a id list and make sure the list is empty if train_data: # None is equivalent to false in boolean expressions for i in range(len(train_data) ): # Iterating through all of the train data self.spatial_length.append( train_data[i][0] ) # Storing spatial lengths, removing the tuples that comes along with the DB queries self.id.append( train_data[i][1] ) # Storing ids, removing the tuples that comes along with the DB queries return None
def find_task(cls, detail, detail_type): # find a task or a list of tasks matching a paramiter with CursorFromConnectionFromPool() as cursor: if detail_type == 'task_id' or detail_type == 'project_id': try: cursor.execute( f'SELECT * FROM tasks WHERE {detail_type} = %s', (detail, )) return Tasks.return_task(cursor) except: print( f'summt wrong with curser when passing id or project id to task serch {cursor}' ) try: cursor.execute( f'SELECT * FROM tasks WHERE {detail_type} ILIKE %s', (detail + '%s', )) return Tasks.return_task(cursor) except: print(f'summt wrong with cursor in find task {cursor}')
def save_to_db(self): with CursorFromConnectionFromPool() as cursor: # add gate keepers to this method, I want to make sure data is a propper format and valid # make outside metthod to check if the project exsists allready in db or the file structure. cursor.execute( 'INSERT INTO tasks' '(project_id,' 'task_description,' 'task_mes_lead,' 'task_start_date,' 'task_end_date,' 'task_triage)' ' Values(%s, %s, %s, %s, %s, %s)', ( int(self.project_id), self.task_description, int(self.task_mes_lead), self.task_start_date, self.task_end_date, self.task_triage.upper(), ))
def save_to_db(self): """ Save the inserted data into the database :return: """ with CursorFromConnectionFromPool() as cursor: """ Open and close the connection --> calling connection_pool.getconn() and after closing the connection calling the connection_pool.putconn(self.connection) to put the connection in the pool --> Note: ConnectionFromPool() is no longer a direct connection so does not commit any more using 'with' so we should add the commit to the ConnectionFromPool class """ try: cursor.execute( 'INSERT INTO usersauth (email, first_name, last_name, oauth_token, oauth_token_secret) ' 'VALUES (%s, %s, %s, %s, %s);', (self.email, self.first_name, self.last_name, self.oauth_token, self.oauth_token_secret)) except: print("Unable to add data")
def save_to_db(self): with CursorFromConnectionFromPool() as cursor: cursor.execute( "insert into slitter_usage (processing_id,smpl_no,slitter_batch,slitter_number,length, thickness)" "values (%s,%s,%s,%s,%s,%s)", (self.processing_id, self.smpl_no, self.slitter_batch, self.slitter_number, self.length, self.thickness)) slitter_no = "slitter_" + self.slitter_number query = "select " + slitter_no + " from slitter_grinding where slitter_batch = '" + self.slitter_batch + "' and grinding_date is NULL " cursor.execute(query) user_data = cursor.fetchone() if user_data: new_length = int(self.length) + int(user_data[0]) else: new_length = int(self.length) update_query = "update slitter_grinding set " + slitter_no + " = " + str( new_length ) + " where slitter_batch = '" + self.slitter_batch + "' and grinding_date is NULL" cursor.execute(update_query)
def load_smpl_by_smpl_no(cls, smpl_no): with CursorFromConnectionFromPool() as cursor: cursor.execute('select * from incoming where smpl_no = %s', (smpl_no, )) user_data = cursor.fetchone() if user_data: return cls(smpl_no=user_data[1], customer=user_data[2], incoming_date=change_date_format(user_data[3]), thickness=user_data[4], width=user_data[5], length=user_data[6], grade=user_data[7], weight=user_data[8], numbers=user_data[9], mill=user_data[10], mill_id=user_data[11], remarks=user_data[12], unit=user_data[13]) else: return False
def save_to_db(self): with CursorFromConnectionFromPool() as cursor: cursor.execute( "insert into processing (smpl_no, operation, processing_date, start_time, " "end_time, setting_start_time, setting_end_time, production_time, setting_time, no_of_qc, " "no_of_helpers, names_of_qc,setting_date, total_processed_wt," "total_cuts) values (%s, %s,%s, %s, " "%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s)", (self.smpl_no, self.operation, self.processing_date, self.start_time, self.end_time, self.setting_start_time, self.setting_end_time, self.processing_time, self.setting_time, self.no_of_qc, self.no_of_helpers, self.names_of_qc, self.setting_date, self.total_processed_wt, self.total_cuts)) cursor.execute( "select processing_id from processing where oid= %s", (cursor.lastrowid, )) data = cursor.fetchone() return data[0]
def load_from_db(self, params): param_id = self.get_param_id(params) with CursorFromConnectionFromPool() as cursor: cursor.execute( "select *, published_at::date from public.search_content where search_param_id=%s;", (param_id, )) data = cursor.fetchall() # create list of dictionaries of articles for article in data: self.content.append({ 'id': article[0], 'search_param_id': article[1], 'source_id': article[2], 'source_name': article[3], 'author': article[4], 'title': article[5], 'description': article[6], 'url': article[7], 'url_to_image': article[8], 'published_at': article[9], 'published_month': article[10] }) return self.content
def rm_list_for_unhold(cls): user_data = [] cs_lst = [] cs_id_lst = [] with CursorFromConnectionFromPool() as cursor: cursor.execute( "select * from current_stock where status = 'RM - On Hold'") user_data = cursor.fetchall() for lst in user_data: cs = CurrentStock(smpl_no=lst[1], weight=Decimal(lst[2]), numbers=int(lst[3]), width=Decimal(lst[4]), length=Decimal(lst[5]), status=lst[6], customer=lst[7], thickness=Decimal(lst[8]), grade=lst[9], unit=lst[10], packet_name=lst[11]) cs_lst.append(cs) cs_id_lst.append(lst[0]) return zip(cs_id_lst, cs_lst)
def insert_acontecimento(self): with CursorFromConnectionFromPool() as cursor: for parcial in reversed(self.lista_acontecimentos): if parcial[1] == 'whistle': self.contador_tempo += 0.5 if self.contador_tempo >= 1.5: self.tempo = 2 if parcial[1] == 'soccer-ball': if parcial[2] == 'C': self.resultado_parcial += 1 else: self.resultado_parcial -= 1 try: cursor.execute( 'INSERT INTO acontecimentos(cod_jogo, tempo, icon, minuto, team, resultado_parcial, texto) ' 'VALUES(%s, %s, %s, %s, %s, %s, %s)', (self.cod_jogo, self.tempo, parcial[1], parcial[0], parcial[2], self.resultado_parcial, parcial[3])) except: print( f'nao foi possivel insert no jogo {self.cod_jogo} + {parcial[0]}' )
def after_return_from_grinding(cls, slitter_batch_no, end_date, return_date, new_od): with CursorFromConnectionFromPool() as cursor: cursor.execute( 'update slitter_grinding set grinding_date = %s where slitter_batch = %s and grinding_date is NULL', (end_date, slitter_batch_no)) cursor.execute( 'update slitter_master set last_grinding_date = %s, current_od = %s where slitter_batch = %s ', (end_date, new_od, slitter_batch_no)) cursor.execute( 'insert into slitter_grinding (slitter_batch, current_od, prev_grinding_date, slitter_1, ' 'slitter_2, slitter_3, slitter_4, slitter_5, slitter_6, slitter_7, slitter_8, slitter_9, ' 'slitter_10, slitter_11, slitter_12, slitter_13, slitter_14, slitter_15, slitter_16, ' 'slitter_17, slitter_18, slitter_19, slitter_20, slitter_21, slitter_22, slitter_23, ' 'slitter_24, slitter_25, slitter_26, slitter_27, slitter_28, slitter_29, slitter_30,' 'slitter_31, slitter_32, slitter_33, slitter_34, slitter_35, slitter_36, slitter_37' ', slitter_38, slitter_39, slitter_40, slitter_41, slitter_42, slitter_43, slitter_44' ', slitter_45, slitter_46, slitter_47, slitter_48, slitter_49, slitter_50) values' '(%s, %s, %s, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0,' ' 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, ' '0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0)', (slitter_batch_no, new_od, return_date))
def load_for_report(cls, processing_id): with CursorFromConnectionFromPool() as cursor: cursor.execute( "select * from processing_detail where processing_id = %s ", (processing_id, )) user_data = cursor.fetchall() processing_dtl_lst = [] for lst in user_data: processing_dtl = ProcessingDetail( smpl_no=lst[1], operation=lst[2], machine=lst[3], processing_id=int(lst[4]), cut_width=Decimal(lst[5]), cut_length=Decimal(lst[6]), processed_numbers=int(lst[7]), processed_wt=Decimal(lst[9]), remarks=lst[8], input_width=Decimal(lst[10]), input_length=Decimal(lst[11]), packet_name=lst[12]) processing_dtl_lst.append(processing_dtl) return processing_dtl_lst
def load_smpl_by_id(cls, cs_id): user_data = [] cs_lst = [] with CursorFromConnectionFromPool() as cursor: cursor.execute("select * from current_stock where cs_id = %s", (cs_id, )) user_data = cursor.fetchone() if user_data: cs = CurrentStock(smpl_no=user_data[1], weight=Decimal(user_data[2]), numbers=int(user_data[3]), width=Decimal(user_data[4]), length=Decimal(user_data[5]), status=user_data[6], customer=user_data[7], thickness=Decimal(user_data[8]), grade=user_data[9], unit=user_data[10], packet_name=user_data[11]) return cs else: return None
def save_to_db(self): # connection = psycopg2.connect(user='******', password='******', database='learning', host='localhost') # with connection.cursor() as cursor: # cursor.execute('INSERT INTO users (email, first_name, last_name) VALUES (%s, %s, %s)', # (self.email, # self.first_name, # self.last_name)) # # connection.commit() # connection.close() # auto commit # with connection_pool.getconn() as connection: # with connection.cursor() as cursor: # cursor.execute('INSERT INTO users (email, first_name, last_name) VALUES (%s, %s, %s)', # (self.email, # self.first_name, # self.last_name)) with CursorFromConnectionFromPool() as cursor: cursor.execute('INSERT INTO users (email, first_name, last_name) VALUES (%s, %s, %s)', (self.email, self.first_name, self.last_name))
def save_to_db(self): with CursorFromConnectionFromPool() as cursor: cursor.execute( 'INSERT INTO users (screen_name, oauth_token, oauth_token_secret) VALUES (%s, %s, %s)', (self.screen_name, self.oauth_token, self.oauth_token_secret))
def save_to_db(self): with CursorFromConnectionFromPool() as cursor: cursor.execute( '''INSERT INTO users (email, first_name, last_name) VALUES (%s, %s, %s)''', (self.email, self.first_name, self.last_name))
def load_from_db(cls, screen_name): # cls is the currently bound class, therefore cls is user with CursorFromConnectionFromPool() as cursor: cursor.execute('select * from users where screen_name = %s', (screen_name,)) user_data = cursor.fetchone() if user_data: return cls(user_data[1], user_data[2], user_data[3], user_data[0])
def get_product_id(cls, name_p): with CursorFromConnectionFromPool() as cursor: some_query = "select id from product where lower(name) = lower(%s)" cursor.execute(some_query, (name_p, )) product_id_tuple = cursor.fetchone() return product_id_tuple[0]