def test_tree_predicate(self):
fs = FieldSpec(field_name='Family',
date_part=None,
root_table=models.Taxon,
join_path=[],
is_relation=False,
op_num=1,
value='Percidae',
negate=False,
display=True,
sort_type=0,
spqueryfieldid=None)
q, f = fs.add_to_query(orm.Query(models.Taxon.taxonId))
sql = str(q)
self.assertEqual(
sql, 'SELECT taxon."TaxonID" AS "taxon_TaxonID" \n'
'FROM taxon '
'JOIN taxon AS taxon_1 '
'ON taxon."TaxonTreeDefID" = taxon_1."TaxonTreeDefID" '
'AND taxon."NodeNumber" BETWEEN taxon_1."NodeNumber" AND taxon_1."HighestChildNodeNumber" '
'JOIN taxontreedefitem AS taxontreedefitem_1 '
'ON taxontreedefitem_1."TaxonTreeDefItemID" = taxon_1."TaxonTreeDefItemID" \n'
'WHERE taxontreedefitem_1."Name" = :Name_1 AND taxon_1."Name" = :Name_2'
)
def aggregate(self, query, field, rel_table, aggregator_name):
logger.info('aggregating field %s on %s using %s', field, rel_table,
aggregator_name)
specify_model = datamodel.get_table(field.relatedModelName,
strict=True)
aggregatorNode = self.getAggregatorDef(specify_model, aggregator_name)
if aggregatorNode is None:
logger.warn("aggregator is not defined")
return literal("<Aggregator not defined.>")
logger.debug("using aggregator: %s",
ElementTree.tostring(aggregatorNode))
formatter_name = aggregatorNode.attrib.get('format', None)
separator = aggregatorNode.attrib.get('separator', None)
order_by = aggregatorNode.attrib.get('orderfieldname', None)
orm_table = getattr(models, field.relatedModelName)
if order_by is not None and order_by != '':
order_by = getattr(orm_table, order_by)
join_column = list(
inspect(getattr(orm_table,
field.otherSideName)).property.local_columns)[0]
subquery = orm.Query([]).select_from(orm_table) \
.filter(join_column == getattr(rel_table, rel_table._id)) \
.correlate(rel_table)
subquery, formatted = self.objformat(subquery, orm_table,
formatter_name, {})
aggregated = coalesce(group_concat(formatted, separator, order_by), '')
return subquery.add_column(aggregated).as_scalar()
def __get__(self, obj, type):
try:
mapper = orm.class_mapper(type)
if mapper:
return orm.Query(mapper, session=self.sa.session())
except UnmappedClassError:
return None
def test_date_part_filter_combined(self):
fs1 = FieldSpec(field_name='startDate',
date_part='year',
root_table=models.CollectionObject,
join_path=[('collectingEvent', models.CollectingEvent)
],
is_relation=False,
op_num=1,
value='2000',
negate=False,
display=True,
sort_type=0,
spqueryfieldid=None)
fs2 = FieldSpec(field_name='lastName',
date_part=None,
root_table=models.CollectionObject,
join_path=[('cataloger', models.Agent)],
is_relation=False,
op_num=1,
value='Bentley',
negate=False,
display=True,
sort_type=0,
spqueryfieldid=None)
q = orm.Query(models.CollectionObject.collectionObjectId)
q, f1 = fs1.add_to_query(q)
q, f2 = fs2.add_to_query(q)
sql = str(q)
self.assertTrue('agent_1."LastName" = :LastName_1' in sql)
self.assertTrue(
'EXTRACT(year FROM collectingevent_1."StartDate") = :param_1' in
sql)
def _get_query_cls(mapper: Type[Base], session: so.Session) -> so.Query:
if mapper:
m = mapper
if isinstance(m, tuple):
m = mapper[0]
if isinstance(m, so.Mapper):
m = m.entity
try:
return m.__query_cls__(mapper, session) # type: ignore
except AttributeError:
pass
return so.Query(mapper, session)
def _create_query(self):
"""
Creates an instance of a SQLAlchemy query which only returns MRNs.
This query should yield all medical record numbers in the
``base_table`` of the condition. It uses the ``.clause`` to select
the relevant patients.
This query is also used by other functions which change the selected
columns to get data about the patients.
"""
return orm.Query(self.base_table).filter(
self.clause).filter(d_pers.ACTIVE_FLAG == 'Y').with_entities(
self.mrn_column).distinct()
def test_basic(self):
fs = FieldSpec(field_name='lastName',
date_part=None,
root_table=models.CollectionObject,
join_path=[('cataloger', models.Agent)],
is_relation=False,
op_num=1,
value='Bentley',
negate=False,
display=True,
sort_type=0,
spqueryfieldid=None)
q, f = fs.add_to_query(
orm.Query(models.CollectionObject.collectionObjectId))
sql = str(q)
self.assertTrue('WHERE agent_1."LastName" = :LastName_1' in sql)
def clean_duplicates(model, unique_cols):
with get_session() as session:
labeled_cols = [c.label(c.key) for c in unique_cols]
pk, *_ = model.get_primary_key()
grouped = sa_orm.Query(labeled_cols).group_by(*unique_cols).having(
sa.func.count(pk) > 1
).subquery('g')
duplicates = pd.DataFrame(
session.query(pk, *labeled_cols).join(
grouped, sa.and_(*(grouped.c[c.key] == c for c in unique_cols))
).order_by(model.updated_at).all()
)
if not duplicates.empty:
session.query(model).filter(pk.in_(
duplicates.set_index(pk.name).loc[lambda df: df.duplicated(keep='last')].index.tolist()
)).delete(synchronize_session='fetch')
try_commit(session, f'clean duplicates for {model.__tablename__}')
def test_year_equal_predicate(self):
fs = FieldSpec(field_name='startDate',
date_part='year',
root_table=models.CollectionObject,
join_path=[('collectingEvent', models.CollectingEvent)],
is_relation=False,
op_num=1,
value='2000',
negate=False,
display=True,
sort_type=0,
spqueryfieldid=None)
q, f = fs.add_to_query(
orm.Query(models.CollectionObject.collectionObjectId))
sql = str(q)
self.assertTrue(
'WHERE EXTRACT(year FROM collectingevent_1."StartDate") = :param_1'
in sql)
def _create_query(self):
"""
Creates an instance of a SQLAlchemy query which only returns MRNs.
This query should yield all medical record numbers in the
``base_table`` of the condition (fact in this case).
It uses the ``.clause`` to select the relevant patients.
This query is also used by other function which change the selected
columns to get data about the patients.
"""
q = orm.Query(self.base_table).join(
d_pers,
self.base_table.person_key == d_pers.person_key
).with_entities(
d_pers.MEDICAL_RECORD_NUMBER
).distinct()
q = q.filter(self.clause)
for dim_name in self.dimensions:
join_definition = self.dimensions_map[dim_name]
if len(join_definition) == 2:
d_table, b_table = join_definition
d_key = f'{dim_name}_key'
b_key = f'{dim_name}_group_key'
q = q.join(
b_table,
getattr(fact, b_key) == getattr(b_table, b_key)
).join(
d_table,
getattr(d_table, d_key) == getattr(b_table, d_key)
)
else:
join_table, join_key, table, key = join_definition
q = q.join(
join_table,
getattr(table, key) == getattr(join_table, join_key)
)
return q
def __get__(self, obj, type):
"""Return orm.Query object for use with model_class.get()"""
return orm.Query(type, self.scoped_session())
def main():
orm = SQLAlchemyManage("test")
users = orm.Query()
for user in users:
print user.FIRST_NAME, user.AGE
def _create_query(self):
return orm.Query(self.base_table).filter(self.clause).with_entities(
self.mrn_column).distinct()
