def get_multi( self, *, skip: int = 0, limit: int = 100, filter_parameters: Optional[List[str]], sort_parameters: Optional[List[str]], ) -> Tuple[List[ModelType], str]: query = db.session.query(self.model) logger.debug( f"Filter and Sort parameters model={self.model}, sort_parameters={sort_parameters}, filter_parameters={filter_parameters}", ) conditions = [] if filter_parameters: for filter_parameter in filter_parameters: key, *value = filter_parameter.split(":", 1) # Use this branch if we detect a key value search (key:value) if it is just a single string (value) # treat the key as the value if len(value) > 0: if key in sa_inspect(self.model).columns.keys(): conditions.append(cast(self.model.__dict__[key], String).ilike("%" + one(value) + "%")) else: logger.info(f"Key: not found in database model key={key}, model={self.model}") query = query.filter(or_(*conditions)) else: for column in sa_inspect(self.model).columns.keys(): conditions.append(cast(self.model.__dict__[column], String).ilike("%" + key + "%")) query = query.filter(or_(*conditions)) if sort_parameters and len(sort_parameters): for sort_parameter in sort_parameters: try: sort_col, sort_order = sort_parameter.split(":") if sort_col in sa_inspect(self.model).columns.keys(): if sort_order.upper() == "DESC": query = query.order_by(expression.desc(self.model.__dict__[sort_col])) else: query = query.order_by(expression.asc(self.model.__dict__[sort_col])) else: logger.debug(f"Sort col does not exist sort_col={sort_col}") except ValueError: if sort_parameter in sa_inspect(self.model).columns.keys(): query = query.order_by(expression.asc(self.model.__dict__[sort_parameter])) else: logger.debug(f"Sort param does not exist sort_parameter={sort_parameter}") # Generate Content Range Header Values count = query.count() if limit: # Limit is not 0: use limit response_range = "{} {}-{}/{}".format(self.model.__table__.name.lower(), skip, skip + limit, count) return query.offset(skip).limit(limit).all(), response_range else: # Limit is 0: unlimited response_range = "{} {}/{}".format(self.model.__table__.name.lower(), skip, count) return query.offset(skip).all(), response_range
def to_dict(self): """ 01 convert *model_obj.column* to *dict['column']* 02 also convert *sqlalchemy-hybrid attributes* if any """ d = {} #region extract db columns to dict columns = self._sa_class_manager.mapper.mapped_table.columns for c in columns: d[c.name] = getattr(self, c.name) #alternative implementation #return {c.key: getattr(self, c.key) # for c in inspect(self).mapper.column_attrs} pass #endregion extract db columns #region extract hybrid property to dict '''ref. https://stackoverflow.com/a/31869608/248616''' from sqlalchemy.inspection import inspect as sa_inspect from sqlalchemy.ext.hybrid import hybrid_property for item in sa_inspect(self.__class__).all_orm_descriptors: if type(item) == hybrid_property: ha = item.__name__ #ha means hybrid-property attribute aka. hybrid attribute d[ha] = getattr(self, ha) #endregion extract hybrid property return d
def test_build_clock_class(): clock = build_clock_class( 'Testing', sa.MetaData(), {'__tablename__': 'test'}) assert clock.__name__ == 'TestingClock' assert issubclass(clock, temporal.EntityClock) actual_primary_keys = [k.name for k in sa_inspect(clock).primary_key] assert actual_primary_keys == ['id']
def delete(self, session=None, is_commit=True): if not session: session = sa_inspect(self).session try: session.delete(self) if is_commit: session.commit() except Exception as error: session.rollback() raise error
def save(self, session=None, is_commit=True): if not session: session = sa_inspect(self).session try: session.add(self) if is_commit: session.commit() except Exception as e: session.rollback() raise e return self
def update(self, data_update, session=None, is_commit=True): if not session: session = sa_inspect(self).session fields_can_update = self.__update_field__ or list() for k, v in data_update.items(): if k in fields_can_update: setattr(self, k, v) try: if is_commit: session.commit() except Exception as error: session.rollback() raise error
def get_models(): models = [] for model in published: name = model.__table__.name inspected = sa_inspect(model) # XXX collection name may vary primary_key = [key.name for key in inspected.primary_key] model = { 'name': name, 'primary_key': primary_key, 'collection_name': name } model['relationships'] = {} for name, relationship in inspected.relationships.items(): rel = {'target': relationship.target.name} model['relationships'][name] = rel models.append(model) return jsonify({'models': models})
def dictize(self): """Return the domain object as a dictionary.""" private = [ 'key', 'id', '_data', 'deleted', 'collection_key', 'data', 'iri', 'language' ] out = {} # Add column values for col in self.__table__.c: obj = getattr(self, col.name) if not obj or col.name in private: continue elif isinstance(obj, datetime.datetime): obj = obj.isoformat() out[col.name] = obj # Add default generator to Annotations generator = current_app.config.get('GENERATOR') if generator and self.__class__.__name__ == 'Annotation': out['generator'] = generator # Add data if self._data: out.update(self._data) # Add hybrid properties for item in sa_inspect(self.__class__).all_orm_descriptors: if type(item) == hybrid_property and item.__name__ not in private: obj = getattr(self, item.__name__) out[item.__name__] = obj # Add generated out['generated'] = make_timestamp() # Add ID if self.iri: out['id'] = self.iri return out
def _loadParams(self, newclass): ''' Loads all parameters from wtforms into a dictionary with key, value = {'parameter_name': 'parent WTForm name'}. Ignores hidden attributes and the Meta class ''' model = newclass.Meta.model schema = model.__table__.schema tablename = model.__table__.name mapper = sa_inspect(model) for key, item in mapper.all_orm_descriptors.items(): if isinstance(item, (hybrid_property, hybrid_method)): key = key elif isinstance(item, InstrumentedAttribute): key = item.key else: continue lookupKeyName = schema + '.' + tablename + '.' + key self._param_form_lookup[lookupKeyName] = newclass self._paramtree[newclass.Meta.model.__name__][key]
def _db_row_to_dict(row, remove_columns=False): """Converts a DB object to a dictionary.""" from sqlalchemy.inspection import inspect as sa_inspect from sqlalchemy.ext.hybrid import hybrid_property from sqlalchemy.orm.attributes import InstrumentedAttribute row_dict = collections.OrderedDict() columns = row.__table__.columns.keys() mapper = sa_inspect(row.__class__) for key, item in mapper.all_orm_descriptors.items(): if isinstance(item, hybrid_property): columns.append(key) for col in columns: if remove_columns and col in remove_columns: continue row_dict[col] = getattr(row, col) return row_dict
['mangasampledb.nsa.pk']), { 'autoload': True, 'schema': 'mangasampledb' }) def __repr__(self): return '<MangaTargetToNSA (pk={0})>'.format(self.pk) # Relationship between NSA and MangaTarget NSA.mangaTargets = relationship(MangaTarget, backref='NSA_objects', secondary=MangaTargetToNSA.__table__) # Now we create the remaining tables. insp = sa_inspect(db.engine) schemaName = 'mangasampledb' allTables = insp.get_table_names(schema=schemaName) done_names = list(db.Base.metadata.tables.keys()) for tableName in allTables: if schemaName + '.' + tableName in done_names: continue className = str(tableName).upper() newClass = ClassFactory(className, tableName, fks=[('catalogue_pk', 'mangasampledb.catalogue.pk') ]) newClass.catalogue = relationship(Catalogue, backref='{0}_objects'.format(tableName))
def order_by_helper(cls, query, order_by): if order_by is not None: return query.order_by(*tolist(order_by)) pk_cols = sa_inspect(cls).primary_key return query.order_by(*pk_cols)
'mangasampledb.{0}.pk'.format(tablename))] relationalTableName = 'manga_target_to_{0}'.format(tablename) relationalClassName = 'MangaTargetTo{0}'.format(tablename.upper()) new_relationalclass = ClassFactory(relationalClassName, relationalTableName, fks=relfks) globals()[relationalClassName] = new_relationalclass new_class.mangaTargets = relationship( MangaTarget, backref='{0}_objects'.format(tablename), secondary=new_relationalclass.__table__) # Now we create any remaining catalogue tables. insp = sa_inspect(database.engine) allTables = insp.get_table_names(schema=SCHEMA) done_names = list(Base.metadata.tables.keys()) for tableName in allTables: if SCHEMA + '.' + tableName in done_names: continue className = str(tableName).upper() # create a new catalogue model class and optional manga_target_to_catalogue relational model relational_tablename = 'manga_target_to_{0}'.format(tableName) has_manga_target = relational_tablename in allTables add_catalogue(className, tableName, has_manga_target=has_manga_target) # add catalog to list of dones done_names.append(SCHEMA + '.' + tableName)
def get_field_names(self): for p in self.__mapper__.iterate_properties: yield p.key for p in sa_inspect(self.__class__).all_orm_descriptors: if type(p) == hybrid_property: yield p.__name__