コード例 #1
0
def monitoredSitesLocality(request):

	req = request.POST

	if 'name_view' in req :
		print('name_view')
		try :
			proto_view_Table=Base.metadata.tables[proto_view_Name]
			join_table=join(proto_view_Table, Station, proto_view_Table.c['TSta_PK_ID'] == Station.id )

		except :
			
			proto_view_Table=dict_proto[proto_view_Name]()
			join_table=join(proto_view_Table, Station, proto_view_Table.FK_TSta_ID == Station.id )

		slct=select([Station.locality]).distinct().select_from(join_table)
		data = DBSession.execute(slct).fetchall()
		return [row['Place' or 'Locality'] for row in data]
	else :
		if 'Region' in req :
			query=select([Station.locality]).distinct().where(Station.area==req.get('Region'))
		else :
			query=select([Station.locality]).distinct()
		data=DBSession.execute(query).fetchall()
		return [row[0] for row in data]
コード例 #2
0
ファイル: fetch.py プロジェクト: inklesspen/mimir
def determine_fetches(db_session, cred):
    for thread in db_session.query(Thread).filter_by(closed=False):
        update_thread_status(thread, cred)
    db_session.flush()
    incomplete_page_ids = (
        sa.select([ThreadPost.page_id])
        .group_by(ThreadPost.page_id)
        .having(sa.func.count(ThreadPost.id) < 40)
        .as_scalar()
    )
    incomplete_pages = sa.select(
        [ThreadPage.thread_id, ThreadPage.page_num], from_obj=sa.join(ThreadPage, Thread)
    ).where(sa.and_(ThreadPage.id.in_(incomplete_page_ids), Thread.closed == sa.false()))
    fetch_status = (
        sa.select(
            [ThreadPage.thread_id.label("thread_id"), sa.func.max(ThreadPage.page_num).label("last_fetched_page")]
        )
        .group_by(ThreadPage.thread_id)
        .alias("fetch_status")
    )
    unfetched_pages = sa.select(
        [
            Thread.id.label("thread_id"),
            sa.func.generate_series(fetch_status.c.last_fetched_page + 1, Thread.page_count).label("page_num"),
        ],
        from_obj=sa.join(Thread, fetch_status, Thread.id == fetch_status.c.thread_id),
    )
    fetched_first_pages = sa.select([ThreadPage.thread_id]).where(ThreadPage.page_num == 1).as_scalar()
    unfetched_first_pages = sa.select(
        [Thread.id.label("thread_id"), sa.literal(1, sa.Integer).label("page_num")], from_obj=Thread
    ).where(Thread.id.notin_(fetched_first_pages))
    q = sa.union(incomplete_pages, unfetched_pages, unfetched_first_pages)
    q = q.order_by(q.c.thread_id.asc(), q.c.page_num.asc())
    return db_session.execute(q).fetchall()
コード例 #3
0
def getIndivEquipment(request):
    session = request.dbsession
    id_indiv = request.matchdict['id']

    table = Base.metadata.tables['IndividualEquipment']
    joinTable = join(table, Sensor, table.c['FK_Sensor'] == Sensor.ID)
    joinTable = join(joinTable, SensorType,
                     Sensor.FK_SensorType == SensorType.ID)
    query = select([table.c['StartDate'], table.c['EndDate'], Sensor.UnicIdentifier, Sensor.ID.label('SensorID'), table.c['FK_Individual'], SensorType.Name.label('Type')]
                   ).select_from(joinTable
                                 ).where(table.c['FK_Individual'] == id_indiv
                                         ).order_by(desc(table.c['StartDate']))

    result = session.execute(query).fetchall()
    response = []
    for row in result:
        curRow = OrderedDict(row)
        curRow['StartDate'] = curRow['StartDate'].strftime('%Y-%m-%d %H:%M:%S')
        if curRow['EndDate'] is not None:
            curRow['EndDate'] = curRow['EndDate'].strftime('%Y-%m-%d %H:%M:%S')
        else:
            curRow['EndDate'] = ''
        response.append(curRow)

    return response
コード例 #4
0
def monitoredSitesArea(request):

	req = request.POST

	if 'name_view' in req :
		print('name_view')
		try :
			proto_view_Table = Base.metadata.tables[proto_view_Name]
			join_table = join(proto_view_Table, Station, proto_view_Table.c['TSta_PK_ID'] == Station.id )
		except :
			proto_view_Table = dict_proto[proto_view_Name]()
			join_table = join(proto_view_Table, Station, proto_view_Table.FK_TSta_ID == Station.id )

		print (proto_view_Table)

	
		slct = select([Station.area]).distinct().select_from(join_table)
		data = DBSession.execute(slct).fetchall()
		return [row['Region' or 'Area'] for row in data]


	else :
		table = Base.metadata.tables['geo_CNTRIES_and_RENECO_MGMTAreas']
		slct = select([table.c['Place']]).distinct()
		data =  DBSession.execute(slct).fetchall()
		return [row[0] for row in data]
コード例 #5
0
ファイル: List.py プロジェクト: FredericBerton/ecoReleve-Data
    def GetFlatDataList(self,searchInfo=None,getFieldWorkers=True) :
        ''' Override parent function to include management of Observation/Protocols and fieldWorkers '''
        fullQueryJoinOrdered = self.GetFullQuery(searchInfo)
        result = self.ObjContext.execute(fullQueryJoinOrdered).fetchall()
        data = []

        if getFieldWorkers:
            # listID = list(map(lambda x: x['ID'],result))
            queryCTE = fullQueryJoinOrdered.cte()
            joinFW = join(Station_FieldWorker,User,Station_FieldWorker.FK_FieldWorker==User.id)
            joinTable = join(queryCTE,joinFW,queryCTE.c['ID']== Station_FieldWorker.FK_Station)

            query = select([Station_FieldWorker.FK_Station,User.Login]).select_from(joinTable)
            FieldWorkers = self.ObjContext.execute(query).fetchall()
        
            list_ = {}
            for x,y in FieldWorkers :
                list_.setdefault(x,[]).append(y)
            for row in result :
                row = OrderedDict(row)
                try :
                    row['FK_FieldWorker_FieldWorkers'] = list_[row['ID']]
                except:
                    pass
                data.append(row)
        else:
            for row in result :
                row = OrderedDict(row)
                data.append(row)
        return data
コード例 #6
0
    def test_moving_plugin_attributes(self):
        clusters = self.meta.tables['clusters']
        attributes = self.meta.tables['attributes']
        plugins = self.meta.tables['plugins']
        cluster_plugins = self.meta.tables['cluster_plugins']

        query = sa.select([attributes.c.editable])\
            .select_from(
                sa.join(
                    attributes, clusters,
                    attributes.c.cluster_id == clusters.c.id))
        result = jsonutils.loads(db.execute(query).fetchone()[0])
        self.assertItemsEqual(result, {})

        query = sa.select([cluster_plugins.c.attributes])\
            .select_from(
                sa.join(
                    cluster_plugins, plugins,
                    cluster_plugins.c.plugin_id == plugins.c.id))\
            .where(plugins.c.name == 'test_plugin_a')
        result = jsonutils.loads(db.execute(query).fetchone()[0])
        self.assertNotIn('metadata', result)
        self.assertItemsEqual(result['attribute'], {
            'value': 'value',
            'type': 'text',
            'description': 'description',
            'weight': 25,
            'label': 'label'
        })
コード例 #7
0
ファイル: sensor.py プロジェクト: romfabbro/ecoReleve-Data
def getSensorEquipment(request):
    session = request.dbsession
    id = request.matchdict['id']
    curSensor = session.query(Sensor).get(id)
    curSensorType = curSensor.GetType().Name

    if ('RFID' in curSensorType.upper()):
        table = Base.metadata.tables['MonitoredSiteEquipment']
        joinTable = join(table, Sensor, table.c['FK_Sensor'] == Sensor.ID)
        joinTable = join(joinTable, MonitoredSite, table.c[
                         'FK_MonitoredSite'] == MonitoredSite.ID)
        query = select([table.c['StartDate'], table.c['EndDate'], Sensor.UnicIdentifier, MonitoredSite.Name, MonitoredSite.ID.label('MonitoredSiteID')]).select_from(joinTable
                                                                                                                                                                     ).where(table.c['FK_Sensor'] == id
                                                                                                                                                                             ).order_by(desc(table.c['StartDate']))

    elif (curSensorType.lower() in ['gsm', 'satellite', 'vhf']):
        table = Base.metadata.tables['IndividualEquipment']
        joinTable = join(table, Sensor, table.c['FK_Sensor'] == Sensor.ID)
        query = select([table.c['StartDate'], table.c['EndDate'], table.c['FK_Individual'], Sensor.UnicIdentifier]).select_from(joinTable
                                                                                                                                ).where(table.c['FK_Sensor'] == id
                                                                                                                                        ).order_by(desc(table.c['StartDate']))
    else:
        return 'bad request'

    result = session.execute(query).fetchall()
    response = []
    for row in result:
        curRow = OrderedDict(row)
        curRow['StartDate'] = curRow['StartDate'].strftime('%Y-%m-%d %H:%M:%S')
        curRow['EndDate'] = curRow['EndDate'].strftime(
            '%Y-%m-%d %H:%M:%S') if curRow['EndDate'] is not None else None
        curRow['format'] = 'YYYY-MM-DD HH:mm:ss'
        response.append(curRow)

    return response
