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]
