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