コード例 #8
0
    def getEquipment(self):
        id_site = self.objectDB.ID
        table = Base.metadata.tables['MonitoredSiteEquipment']

        joinTable = join(table, Sensor, table.c['FK_Sensor'] == Sensor.ID)
        joinTable = join(joinTable, SensorType,
                         Sensor.FK_SensorType == SensorType.ID)
        query = select([table.c['StartDate'],
                        table.c['EndDate'],
                        Sensor.UnicIdentifier,
                        table.c['FK_MonitoredSite'],
                        SensorType.Name.label('Type')]
                       ).select_from(joinTable
                                     ).where(table.c['FK_MonitoredSite'] == id_site
                                             ).order_by(desc(table.c['StartDate']))

        result = self.session.execute(query).fetchall()
        response = []
        for row in result:
            curRow = OrderedDict(row)
            curRow['StartDate'] = curRow['StartDate'].strftime('%Y-%m-%d %H:%M:%S')
            if curRow['EndDate'] is not None:
                curRow['EndDate'] = curRow['EndDate'].strftime('%Y-%m-%d %H:%M:%S')
            else:
                curRow['EndDate'] = ''
            response.append(curRow)

        return response
コード例 #9
0
ファイル: propagation.py プロジェクト: egorhm/ggrc-core
def _rel_child(parent_acl_ids, source=True):
  """Get left side of relationships mappings through source."""
  rel_table = all_models.Relationship.__table__
  acl_table = all_models.AccessControlList.__table__
  parent_acr = all_models.AccessControlRole.__table__.alias(
      "parent_acr_{}".format(source)
  )
  child_acr = all_models.AccessControlRole.__table__.alias(
      "child_acr_{}".format(source)
  )

  if source:
    object_id = rel_table.c.destination_id
    object_type = rel_table.c.destination_type
  else:
    object_id = rel_table.c.source_id
    object_type = rel_table.c.source_type

  acl_link = sa.and_(
      acl_table.c.object_id == rel_table.c.id,
      acl_table.c.object_type == all_models.Relationship.__name__,
  )

  select_statement = sa.select([
      acl_table.c.person_id.label("person_id"),
      child_acr.c.id.label("ac_role_id"),
      object_id.label("object_id"),
      object_type.label("object_type"),
      sa.func.now().label("created_at"),
      sa.literal(login.get_current_user_id()).label("modified_by_id"),
      sa.func.now().label("updated_at"),
      acl_table.c.id.label("parent_id"),
      acl_table.c.id.label("parent_id_nn"),
  ]).select_from(
      sa.join(
          sa.join(
              sa.join(
                  rel_table,
                  acl_table,
                  acl_link
              ),
              parent_acr,
              parent_acr.c.id == acl_table.c.ac_role_id
          ),
          child_acr,
          child_acr.c.parent_id == parent_acr.c.id
      )
  ).where(
      sa.and_(
          acl_table.c.id.in_(parent_acl_ids),
          child_acr.c.object_type == object_type,
      )
  )
  return select_statement
コード例 #10
0
ファイル: jobs.py プロジェクト: ImmPortDB/immport-galaxy
    def user_per_month(self, trans, **kwd):
        params = util.Params(kwd)
        message = ''

        email = util.restore_text(params.get('email', ''))
        specs = sorter('date', kwd)
        sort_id = specs.sort_id
        order = specs.order
        arrow = specs.arrow
        _order = specs.exc_order

        q = sa.select((self.select_month(model.Job.table.c.create_time).label('date'),
                       sa.func.count(model.Job.table.c.id).label('total_jobs')),
                      whereclause=model.User.table.c.email == email,
                      from_obj=[sa.join(model.Job.table, model.User.table)],
                      group_by=self.group_by_month(model.Job.table.c.create_time),
                      order_by=[_order])

        all_jobs_per_user = sa.select((model.Job.table.c.create_time.label('date'),
                                       model.Job.table.c.id.label('job_id')),
                                      whereclause=sa.and_(model.User.table.c.email == email),
                                      from_obj=[sa.join(model.Job.table, model.User.table)])

        trends = dict()
        for job in all_jobs_per_user.execute():
            job_day = int(job.date.strftime("%-d")) - 1
            job_month = int(job.date.strftime("%-m"))
            job_month_name = job.date.strftime("%B")
            job_year = job.date.strftime("%Y")
            key = str(job_month_name + job_year)

            try:
                trends[key][job_day] += 1
            except KeyError:
                job_year = int(job_year)
                wday, day_range = calendar.monthrange(job_year, job_month)
                trends[key] = [0] * day_range
                trends[key][job_day] += 1

        jobs = []
        for row in q.execute():
            jobs.append((row.date.strftime("%Y-%m"),
                         row.total_jobs,
                         row.date.strftime("%B"),
                         row.date.strftime("%Y")))
        return trans.fill_template('/webapps/reports/jobs_user_per_month.mako',
                                   order=order,
                                   arrow=arrow,
                                   sort_id=sort_id,
                                   id=kwd.get('id'),
                                   trends=trends,
                                   email=util.sanitize_text(email),
                                   jobs=jobs, message=message)
コード例 #11
0
ファイル: workflow.py プロジェクト: egorhm/ggrc-core
def _propagate_to_wf_children(new_wf_acls, child_class):
  """Propagate newly added roles to workflow objects.

  Args:
    wf_new_acl: list of all newly created acl entries for workflows

  Returns:
    list of newly created acl entries for task groups.
  """

  child_table = child_class.__table__
  acl_table = all_models.AccessControlList.__table__
  acr_table = all_models.AccessControlRole.__table__.alias("parent_acr")
  acr_mapped_table = all_models.AccessControlRole.__table__.alias("mapped")

  current_user_id = login.get_current_user_id()

  select_statement = sa.select([
      acl_table.c.person_id,
      acr_mapped_table.c.id,
      child_table.c.id,
      sa.literal(child_class.__name__),
      sa.func.now(),
      sa.literal(current_user_id),
      sa.func.now(),
      acl_table.c.id.label("parent_id"),
      acl_table.c.id.label("parent_id_nn"),
  ]).select_from(
      sa.join(
          sa.join(
              sa.join(
                  child_table,
                  acl_table,
                  sa.and_(
                      acl_table.c.object_id == child_table.c.workflow_id,
                      acl_table.c.object_type == all_models.Workflow.__name__,
                  )
              ),
              acr_table,
          ),
          acr_mapped_table,
          acr_mapped_table.c.name == sa.func.concat(
              acr_table.c.name, " Mapped")
      )
  ).where(
      acl_table.c.id.in_(new_wf_acls)
  )

  acl_utils.insert_select_acls(select_statement)

  return _get_child_ids(new_wf_acls, child_class)
コード例 #12
0
ファイル: messages.py プロジェクト: docstack/marconi
    def bulk_get(self, queue, message_ids, project):
        if project is None:
            project = ''

        message_ids = [id for id in
                       map(utils.msgid_decode, message_ids)
                       if id is not None]

        statement = sa.sql.select([tables.Messages.c.id,
                                   tables.Messages.c.body,
                                   tables.Messages.c.ttl,
                                   tables.Messages.c.created])

        and_stmt = [tables.Messages.c.id.in_(message_ids),
                    tables.Queues.c.name == queue,
                    tables.Queues.c.project == project,
                    tables.Messages.c.ttl >
                    sfunc.now() - tables.Messages.c.created]

        j = sa.join(tables.Messages, tables.Queues,
                    tables.Messages.c.qid == tables.Queues.c.id)

        statement = statement.select_from(j).where(sa.and_(*and_stmt))

        now = timeutils.utcnow_ts()
        records = self.driver.run(statement)
        for id, body, ttl, created in records:
            yield {
                'id': utils.msgid_encode(id),
                'ttl': ttl,
                'age': now - calendar.timegm(created.timetuple()),
                'body': json.loads(body),
            }
