def get_project_labnumbers(self, project_names, filter_non_run, low_post=None, high_post=None, analysis_types=None, mass_spectrometers=None): with self.session_ctx() as sess: q = sess.query(IrradiationPositionTbl) q = q.join(SampleTbl, ProjectTbl) # filter_non_run = False if filter_non_run: if mass_spectrometers or analysis_types or low_post or high_post: q = q.join(AnalysisTbl) if mass_spectrometers: if not hasattr(mass_spectrometers, '__iter__'): mass_spectrometers = (mass_spectrometers,) q = q.filter(AnalysisTbl.mass_spectrometer.in_(mass_spectrometers)) if analysis_types: q = q.filter(AnalysisTbl.analysis_type.in_(analysis_types)) project_names.append('references') q = q.group_by(IrradiationPositionTbl.identifier) q = q.having(count(AnalysisTbl.idanalysisTbl) > 0) if low_post: q = q.filter(AnalysisTbl.timestamp >= str(low_post)) if high_post: q = q.filter(AnalysisTbl.timestamp <= str(high_post)) if project_names: q = q.filter(ProjectTbl.name.in_(project_names)) self.debug(compile_query(q)) return self._query_all(q)
def _get_selector_records(self, queries=None, limit=None, **kw): sess = self.db.get_session() q = sess.query(ScanTable) lookup = dict() q = self._assemble_query(q, queries, lookup) records = q.all() return records, compile_query(q)
def __retrieve(s): q = s.query(table) # if options: # q = q.options(subqueryload(options)) if joins: try: for ji in joins: if ji != table: q = q.join(ji) except InvalidRequestError: pass if filters is not None: for fi in filters: q = q.filter(fi) for k, v in zip(key, value): q = q.filter(getattr(table, k) == v) if last: q = q.order_by(last) if verbose_query or self.verbose_retrieve_query: self.debug(compile_query(q)) ntries = 3 import traceback for i in range(ntries): try: return q.one() except (DBAPIError, OperationalError, StatementError): self.debug(traceback.format_exc()) s.rollback() continue except MultipleResultsFound: if verbose: self.debug( 'multiples row found for {} {} {}. Trying to get last row' .format(table.__tablename__, key, value)) try: if hasattr(table, 'id'): q = q.order_by(table.id.desc()) return q.limit(1).all()[-1] except (SQLAlchemyError, IndexError, AttributeError) as e: if verbose: self.debug('no rows for {} {} {}'.format( table.__tablename__, key, value)) break except NoResultFound: if verbose and self.verbose: self.debug('no row found for {} {} {}'.format( table.__tablename__, key, value)) break
def _get_selector_records(self, queries=None, limit=None, **kw): # return self.db.get_powermaps(**kw) sess = self.db.get_session() q = sess.query(PowerMapTable) lookup = dict() q = self._assemble_query(q, queries, lookup) records = q.all() return records, compile_query(q)
def __retrieve(s): q = s.query(table) # if options: # q = q.options(subqueryload(options)) if joins: try: for ji in joins: if ji != table: q = q.join(ji) except InvalidRequestError: pass if filters is not None: for fi in filters: q = q.filter(fi) for k, v in zip(key, value): q = q.filter(getattr(table, k) == v) if last: q = q.order_by(last) if verbose_query or self.verbose_retrieve_query: self.debug(compile_query(q)) ntries = 3 import traceback for i in range(ntries): try: return q.one() except (DBAPIError, OperationalError, StatementError): self.debug(traceback.format_exc()) s.rollback() continue except MultipleResultsFound: if verbose: self.debug( 'multiples row found for {} {} {}. Trying to get last row'.format(table.__tablename__, key, value)) try: if hasattr(table, 'id'): q = q.order_by(table.id.desc()) return q.limit(1).all()[-1] except (SQLAlchemyError, IndexError, AttributeError) as e: if verbose: self.debug('no rows for {} {} {}'.format(table.__tablename__, key, value)) break except NoResultFound: if verbose and self.verbose: self.debug('no row found for {} {} {}'.format(table.__tablename__, key, value)) break
def _query(self, q, func, reraise=False, verbose_query=False): if verbose_query: self.debug(compile_query(q)) # print compile_query(q) f = getattr(q, func) try: return f() except SQLAlchemyError, e: if reraise: raise e
def _query(self, q, func, reraise=False, verbose_query=False): if verbose_query: self.debug(compile_query(q)) # print compile_query(q) f = getattr(q, func) try: return f() except NoResultFound: if verbose_query: self.info('no results found for query -- {}'.format( compile_query(q))) except SQLAlchemyError as e: if self.verbose: self.debug('_query exception {}'.format(e)) self.rollback() self.reset_connection() self.connect() if reraise: raise e
def _query(self, q, func, reraise=False, verbose_query=False): if verbose_query: self.debug(compile_query(q)) # print compile_query(q) f = getattr(q, func) try: return f() except SQLAlchemyError as e: if self.verbose: self.debug('_query exception {}'.format(e)) if reraise: raise e
def _get_records(self, q, queries, limit, timestamp='timestamp'): if queries: q = self._assemble_query(q, queries, self.lookup) tattr = getattr(self.query_table, timestamp) q = q.order_by(tattr.desc()) if limit and limit > 0: q = q.limit(limit) q = q.from_self() q = q.order_by(tattr.asc()) records = q.all() return records, compile_query(q)
def _assemble_query(self, q, queries, lookup): joined = [] ands = [] ors = [] for qi in queries: if not qi.use: continue if not qi.criterion: continue if lookup.has_key(qi.parameter): tabs, attr = lookup[qi.parameter] for tab in tabs: if not tab in joined: joined.append(tab) q = q.join(tab) try: f, is_or = qi.assemble_filter(q, attr) except ValueError: self.warning_dialog('Invalid query "{}", "{}"'.format( qi.parameter, attr)) return if is_or: ors.append(f) else: ands.append(f) if ands: fs = and_(*ands) if ors: fs = or_(fs, *ors) elif ors: fs = or_(*ors) q = q.filter(fs) self.debug('Query={}'.format(compile_query(q))) return q
def _assemble_query(self, q, queries, lookup): joined = [] ands = [] ors = [] for qi in queries: if not qi.use: continue if not qi.criterion: continue if lookup.has_key(qi.parameter): tabs, attr = lookup[qi.parameter] for tab in tabs: if not tab in joined: joined.append(tab) q = q.join(tab) try: f, is_or = qi.assemble_filter(q, attr) except ValueError: self.warning_dialog('Invalid query "{}", "{}"'.format(qi.parameter, attr)) return if is_or: ors.append(f) else: ands.append(f) if ands: fs = and_(*ands) if ors: fs=or_(fs, *ors) elif ors: fs=or_(*ors) q = q.filter(fs) self.debug('Query={}'.format(compile_query(q))) return q
def get_project_labnumbers(self, project_names, filter_non_run, low_post=None, high_post=None, analysis_types=None, mass_spectrometers=None): with self.session_ctx() as sess: q = sess.query(IrradiationPositionTbl) q = q.join(SampleTbl, ProjectTbl) # filter_non_run = False if filter_non_run: if mass_spectrometers or analysis_types or low_post or high_post: q = q.join(AnalysisTbl) if mass_spectrometers: if not hasattr(mass_spectrometers, '__iter__'): mass_spectrometers = (mass_spectrometers, ) q = q.filter( AnalysisTbl.mass_spectrometer.in_(mass_spectrometers)) if analysis_types: q = q.filter(AnalysisTbl.analysis_type.in_(analysis_types)) project_names.append('references') q = q.group_by(IrradiationPositionTbl.identifier) q = q.having(count(AnalysisTbl.idanalysisTbl) > 0) if low_post: q = q.filter(AnalysisTbl.timestamp >= str(low_post)) if high_post: q = q.filter(AnalysisTbl.timestamp <= str(high_post)) if project_names: q = q.filter(ProjectTbl.name.in_(project_names)) self.debug(compile_query(q)) return self._query_all(q)
def _retrieve_items(self, table, joins=None, filters=None, limit=None, order=None, distinct_=False, query_hook=None, reraise=False, func='all', group_by=None, verbose_query=False): sess = self.sess if sess is None: if self.session_factory: sess = self.session_factory() with self.session_ctx(sess): # print 'get items', sess, self.session_factory # sess = self.get_session() # if sess is not None: if distinct_: if isinstance(distinct_, bool): q = sess.query(distinct(table)) else: q = sess.query(distinct(distinct_)) elif isinstance(table, tuple): q = sess.query(*table) else: q = sess.query(table) if joins: # print joins # joins = list(set(joins)) # print joins try: for ji in joins: if ji != table: q = q.join(ji) except InvalidRequestError: if reraise: raise if filters is not None: for fi in filters: q = q.filter(fi) if order is not None: if not isinstance(order, tuple): order = (order,) q = q.order_by(*order) if group_by is not None: if not isinstance(order, tuple): group_by = (group_by,) q = q.group_by(*group_by) if limit is not None: q = q.limit(limit) if query_hook: q = query_hook(q) if verbose_query or self.verbose_retrieve_query: # print compile_query(q) self.debug(compile_query(q)) return self._query(q, func, reraise)
def _retrieve_items(self, table, joins=None, filters=None, limit=None, order=None, distinct_=False, query_hook=None, reraise=False, func='all', group_by=None, verbose_query=False): sess = self.session if sess is None or isinstance(sess, MockSession): self.debug('USING MOCKSESSION************** {}'.format(sess)) return [] if distinct_: if isinstance(distinct_, bool): q = sess.query(distinct(table)) else: q = sess.query(distinct(distinct_)) elif isinstance(table, tuple): q = sess.query(*table) else: q = sess.query(table) if joins: try: for ji in joins: if ji != table: q = q.join(ji) except InvalidRequestError: if reraise: raise if filters is not None: for fi in filters: q = q.filter(fi) if order is not None: if not isinstance(order, tuple): order = (order, ) q = q.order_by(*order) if group_by is not None: if not isinstance(order, tuple): group_by = (group_by, ) q = q.group_by(*group_by) if limit is not None: q = q.limit(limit) if query_hook: q = query_hook(q) if verbose_query or self.verbose_retrieve_query: # print compile_query(q) self.debug(compile_query(q)) items = self._query(q, func, reraise) if items is None: items = [] return items
def _retrieve_items(self, table, joins=None, filters=None, limit=None, order=None, distinct_=False, query_hook=None, reraise=False, func='all', group_by=None, verbose_query=False): sess = self.session if sess is None or isinstance(sess, MockSession): self.debug('USING MOCKSESSION************** {}'.format(sess)) return [] if distinct_: if isinstance(distinct_, bool): q = sess.query(distinct(table)) else: q = sess.query(distinct(distinct_)) elif isinstance(table, tuple): q = sess.query(*table) else: q = sess.query(table) if joins: try: for ji in joins: if ji != table: q = q.join(ji) except InvalidRequestError: if reraise: raise if filters is not None: for fi in filters: q = q.filter(fi) if order is not None: if not isinstance(order, tuple): order = (order,) q = q.order_by(*order) if group_by is not None: if not isinstance(order, tuple): group_by = (group_by,) q = q.group_by(*group_by) if limit is not None: q = q.limit(limit) if query_hook: q = query_hook(q) if verbose_query or self.verbose_retrieve_query: # print compile_query(q) self.debug(compile_query(q)) items = self._query(q, func, reraise) if items is None: items = [] return items