Exemple #1
0
    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)
Exemple #3
0
    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)
Exemple #6
0
    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)
Exemple #7
0
        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
Exemple #10
0
    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
Exemple #11
0
    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)
Exemple #12
0
    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
Exemple #14
0
    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
Exemple #15
0
    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
Exemple #18
0
    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