コード例 #13
0
ファイル: messages.py プロジェクト: docstack/marconi
    def get(self, queue, message_id, project, count=False):

        if project is None:
            project = ''

        mid = utils.msgid_decode(message_id)

        if mid is None:
            raise errors.MessageDoesNotExist(message_id, queue, project)

        try:
            j = sa.join(tables.Messages, tables.Queues,
                        tables.Messages.c.qid == tables.Queues.c.id)

            sel = sa.sql.select([tables.Messages.c.body,
                                 tables.Messages.c.ttl,
                                 tables.Messages.c.created])

            if count:
                sel = sa.sql.select([sfunc.count(tables.Messages.c.id)])

            sel = sel.select_from(j)
            sel = sel.where(sa.and_(tables.Messages.c.id == mid,
                                    tables.Queues.c.project == project,
                                    tables.Queues.c.name == queue,
                                    tables.Messages.c.ttl >
                                    sfunc.now() - tables.Messages.c.created))

            return self.driver.get(sel)[0]
        except utils.NoResult:
            raise errors.MessageDoesNotExist(message_id, queue, project)
コード例 #14
0
ファイル: individual.py プロジェクト: gerald13/ecoReleve-Data
def getIndivHistory(request):
    session = request.dbsession
    id = request.matchdict['id']

    tableJoin = join(IndividualDynPropValue,IndividualDynProp
        ,IndividualDynPropValue.FK_IndividualDynProp == IndividualDynProp.ID)
    query = select([IndividualDynPropValue,IndividualDynProp.Name]).select_from(tableJoin).where(
        IndividualDynPropValue.FK_Individual == id
        ).order_by(desc(IndividualDynPropValue.StartDate))

    result = session.execute(query).fetchall()
    response = []
    for row in result:
        curRow = OrderedDict(row)
        dictRow = {}
        for key in curRow :
            if curRow[key] is not None :
                if 'Value' in key :
                    dictRow['value'] = curRow[key] 
                elif 'FK' not in key :
                    dictRow[key] = curRow[key]
        dictRow['StartDate'] = curRow['StartDate'].strftime('%Y-%m-%d %H:%M:%S')
        response.append(dictRow)

    return response
コード例 #15
0
ファイル: messages.py プロジェクト: gashe5363/zaqar
    def bulk_get(self, queue, message_ids, project):
        if project is None:
            project = ''

        message_ids = [id for id in
                       map(utils.msgid_decode, message_ids)
                       if id is not None]

        statement = sa.sql.select([tables.Messages.c.id,
                                   tables.Messages.c.body,
                                   tables.Messages.c.ttl,
                                   tables.Messages.c.created,
                                   tables.Messages.c.cid])

        and_stmt = [tables.Messages.c.id.in_(message_ids)]
        and_stmt.extend(self._and_stmt_with_ttl(queue, project))

        j = sa.join(tables.Messages, tables.Queues,
                    tables.Messages.c.qid == tables.Queues.c.id)

        statement = statement.select_from(j).where(sa.and_(*and_stmt))

        now = timeutils.utcnow_ts()
        records = self.driver.run(statement)
        for id, body, ttl, created, cid in records:
            yield {
                'id': utils.msgid_encode(int(id)),
                'ttl': ttl,
                'age': now - calendar.timegm(created.timetuple()),
                'body': utils.json_decode(body),
                'claim_id': utils.cid_encode(cid) if cid else None,
            }
コード例 #16
0
ファイル: buildsets.py プロジェクト: jhnwsk/buildbot
        def thd(conn):
            bs_tbl = self.db.model.buildsets
            ch_tbl = self.db.model.changes
            j = sa.join(self.db.model.buildsets,
                               self.db.model.sourcestampsets)
            j = j.join(self.db.model.sourcestamps)
            j = j.join(self.db.model.sourcestamp_changes)
            j = j.join(ch_tbl)
            q = sa.select(columns=[bs_tbl], from_obj=[j],
                                         distinct=True)
            q = q.order_by(sa.desc(bs_tbl.c.id))
            q = q.limit(count)

            if complete is not None:
                if complete:
                    q = q.where(bs_tbl.c.complete != 0)
                else:
                    q = q.where((bs_tbl.c.complete == 0) |
                                (bs_tbl.c.complete == None))
            if branch:
              q = q.where(ch_tbl.c.branch == branch)
            if repository:
              q = q.where(ch_tbl.c.repository == repository)
            res = conn.execute(q)
            return list(reversed([ self._row2dict(row)
                                  for row in res.fetchall() ]))
コード例 #17
0
ファイル: messages.py プロジェクト: gashe5363/zaqar
    def _get(self, queue, message_id, project, count=False):

        if project is None:
            project = ''

        mid = utils.msgid_decode(message_id)

        if mid is None:
            raise errors.MessageDoesNotExist(message_id, queue, project)

        try:
            j = sa.join(tables.Messages, tables.Queues,
                        tables.Messages.c.qid == tables.Queues.c.id)

            sel = sa.sql.select([tables.Messages.c.body,
                                 tables.Messages.c.ttl,
                                 tables.Messages.c.created,
                                 tables.Messages.c.cid])

            if count:
                sel = sa.sql.select([sfunc.count(tables.Messages.c.id)])

            sel = sel.select_from(j)
            and_stmt = [tables.Messages.c.id == mid]
            and_stmt.extend(self._and_stmt_with_ttl(queue, project))

            sel = sel.where(sa.and_(*and_stmt))

            return self.driver.get(sel)
        except utils.NoResult:
            raise errors.MessageDoesNotExist(message_id, queue, project)
コード例 #18
0
ファイル: app.py プロジェクト: dmc2015/moxie
def job(request, name):
    engine = yield from aiopg.sa.create_engine(DATABASE_URL)
    with (yield from engine) as conn:

        jobs = yield from conn.execute(select(
            [Job.__table__, Maintainer.__table__,],
            use_labels=True
        ).select_from(join(
            Maintainer.__table__,
            Job.__table__,
            Maintainer.id == Job.maintainer_id
        )).where(Job.name == name).limit(1))
        job = yield from jobs.first()

        runs = yield from conn.execute(
            select([Run.id, Run.failed, Run.start_time, Run.end_time]).
            where(Run.job_id == job.job_id).
            order_by(Run.id.desc())
        )

        return request.render('job.html', {
            "job": job,
            "runs": runs,
            "next_run": humanize.naturaltime(job.job_scheduled),
        })
コード例 #19
0
ファイル: statistics.py プロジェクト: gerald13/ecoReleve-Data
def location_graph(request):
    session = request.dbsession
    joinTable = join(Individual_Location, Sensor, Individual_Location.FK_Sensor == Sensor.ID)
    data = []
    query = select([Individual_Location.type_, func.count("*").label("nb")]).group_by(Individual_Location.type_)

    global graphDataDate
    global indivLocationData

    d = datetime.datetime.now() - datetime.timedelta(days=1)

    if graphDataDate["indivLocationData"] is None or graphDataDate["indivLocationData"] < d:
        graphDataDate["indivLocationData"] = datetime.datetime.now()
        for row in session.execute(query).fetchall():
            curRow = OrderedDict(row)
            lab = curRow["type_"].upper()
            if "ARG" in lab:
                try:
                    nbArg = nbArg + curRow["nb"]
                except:
                    nbArg = curRow["nb"]
            else:
                data.append({"value": curRow["nb"], "label": lab})
        data.append({"value": nbArg, "label": "ARGOS"})
        data.sort(key=itemgetter("label"))
        indivLocationData = data

    else:
        print("indiv loc already fetched")
    return indivLocationData
コード例 #20
0
ファイル: user.py プロジェクト: agdsn/pycroft
 def active_property_groups(cls, when=None):
     return select([PropertyGroup]).select_from(
         join(PropertyGroup,
              Membership).join(cls)
     ).where(
         Membership.active(when)
     )
