def update(self, account_main_id, organisation: Organisation):
query = sql.SQL(""" UPDATE
organisation
SET
name = CASE WHEN {name_field}::varchar != '-1' THEN {name_field}::varchar WHEN {name_field}::varchar = '-1' THEN name END,
login = CASE WHEN {login_field}::varchar != '-1' THEN {login_field}::varchar WHEN {login_field}::varchar = '-1' THEN login END,
photo_link = CASE WHEN {photo_link_field}::varchar != '-1' THEN {photo_link_field}::varchar WHEN {photo_link_field}::varchar = '-1' THEN photo_link END,
description = CASE WHEN {description_field}::varchar != '-1' THEN {description_field}::varchar WHEN {description_field}::varchar = '-1' THEN description END,
edited_at = current_timestamp
WHERE
account_main_id = {account_main_id}
RETURNING id, edited_at;""").format(
name_field=sql.Identifier(organisation.name),
login_field=sql.Identifier(organisation.login),
photo_link_field=sql.Identifier(organisation.photo_link),
description_field=sql.Identifier(organisation.description),
account_main_id=sql.Identifier(organisation.account_main.id))
with self.pool.getconn() as conn:
with conn.cursor(cursor_factory=extras.RealDictCursor) as cur:
cur.execute(query)
row = cur.fetchone()
cur.close()
conn.commit()
self.pool.putconn(conn)
organisation.id = row['id']
organisation.edited_at = row['edited_at']
return organisation, None
def add(self, organisation: Organisation):
sql = """ INSERT INTO organisation(account_main_id, name, login, photo_link, description) VALUES
(%s, %s, %s, %s, %s)
RETURNING id, created_at, edited_at;"""
with self.pool.getconn() as conn:
with conn.cursor(cursor_factory=extras.RealDictCursor) as cur:
try:
cur.execute(
sql, (organisation.account_main.id, organisation.name,
organisation.login, organisation.photo_link,
organisation.description))
row = cur.fetchone()
cur.close()
conn.commit()
except psycopg2.IntegrityError as err:
if err.pgcode == errorcodes.UNIQUE_VIOLATION:
return None, "Эта организация уже зарегистрирована"
else:
raise TypeError
self.pool.putconn(conn)
organisation.id = row['id']
organisation.created_at = row['created_at']
organisation.edited_at = row['edited_at']
return organisation, None