def delete(self, values): query = QueryBuilder('user_has_roles')\ .delete()\ .where('user_id')\ .where('role_id')\ .sql() db.execute_sql(query, (values['user_id'], values['role_id']))
def delete(self, pk): query = QueryBuilder(self.model.table)\ .delete()\ .where(self.model.primary_key)\ .sql() db.execute_sql(query, (pk,))
def findByUsername(self, username): query = QueryBuilder(self.model.table, self.model.columns)\ .where('username')\ .limit(1)\ .sql() cursor = db.execute_sql(query, (username,)) return self.as_object(cursor)
def update(self, pk, values): if isinstance(values, self.model): values = dict(values) values['updated_at'] = datetime.datetime.now() query = QueryBuilder(self.model.table, self.model.columns[1:])\ .update()\ .where(self.model.primary_key)\ .sql() db.execute_sql( query, (*[values.get(c, None) for c in self.model.columns[1:]], pk) ) return self.find(pk, True)
def allByPerson(self, person_id): query = QueryBuilder(models.Person.table, models.Person.columns)\ .join(self.model.table)\ .on('person_id')\ .on('associated_id', '=', 'id')\ .sql() cursor = db.execute_sql(query, (person_id,)) return self.as_iterator(cursor, models.Person)
def allByFederativeUnit(self, federative_unit_id, order_by=[]): query = QueryBuilder(self.model.table, self.model.columns)\ .where('federative_unit_id') for o in order_by: query = query.order_by(*o) cursor = db.execute_sql(query.sql(), (federative_unit_id,)) return self.as_iterator(cursor)
def allByUser(self, user_id): query = QueryBuilder(self.model.table, self.model.columns)\ .join('user_has_roles')\ .on('role_id', '=', 'id')\ .on('user_id')\ .sql() cursor = db.execute_sql(query, (user_id,)) return self.as_iterator(cursor)
def findByName(self, name, with_trash=False): query = QueryBuilder(self.model.table, self.model.columns)\ .where('name')\ .limit(1) query = query.sql() cursor = db.execute_sql(query, (name,)) return self.as_object(cursor)
def create(self, values): query = QueryBuilder('user_has_roles', ['user_id', 'role_id'])\ .insert()\ .returning(['user_id', 'role_id'])\ .sql() cursor = db.execute_sql(query, (values['user_id'], values['role_id'])) return models.UserHasRole(**dict( zip(models.UserHasRole.columns, cursor.fetchone()) ))
def findByAssociated(self, person_id): query = QueryBuilder(models.Person.table, models.Person.columns)\ .join(self.model.table)\ .on('person_id', '=', 'id')\ .on('associated_id')\ .limit(1)\ .sql() cursor = db.execute_sql(query, (person_id,)) return self.as_object(cursor, models.Person)
def all(self, with_trash=False, order_by=[('id',)]): query = QueryBuilder(self.model.table, self.model.columns) for o in order_by: query = query.order_by(*o) if self.use_soft_delete and not with_trash: query = query.where('deleted_at', 'IS', 'NULL') query = query.sql() cursor = db.execute_sql(query) return self.as_iterator(cursor)
def find(self, pk, with_trash=False): query = QueryBuilder(self.model.table, self.model.columns)\ .where(self.model.primary_key)\ .limit(1) if self.use_soft_delete and not with_trash: query = query.where('deleted_at', 'IS', 'NULL') query = query.sql() cursor = db.execute_sql(query, (pk,)) return self.as_object(cursor)
def findByIdentifier(self, identifier, with_trash=False): query = QueryBuilder(self.model.table, self.model.columns)\ .where('identifier')\ .limit(1) if not with_trash: query = query.where('deleted_at', 'IS', 'NULL') query = query.sql() cursor = db.execute_sql(query, (identifier,)) return self.as_object(cursor)
def create(self, values): if isinstance(values, self.model): values = dict(values) values['created_at'] = datetime.datetime.now() query = QueryBuilder(self.model.table, self.model.columns[1:])\ .insert()\ .returning([self.model.primary_key])\ .sql() cursor = db.execute_sql( query, [values.get(c, None) for c in self.model.columns[1:]] ) return self.find(cursor.fetchone()[0])
def deleteByPerson(self, person_id): query = QueryBuilder(self.model.table).delete().where( 'person_id').sql() db.execute_sql(query, (person_id,))
def deleteByModuleType(self, module_type_id): query = QueryBuilder(self.model.table, self.model.columns)\ .delete()\ .where('module_type_id')\ .sql() db.execute_sql(query, (module_type_id,))
def allByModuleType(self, module_type_id): query = QueryBuilder(self.model.table, self.model.columns)\ .where('module_type_id')\ .sql() cursor = db.execute_sql(query, (module_type_id,)) return self.as_iterator(cursor)
def allRegistered(self): query = QueryBuilder(self.model.table, self.model.columns)\ .where('person_id','IS NOT', 'NULL')\ .sql() cursor = db.execute_sql(query) return self.as_iterator(cursor)
def allByPerson(self, person_id): query = QueryBuilder(self.model.table, self.model.columns)\ .where('person_id')\ .sql() return self.as_iterator(db.execute_sql(query, (person_id,)))
def deleteAssociation(self, person_id, associated_id): query = QueryBuilder(self.model.table).delete().where( 'person_id').where('associated_id').sql() db.execute_sql(query, (person_id, associated_id))