コード例 #21
0
ファイル: user.py プロジェクト: agdsn/pycroft
 def active_traffic_groups(cls, when=None):
     return select([TrafficGroup]).select_from(
         join(TrafficGroup,
              Membership).join(cls)
     ).where(
         Membership.active(when)
     )
コード例 #22
0
ファイル: stats.py プロジェクト: PublicaMundi/ckan
 def top_tags(cls, limit=10, returned_tag_info='object'): # by package
     assert returned_tag_info in ('name', 'id', 'object')
     tag = table('tag')
     package_tag = table('package_tag')
     package = table('package')
     if returned_tag_info == 'name':
         from_obj = [package_tag.join(tag)]
         tag_column = tag.c.name
     else:
         from_obj = None
         tag_column = package_tag.c.tag_id
     j = join(package_tag, package,
              package_tag.c.package_id == package.c.id)
     s = select([tag_column, func.count(package_tag.c.package_id)],
                 from_obj=from_obj).\
         select_from(j).\
         where(and_(package_tag.c.state=='active', package.c.private == False, package.c.state == 'active' ))
     s = s.group_by(tag_column).\
         order_by(func.count(package_tag.c.package_id).desc()).\
         limit(limit)
     res_col = model.Session.execute(s).fetchall()
     if returned_tag_info in ('id', 'name'):
         return res_col
     elif returned_tag_info == 'object':
         res_tags = [(model.Session.query(model.Tag).get(text_type(tag_id)), val) for tag_id, val in res_col]
         return res_tags
コード例 #23
0
    def test_nested_joins(self):
        task, Task_Type, Joined, prj, task_type, msg = (self.tables.task,
                                                        self.classes.Task_Type,
                                                        self.classes.Joined,
                                                        self.tables.prj,
                                                        self.tables.task_type,
                                                        self.tables.msg)

        # this is testing some subtle column resolution stuff,
        # concerning corresponding_column() being extremely accurate
        # as well as how mapper sets up its column properties

        mapper(Task_Type, task_type)

        tsk_cnt_join = sa.outerjoin(prj, task, task.c.prj_id == prj.c.id)

        j = sa.outerjoin(task, msg, task.c.id == msg.c.task_id)
        jj = sa.select([task.c.id.label('task_id'),
                        sa.func.count(msg.c.id).label('props_cnt')],
                       from_obj=[j],
                       group_by=[task.c.id]).alias('prop_c_s')
        jjj = sa.join(task, jj, task.c.id == jj.c.task_id)

        mapper(Joined, jjj, properties=dict(
            type=relationship(Task_Type, lazy='joined')))

        session = create_session()

        eq_(session.query(Joined).limit(10).offset(0).one(),
            Joined(id=1, title='task 1', props_cnt=0))
コード例 #24
0
    def test_cross_schema_reflection_one(self):

        meta1 = self.metadata

        users = Table('users', meta1,
                      Column('user_id', Integer, primary_key=True),
                      Column('user_name', String(30), nullable=False),
                      schema='test_schema')
        addresses = Table(
            'email_addresses', meta1,
            Column(
                'address_id', Integer, primary_key=True),
            Column(
                'remote_user_id', Integer, ForeignKey(
                    users.c.user_id)),
                Column(
                    'email_address', String(20)), schema='test_schema')
        meta1.create_all()
        meta2 = MetaData(testing.db)
        addresses = Table('email_addresses', meta2, autoload=True,
                          schema='test_schema')
        users = Table('users', meta2, mustexist=True,
                      schema='test_schema')
        j = join(users, addresses)
        self.assert_((users.c.user_id
                      == addresses.c.remote_user_id).compare(j.onclause))
コード例 #25
0
    def test_composite_fk(self):
        """test reflection of composite foreign keys"""

        meta = MetaData(testing.db)
        multi = Table(
            'multi', meta,
            Column('multi_id', sa.Integer, primary_key=True),
            Column('multi_rev', sa.Integer, primary_key=True),
            Column('multi_hoho', sa.Integer, primary_key=True),
            Column('name', sa.String(50), nullable=False),
            Column('val', sa.String(100)),
            test_needs_fk=True,
        )
        multi2 = Table('multi2', meta,
            Column('id', sa.Integer, primary_key=True),
            Column('foo', sa.Integer),
            Column('bar', sa.Integer),
            Column('lala', sa.Integer),
            Column('data', sa.String(50)),
            sa.ForeignKeyConstraint(['foo', 'bar', 'lala'], ['multi.multi_id', 'multi.multi_rev', 'multi.multi_hoho']),
            test_needs_fk=True,
        )
        meta.create_all()

        try:
            meta2 = MetaData()
            table = Table('multi', meta2, autoload=True, autoload_with=testing.db)
            table2 = Table('multi2', meta2, autoload=True, autoload_with=testing.db)
            self.assert_tables_equal(multi, table)
            self.assert_tables_equal(multi2, table2)
            j = sa.join(table, table2)
            self.assert_(sa.and_(table.c.multi_id==table2.c.foo, table.c.multi_rev==table2.c.bar, table.c.multi_hoho==table2.c.lala).compare(j.onclause))
        finally:
            meta.drop_all()
コード例 #26
0
ファイル: migration.py プロジェクト: AnyBlok/AnyBlok
    def apply_default_value(self, column):
        if column.default:
            execute = self.table.migration.conn.execute
            val = column.default.arg
            table = self.table.migration.metadata.tables[self.table.name]
            table.append_column(column)
            cname = getattr(table.c, column.name)
            if column.default.is_callable:
                Table = self.table.migration.metadata.tables['system_model']
                Column = self.table.migration.metadata.tables['system_column']
                j1 = join(Table, Column, Table.c.name == Column.c.model)
                query = select([Column.c.name]).select_from(j1)
                query = query.where(Column.c.primary_key.is_(True))
                query = query.where(Table.c.table == self.table.name)
                columns = [x[0] for x in execute(query).fetchall()]

                query = select([func.count()]).select_from(table)
                query = query.where(cname.is_(None))
                nb_row = self.table.migration.conn.execute(query).fetchone()[0]
                for offset in range(nb_row):
                    query = select(columns).select_from(table)
                    query = query.where(cname.is_(None)).limit(1)
                    res = execute(query).fetchone()
                    where = and_(
                        *[getattr(table.c, x) == res[x] for x in columns])
                    query = update(table).where(where).values(
                        {cname: val(None)})
                    execute(query)

            else:
                query = update(table).where(cname.is_(None)).values(
                    {cname: val})
                execute(query)
コード例 #27
0
    def test_schema_reflection(self):
        """note: this test requires that the 'test_schema' schema be
        separate and accessible by the test user"""

        meta1 = self.metadata

        users = Table('users', meta1, Column('user_id', Integer,
                      primary_key=True), Column('user_name',
                      String(30), nullable=False), schema='test_schema')
        addresses = Table(
            'email_addresses',
            meta1,
            Column('address_id', Integer, primary_key=True),
            Column('remote_user_id', Integer,
                   ForeignKey(users.c.user_id)),
            Column('email_address', String(20)),
            schema='test_schema',
            )
        meta1.create_all()
        meta2 = MetaData(testing.db)
        addresses = Table('email_addresses', meta2, autoload=True,
                          schema='test_schema')
        users = Table('users', meta2, mustexist=True,
                      schema='test_schema')
        j = join(users, addresses)
        self.assert_((users.c.user_id
                     == addresses.c.remote_user_id).compare(j.onclause))
コード例 #28
0
def indiv_details(request):

    params=int(request.matchdict['id'])
    join_table = join(SatTrx, ObjectsCaracValues, SatTrx.ptt == cast(ObjectsCaracValues.value, Integer)
        ).join(Individual, ObjectsCaracValues.object==Individual.id) 

    query=select([ObjectsCaracValues.value.label('id'), Individual.id.label('ind_id'),Individual.survey_type.label('survey_type'), Individual.status.label('status')
        , Individual.monitoring_status.label('monitoring_status'), Individual.birth_date.label('birth_date'), Individual.ptt.label('ptt'),ObjectsCaracValues.begin_date.label('begin_date'),ObjectsCaracValues.end_date.label('end_date')]
        ).select_from(join_table
        ).where(and_(SatTrx.model.like('GSM%'),ObjectsCaracValues.carac_type==19,ObjectsCaracValues.object_type=='Individual')
        ).where(ObjectsCaracValues.value==params).order_by(desc(ObjectsCaracValues.begin_date))
   
    data=DBSession.execute(query).first()
    transaction.commit()
    if data['end_date'] == None :
        end_date=datetime.datetime.now()
    else :
        end_date=data['end_date'] 

    result=dict([ (key[0],key[1]) for key in data.items()])
    print(result)
    result['duration']=(end_date.month-data['begin_date'].month)+(end_date.year-data['begin_date'].year)*12
    
    query = select([V_Individuals_LatLonDate.c.date]
                     ).where(V_Individuals_LatLonDate.c.ind_id == result['ind_id']
                     ).order_by(desc(V_Individuals_LatLonDate.c.date)).limit(1)
     
    lastObs=DBSession.execute(query).fetchone()
    result['last_observation']=lastObs['date'].strftime('%d/%m/%Y')
    if result['birth_date']!= None:
        result['birth_date']=result['birth_date'].strftime('%d/%m/%Y')
    del result['begin_date'], result['end_date']
    print (result)
    return result
