async def save(self):
"""Update if exists in db or create if not"""
for field_name, field in self._fields.items():
value = field.validate(getattr(self, field_name))
setattr(self, field_name, value)
object_fields = ['id', *list(self._fields.keys())]
await self.check_fields()
if self.__dict__.get('id') is not None:
set_arr = []
for i in object_fields:
attr_value = getattr(self, i)
if attr_value is None:
set_arr.append(
sql.SQL("{}=null").format(sql.Identifier(i)))
else:
set_arr.append(
sql.SQL("{}={}").format(sql.Identifier(i),
sql.Literal(attr_value)))
update_query = sql.SQL("UPDATE {} SET {} WHERE id={}").format(
sql.Identifier(str(self._table_name).lower()),
sql.SQL(', ').join(set_arr), sql.Literal(self.id))
# print(update_query.as_string(psycopg_conn))
async with conn_pool.acquire() as con:
await con.execute(update_query.as_string(psycopg_conn))
else:
values = []
for i in object_fields:
if getattr(self, i) is not None:
values.append(
sql.SQL("{}").format(sql.Literal(getattr(self, i))))
else:
if i == 'id':
values.append(sql.SQL('DEFAULT'))
else:
values.append(sql.SQL("null"))
insert_query = sql.SQL(
"INSERT INTO {0} ({1}) VALUES ({2}) RETURNING id;").format(
sql.Identifier(str(self._table_name).lower()),
sql.SQL(', ').join(
[sql.Identifier(i) for i in object_fields]),
sql.SQL(', ').join(values))
# print(insert_query.as_string(psycopg_conn))
async with conn_pool.acquire() as con:
res = await con.fetch(insert_query.as_string(psycopg_conn))
self.id = res[0].get('id')
async def create(self, *_, **kwargs):
"""Create object"""
if not kwargs:
raise IntegrityError("no parameters to create")
for field_name, field in self.model_cls._fields.items():
if (getattr(self.model_cls, field_name) is None or getattr(
self.model_cls, field_name) == "None") and field.required:
raise IntegrityError(
'NOT NULL constraint failed: {} in {} column'.format(
getattr(self.model_cls, field_name), field_name))
edited_kw = {}
for field_name, field in kwargs.items():
value = getattr(self.model_cls,
field_name).validate(kwargs.get(field_name))
edited_kw[field_name] = value
insert_query = sql.SQL(
"INSERT INTO {0} ({1}) VALUES ({2}) RETURNING *;").format(
sql.Identifier(str(self.model_cls._table_name).lower()),
sql.SQL(', ').join(
[sql.Identifier(i) for i in edited_kw.keys()]),
sql.SQL(', ').join(
[sql.Literal(i) for i in edited_kw.values()]))
# print(insert_query.as_string(psycopg_conn))
async with conn_pool.acquire() as con:
res = await con.fetchrow(insert_query.as_string(psycopg_conn))
res = dict(zip([i for i in res.keys()], res))
return self.model_cls(**res)
async def _count_perform(self):
if self.res is not None:
res_len = len(self.res)
self.__cache['count'] = res_len
return res_len
query = [
sql.SQL('SELECT count(*) FROM (SELECT * FROM {}').format(
sql.Identifier(str(self.model_cls._table_name).lower()))
]
if self.where:
query.extend([
sql.SQL('WHERE'),
sql.SQL(' AND ').join(
[sql.SQL(i) for i in self.format_where()])
])
if self.limit:
query.extend(self.format_limit())
query.append(sql.SQL(') as tmp_table'))
# print(' '.join([i.as_string(psycopg_conn) for i in query]))
async with conn_pool.acquire() as con:
res_len = (await con.fetchrow(' '.join(
[i.as_string(psycopg_conn) for i in query])))[0]
self.__cache['count'] = res_len
return res_len
async def delete(self):
"""Delete object"""
if self.id is None:
raise DeleteError(
'{} object can\'t be deleted because its id attribute is set to None.'
.format(self._table_name))
try:
delete_query = sql.SQL("DELETE FROM {} WHERE id={}").format(
sql.Identifier(str(self._table_name).lower()),
sql.Literal(self.id))
# print(delete_query.as_string(psycopg_conn))
async with conn_pool.acquire() as con:
await con.execute(delete_query.as_string(psycopg_conn))
except Exception as e:
raise DeleteError(
'{} object can\'t be deleted because its id is incorrect.'.
format(self._table_name))
async def _build(self):
if self.where:
[
Condition.check_fields(i, self.model_cls)
for i in self.where.items()
]
query = [sql.SQL("SELECT *")]
query.extend([
sql.SQL("FROM"),
sql.Identifier(str(self.model_cls._table_name).lower())
])
if self.where:
query.extend([
sql.SQL("WHERE"),
sql.SQL(" AND ").join(
[sql.SQL(i) for i in self.format_where()])
])
if self._order_by:
query.extend([
sql.SQL("ORDER BY"),
sql.SQL(", ").join(self.format_order_list())
])
if self.limit:
query.extend(self.format_limit())
# print(' '.join([i.as_string(psycopg_conn) for i in query]))
async with conn_pool.acquire() as con:
res = await con.fetch(' '.join(
[i.as_string(psycopg_conn) for i in query]))
if isinstance(self.limit, int):
return self.model_cls(
**dict(zip([i for i in res[0].keys()], res[0])))
self.res = [
self.model_cls(**dict(zip([ii
for ii in res[0].keys()], res[i])))
for i in range(len(res))
]
async def update(self, *_, **kwargs):
if not kwargs:
raise ValueError("you should write params")
[Condition.check_fields(i, self.model_cls) for i in kwargs.items()]
query = [
sql.SQL('UPDATE {}').format(
sql.Identifier(str(self.model_cls._table_name).lower())),
sql.SQL('SET'),
sql.SQL(', ').join([
sql.SQL("{}={}").format(sql.Identifier(i[0]),
sql.Literal(i[1]))
for i in kwargs.items()
])
]
if self.where:
sql_where = [sql.SQL(i) for i in self.format_where()]
query.extend([
sql.SQL('WHERE'),
sql.SQL('id IN (SELECT id FROM {} WHERE').format(
sql.Identifier(str(self.model_cls._table_name).lower())),
sql.SQL(' AND ').join(sql_where)
])
if self._order_by:
query.extend([
sql.SQL("ORDER BY"),
sql.SQL(", ").join(self.format_order_list())
])
if self.limit:
query.extend(self.format_limit())
query.append(sql.SQL(')'))
async with conn_pool.acquire() as con:
# print(' '.join([i.as_string(psycopg_conn) for i in query]))
return await con.execute(' '.join(
[i.as_string(psycopg_conn) for i in query]))
async def delete(self):
query = [
sql.SQL(
'DELETE FROM {0} WHERE ctid in (SELECT ctid FROM {0}').format(
sql.Identifier(str(self.model_cls._table_name).lower()))
]
if self.where:
query.extend([
sql.SQL('WHERE'),
sql.SQL(' AND ').join(
[sql.SQL(i) for i in self.format_where()])
])
if self._order_by is not None:
query.extend([
sql.SQL("ORDER BY"),
sql.SQL(", ").join(self.format_order_list())
])
if self.limit:
query.extend(self.format_limit())
query.append(sql.SQL(')'))
async with conn_pool.acquire() as con:
return await con.execute(' '.join(
[i.as_string(psycopg_conn) for i in query]))