def get_request(self, id: int):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = 'SELECT * FROM contract WHERE owner = {};'.format(id)
query = DBQuery(conn)
if not query.exec_query(str_query):
print(query.get_error())
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__to_json(result, query.get_column_names())
def __add_position_to_db(self, json): conn_name = str(uuid.uuid4()) conn = access_manager.connect(conn_name) str_query = self.__get_str_query(json) query = DBQuery(conn) if not query.exec_query(str_query): print(query.get_error()) return False access_manager.disconnect(conn_name) return True
def __update_db(self, str_query: str):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
query = DBQuery(conn)
query.begin_transaction()
if not query.exec_query(str_query):
print(query.get_error())
query.rollback_transaction()
access_manager.disconnect(conn_name)
return False
query.commit_transaction()
access_manager.disconnect(conn_name)
return True
def __queryDb(self, vis_id):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
str_query = '''SELECT vis.*, stat.*, anim.anim_id, anim.name, anim.species, s.staff_id,
pos.title, pass.name, pass.surname, pass.patronymic
FROM visits vis JOIN animals_medical_records anim ON vis.animal=anim.anim_id
JOIN staff s ON vis.doctor=s.staff_id
JOIN position pos ON s.position=pos.pos_id
JOIN passports pass ON s.passport=pass.pass_id
JOIN animal_states stat ON vis.cur_state=stat.state_id
WHERE vis.vis_id = {i};'''.format(i=vis_id)
query = DBQuery(conn)
if not query.exec_query(str_query):
print(query.get_error())
return False, ""
else:
result = query.get_values()
access_manager.disconnect(conn_name)
return True, self.__construct_json(result, query.get_column_names())
def __queryInsertDb(self, data: dict, owner: int):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
query = DBQuery(conn)
chip = data["chip_id"]
query.begin_transaction()
contract = data["contract"]
chip = data["chip_id"]
last_update_str = contract['last_update_date']
if len(last_update_str) == 0:
last_update_str = 'NULL'
else:
last_update_str = "'{}'::date".format(last_update_str)
str_query = '''INSERT INTO contract (code, conclusion_date, last_update_date, owner, valid_until) VALUES('{code}', '{conclusion_date}'::date,
{last_update_date}, '{owner}', '{valid_until}'::date) RETURNING contr_id;'''.format(
code=contract['code'],
conclusion_date=contract['conclusion_date'],
last_update_date=last_update_str,
valid_until=contract['valid_until'],
owner=owner)
contr_id: int
if not query.exec_query(str_query):
query.rollback_transaction()
print(query.get_error())
return False
else:
contr_id = query.get_values()[0][0]
str_query = '''INSERT INTO microchips (chip_num, impl_date, country, location)
VALUES('{chip_num}', '{impl_date}'::date, '{country}', '{location}') RETURNING chip_id;'''\
.format(chip_num=chip["chip_num"], impl_date=chip["impl_date"], country=chip["country"], location=chip["location"])
chip_id: int
if not query.exec_query(str_query):
query.rollback_transaction()
print(query.get_error())
return False
else:
chip_id = query.get_values()[0][0]
str_query = '''INSERT INTO animals_medical_records (name, breed, species, sex, castrated, birth, other_data,
color, special_signs, registr_date, chip_id, contract, rel_path_to_photo)
VALUES ('{name}', '{breed}', '{species}', '{sex}', '{castrated}', '{birth}'::date, '{other}', '{color}', '{special_signs}',
'{registr_date}'::date, {chip_id}, {contract}, '{rel_path_to_photo}') RETURNING anim_id;'''\
.format(name=data["name"], breed=data["breed"], species=data["species"],
sex=data["sex"], castrated=data["castrated"], birth=data["birth"],
other=data["other_data"], color=data["color"], special_signs=data["special_signs"],
registr_date=data["registr_date"], contract=contr_id,
chip_id=chip_id, rel_path_to_photo=data["rel_path_to_photo"])
anim_id: int
if not query.exec_query(str_query):
query.rollback_transaction()
print(query.get_error())
return False, 0
else:
anim_id = query.get_values()[0][0]
query.commit_transaction()
access_manager.disconnect(conn_name)
return True, anim_id
def __queryUpdateDb(self, data: dict):
conn_name = str(uuid.uuid4())
conn = access_manager.connect(conn_name)
query = DBQuery(conn)
chip = data["chip_id"]
query.begin_transaction()
contract = data["contract"]
chip = data["chip_id"]
str_queries = []
last_update_str = contract['last_update_date']
if len(last_update_str) == 0:
last_update_str = 'NULL'
else:
last_update_str = "'{}'::date".format(last_update_str)
str_queries.append('''UPDATE animals_medical_records
SET name='{name}',
breed='{breed}',species='{species}', sex='{sex}', castrated='{castrated}', birth='{birth}'::date,
other_data='{other}', color='{color}', special_signs='{special_signs}',
registr_date='{registr_date}'::date, chip_id={chip_id}, contract={contract},
rel_path_to_photo='{rel_path_to_photo}' WHERE anim_id={anim_id};'''.
format(anim_id=data["anim_id"],
name=data["name"],
breed=data["breed"],
species=data["species"],
sex=data["sex"],
castrated=data["castrated"],
birth=data["birth"],
other=data["other_data"],
color=data["color"],
special_signs=data["special_signs"],
registr_date=data["registr_date"],
contract=contract["contr_id"],
chip_id=chip["chip_id"],
rel_path_to_photo=data["rel_path_to_photo"]))
str_queries.append('''UPDATE contract
SET code='{code}', conclusion_date='{conclusion_date}'::date,
last_update_date={last_update_date}, valid_until='{valid_until}'::date WHERE contr_id={contr_id};'''
.format(code=contract['code'],
conclusion_date=contract['conclusion_date'],
last_update_date=last_update_str,
valid_until=contract['valid_until'],
contr_id=contract['contr_id']))
str_queries.append('''UPDATE microchips
SET chip_num='{chip_num}', impl_date='{impl_date}'::date,
country='{country}', location='{location}' WHERE chip_id={chip_id}'''.
format(chip_num=chip["chip_num"],
impl_date=chip["impl_date"],
country=chip["country"],
location=chip["location"],
chip_id=chip["chip_id"]))
for str_query in str_queries:
if not query.exec_query(str_query):
query.rollback_transaction()
print(query.get_error())
return False
query.commit_transaction()
access_manager.disconnect(conn_name)
return True