コード例 #29
0
ファイル: jobs.py プロジェクト: Christian-B/galaxy
    def user_per_month( self, trans, **kwd ):
        params = util.Params( kwd )
        message = ''

        email = util.restore_text( params.get( 'email', '' ) )
        specs = sorter( 'date', kwd )
        sort_id = specs.sort_id
        order = specs.order
        arrow = specs.arrow
        _order = specs.exc_order
        q = sa.select( ( self.select_month( model.Job.table.c.create_time ).label( 'date' ),
                         sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ),
                       whereclause=sa.and_( model.Job.table.c.session_id == model.GalaxySession.table.c.id,
                                            model.GalaxySession.table.c.user_id == model.User.table.c.id,
                                            model.User.table.c.email == email ),
                       from_obj=[ sa.join( model.Job.table, model.User.table ) ],
                       group_by=self.group_by_month( model.Job.table.c.create_time ),
                       order_by=[ _order ] )
        jobs = []
        for row in q.execute():
            jobs.append( ( row.date.strftime( "%Y-%m" ),
                           row.total_jobs,
                           row.date.strftime( "%B" ),
                           row.date.strftime( "%Y" ) ) )
        return trans.fill_template( '/webapps/reports/jobs_user_per_month.mako',
                                    order=order,
                                    arrow=arrow,
                                    sort_id=sort_id,
                                    id=kwd.get('id'),
                                    email=util.sanitize_text( email ),
                                    jobs=jobs, message=message )
コード例 #30
0
ファイル: queries.py プロジェクト: nkabir/pg-discuss
def fetch_comments_by_thread_client_id(thread_client_id):
    """Fetch a list of comments for the given thread's client_id from the
    database."""
    t_comment = tables.comment
    t_thread = tables.thread
    stmt = (
        sa.select(t_comment.c)
        .select_from(sa.join(t_comment, t_thread))
        .where(t_thread.c.client_id == thread_client_id)
        .order_by(sa.asc(t_comment.c.created))
    )

    # Run add_comment_filter_predicate hooks
    stmt = ext.exec_filter_hooks(ext.AddCommentFilterPredicate, stmt)

    result = db.engine.execute(stmt)
    # Very large result sets can cause a lot of memory allocation here
    # that CPython may not give back to the OS, due to a lack of compacting
    # GC. See: http://stackoverflow.com/a/5495318
    # However, assuming a worst case of 50K smallish rows, the memory for the
    # process will only roughly double (to about 100MB).
    # If comments could be sorted in the database, it would not be necessary
    # to fetch all the results and sort in-process. This has the drawback
    # of putting more load on the database server. If memory due to large
    # allocations for results is an issue, it is recommended to reload
    # workers after exceeding a memory limit via uwsgi's `reload-on-rss`.
    comments_seq = [dict(x) for x in result.fetchall()]
    result.close()

    return comments_seq
コード例 #31
0
    def get_request_review(self, num):
        """
        Displays one single review request
        after the user clicks on the title when she sees it on mainpage

        accepts an int
        returns a dict 
        """
        query = select(self.cols).  \
            select_from( \
            join(self.structure, User.structure)). \
            where(self.structure.c.reqId == num)
        result = connect_and_get(query).fetchall()
        if result:
            return zip_results(self.cols, result)[0]
        return False
コード例 #32
0
ファイル: __init__.py プロジェクト: glensc/griffith
 def process_movie_tags(self, document, movieelement, movie):
     # build query for movie tags processing
     tagsquerycolumns = [
         db.metadata.tables['movie_tag'].c.movie_id,
         db.metadata.tables['movie_tag'].c.tag_id,
         db.metadata.tables['tags'].c.name
     ]
     tag_join = join(db.metadata.tables['movie_tag'], \
         db.metadata.tables['tags'], \
         db.metadata.tables['movie_tag'].c.tag_id==db.metadata.tables['tags'].c.tag_id)
     tagsquery = select(\
         bind=self.db.session.bind, \
         columns = tagsquerycolumns, \
         from_obj = [tag_join], \
         whereclause = db.metadata.tables['movie_tag'].c.movie_id==movie['movies_movie_id'])
     self.process_tags(document, movieelement, tagsquery)
コード例 #33
0
async def show_julia(conn):
    print("Lookup for Julia:")
    join = sa.join(emails, users, users.c.id == emails.c.user_id)
    query = (
        sa.select([users, emails], use_labels=True)
        .select_from(join)
        .where(users.c.name == "Julia")
    )
    async for row in conn.execute(query):
        print(
            row.users_name,
            row.users_birthday,
            row.emails_email,
            row.emails_private,
        )
    print()
コード例 #34
0
ファイル: bibfiles_db.py プロジェクト: zogamnuam/glottolog
 def show_merges(self):
     other = sa.orm.aliased(Entry)
     select_entries = sa.select([
             Entry.hash, Entry.refid, File.name.label('filename'), Entry.bibkey,
         ]).select_from(sa.join(Entry, File))\
         .order_by(Entry.hash, Entry.refid.desc(), File.name, Entry.bibkey)\
         .where(sa.exists().where(other.hash == Entry.hash).where(other.refid != Entry.refid))
     with self.engine.connect() as conn:
         for hash, group in group_first(conn.execute(select_entries)):
             self._print_group(conn, group)
             new = self._merged_entry(self._entrygrp(conn, hash), raw=True)
             cand = [
                 (ri, self._merged_entry(self._entrygrp(conn, ri), raw=True))
                 for ri in unique(ri for _, ri, _, _ in group)]
             old = min(cand, key=lambda p: distance(new, p[1]))[0]
             print('-> %s\n' % old)
コード例 #35
0
ファイル: models.py プロジェクト: callmead/SWPracticum
class MeterSample(Base):
    """Helper model.

    It's needed as many of the filters work against Sample data joined with
    Meter data.
    """
    meter = Meter.__table__
    sample = Sample.__table__
    __table__ = join(meter, sample)

    id = column_property(sample.c.id)
    meter_id = column_property(meter.c.id, sample.c.meter_id)
    counter_name = column_property(meter.c.name)
    counter_type = column_property(meter.c.type)
    counter_unit = column_property(meter.c.unit)
    counter_volume = column_property(sample.c.volume)
コード例 #36
0
ファイル: specialities.py プロジェクト: hdrobins/dao
 def single_speciality_of_headquarter(cls, *, heq_id, msp_id):
     """
         Especialidades médicas de la sede heq_id y de la especialidad msp_id
     """
     j = join(
         cls.msp,
         doctors.Doctors.doc,
         cls.msp.c.msp_id == doctors.Doctors.doc.c.msp_id,
     ).join(
         HealthRelations.hre,
         HealthRelations.hre.c.doc_id == doctors.Doctors.doc.c.doc_id,
     )
     query = (select([cls.msp]).distinct().select_from(j).where(
         HealthRelations.hre.c.heq_id == heq_id).where(
             cls.msp.c.msp_id == msp_id))
     return query
コード例 #37
0
ファイル: bibfiles_db.py プロジェクト: zogamnuam/glottolog
 def show_splits(self):
     other = sa.orm.aliased(Entry)
     select_entries = sa.select([
             Entry.refid, Entry.hash, File.name.label('filename'), Entry.bibkey,
         ]).select_from(sa.join(Entry, File))\
         .order_by(Entry.refid, Entry.hash, File.name, Entry.bibkey)\
         .where(sa.exists().where(other.refid == Entry.refid).where(other.hash != Entry.hash))
     with self.engine.connect() as conn:
         for refid, group in group_first(conn.execute(select_entries)):
             self._print_group(conn, group)
             old = self._merged_entry(self._entrygrp(conn, refid), raw=True)
             cand = [
                 (hs, self._merged_entry(self._entrygrp(conn, hs), raw=True))
                 for hs in unique(hs for _, hs, _, _ in group)]
             new = min(cand, key=lambda p: distance(old, p[1]))[0]
             print('-> %s\n' % new)
