import psycopg2 as pg from sshtunnel import SSHTunnelForwarder from config import SSH_H import datetime #Проверка соединения и само соединение def ConnectToDatbase(): try: print('Connecting to the PostgreSQL Database...') ssh_tunnel = SSHTunnelForwarder( (SSH_H, 334), #ip address and port ssh_username="artem_valiakhmetov", #имя пользователя ssh_private_key= 'D:/keys/home/.ssh/id_rsa',# путь к файлу где расположен ssh ключ (не .pub) ssh_private_key_password= '',# пароль (в данном случае пароль отсутствует) remote_bind_address=("localhost", 5432) # не особо понял что это ) ssh_tunnel.start() print("Success ssh connect!") conn = pg.connect( host = "localhost", port = ssh_tunnel.local_bind_port, user = "postgres", password = "", database = "postgres" ) print("Successfully connection...") return conn except Exception as ex: print("Error connection to database...") print(ex) class RequestDataBase: def __init__(self): self.conn = ConnectToDatbase() def add_car_log(self, operation): try: with self.conn.cursor() as cursor: date_cur = datetime.datetime.now() insert_car_log = "INSERT INTO cars_log (id, resource_id, operation, time_log_created, user_id) VALUES(%s, %s, %s, %s, %s)" val = [2, 3, operation, date_cur, 1] cursor.execute(insert_car_log, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def add_order_log(self, operation): try: with self.conn.cursor() as cursor: date_cur = datetime.datetime.now() insert_order_log = "INSERT INTO order_list_log (id serial primary key, payment_transaction_id int NOT NULL, service_id int NOT NULL, service_type_id int NOT NULL, time_placed timestamptz NOT NULL, time_start timestamptz NOT NULL, time_finish_predicted timestamptz, time_finish_real timestamptz NOT NULL, order_place_start varchar(255) NOT NULL, order_place_predicted varchar(255) NOT NULL, order_place_real varchar(255) NOT NULL, price money NOT NULL, provider int NOT NULL, receiver int NOT NULL, client int NOT NULL) VALUES (1,1,1,'2022-06-17 01:02:03', '2022-06-17 01:02:03','2022-06-17 12:02:03','2022-06-17 12:02:03', 'Walmart street, 14.24', 'Times street, 13.14', 'Walmart street, 14.24', 100, 1,1,1);" val = [2, 3, operation, date_cur, 1] cursor.execute(insert_car_log, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def del_car_log(self, id): try: with self.conn.cursor() as cursor: delete_car_log = "DELETE FROM cars_log WHERE id = %s" cursor.execute(delete_car_log, id) self.conn.commit() return "Успешно!" except Exception as ex: return str(ex) def insert_current_order(self, order_id, service_type_id, provider_id, resourse_id, time_start_predicted,time_finished_predicted): try: with self.conn.cursor() as cursor: gap = 60 insert_current_order = "INSERT INTO current_timetable (order_id, service_type_id, provider_id, resourse_id, time_start_predicted, time_finished_predicted, gap) VALUES (%s, %s, %s, %s, %s, %s, %s)" val = [order_id, service_type_id, provider_id, resourse_id, time_start_predicted,time_finished_predicted, gap] cursor.execute(insert_current_order, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def del_current_order(self, order_id): try: with self.conn.cursor() as cursor: delete_current_order = "DELETE FROM current_timetable WHERE order_id = %s" val = [order_id] cursor.execute(delete_current_order, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def get_upcoming_orders(self): try: with self.conn.cursor() as cursor: select_upcoming_orders = "SELECT * FROM current_timetable" cursor.execute(select_upcoming_orders) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def change_status(self, status): try: with self.conn.cursor() as cursor: date_cur = datetime.datetime.now() insert_car_log = "INSERT INTO cars_log (resource_id, operation, time_log_created, user_id) VALUES(%s, %s, %s, %s)" val = [2, operation, date_cur, 5] cursor.execute(insert_car_log, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def create_ticket(self, type, reporter, assignee, time_created, time_assigned, time_changed, time_closed, status, description): try: with self.conn.cursor() as cursor: date_cur = datetime.datetime.now() insert_car_log = "INSERT INTO cars_log (resource_id, operation, time_log_created, user_id) VALUES(%s, %s, %s, %s)" val = [2, operation, date_cur, 5] cursor.execute(insert_car_log, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def change_ticket_status(self, id, status): try: with self.conn.cursor() as cursor: date_cur = datetime.datetime.now() insert_car_log = "INSERT INTO cars_log (resource_id, operation, time_log_created, user_id) VALUES(%s, %s, %s, %s)" val = [2, operation, date_cur, 5] cursor.execute(insert_car_log, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def add_car(self, resource_id, user_id, order_status, order_id, direction, battery_level, board_voltage): try: with self.conn.cursor() as cursor: date_cur = datetime.datetime.now() insert_car_log = "INSERT INTO cars_log (resource_id, operation, time_log_created, user_id) VALUES(%s, %s, %s, %s)" val = [2, operation, date_cur, 5] cursor.execute(insert_car_log, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def del_car(self, id): try: with self.conn.cursor() as cursor: date_cur = datetime.datetime.now() insert_car_log = "INSERT INTO cars_log (resource_id, operation, time_log_created, user_id) VALUES(%s, %s, %s, %s)" val = [2, operation, date_cur, 5] cursor.execute(insert_car_log, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def change_car(self, id, resource_id, user_id, order_status, order_id, doors_output, engine_input, central_lock_input, ignition_input, doors_input, input4, controller_status, signal_gsm, operator_gsm, signal_gps, location, speed, direction, address, battery_level, board_voltage): try: with self.conn.cursor() as cursor: date_cur = datetime.datetime.now() insert_car_log = "INSERT INTO cars_log (resource_id, operation, time_log_created, user_id) VALUES(%s, %s, %s, %s)" val = [2, operation, date_cur, 5] cursor.execute(insert_car_log, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def change_current_token(self, id, current_token): try: with self.conn.cursor() as cursor: change_current_token = "UPDATE current_cars SET current_token = %s WHERE id = %s" val = [current_token, id] cursor.execute(change_current_token, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def update_current_order(self, order_id, service_type_id, provider_id, resource_id, time_start_predicted, time_finished_predicted): try: with self.conn.cursor() as cursor: update_current_order = "UPDATE current_timetable SET order_id = %s, service_type_id = %s, provider_id = %s, resource_id = %s, time_start_predicted = %s, time_finished_predicted = %s, gap = %s" val = [order_id, service_type_id, provider_id, resource_id, time_start_predicted, time_finished_predicted] cursor.execute(update_current_order, val) self.conn.commit() return "Успешный запрос!" except Exception as ex: return str(ex) def actuality_check(self,id): try: with self.conn.cursor() as cursor: date_cur = datetime.datetime.now() insert_car_log = "INSERT INTO cars_log (resource_id, operation, time_log_created, user_id) VALUES(%s, %s, %s, %s)" val = [2, operation, date_cur, 5] cursor.execute(insert_car_log, val) self.conn.commit() select_orders = "SELECT * FROM current_orders WHERE id = %s AND state = 151 OR state = 159" val = [id] res = cursor.execute(select_orders) if (res == True): delete_orders = "DELETE FROM current_timetable WHERE id = %s" val = [id] cursor.execute(delete_orders, val) return "Успешный запрос!" except Exception as ex: return str(ex)