-
Notifications
You must be signed in to change notification settings - Fork 0
/
dbworker.py
123 lines (108 loc) · 4.65 KB
/
dbworker.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
from llibs import pg
from helpers import create_query_text
from config import connect_data as CONFIG_PARAMS
error_message = "Параметры указаны не верно"
# CONFIG_PARAMS = load_config()
class Donor:
"""
Поля таблицы:
"id": int,
"blood_type": smallint,
"rhesus": boolean,
"longitude": real,
"latitude": real,
"""
@staticmethod
def new_donor(donor_info: dict):
query_text = 'INSERT INTO "Donor" ({columns}) VALUES ({values})'
columns, values_len, values = create_query_text(donor_info)
query = query_text.format(columns=columns, values=values_len)
with pg.DB(**CONFIG_PARAMS) as conn:
conn.query(query, *values)
@staticmethod
def try_exist(user_id: int):
query_text = 'SELECT EXISTS (SELECT 1 FROM "Donor" WHERE "id" = $1)'
with pg.DB(**CONFIG_PARAMS) as conn:
return conn.query(query_text, user_id).getresult()[0][0]
@staticmethod
def get_donor_data(user_id: int) -> tuple:
"""
использовать только если донор есть в базе
:param user_id:
:return: неизменяймый список типа:
(id(int),
blood_type(smallint),
rhesus(1/0),
birth_date(datetime.date(1994, 12, 07)),
longitude(real),
latitude(real))
"""
query_text = 'SELECT * FROM "Donor" WHERE "id" = $1'
with pg.DB(**CONFIG_PARAMS) as conn:
return conn.query(query_text, user_id).getresult()[0]
@staticmethod
def update_with_data(user_id: int, donor_data: dict):
if donor_data:
query_text = 'UPDATE "Donor" SET ({columns}) = ({values}) WHERE "id" = ${user_param}'
columns, values_len, values = create_query_text(donor_data)
values.append(user_id)
query = query_text.format(columns=columns, values=values_len, user_param=len(values))
with pg.DB(**CONFIG_PARAMS) as conn:
conn.query(query, *values)
class Request:
"""
Поля таблицы:
"request_id" smallserial,
"user_id" int,
"phone_number" text,
"need_blood_type" smallint,
"need_rhesus" boolean,
"message" text,
"post_date" timestamp,
"longitude" real,
"latitude" real,
"registration_flag" boolean,
"send_flag" boolean
"""
# @staticmethod
# def empty_request(user_id):
# query_text = 'SELECT "request_id" FROM "Request" WHERE "user_id"= $1 AND "registration_flag" is False'
# with pg.DB(**CONFIG_PARAMS) as conn:
# return conn.query(query_text, user_id).getresult() # проверяй массив на пустоту для ответа
@staticmethod
def update_request(request_info, user_id):
query_text = '''
UPDATE "Request"
SET ({columns}) = ({values})
WHERE "user_id" = ${user_id}
AND "registration_flag" Is FALSE
'''
columns, values_len, values = create_query_text(request_info)
values.append(user_id)
query = query_text.format(columns=columns, values=values_len, user_id=len(values))
print(query)
with pg.DB(**CONFIG_PARAMS) as conn:
conn.query(query, *values)
@staticmethod
def upsert_request(request_info: dict):
query_text = '''
INSERT INTO "Request" ({columns})
VALUES ({values})
ON CONFLICT ("user_id")
WHERE "registration_flag" Is FALSE
DO UPDATE SET ({columns}) = ({values})'''
columns, values_len, values = create_query_text(request_info)
query = query_text.format(columns=columns, values=values_len)
with pg.DB(**CONFIG_PARAMS) as conn:
conn.query(query, *values)
# empty_exist = Request.empty_request(request_info["user_id"])
# if empty_exist:
# Request.update_request(request_info, empty_exist[0][0])
# return empty_exist[0][0] # возвращаю номер запроса
#
# else:
# query_text = 'INSERT INTO "Request" ({columns}) VALUES ({values}) RETURNING request_id'
# columns, values_len, values = create_query_text(request_info)
# query = query_text.format(columns=columns, values=values_len)
# with pg.DB(**CONFIG_PARAMS) as conn:
# return conn.query(query, *values).getresult()[0][0]