コード例 #38
0
    def get_vw(self, search_kwargs, search_extras_regexp=None):
        """Get a view for our model.

        Our model will consist of items found in our database + any
        ingredients specified when we were created. In other words,
        we'll list all ingredients that we're told about, whether
        they're in the nutrition aliases table or not.

        search_kwargs are the arguments handed to our database search.
        search_extras_text is a regexp used to filter our "extras."
        """
        select = sqlalchemy.select(
            [
                self.rd.nutritionaliases_table.c.ingkey,
                self.rd.nutritionaliases_table.c.density_equivalent,
                self.rd.nutrition_table.c.desc, self.rd.nutrition_table.c.ndbno
            ],
            *gourmet.backends.db.make_simple_select_arg(
                search_kwargs, self.rd.nutrition_table,
                self.rd.nutritionaliases_table), **{
                    'from_obj': [
                        sqlalchemy.join(self.rd.nutrition_table,
                                        self.rd.nutritionaliases_table)
                    ]
                })
        vw = select.execute().fetchall()
        #vw = self.rd.fetch_join(self.rd.nutritionaliases_table,self.rd.nutrition_table,
        #                        'ndbno','ndbno',sort_by=[('ingkey',1)],
        #                        **search_kwargs)
        # We must show ingredients whether we have them or not...
        extras = []
        if self.ingredients:
            ings_to_add = self.ingredients[:]
            if search_extras_regexp:
                ings_to_add = filter(
                    lambda i: re.match(search_extras_regexp, i), ings_to_add)
            for row in vw:
                while row.ingkey in ings_to_add:
                    ings_to_add.remove(row.ingkey)
            for extra_ing in ings_to_add:
                if extra_ing:
                    extras.append(
                        MockObject(ingkey=extra_ing,
                                   ndbno=0,
                                   desc='Not in database',
                                   density_equivalent=None))
        return vw + extras
コード例 #39
0
def safety_stock_controller(uri: str,
                            sku_id: str = None,
                            direction: str = None):
    """ Retrieves safety stock.

    Args:
        uri (str):          Database connection string.
        sku_id (str):       SKU unique identification.
        direction (str):    Indication of sort direction.

    Returns:

    """

    meta = MetaData()
    connection = engine(uri)
    inventory_analysis = Table('inventory_analysis',
                               meta,
                               autoload=True,
                               autoload_with=connection)
    msk = Table('master_sku_list',
                meta,
                autoload=True,
                autoload_with=connection)
    j = join(inventory_analysis, msk,
             msk.columns.id == inventory_analysis.columns.sku_id)

    if direction == 'smallest':
        sku_classification = select([
            func.min(inventory_analysis.columns.safety_stock),
            msk.columns.sku_id
        ]).select_from(j)
        rp = connection.execute(sku_classification)
    else:
        sku_classification = select([
            func.max(inventory_analysis.columns.safety_stock),
            msk.columns.sku_id
        ]).select_from(j)

        rp = connection.execute(sku_classification)

    safety_stock = ''
    for i in rp:
        safety_stock = i[0]
        sku_identification = i[1]

    return safety_stock, sku_identification
コード例 #40
0
    def get_data_query(self):
        all_snapshot = db_tables.clean_all_snapshots_table

        student_lookup = all_snapshot.c.student_lookup
        school_year = all_snapshot.c.school_year
        grade = all_snapshot.c.grade
        discipline_incidents = all_snapshot.c.discipline_incidents

        student_years = sql.select([
            student_lookup,
            school_year.label('end_year'),
            grade,
        ]).distinct(student_lookup, school_year,
                    grade).where(grade >= 9).alias('student_years')

        student_discipline = sql.select(
            [student_lookup, school_year, grade, discipline_incidents]).where(
                grade >= features_config.min_grade).alias('student_discipline')

        joined = sql.join(
            left=student_discipline,
            right=student_years,
            onclause=sql.and_(
                student_discipline.c.student_lookup ==
                student_years.c.student_lookup,
                student_discipline.c.school_year <= student_years.c.end_year))

        discipline_incident_rates = sql.select([
            joined.c.student_discipline_student_lookup.label('student_lookup'),
            joined.c.student_years_end_year.label('school_year'),
            joined.c.student_years_grade.label('grade'),
            db_func.avg(
                joined.c.student_discipline_discipline_incidents).label(
                    'discipline_incident_rate'),
            db_func.percent_rank().over(
                order_by=db_func.avg(
                    joined.c.student_discipline_discipline_incidents),
                partition_by=[
                    joined.c.student_years_end_year,
                    joined.c.student_years_grade
                ]).label('discipline_incident_rate_perc')
        ]).select_from(joined).group_by(
            joined.c.student_discipline_student_lookup.label('student_lookup'),
            joined.c.student_years_end_year.label('school_year'),
            joined.c.student_years_grade.label('grade'))

        return discipline_incident_rates
コード例 #41
0
def get_release_id(timestamp: Union[str, datetime],
                   process_name: str) -> Tuple[int, datetime]:
    # Prevent duplication
    sleep(random() * 10)

    session = Session()

    try:
        query = session.execute(
            select([
                ReleaseReference.id, ReleaseReference.timestamp
            ]).select_from(
                join(ReleaseReference, ReleaseCategory,
                     ReleaseReference.id == ReleaseCategory.release_id)).where(
                         and_(
                             func.DATE(ReleaseReference.timestamp) ==
                             timestamp.date(),
                             ReleaseCategory.process_name == process_name)))

        result = query.fetchone()

        if result is not None:
            return result

    except Exception as err:
        session.rollback()
        raise err
    finally:
        session.close()

    session = Session(autocommit=True)
    try:
        release = ReleaseReference(timestamp=timestamp)
        session.add(release)
        session.flush()

        category = ReleaseCategory(release_id=release.id,
                                   process_name=process_name)
        session.add(category)
        session.flush()
    except Exception as err:
        session.rollback()
        raise err
    finally:
        session.close()

    return get_release_id(timestamp, process_name)
コード例 #42
0
    def resolve_num_children(self, args, context, info):
        phase = args.get('identifier', '')
        if phase == 'multiColumns':
            _it = models.Idea.__table__
            _ilt = models.IdeaLink.__table__
            _target_it = models.Idea.__table__.alias()
            j = join(_ilt, _it, _ilt.c.source_id == _it.c.id).join(
                _target_it, _ilt.c.target_id == _target_it.c.id)
            num = select([func.count(_ilt.c.id)]).select_from(j).where(
                (_ilt.c.tombstone_date == None)
                & (_it.c.tombstone_date == None) & (  # noqa: E711
                    _it.c.id == self.id)
                & (_target_it.c.message_view_override == 'messageColumns')
            ).correlate_except(_ilt)
            return self.db.execute(num).fetchone()[0]

        return self.num_children
コード例 #43
0
ファイル: models.py プロジェクト: vstasn/aiobot
async def get_list_active_notifications():
    try:
        async with create_engine(dsn) as engine:

            async with engine.acquire() as conn:
                join = sa.join(user_notify, user_note,
                               user_notify.c.note_id == user_note.c.id)
                select = sa.select([user_notify, user_note], use_labels=True).select_from(join).\
                    where(user_notify.c.notify_status == NotifyStatus.processing)

                notify_list = []
                async for row in conn.execute(select):
                    notify_list.append(row)

                return notify_list
    except Exception as message:
        logging.warning(message)
