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__
