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]))