コード例 #44
0
    def get_data_query(self):
        all_snapshots = db_tables.clean_all_snapshots_table
        student_lookup = all_snapshots.c.student_lookup
        school_year = all_snapshots.c.school_year
        grade = all_snapshots.c.grade
        zipcode = all_snapshots.c.zip

        # get first 5 digits for zipcode
        processed_zipcode = sql.case([(zipcode == None, None)],
                                     else_=sql.func.substr(
                                         sql.cast(zipcode, VARCHAR), 1, 5))

        snapshots = sql.select([
                student_lookup,
                school_year,
                grade,
                processed_zipcode.label('zipcode'),
            ]).\
            distinct(student_lookup, school_year, grade).\
            where(
                student_lookup != None,
            ).\
            cte('acs_temp_a')

        to_join = [snapshots]
        for k, v in db_tables.__dict__.items():
            if k.startswith('acs_'):
                to_join.append(v)

        joined = to_join[0]
        for i in range(1, len(to_join)):
            if i == 1:
                on_clause = (joined.c.zipcode == to_join[i].c.zipcode)
            else:
                on_clause = (joined.c[to_join[0].name +
                                      '_zipcode'] == to_join[i].c.zipcode)

            joined = sql.join(left=joined,
                              right=to_join[i],
                              onclause=on_clause,
                              isouter=True)

        cols = [c for c in joined.c if c.name != 'zipcode']
        final = sql.select(cols).select_from(joined)

        return final
コード例 #45
0
    def getDatas(self):
        if self.type_ == 'camtrap':
            if self.request.method == 'GET':
                joinTable = join(CamTrap, self.viewTable,
                                CamTrap.pk_id == self.viewTable.c['pk_id'])
                query = select([CamTrap]).select_from(joinTable)
                query = query.where(self.viewTable.c['sessionID'] == self.sessionID)
                query = query.where(or_(self.viewTable.c['checked'] == 0, self.viewTable.c['checked'] == None))
                query = query.order_by(asc(self.viewTable.c['date_creation']))

        else:
            query = select([self.viewTable]
                           ).where(self.viewTable.c['sessionID'] == self.sessionID
                                   ).where(or_(self.viewTable.c['checked'] == 0, self.viewTable.c['checked'] == None))
        query = self.handleQuery(query)
        data = self.session.execute(query).fetchall()
        return self.handleResult(data)
コード例 #46
0
def select_languoid_endangerment(languoid=Languoid,
                                 *, label: str = 'endangerment',
                                 sort_keys: bool = False,
                                 bib_suffix: str = '_e') -> sa.sql.Select:
    bibitem = aliased(Bibitem, name=f'bibitem{bib_suffix}')
    bibfile = aliased(Bibfile, name=f'bibfile{bib_suffix}')

    return (select(Endangerment.jsonf(EndangermentSource,
                                      bibfile, bibitem,
                                      sort_keys=sort_keys,
                                      label=label))
            .select_from(Endangerment)
            .filter_by(languoid_id=languoid.id)
            .correlate(languoid)
            .join(Endangerment.source)
            .outerjoin(sa.join(bibitem, bibfile))
            .label(label))
コード例 #47
0
ファイル: orm_mapping.py プロジェクト: jsws/autoreduce
class RunJoin(Base):
    reduction_variable_table = Table(
        'reduction_variables_runvariable', metadata,
        Column('variable_ptr_id',
               Integer,
               ForeignKey('reduction_variables_variable.id'),
               primary_key=True),
        Column('reduction_run_id', Integer,
               ForeignKey('reduction_viewer_reductionrun.id')))

    reduction_variable_join = join(variable_table, reduction_variable_table)

    __table__ = reduction_variable_join
    id = column_property(variable_table.c.id,
                         reduction_variable_table.c.variable_ptr_id)
    reduction_run = relationship('ReductionRun',
                                 foreign_keys='RunJoin.reduction_run_id')
    def get_data_query(self):
        # FIXME: Make end year go upto the last year on record
        index_cols_dict = self.index_cols_dict

        student_years = sql.select(
                            list(index_cols_dict.values())
                        ).distinct(
                            *list(index_cols_dict.values())
                        ).where(
                            index_cols_dict['grade'] >= 9
                        ).alias('student_years')

        student_block = sql.select(
                            list(index_cols_dict.values()) +
                            [self.blocking_col]
                        ).where(
                            index_cols_dict['grade'] >= features_config.min_grade
                        ).alias('student_block')

        joined = sql.join(
            left=student_block,
            right=student_years,
            onclause=sql.and_(
                        student_block.c.student_lookup == student_years.c.student_lookup,
                        student_block.c.school_year <= student_years.c.school_year
                    )
        )

        value_col = db_func.count() * 1.0 / db_func.count(sql.distinct(joined.c.student_block_school_year))

        inv_rates = sql.select([
                        joined.c.student_block_student_lookup.label('student_lookup'),
                        joined.c.student_years_school_year.label('school_year'),
                        joined.c.student_years_grade,
                        joined.c['student_block_' + self.blocking_col.name].label(self.blocking_col.name),
                        value_col.label(self.value_col_name),
                    ]).select_from(
                        joined
                    ).group_by(
                        joined.c.student_block_student_lookup,
                        joined.c.student_years_school_year,
                        joined.c.student_years_grade,
                        joined.c['student_block_' + self.blocking_col.name],
                    )

        return inv_rates
コード例 #49
0
 async def batch_load_by_name_user(cls, context, names):
     async with context['dbpool'].acquire() as conn:
         access_key = context['access_key']
         j = sa.join(
             keypairs, keypair_resource_policies,
             keypairs.c.resource_policy == keypair_resource_policies.c.name)
         query = (sa.select([keypair_resource_policies]).select_from(
             j).where((keypair_resource_policies.c.name.in_(names))
                      & (keypairs.c.access_key == access_key)).order_by(
                          keypair_resource_policies.c.name))
         objs_per_key = OrderedDict()
         for k in names:
             objs_per_key[k] = None
         async for row in conn.execute(query):
             o = cls.from_row(context, row)
             objs_per_key[row.name] = o
     return tuple(objs_per_key.values())
コード例 #50
0
def reorder_level_controller(uri: str,
                             sku_id: str = None,
                             direction: str = None):
    """ Retrieves reorder level.

    Args:
        uri:
        sku_id:
        direction:

    Returns:

    """
    meta = MetaData()
    connection = engine(uri)
    inventory_analysis = Table('inventory_analysis',
                               meta,
                               autoload=True,
                               autoload_with=connection)
    msk = Table('master_sku_list',
                meta,
                autoload=True,
                autoload_with=connection)
    j = join(inventory_analysis, msk,
             msk.columns.id == inventory_analysis.columns.sku_id)

    if direction == 'smallest':
        sku_classification = select([
            func.min(inventory_analysis.columns.reorder_level),
            msk.columns.sku_id
        ]).select_from(j)
        rp = connection.execute(sku_classification)
    else:
        sku_classification = select([
            func.max(inventory_analysis.columns.reorder_level),
            msk.columns.sku_id
        ]).select_from(j)
        rp = connection.execute(sku_classification)

    reorder_level = ''
    sku_identification = ''
    for i in rp:
        reorder_level = i[0]
        sku_identification = i[1]

    return reorder_level, sku_identification
コード例 #51
0
    async def list_contacts_in_segment(self, segment_id: int):
        async with self._db_engine.acquire() as conn:
            query = (select(
                [
                    contact_table.c.id, contact_table.c.name,
                    contact_table.c.email
                ],
                segment_contact_table.c.segment_id == segment_id,
            ).select_from(
                join(
                    contact_table,
                    segment_contact_table,
                    contact_table.c.id == segment_contact_table.c.contact_id,
                )).order_by(contact_table.c.id))

            contacts = await conn.execute(query)
            return list(map(dict, await contacts.fetchall()))
コード例 #52
0
ファイル: stats.py プロジェクト: wgw335363240/ckan
    def largest_groups(cls, limit=10):
        member = table('member')
        package = table('package')

        j = join(member, package,
                 member.c.table_id == package.c.id)

        s = select([member.c.group_id, func.count(member.c.table_id)]).\
            select_from(j).\
            group_by(member.c.group_id).\
            where(and_(member.c.group_id!=None, member.c.table_name=='package', package.c.private==False, package.c.state=='active')).\
            order_by(func.count(member.c.table_id).desc()).\
            limit(limit)

        res_ids = model.Session.execute(s).fetchall()
        res_groups = [(model.Session.query(model.Group).get(unicode(group_id)), val) for group_id, val in res_ids]
        return res_groups
コード例 #53
0
def _bulkLoadDispsTask(importGroupHash: str, disps: List):
    """ Import Disps Links

    1) Drop all disps with matching importGroupHash

    2) set the  coordSetId

    :param importGroupHash:
    :param disps: An array of disp objects to import
    :return:
    """

    dispTable = DispBase.__table__
    gridKeyIndexTable = GridKeyIndex.__table__
    gridQueueTable = GridKeyCompilerQueue.__table__

    engine = CeleryDbConn.getDbEngine()
    conn = engine.connect()
    transaction = conn.begin()

    try:

        stmt = select([gridKeyIndexTable.c.coordSetId,
                       gridKeyIndexTable.c.gridKey]) \
            .where(dispTable.c.importGroupHash == importGroupHash) \
            .select_from(join(gridKeyIndexTable, dispTable,
                              gridKeyIndexTable.c.dispId == dispTable.c.id)) \
            .distinct()

        ins = gridQueueTable.insert().from_select(['coordSetId', 'gridKey'],
                                                  stmt)
        conn.execute(ins)

        conn.execute(dispTable.delete().where(
            dispTable.c.importGroupHash == importGroupHash))

        transaction.commit()

        _bulkInsertDisps(engine, disps)

    except Exception:
        transaction.rollback()
        raise

    finally:
        conn.close()
コード例 #54
0
 async def batch_load_by_ak(context, access_keys, *, domain_name=None):
     async with context['dbpool'].acquire() as conn:
         from .user import users
         j = sa.join(keypairs, users, keypairs.c.user == users.c.uuid)
         query = (sa.select([keypairs]).select_from(j).where(
             keypairs.c.access_key.in_(access_keys)))
         if domain_name is not None:
             query = query.where(users.c.domain_name == domain_name)
         objs_per_key = OrderedDict()
         # For each access key, there is only one keypair.
         # So we don't build lists in objs_per_key variable.
         for k in access_keys:
             objs_per_key[k] = None
         async for row in conn.execute(query):
             o = KeyPair.from_row(row)
             objs_per_key[row.access_key] = o
     return tuple(objs_per_key.values())
コード例 #55
0
    def retrieve(self):
        from ..utils.parseValue import formatThesaurus

        propertiesTable = Base.metadata.tables[
            self.parent.objectDB.GetDynPropTable()]
        dynamicValuesTable = Base.metadata.tables[
            self.parent.objectDB.GetDynPropValuesTable()]
        FK_name = self.parent.objectDB.GetSelfFKNameInValueTable()
        FK_property_name = self.parent.objectDB.GetDynPropFKName()

        tableJoin = join(
            dynamicValuesTable, propertiesTable,
            dynamicValuesTable.c[FK_property_name] == propertiesTable.c['ID'])
        query = select(
            [dynamicValuesTable,
             propertiesTable.c['Name']]).select_from(tableJoin).where(
                 dynamicValuesTable.c[FK_name] == self.parent.objectDB.ID)

        query = query.where(
            not_(propertiesTable.c['Name'].in_([
                'Release_Comments', 'Breeding ring kept after release',
                'Box_ID', 'Date_Sortie', 'Poids'
            ]))).order_by(desc(dynamicValuesTable.c['StartDate']))

        result = self.session.execute(query).fetchall()
        response = []

        for row in result:
            curRow = OrderedDict(row)
            dictRow = {}
            for key in curRow:
                if curRow[key] is not None:
                    if key == 'ValueString' in key and curRow[key] is not None:
                        try:
                            thesauralValueObj = formatThesaurus(curRow[key])
                            dictRow['value'] = thesauralValueObj[
                                'displayValue']
                        except:
                            dictRow['value'] = curRow[key]
                    elif 'FK' not in key:
                        dictRow[key] = curRow[key]
            dictRow['StartDate'] = curRow['StartDate'].strftime(
                '%Y-%m-%d %H:%M:%S')
            response.append(dictRow)

        return response
コード例 #56
0
ファイル: __init__.py プロジェクト: glensc/griffith
 def process_movie_acodecs(self, document, movieelement, movie):
     # build query for audio codec processing
     acodecscolumns = [
         db.metadata.tables['movie_lang'].c.movie_id,
         db.metadata.tables['movie_lang'].c.type,
         db.metadata.tables['movie_lang'].c.acodec_id,
         db.metadata.tables['acodecs'].c.name
     ]
     acodec_join = join(db.metadata.tables['movie_lang'], \
         db.metadata.tables['acodecs'], \
         db.metadata.tables['movie_lang'].c.acodec_id==db.metadata.tables['acodecs'].c.acodec_id)
     acodecsquery = select(\
         bind=self.db.session.bind, \
         columns = acodecscolumns, \
         from_obj = [acodec_join], \
         whereclause = db.metadata.tables['movie_lang'].c.movie_id==movie['movies_movie_id'])
     self.process_acodecs(document, movieelement, acodecsquery)
コード例 #57
0
 async def load_all_user(cls, context, access_key):
     async with context['dbpool'].acquire() as conn:
         query = (sa.select([keypairs.c.user_id
                             ]).select_from(keypairs).where(
                                 keypairs.c.access_key == access_key))
         result = await conn.execute(query)
         row = await result.fetchone()
         user_id = row['user_id']
         j = sa.join(
             keypairs, keypair_resource_policies,
             keypairs.c.resource_policy == keypair_resource_policies.c.name)
         query = (sa.select([keypair_resource_policies
                             ]).select_from(j).where(
                                 (keypairs.c.user_id == user_id)))
         result = await conn.execute(query)
         rows = await result.fetchall()
         return [cls.from_row(context, r) for r in rows]
コード例 #58
0
ファイル: __init__.py プロジェクト: glensc/griffith
 def process_movie_subtitles(self, document, movieelement, movie):
     # build query for movie subtitles processing
     languagescolumns = [
         db.metadata.tables['movie_lang'].c.movie_id,
         db.metadata.tables['movie_lang'].c.type,
         db.metadata.tables['movie_lang'].c.lang_id,
         db.metadata.tables['languages'].c.name
     ]
     language_join = join(db.metadata.tables['movie_lang'], \
         db.metadata.tables['languages'], \
         db.metadata.tables['movie_lang'].c.lang_id==db.metadata.tables['languages'].c.lang_id)
     languagesquery = select(\
         bind=self.db.session.bind, \
         columns = languagescolumns, \
         from_obj = [language_join], \
         whereclause = and_(db.metadata.tables['movie_lang'].c.movie_id==movie['movies_movie_id'], db.metadata.tables['movie_lang'].c.type==3))
     self.process_subtitles(document, movieelement, languagesquery)
コード例 #59
0
ファイル: sa.py プロジェクト: mafeicnnui/aiopg
async def count(conn):
    c1 = (await conn.scalar(users.count()))
    c2 = (await conn.scalar(emails.count()))
    print("Population consists of", c1, "people with",
          c2, "emails in total")
    join = sa.join(emails, users, users.c.id == emails.c.user_id)
    query = (sa.select([users.c.name])
             .select_from(join)
             .where(emails.c.private == False)  # noqa
             .group_by(users.c.name)
             .having(sa.func.count(emails.c.private) > 0))

    print("Users with public emails:")
    async for row in conn.execute(query):
        print(row.name)

    print()
コード例 #60
0
    def _convert(cls, session, id, node_type_cache=None, scan_id_cache=None):
        if node_type_cache is None:
            node_type_cache = dict()
        if scan_id_cache is None:
            scan_id_cache = dict()

        node_table = cls.__table__
        attribs = session.execute(
            node_table.select().where(node_table.c.id == id)).fetchone()

        peak_table = DeconvolutedPeak.__table__

        selector = select([DeconvolutedPeak.__table__]).select_from(
            join(peak_table, ChromatogramTreeNodeToDeconvolutedPeak)).where(
                ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == id)

        members = session.execute(selector).fetchall()

        try:
            scan_id = scan_id_cache[attribs.scan_id]
        except KeyError:
            selector = select([
                MSScan.__table__.c.scan_id
            ]).where(MSScan.__table__.c.id == attribs.scan_id)
            scan_id = session.execute(selector).fetchone()
            scan_id_cache[attribs.scan_id] = scan_id

        members = [make_memory_deconvoluted_peak(m) for m in members]

        try:
            node_type = node_type_cache[attribs.node_type_id]
        except KeyError:
            shift = session.query(CompoundMassShift).get(attribs.node_type_id)
            node_type = shift.convert()
            node_type_cache[attribs.node_type_id] = node_type

        children_ids = session.query(
            ChromatogramTreeNodeBranch.child_id).filter(
                ChromatogramTreeNodeBranch.parent_id == id)
        children = [
            cls._convert(session, i[0], node_type_cache) for i in children_ids
        ]

        return MemoryChromatogramTreeNode(attribs.retention_time, scan_id,
                                          children, members, node_type)