def _process_null_records(table, col_name, check_fkeys, delete=False): """Queries the database and optionally deletes the NULL records. :param table: sqlalchemy.Table object. :param col_name: The name of the column to check in the table. :param check_fkeys: If True, check the table for foreign keys back to the instances table and if not found, return. :param delete: If true, run a delete operation on the table, else just query for number of records that match the NULL column. :returns: The number of records processed for the table and column. """ records = 0 if col_name in table.columns: # NOTE(mriedem): filter out tables that don't have a foreign key back # to the instances table since they could have stale data even if # instances.uuid wasn't NULL. if check_fkeys: fkey_found = False fkeys = table.c[col_name].foreign_keys or [] for fkey in fkeys: if fkey.column.table.name == 'instances': fkey_found = True if not fkey_found: return 0 if delete: records = table.delete().where( table.c[col_name] == null() ).execute().rowcount else: records = len(list( table.select().where(table.c[col_name] == null()).execute() )) return records
def test_reduce_aliased_union_2(self): metadata = MetaData() page_table = Table('page', metadata, Column('id', Integer, primary_key=True), ) magazine_page_table = Table('magazine_page', metadata, Column('page_id', Integer, ForeignKey('page.id'), primary_key=True), ) classified_page_table = Table('classified_page', metadata, Column('magazine_page_id', Integer, ForeignKey('magazine_page.page_id'), primary_key=True), ) # this is essentially the union formed by the ORM's polymorphic_union function. # we define two versions with different ordering of selects. # the first selectable has the "real" column classified_page.magazine_page_id pjoin = union( select([ page_table.c.id, magazine_page_table.c.page_id, classified_page_table.c.magazine_page_id ]).select_from(page_table.join(magazine_page_table).join(classified_page_table)), select([ page_table.c.id, magazine_page_table.c.page_id, cast(null(), Integer).label('magazine_page_id') ]).select_from(page_table.join(magazine_page_table)), ).alias('pjoin') eq_( util.column_set(sql_util.reduce_columns([pjoin.c.id, pjoin.c.page_id, pjoin.c.magazine_page_id])), util.column_set([pjoin.c.id]) ) # the first selectable has a CAST, which is a placeholder for # classified_page.magazine_page_id in the second selectable. reduce_columns # needs to take into account all foreign keys derived from pjoin.c.magazine_page_id. # the UNION construct currently makes the external column look like that of the first # selectable only. pjoin = union( select([ page_table.c.id, magazine_page_table.c.page_id, cast(null(), Integer).label('magazine_page_id') ]).select_from(page_table.join(magazine_page_table)), select([ page_table.c.id, magazine_page_table.c.page_id, classified_page_table.c.magazine_page_id ]).select_from(page_table.join(magazine_page_table).join(classified_page_table)) ).alias('pjoin') eq_( util.column_set(sql_util.reduce_columns([pjoin.c.id, pjoin.c.page_id, pjoin.c.magazine_page_id])), util.column_set([pjoin.c.id]) )
def test_union_against_join(self): # same as testunion, except its an alias of the union u = select([table1.c.col1, table1.c.col2, table1.c.col3, table1.c.colx, null().label('coly')]).union( select([table2.c.col1, table2.c.col2, table2.c.col3, null().label('colx'), table2.c.coly]) ).alias('analias') j1 = table1.join(table2) assert u.corresponding_column(j1.c.table1_colx) is u.c.colx assert j1.corresponding_column(u.c.colx) is j1.c.table1_colx
def col(name, table): try: return colnamemaps[table][name] except KeyError: if cast_nulls: return sql.cast(sql.null(), types[name]).label(name) else: return sql.type_coerce(sql.null(), types[name]).label(name)
def test_select_union(self): # like testaliasunion, but off a Select off the union. u = select([table1.c.col1, table1.c.col2, table1.c.col3, table1.c.colx, null().label('coly')]).union( select([table2.c.col1, table2.c.col2, table2.c.col3, null().label('colx'), table2.c.coly]) ).alias('analias') s = select([u]) s1 = table1.select(use_labels=True) s2 = table2.select(use_labels=True) assert s.corresponding_column(s1.c.table1_col2) is s.c.col2 assert s.corresponding_column(s2.c.table2_col2) is s.c.col2
def ___images_by_windowed_meta( self, context, period_start, period_stop, project_id, metadata ): """Simulated bottom most layer :param context: :param period_start: Datetime :param period_stop: Datetime :param project_id: String :param metadata: """ if metadata: aliases = [aliased(models.ImageProperty) for i in metadata] else: aliases = [] session = get_session() query = session.query( models.Image, *aliases ) query = query.filter(or_(models.Image.deleted_at == null(), models.Image.deleted_at > period_start)) if period_stop: query = query.filter(models.Image.created_at < period_stop) if project_id: query = query.filter_by(project_id=project_id) if metadata: for keypair, alias in zip(metadata.items(), aliases): query = query.filter(alias.name == keypair[0]) query = query.filter(alias.value == keypair[1]) query = query.filter(alias.image_id == models.Image.id) query = query.filter(or_( alias.deleted_at == null(), alias.deleted_at == models.Image.deleted_at )) images = [] for tup in query.all(): if aliases: image = tup[0] # props = tup[1:] else: image = tup # props = None images.append(dict(image)) return images
def test_union(self): # tests that we can correspond a column in a Select statement with a certain Table, against # a column in a Union where one of its underlying Selects matches to that same Table u = select([table1.c.col1, table1.c.col2, table1.c.col3, table1.c.colx, null().label('coly')]).union( select([table2.c.col1, table2.c.col2, table2.c.col3, null().label('colx'), table2.c.coly]) ) s1 = table1.select(use_labels=True) s2 = table2.select(use_labels=True) c = u.corresponding_column(s1.c.table1_col2) assert u.corresponding_column(s1.c.table1_col2) is u.c.col2 assert u.corresponding_column(s2.c.table2_col2) is u.c.col2
def test_alias_union(self): # same as testunion, except its an alias of the union u = select([table1.c.col1, table1.c.col2, table1.c.col3, table1.c.colx, null().label('coly')]).union( select([table2.c.col1, table2.c.col2, table2.c.col3, null().label('colx'), table2.c.coly]) ).alias('analias') s1 = table1.select(use_labels=True) s2 = table2.select(use_labels=True) assert u.corresponding_column(s1.c.table1_col2) is u.c.col2 assert u.corresponding_column(s2.c.table2_col2) is u.c.col2 assert u.corresponding_column(s2.c.table2_coly) is u.c.coly assert s2.corresponding_column(u.c.coly) is s2.c.table2_coly
def ___get_active_by_window_metadata(self, context, period_start, period_stop=None, project_id=None, metadata=None, use_slave=False): """Simulate bottom most layer :param context: wsgi context :param period_start: Datetime :param period_stop: Datetime :param project_id: String|None :param metadata: Dict|None :param use_slave: Boolean """ if metadata: aliases = [aliased(models.VolumeMetadata) for i in metadata] else: aliases = [] session = get_session(use_slave=use_slave) query = session.query( models.Volume, *aliases ) query = query.filter(or_(models.Volume.terminated_at == null(), models.Volume.terminated_at > period_start)) if period_stop: query = query.filter(models.Volume.launched_at < period_stop) if project_id: query = query.filter_by(project_id=project_id) if metadata: for keypair, alias in zip(metadata.items(), aliases): query = query.filter(alias.key == keypair[0]) query = query.filter(alias.value == keypair[1]) query = query.filter(alias.volume_id == models.Volume.id) query = query.filter(or_( alias.deleted_at == null(), alias.deleted_at == models.Volume.deleted_at )) volumes = [] for tup in query.all(): # If no metadata filters, then no aliases. if aliases: volume = tup[0] else: volume = tup volumes.append(dict(volume)) return volumes
def _get_node_empty_ratio(context, max_count): """Query the DB for non-deleted compute_nodes with 0.0/None alloc ratios Results are limited by ``max_count``. """ return context.session.query(models.ComputeNode).filter(or_( models.ComputeNode.ram_allocation_ratio == '0.0', models.ComputeNode.cpu_allocation_ratio == '0.0', models.ComputeNode.disk_allocation_ratio == '0.0', models.ComputeNode.ram_allocation_ratio == null(), models.ComputeNode.cpu_allocation_ratio == null(), models.ComputeNode.disk_allocation_ratio == null() )).filter(models.ComputeNode.deleted == 0).limit(max_count).all()
def organizations_and_counters(): '''Query organizations with their counters''' query = DB.query(Group, func.count(distinct(Package.id)).label('nb_datasets'), func.count(distinct(Member.id)).label('nb_members') ) query = query.outerjoin(CertifiedPublicService) query = query.outerjoin(Package, and_( Group.id == Package.owner_org, ~Package.private, Package.state == 'active', )) query = query.outerjoin(Member, and_( Member.group_id == Group.id, Member.state == 'active', Member.table_name == 'user' )) query = query.filter(Group.state == 'active') query = query.filter(Group.approval_status == 'approved') query = query.filter(Group.is_organization == True) query = query.group_by(Group.id, CertifiedPublicService.organization_id) query = query.order_by( CertifiedPublicService.organization_id == null(), desc('nb_datasets'), desc('nb_members'), Group.title ) query = query.options(orm.joinedload(Group.certified_public_service)) return query
def bm_node_get_associated(context, service_host=None): query = model_query(context, models.BareMetalNode, read_deleted="no").filter( models.BareMetalNode.instance_uuid != null() ) if service_host: query = query.filter_by(service_host=service_host) return query.all()
def scan_for_null_records(table, col_name, check_fkeys): """Queries the table looking for NULL instances of the given column. :param col_name: The name of the column to look for in the table. :param check_fkeys: If True, check the table for foreign keys back to the instances table and if not found, return. :raises: exception.ValidationError: If any records are found. """ if col_name in table.columns: # NOTE(mriedem): filter out tables that don't have a foreign key back # to the instances table since they could have stale data even if # instances.uuid wasn't NULL. if check_fkeys: fkey_found = False fkeys = table.c[col_name].foreign_keys or [] for fkey in fkeys: if fkey.column.table.name == 'instances': fkey_found = True if not fkey_found: return records = len(list( table.select().where(table.c[col_name] == null()).execute() )) if records: msg = _("There are %(records)d records in the " "'%(table_name)s' table where the uuid or " "instance_uuid column is NULL. These must be " "manually cleaned up before the migration will pass. " "Consider running the " "'nova-manage db null_instance_uuid_scan' command.") % ( {'records': records, 'table_name': table.name}) raise exception.ValidationError(detail=msg)
def check_versions(self): """Checks the whole database for incompatible objects. This scans all the tables in search of objects that are not supported; i.e., those that are not specified in `ironic.common.release_mappings.RELEASE_MAPPING`. This includes objects that have null 'version' values. :returns: A Boolean. True if all the objects have supported versions; False otherwise. """ object_versions = release_mappings.get_object_versions() for model in models.Base.__subclasses__(): if model.__name__ in object_versions: supported_versions = object_versions[model.__name__] if not supported_versions: continue # NOTE(rloo): .notin_ does not handle null: # http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.notin_ query = model_query(model).filter( sql.or_(model.version == sql.null(), model.version.notin_(supported_versions))) if query.count(): return False return True
def update_beacon_receiver_distance(name): """ Calculate the distance between the receiver and its received aircrafts and write this data into each aircraft_beacon. """ last_receiver_beacon = app.session.query(ReceiverBeacon) \ .filter(ReceiverBeacon.name == name) \ .order_by(desc(ReceiverBeacon.timestamp)) \ .first() if (last_receiver_beacon is None): return aircraft_beacons_query = app.session.query(AircraftBeacon) \ .filter(and_(AircraftBeacon.timestamp > last_receiver_beacon.timestamp, AircraftBeacon.receiver_name == name, AircraftBeacon.radius == null())) for aircraft_beacon in aircraft_beacons_query.all(): location0 = (last_receiver_beacon.latitude, last_receiver_beacon.longitude) location1 = (aircraft_beacon.latitude, aircraft_beacon.longitude) alt0 = last_receiver_beacon.altitude alt1 = aircraft_beacon.altitude (flat_distance, phi) = haversine_distance(location0, location1) theta = atan2(alt1 - alt0, flat_distance) * 180 / pi distance = sqrt(flat_distance**2 + (alt1 - alt0)**2) aircraft_beacon.radius = distance aircraft_beacon.theta = theta aircraft_beacon.phi = phi app.session.commit() logger.warning("Updated receiver {}.".format(name))
def organizations_and_counters(): '''Query organizations with their counters''' memberships = aliased(model.Member) query = DB.query(model.Group, func.count(distinct(model.Package.id)).label('nb_datasets'), func.count(distinct(memberships.id)).label('nb_members') ) query = query.outerjoin(CertifiedPublicService) query = query.outerjoin(model.Package, and_( model.Group.id == model.Package.owner_org, ~model.Package.private, model.Package.state == 'active', )) query = query.outerjoin(memberships, and_( memberships.group_id == model.Group.id, memberships.state == 'active', memberships.table_name == 'user' )) query = query.filter(model.Group.state == 'active') query = query.filter(model.Group.approval_status == 'approved') query = query.filter(model.Group.is_organization == True) query = query.group_by(model.Group.id, CertifiedPublicService.organization_id) query = query.order_by( CertifiedPublicService.organization_id == null(), desc('nb_datasets'), desc('nb_members'), model.Group.title ) return query
def _get_build_requests_with_no_instance_uuid(context, limit): """Returns up to $limit build_requests where instance_uuid is null""" # build_requests don't use the SoftDeleteMixin so we don't have to filter # on the deleted column. return context.session.query(api_models.BuildRequest).\ filter_by(instance_uuid=null()).\ limit(limit).\ all()
def upgrade(): network_iface = (CONF.default_network_interface or ('flat' if CONF.dhcp.dhcp_provider == 'neutron' else 'noop')) op.execute( node.update().where( node.c.network_interface == null()).values( {'network_interface': network_iface}))
def const( me, value): if _debug: print '?const', value if value is None: return sql.null() try: m = object_mapper( value) except Exception, e: #print 'XXXXXXXXXXXXXX', e pass
def reschedule_routers_from_down_agents(self): """Reschedule routers from down l3 agents if admin state is up.""" # give agents extra time to handle transient failures agent_dead_limit = cfg.CONF.agent_down_time * 2 # check for an abrupt clock change since last check. if a change is # detected, sleep for a while to let the agents check in. tdelta = timeutils.utcnow() - getattr(self, "_clock_jump_canary", timeutils.utcnow()) if timeutils.total_seconds(tdelta) > cfg.CONF.agent_down_time: LOG.warn( _LW( "Time since last L3 agent reschedule check has " "exceeded the interval between checks. Waiting " "before check to allow agents to send a heartbeat " "in case there was a clock adjustment." ) ) time.sleep(agent_dead_limit) self._clock_jump_canary = timeutils.utcnow() context = n_ctx.get_admin_context() cutoff = timeutils.utcnow() - datetime.timedelta(seconds=agent_dead_limit) down_bindings = ( context.session.query(RouterL3AgentBinding) .join(agents_db.Agent) .filter(agents_db.Agent.heartbeat_timestamp < cutoff, agents_db.Agent.admin_state_up) .outerjoin( l3_attrs_db.RouterExtraAttributes, l3_attrs_db.RouterExtraAttributes.router_id == RouterL3AgentBinding.router_id, ) .filter( sa.or_( l3_attrs_db.RouterExtraAttributes.ha == sql.false(), l3_attrs_db.RouterExtraAttributes.ha == sql.null(), ) ) ) try: for binding in down_bindings: LOG.warn( _LW( "Rescheduling router %(router)s from agent %(agent)s " "because the agent did not report to the server in " "the last %(dead_time)s seconds." ), {"router": binding.router_id, "agent": binding.l3_agent_id, "dead_time": agent_dead_limit}, ) try: self.reschedule_router(context, binding.router_id) except (l3agentscheduler.RouterReschedulingFailed, n_rpc.RemoteError): # Catch individual router rescheduling errors here # so one broken one doesn't stop the iteration. LOG.exception(_LE("Failed to reschedule router %s"), binding.router_id) except db_exc.DBError: # Catch DB errors here so a transient DB connectivity issue # doesn't stop the loopingcall. LOG.exception(_LE("Exception encountered during router " "rescheduling."))
def reschedule_routers_from_down_agents(self): """Reschedule routers from down l3 agents if admin state is up.""" agent_dead_limit = self.agent_dead_limit_seconds() self.wait_down_agents('L3', agent_dead_limit) cutoff = self.get_cutoff_time(agent_dead_limit) context = n_ctx.get_admin_context() down_bindings = ( context.session.query(RouterL3AgentBinding). join(agents_db.Agent). filter(agents_db.Agent.heartbeat_timestamp < cutoff, agents_db.Agent.admin_state_up). outerjoin(l3_attrs_db.RouterExtraAttributes, l3_attrs_db.RouterExtraAttributes.router_id == RouterL3AgentBinding.router_id). filter(sa.or_(l3_attrs_db.RouterExtraAttributes.ha == sql.false(), l3_attrs_db.RouterExtraAttributes.ha == sql.null()))) try: agents_back_online = set() for binding in down_bindings: if binding.l3_agent_id in agents_back_online: continue else: agent = self._get_agent(context, binding.l3_agent_id) if agent.is_active: agents_back_online.add(binding.l3_agent_id) continue agent_mode = self._get_agent_mode(binding.l3_agent) if agent_mode == constants.L3_AGENT_MODE_DVR: # rescheduling from l3 dvr agent on compute node doesn't # make sense. Router will be removed from that agent once # there are no dvr serviceable ports on that compute node LOG.warn(_LW('L3 DVR agent on node %(host)s is down. ' 'Not rescheduling from agent in \'dvr\' ' 'mode.'), {'host': binding.l3_agent.host}) continue LOG.warn(_LW( "Rescheduling router %(router)s from agent %(agent)s " "because the agent did not report to the server in " "the last %(dead_time)s seconds."), {'router': binding.router_id, 'agent': binding.l3_agent_id, 'dead_time': agent_dead_limit}) try: self.reschedule_router(context, binding.router_id) except (l3agentscheduler.RouterReschedulingFailed, oslo_messaging.RemoteError): # Catch individual router rescheduling errors here # so one broken one doesn't stop the iteration. LOG.exception(_LE("Failed to reschedule router %s"), binding.router_id) except Exception: # we want to be thorough and catch whatever is raised # to avoid loop abortion LOG.exception(_LE("Exception encountered during router " "rescheduling."))
def software_version(software_id, version_id): software = Software.query.filter_by(id=software_id).first() if not software: return (render_template("pyfarm/error.html", error="Software %s not found" % software_id), NOT_FOUND) version = SoftwareVersion.query.filter_by( software=software, id=version_id).first() if not version: return (render_template("pyfarm/error.html", error="Version %s not found" % version_id), NOT_FOUND) if request.method == "POST": version.discovery_code =\ request.form["discovery_code"].strip() or sql.null() version.discovery_function_name =\ request.form["discovery_function"].strip() or sql.null() if ((version.discovery_code is None and version.discovery_function_name is not None) or (version.discovery_code is not None and version.discovery_function_name is None)): return (render_template( "pyfarm/error.html", error="`discovery_code` and `discovery_function_name` must " "either be both unset or both set" % software_id), BAD_REQUEST) db.session.add(version) db.session.commit() flash("Discovery code for version %s has been updated." % version.version) return redirect(url_for("single_software_version_ui", software_id=software.id, version_id=version.id), SEE_OTHER) else: return render_template("pyfarm/user_interface/software_version.html", software=software, version=version)
def update_relations(): """Update AircraftBeacon and ReceiverBeacon relations""" # Create missing Receiver from ReceiverBeacon available_receivers = session.query(Receiver.name) \ .subquery() missing_receiver_query = session.query(distinct(ReceiverBeacon.name)) \ .filter(ReceiverBeacon.receiver_id == null()) \ .filter(~ReceiverBeacon.name.in_(available_receivers)) ins = insert(Receiver).from_select([Receiver.name], missing_receiver_query) session.execute(ins) # Create missing Device from AircraftBeacon available_addresses = session.query(Device.address) \ .subquery() missing_addresses_query = session.query(distinct(AircraftBeacon.address)) \ .filter(AircraftBeacon.device_id == null()) \ .filter(~AircraftBeacon.address.in_(available_addresses)) ins2 = insert(Device).from_select([Device.address], missing_addresses_query) session.execute(ins2) # Update AircraftBeacons upd = session.query(AircraftBeacon) \ .filter(AircraftBeacon.device_id == null()) \ .filter(AircraftBeacon.receiver_id == null()) \ .filter(AircraftBeacon.address == Device.address) \ .filter(AircraftBeacon.receiver_name == Receiver.name) \ .update({AircraftBeacon.device_id: Device.id, AircraftBeacon.receiver_id: Receiver.id}, synchronize_session='fetch') upd2 = session.query(ReceiverBeacon) \ .filter(ReceiverBeacon.receiver_id == null()) \ .filter(ReceiverBeacon.receiver_name == Receiver.name) \ .update({Receiver.name: ReceiverBeacon.receiver_name}, synchronize_session='fetch') session.commit() print("Updated {} AircraftBeacons and {} ReceiverBeacons". format(upd, upd2))
def _add_nodes_filters(self, query, filters): if filters is None: filters = {} if 'associated' in filters: if filters['associated']: query = query.filter(models.Node.ironic_node_id != sql.null()) else: query = query.filter(models.Node.ironic_node_id == sql.null()) if 'type' in filters: query = query.filter_by(type=filters['type']) if 'image_id' in filters: query = query.filter_by(image_id=filters['image_id']) if 'project_id' in filters: query = query.filter_by(project_id=filters['project_id']) if 'user_id' in filters: query = query.filter_by(user_id=filters['user_id']) return query
def _add_nodes_filters(self, query, filters): if filters is None: filters = [] if 'chassis_uuid' in filters: # get_chassis_by_uuid() to raise an exception if the chassis # is not found chassis_obj = self.get_chassis_by_uuid(filters['chassis_uuid']) query = query.filter_by(chassis_id=chassis_obj.id) if 'associated' in filters: if filters['associated']: query = query.filter(models.Node.instance_uuid != sql.null()) else: query = query.filter(models.Node.instance_uuid == sql.null()) if 'reserved' in filters: if filters['reserved']: query = query.filter(models.Node.reservation != sql.null()) else: query = query.filter(models.Node.reservation == sql.null()) if 'reserved_by_any_of' in filters: query = query.filter(models.Node.reservation.in_( filters['reserved_by_any_of'])) if 'maintenance' in filters: query = query.filter_by(maintenance=filters['maintenance']) if 'driver' in filters: query = query.filter_by(driver=filters['driver']) if 'resource_class' in filters: query = query.filter_by(resource_class=filters['resource_class']) if 'provision_state' in filters: query = query.filter_by(provision_state=filters['provision_state']) if 'provisioned_before' in filters: limit = (timeutils.utcnow() - datetime.timedelta(seconds=filters['provisioned_before'])) query = query.filter(models.Node.provision_updated_at < limit) if 'inspection_started_before' in filters: limit = ((timeutils.utcnow()) - (datetime.timedelta( seconds=filters['inspection_started_before']))) query = query.filter(models.Node.inspection_started_at < limit) if 'console_enabled' in filters: query = query.filter_by(console_enabled=filters['console_enabled']) return query
def reschedule_routers_from_down_agents(self): """Reschedule routers from down l3 agents if admin state is up.""" agent_dead_limit = self.agent_dead_limit_seconds() self.wait_down_agents('L3', agent_dead_limit) cutoff = self.get_cutoff_time(agent_dead_limit) context = n_ctx.get_admin_context() try: down_bindings = ( context.session.query(RouterL3AgentBinding). join(agents_db.Agent). filter(agents_db.Agent.heartbeat_timestamp < cutoff, agents_db.Agent.admin_state_up). outerjoin(l3_attrs_db.RouterExtraAttributes, l3_attrs_db.RouterExtraAttributes.router_id == RouterL3AgentBinding.router_id). filter(sa.or_(l3_attrs_db.RouterExtraAttributes.ha == sql.false(), l3_attrs_db.RouterExtraAttributes.ha == sql.null()))) agents_back_online = set() for binding in down_bindings: if binding.l3_agent_id in agents_back_online: continue else: # we need new context to make sure we use different DB # transaction - otherwise we may fetch same agent record # each time due to REPEATABLE_READ isolation level context = n_ctx.get_admin_context() agent = self._get_agent(context, binding.l3_agent_id) if agent.is_active: agents_back_online.add(binding.l3_agent_id) continue LOG.warning(_LW( "Rescheduling router %(router)s from agent %(agent)s " "because the agent did not report to the server in " "the last %(dead_time)s seconds."), {'router': binding.router_id, 'agent': binding.l3_agent_id, 'dead_time': agent_dead_limit}) try: self.reschedule_router(context, binding.router_id) except (l3agentscheduler.RouterReschedulingFailed, oslo_messaging.RemoteError): # Catch individual router rescheduling errors here # so one broken one doesn't stop the iteration. LOG.exception(_LE("Failed to reschedule router %s"), binding.router_id) except Exception: # we want to be thorough and catch whatever is raised # to avoid loop abortion LOG.exception(_LE("Exception encountered during router " "rescheduling."))
def get_down_router_bindings(self, context, agent_dead_limit): cutoff = self.get_cutoff_time(agent_dead_limit) return (context.session.query(RouterL3AgentBinding). join(agents_db.Agent). filter(agents_db.Agent.heartbeat_timestamp < cutoff, agents_db.Agent.admin_state_up). outerjoin(l3_attrs_db.RouterExtraAttributes, l3_attrs_db.RouterExtraAttributes.router_id == RouterL3AgentBinding.router_id).filter( sa.or_( l3_attrs_db.RouterExtraAttributes.ha == sql.false(), l3_attrs_db.RouterExtraAttributes.ha == sql.null())))
def upgrade(): op.add_column('nodes', sa.Column('version_id', sa.String(36), server_default='')) op.add_column('nodes', sa.Column('state', sa.Enum(*istate.States.all(), name='node_state'), nullable=False, default=istate.States.finished, server_default=istate.States.finished)) # correct the state: finished -> error if Node.error is not null stmt = Node.update().where(Node.c.error != sql.null()).values( {'state': op.inline_literal(istate.States.error)}) op.execute(stmt)
def bm_node_find_free(context, service_host=None, cpus=None, memory_mb=None, local_gb=None): query = model_query(context, models.BareMetalNode, read_deleted="no") query = query.filter(models.BareMetalNode.instance_uuid == null()) if service_host: query = query.filter_by(service_host=service_host) if cpus is not None: query = query.filter(models.BareMetalNode.cpus >= cpus) if memory_mb is not None: query = query.filter(models.BareMetalNode.memory_mb >= memory_mb) if local_gb is not None: query = query.filter(models.BareMetalNode.local_gb >= local_gb) query = _build_node_order_by(query) return query.first()
def _sql_crit(expression, value): """Produce an equality expression against the given value. This takes into account a value that is actually a collection of values, as well as a value of None or collection that contains None. """ values = utils.to_list(value, default=(None, )) if len(values) == 1: if values[0] is None: return expression == sql.null() else: return expression == values[0] elif _none_set.intersection(values): return sql.or_( expression == sql.null(), _sql_crit(expression, set(values).difference(_none_set)) ) else: return expression.in_(values)
def _get_replica_schedules_filter(context, replica_id=None, schedule_id=None, expired=True): now = timeutils.utcnow() q = _soft_delete_aware_query(context, models.ReplicaSchedule) q = q.join(models.Replica) sched_filter = q.filter() if is_user_context(context): sched_filter = sched_filter.filter( models.Replica.project_id == context.tenant) if replica_id: sched_filter = sched_filter.filter(models.Replica.id == replica_id) if schedule_id: sched_filter = sched_filter.filter( models.ReplicaSchedule.id == schedule_id) if not expired: sched_filter = sched_filter.filter( or_(models.ReplicaSchedule.expiration_date == null(), models.ReplicaSchedule.expiration_date > now)) return sched_filter
def subscribe(): """ Subscribe a limb Use this route to subscribe a limb on Virtual Brain API --- tags: - brain responses: 200: description: Return json with new limb """ req = request.get_json(force=True) name = req['name'] if type(req['name']) is unicode else abort( c.CONFLICT, c.TYPE_ERROR.format('name')) ip = req['ip'] if type(req['ip']) is unicode else abort( c.CONFLICT, c.TYPE_ERROR.format('ip')) area = req['area'] if type(req['area']) is unicode else abort( c.CONFLICT, c.TYPE_ERROR.format('area')) sub_area = req['sub_area'] if 'sub_area' in req else sql.null() result = {"name": name, "ip": ip, "area": area, "sub_area": str(sub_area)} n_limb = Limb(name=name, ip=ip, area=area, sub_area=sub_area) r_limb = db.session.query(Limb).filter(Limb.name == name) if r_limb is None: db.session.add(n_limb) db.session.commit() print str(os.environ.get('VIRTUALSKIN_BRAIN_ADMIN')) p = subprocess.call('bash {} {}'.format( str(os.environ.get('VIRTUALSKIN_BRAIN_ADMIN')) + '/ssh-copy.sh', ip), shell=True) if not p: p = subprocess.call('bash {} {}'.format( str(os.environ.get('VIRTUALSKIN_BRAIN_ADMIN')) + '/ssh-config.sh', ip), shell=True) result = {"status": p} return app.response_class(response=json.dumps(result), status=c.OK, mimetype=c.JSON)
def load_item_into_database(item, item_type, session): # Check if item exists if item is None: return # Get Existing Record instance = find_instance_from_item(item, item_type, session) # If its new, create a new one in the database if instance is None: # Process Race instance = create_new_instance_from_item(item, item_type, session) else: # Set the new attributes for key, value in item.items(): # Exceptions if item_type == 'race': # Never move the off_time later if key == 'off_time': if instance.off_time is not None: if value > instance.off_time: continue if value is None: value = null() # Set the attributes setattr(instance, key, value) # Commit changes session.commit() # Return race instance return instance
def new_file(): form = FileForm() db = OracleDb() users = db.sqlalchemy_session.query(OrmUser).all() if request.method == 'POST': if not form.validate(): return render_template('file_form.html', form=form, form_name="New file", action="new_file") else: if request.form.get('owner_select') != 'None': owner_id = [ user.user_id for user in users if user.user_name == request.form.get('owner_select') ][0] else: owner_id = sql.null() file_obj = OrmFile( file_name=form.file_name.data, file_type=form.file_type.data, file_context=form.file_context.data, file_owner_id=owner_id, file_date=form.file_date.data.strftime("%d-%b-%y")) db = OracleDb() db.sqlalchemy_session.add(file_obj) db.sqlalchemy_session.commit() return redirect(url_for('index_file')) return render_template('file_form.html', form=form, form_name="New file", action="new_file", users=users)
def create_new_instance_from_item(item, item_type, session): # Model Dict model_dict = { 'track': Tracks, 'race': Races, 'horse': Horses, 'jockey': Jockeys, 'trainer': Trainers, 'entry': Entries, 'owner': Owners, 'entry_pool': EntryPools, 'payoff': Payoffs, 'probable': Probables, 'pick': Picks, 'workout': Workouts, 'analysis_probability': AnalysisProbabilities, 'betting_result': BettingResults, 'fractional_time': FractionalTimes, 'point_of_call': PointsOfCall, 'database_statistic': DatabaseStatistics } # Fix any nulls for key, value in item.items(): if value is None: item[key] = null() # Create Instance instance = model_dict[item_type](**item) # Add and commit session.add(instance) session.commit() # Return Instance return instance
def scan_for_null_records(table, col_name, check_fkeys): """Queries the table looking for NULL instances of the given column. :param col_name: The name of the column to look for in the table. :param check_fkeys: If True, check the table for foreign keys back to the instances table and if not found, return. :raises: exception.ValidationError: If any records are found. """ if col_name in table.columns: # NOTE(mriedem): filter out tables that don't have a foreign key back # to the instances table since they could have stale data even if # instances.uuid wasn't NULL. if check_fkeys: fkey_found = False fkeys = table.c[col_name].foreign_keys or [] for fkey in fkeys: if fkey.column.table.name == 'instances': fkey_found = True if not fkey_found: return records = len( list(table.select().where(table.c[col_name] == null()).execute())) if records: msg = _("There are %(records)d records in the " "'%(table_name)s' table where the uuid or " "instance_uuid column is NULL. These must be " "manually cleaned up before the migration will pass. " "Consider running the " "'compute-manage db null_instance_uuid_scan' command.") % ( { 'records': records, 'table_name': table.name }) raise exception.ValidationError(detail=msg)
def load_seed_data(migrate_engine): # Columns in general_seed_data_file.: # user_specimen_id, field_call, bcoral_genet_id, bsym_genet_id, reef, # region, latitude, longitude, geographic_origin, colony_location, # latitude_outplant, longitude_outplant, depth, dist_shore, disease_resist, # bleach_resist, mortality, tle, spawning, collector, org, # collection_date, contact_email, seq_facility, array_version, public, # public_after_date, coral_mlg_clonal_id, symbio_mlg_clonal_id, genetic_coral_species_call, percent_missing_data, # percent_apalm, percent_acerv, percent_mixed, affy_id collector_table_inserts = 0 colony_table_inserts = 0 experiment_table_inserts = 0 genotype_table_inserts = 0 person_table_inserts = 0 phenotype_table_inserts = 0 reef_table_inserts = 0 sample_table_inserts = 0 SAMPLE_ID = 10000 with open(GENERAL_SEED_DATA_FILE, "r") as fh: for i, line in enumerate(fh): if i == 0: # Skip the header. continue line = line.rstrip('\r\n') items = line.split("\t") # Automatically generate the sample_id. sample_id = "A%d" % SAMPLE_ID SAMPLE_ID += 1 user_specimen_id = items[0] if len(items[1]) == 0: field_call = sql.null() else: field_call = items[1] if len(items[2]) == 0: bcoral_genet_id = sql.null() else: bcoral_genet_id = items[2] if len(items[3]) == 0: bsym_genet_id = sql.null() else: bsym_genet_id = items[3] reef = items[4] region = items[5] try: latitude = "%6f" % float(items[6]) except Exception: latitude = sql.null() try: longitude = "%6f" % float(items[7]) except Exception: longitude = sql.null() if len(items[8]) == 0: geographic_origin = sql.null() else: geographic_origin = items[8] if len(items[9]) == 0: colony_location = sql.null() else: colony_location = items[9] try: latitude_outplant = "%6f" % float(items[10]) except Exception: latitude_outplant = sql.null() try: longitude_outplant = "%6f" % float(items[11]) except Exception: longitude_outplant = sql.null() try: depth = int(items[12]) except Exception: depth = 0 if len(items[13]) == 0: dist_shore = sql.null() else: dist_shore = items[13] disease_resist = items[14] bleach_resist = items[15] mortality = items[16] tle = items[17] # Convert original spawning value to Boolean. spawning = string_as_bool(items[18]) collector = items[19] org = items[20] try: collection_date = convert_date_string_for_database(items[21]) except Exception: collection_date = localtimestamp(migrate_engine) contact_email = items[22] seq_facility = items[23] array_version = items[24] # Convert original public value to Boolean. public = string_as_bool(items[25]) if public: public_after_date = sql.null() else: if len(items[26]) == 0: # Set the value of public_after_date to the default. public_after_date = year_from_now else: public_after_date = convert_date_string_for_database( items[26]) coral_mlg_clonal_id = items[27] symbio_mlg_clonal_id = items[28] genetic_coral_species_call = items[29] try: percent_missing_data = "%6f" % float(items[30]) except Exception: percent_missing_data = sql.null() try: percent_apalm = "%6f" % float(items[31]) except Exception: percent_apalm = sql.null() try: percent_acerv = "%6f" % float(items[32]) except Exception: percent_acerv = sql.null() try: percent_mixed = "%6f" % float(items[33]) except Exception: percent_mixed = sql.null() affy_id = items[34] # Process the experiment items. Dependent tables: sample. table = "experiment" # See if we need to add a row to the experiment table. cmd = "SELECT id FROM experiment WHERE seq_facility = '%s' AND array_version = '%s'" cmd = cmd % (seq_facility, array_version) experiment_id = get_primary_id(migrate_engine, table, cmd) if experiment_id is None: # Add a row to the experiment table. cmd = "INSERT INTO experiment VALUES (%s, %s, %s, '%s', '%s')" cmd = cmd % (nextval(migrate_engine, table), localtimestamp(migrate_engine), localtimestamp(migrate_engine), seq_facility, array_version) migrate_engine.execute(cmd) experiment_table_inserts += 1 experiment_id = get_latest_id(migrate_engine, table) # Process the genotype items. Dependent tables: sample. table = "genotype" # See if we need to add a row to the table. cmd = "SELECT id FROM genotype WHERE coral_mlg_clonal_id = '%s' AND symbio_mlg_clonal_id = '%s' AND genetic_coral_species_call = '%s'" cmd = cmd % (coral_mlg_clonal_id, symbio_mlg_clonal_id, genetic_coral_species_call) genotype_id = get_primary_id(migrate_engine, table, cmd) if genotype_id is None: # Add a row to the table. cmd = "INSERT INTO genotype VALUES (%s, %s, %s, '%s', '%s', '%s')" cmd = cmd % (nextval(migrate_engine, table), localtimestamp(migrate_engine), localtimestamp(migrate_engine), coral_mlg_clonal_id, symbio_mlg_clonal_id, genetic_coral_species_call) migrate_engine.execute(cmd) genotype_table_inserts += 1 genotype_id = get_latest_id(migrate_engine, table) # Process the phenotype items. Dependent tables: sample. table = "phenotype" # See if we need to add a row to the table. cmd = "SELECT id FROM phenotype WHERE disease_resist = '%s' AND bleach_resist = '%s' AND mortality = '%s'" cmd += " AND tle = '%s' AND spawning = '%s'" cmd = cmd % (disease_resist, bleach_resist, mortality, tle, spawning) phenotype_id = get_primary_id(migrate_engine, table, cmd) if phenotype_id is None: # Add a row to the table. cmd = "INSERT INTO phenotype VALUES (%s, %s, %s, '%s', '%s', '%s', '%s', '%s')" cmd = cmd % (nextval(migrate_engine, table), localtimestamp(migrate_engine), localtimestamp(migrate_engine), disease_resist, bleach_resist, mortality, tle, spawning) migrate_engine.execute(cmd) phenotype_table_inserts += 1 phenotype_id = get_latest_id(migrate_engine, table) # Process the person items. Dependent tables: collector. table = "person" # See if we need to add a row to the table. if collector.find(" ") > 0: # We have a first and last name spearated by a space. first_last = collector.split(" ") first_name = first_last[0] last_name = first_last[1] cmd = "SELECT id FROM person WHERE last_name = '%s' AND first_name = '%s' AND email = '%s'" % ( last_name, first_name, contact_email) else: # We have a last name with no first name. if len(collector) > 0: last_name = collector else: last_name = 'Unknown' first_name = sql.null() cmd = "SELECT id FROM person WHERE last_name = '%s' and email = '%s'" % ( last_name, contact_email) person_id = get_primary_id(migrate_engine, table, cmd) if person_id is None: # Add a row to the table. cmd = "INSERT INTO person VALUES (%s, %s, %s, '%s', '%s', '%s', '%s')" cmd = cmd % (nextval(migrate_engine, table), localtimestamp(migrate_engine), localtimestamp(migrate_engine), last_name, first_name, org, contact_email) migrate_engine.execute(cmd) person_table_inserts += 1 person_id = get_latest_id(migrate_engine, table) # Process the collector items. Dependent tables: sample. table = "collector" # See if we need to add a row to the table. cmd = "SELECT id FROM collector WHERE person_id = %s" % person_id collector_id = get_primary_id(migrate_engine, table, cmd) if collector_id is None: # Add a row to the table. cmd = "INSERT INTO collector VALUES (%s, %s, %s, %s, %s)" cmd = cmd % (nextval(migrate_engine, table), localtimestamp(migrate_engine), localtimestamp(migrate_engine), person_id, person_id) migrate_engine.execute(cmd) collector_table_inserts += 1 collector_id = get_latest_id(migrate_engine, table) # Process the reef items. Dependent tables: colony. table = "reef" # See if we need to add a row to the table. cmd = "SELECT id FROM reef WHERE name = '%s'" % reef reef_id = get_primary_id(migrate_engine, table, cmd) if reef_id is None: # Add a row to the table. cmd = "INSERT INTO reef VALUES (%s, %s, %s, '%s', '%s', %s, %s)" cmd = cmd % (nextval(migrate_engine, table), localtimestamp(migrate_engine), localtimestamp(migrate_engine), reef, region, latitude, longitude) migrate_engine.execute(cmd) reef_table_inserts += 1 reef_id = get_latest_id(migrate_engine, table) # Process the colony items. Dependent tables: fragment, sample. table = "colony" # See if we need to add a row to the table. cmd = "SELECT id FROM colony WHERE latitude = %s AND longitude = %s and reef_id = %s" cmd = cmd % (latitude_outplant, longitude_outplant, reef_id) colony_id = get_primary_id(migrate_engine, table, cmd) if colony_id is None: # Add a row to the table. cmd = "INSERT INTO colony VALUES (%s, %s, %s, %s, %s, %s, %s)" cmd = cmd % (nextval(migrate_engine, table), localtimestamp(migrate_engine), localtimestamp(migrate_engine), latitude_outplant, longitude_outplant, depth, reef_id) migrate_engine.execute(cmd) colony_table_inserts += 1 colony_id = get_latest_id(migrate_engine, table) # Process the sample items. Dependent tables: None. table = "sample" # See if we need to add a row to the table. cmd = "SELECT id FROM sample WHERE sample_id = '%s'" % sample_id sample_id_db = get_primary_id(migrate_engine, table, cmd) if sample_id_db is None: # Add a row to the table. Values for # the following are not in the seed data. fragment_id = sql.null() taxonomy_id = sql.null() dna_extraction_method = sql.null() dna_concentration = sql.null() percent_missing_data_coral = sql.null() percent_missing_data_sym = sql.null() percent_reference_coral = sql.null() percent_reference_sym = sql.null() percent_alternative_coral = sql.null() percent_alternative_sym = sql.null() percent_hererozygous_coral = sql.null() percent_hererozygous_sym = sql.null() # id, create_time, update_time, affy_id, sample_id, # genotype_id, phenotype_id, experiment_id, colony_id, colony_location, # fragment_id, taxonomy_id, collector_id cmd = "INSERT INTO sample VALUES (%s, %s, %s, '%s', '%s', %s, %s, %s, %s, %s, %s, %s, %s, " if collection_date == "LOCALTIMESTAMP": # collection_date cmd += "%s, " else: # collection_date cmd += "'%s', " # user_specimen_id, depth, dna_extraction_method, dna_concentration, public, # public_after_date, percent_missing_data_coral, percent_missing_data_sym, # percent_reference_coral, percent_reference_sym, percent_alternative_coral, # percent_alternative_sym, percent_hererozygous_coral, percent_hererozygous_sym cmd += "'%s', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" cmd = cmd % ( nextval(migrate_engine, table), localtimestamp(migrate_engine), localtimestamp(migrate_engine), affy_id, sample_id, genotype_id, phenotype_id, experiment_id, colony_id, colony_location, fragment_id, taxonomy_id, collector_id, collection_date, user_specimen_id, depth, dna_extraction_method, dna_concentration, public, public_after_date, percent_missing_data_coral, percent_missing_data_sym, percent_reference_coral, percent_reference_sym, percent_alternative_coral, percent_alternative_sym, percent_hererozygous_coral, percent_hererozygous_sym) migrate_engine.execute(cmd) sample_table_inserts += 1 sample_id = get_latest_id(migrate_engine, table) print("Inserted %d rows into the collector table." % collector_table_inserts) print("Inserted %d rows into the colony table." % colony_table_inserts) print("Inserted %d rows into the experiment table." % experiment_table_inserts) print("Inserted %d rows into the genotype table." % genotype_table_inserts) print("Inserted %d rows into the person table." % person_table_inserts) print("Inserted %d rows into the phenotype table." % phenotype_table_inserts) print("Inserted %d rows into the reef table." % reef_table_inserts) print("Inserted %d rows into the sample table." % sample_table_inserts)
def activo(self): return sql.select([sql.column('fecha_finalizacion') == sql.null()])
def compute_logbook_entries(session=None): logger.info("Compute logbook.") if session is None: session = app.session or_args = [ between(TakeoffLanding.timestamp, '2016-06-28 00:00:00', '2016-06-28 23:59:59') ] or_args = [] # 'wo' is the window order for the sql window function wo = and_(func.date(TakeoffLanding.timestamp), TakeoffLanding.device_id, TakeoffLanding.timestamp, TakeoffLanding.airport_id) # make a query with current, previous and next "takeoff_landing" event, so we can find complete flights sq = session.query( TakeoffLanding.device_id, func.lag(TakeoffLanding.device_id).over(order_by=wo).label('device_id_prev'), func.lead(TakeoffLanding.device_id).over(order_by=wo).label('device_id_next'), TakeoffLanding.timestamp, func.lag(TakeoffLanding.timestamp).over(order_by=wo).label('timestamp_prev'), func.lead(TakeoffLanding.timestamp).over(order_by=wo).label('timestamp_next'), TakeoffLanding.track, func.lag(TakeoffLanding.track).over(order_by=wo).label('track_prev'), func.lead(TakeoffLanding.track).over(order_by=wo).label('track_next'), TakeoffLanding.is_takeoff, func.lag(TakeoffLanding.is_takeoff).over(order_by=wo).label('is_takeoff_prev'), func.lead(TakeoffLanding.is_takeoff).over(order_by=wo).label('is_takeoff_next'), TakeoffLanding.airport_id, func.lag(TakeoffLanding.airport_id).over(order_by=wo).label('airport_id_prev'), func.lead(TakeoffLanding.airport_id).over(order_by=wo).label('airport_id_next')) \ .filter(*or_args) \ .subquery() # find complete flights (with takeoff and landing on the same day) complete_flight_query = session.query( sq.c.timestamp.label('reftime'), sq.c.device_id.label('device_id'), sq.c.timestamp.label('takeoff_timestamp'), sq.c.track.label('takeoff_track'), sq.c.airport_id.label('takeoff_airport_id'), sq.c.timestamp_next.label('landing_timestamp'), sq.c.track_next.label('landing_track'), sq.c.airport_id_next.label('landing_airport_id'), label('duration', sq.c.timestamp_next - sq.c.timestamp)) \ .filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \ .filter(sq.c.device_id == sq.c.device_id_next) \ .filter(func.date(sq.c.timestamp_next) == func.date(sq.c.timestamp)) # split complete flights (with takeoff and landing on different days) into one takeoff and one landing split_start_query = session.query( sq.c.timestamp.label('reftime'), sq.c.device_id.label('device_id'), sq.c.timestamp.label('takeoff_timestamp'), sq.c.track.label('takeoff_track'), sq.c.airport_id.label('takeoff_airport_id'), null().label('landing_timestamp'), null().label('landing_track'), null().label('landing_airport_id'), null().label('duration')) \ .filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \ .filter(sq.c.device_id == sq.c.device_id_next) \ .filter(func.date(sq.c.timestamp_next) != func.date(sq.c.timestamp)) split_landing_query = session.query( sq.c.timestamp_next.label('reftime'), sq.c.device_id.label('device_id'), null().label('takeoff_timestamp'), null().label('takeoff_track'), null().label('takeoff_airport_id'), sq.c.timestamp_next.label('landing_timestamp'), sq.c.track_next.label('landing_track'), sq.c.airport_id_next.label('landing_airport_id'), null().label('duration')) \ .filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \ .filter(sq.c.device_id == sq.c.device_id_next) \ .filter(func.date(sq.c.timestamp_next) != func.date(sq.c.timestamp)) # find landings without start only_landings_query = session.query( sq.c.timestamp.label('reftime'), sq.c.device_id.label('device_id'), null().label('takeoff_timestamp'), null().label('takeoff_track'), null().label('takeoff_airport_id'), sq.c.timestamp.label('landing_timestamp'), sq.c.track.label('landing_track'), sq.c.airport_id.label('landing_airport_id'), null().label('duration')) \ .filter(sq.c.is_takeoff == false()) \ .filter(or_(sq.c.device_id != sq.c.device_id_prev, sq.c.is_takeoff_prev == false(), sq.c.is_takeoff_prev == null())) # find starts without landing only_starts_query = session.query( sq.c.timestamp.label('reftime'), sq.c.device_id.label('device_id'), sq.c.timestamp.label('takeoff_timestamp'), sq.c.track.label('takeoff_track'), sq.c.airport_id.label('takeoff_airport_id'), null().label('landing_timestamp'), null().label('landing_track'), null().label('landing_airport_id'), null().label('duration')) \ .filter(sq.c.is_takeoff == true()) \ .filter(or_(sq.c.device_id != sq.c.device_id_next, sq.c.is_takeoff_next == true(), sq.c.is_takeoff_next == null())) # unite all computated flights union_query = complete_flight_query.union( split_start_query, split_landing_query, only_landings_query, only_starts_query) \ .subquery() # if a logbook entry exist --> update it upd = update(Logbook) \ .where(and_(Logbook.device_id == union_query.c.device_id, union_query.c.takeoff_airport_id != null(), union_query.c.landing_airport_id != null(), or_(and_(Logbook.takeoff_airport_id == union_query.c.takeoff_airport_id, Logbook.takeoff_timestamp == union_query.c.takeoff_timestamp, Logbook.landing_airport_id == null()), and_(Logbook.takeoff_airport_id == null(), Logbook.landing_airport_id == union_query.c.landing_airport_id, Logbook.landing_timestamp == union_query.c.landing_timestamp)))) \ .values({"takeoff_timestamp": union_query.c.takeoff_timestamp, "takeoff_track": union_query.c.takeoff_track, "takeoff_airport_id": union_query.c.takeoff_airport_id, "landing_timestamp": union_query.c.landing_timestamp, "landing_track": union_query.c.landing_track, "landing_airport_id": union_query.c.landing_airport_id, "duration": union_query.c.duration}) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.debug("Updated logbook entries: {}".format(update_counter)) # if a logbook entry doesnt exist --> insert it new_logbook_entries = session.query(union_query) \ .filter(~exists().where( and_(Logbook.device_id == union_query.c.device_id, or_(and_(Logbook.takeoff_airport_id == union_query.c.takeoff_airport_id, Logbook.takeoff_timestamp == union_query.c.takeoff_timestamp), and_(Logbook.takeoff_airport_id == null(), union_query.c.takeoff_airport_id == null())), or_(and_(Logbook.landing_airport_id == union_query.c.landing_airport_id, Logbook.landing_timestamp == union_query.c.landing_timestamp), and_(Logbook.landing_airport_id == null(), union_query.c.landing_airport_id == null()))))) ins = insert(Logbook).from_select( (Logbook.reftime, Logbook.device_id, Logbook.takeoff_timestamp, Logbook.takeoff_track, Logbook.takeoff_airport_id, Logbook.landing_timestamp, Logbook.landing_track, Logbook.landing_airport_id, Logbook.duration), new_logbook_entries) result = session.execute(ins) insert_counter = result.rowcount session.commit() logger.debug("New logbook entries: {}".format(insert_counter)) return "{}/{}".format(update_counter, insert_counter)
def update_receivers(session, logger=None): """Update receivers with stats.""" if logger is None: logger = app.logger receiver_stats = (session.query( distinct(ReceiverStats.receiver_id).label("receiver_id"), func.first_value(ReceiverStats.firstseen).over( partition_by=ReceiverStats.receiver_id, order_by=case([(ReceiverStats.firstseen == null(), None)], else_=ReceiverStats.date).asc().nullslast()).label( "firstseen"), func.first_value(ReceiverStats.lastseen).over( partition_by=ReceiverStats.receiver_id, order_by=case([(ReceiverStats.lastseen == null(), None)], else_=ReceiverStats.date).desc().nullslast()).label( "lastseen"), func.first_value(ReceiverStats.location_wkt).over( partition_by=ReceiverStats.receiver_id, order_by=case([(ReceiverStats.location_wkt == null(), None)], else_=ReceiverStats.date).desc().nullslast()).label( "location_wkt"), func.first_value(ReceiverStats.altitude).over( partition_by=ReceiverStats.receiver_id, order_by=case([(ReceiverStats.altitude == null(), None)], else_=ReceiverStats.date).desc().nullslast()).label( "altitude"), func.first_value(ReceiverStats.version).over( partition_by=ReceiverStats.receiver_id, order_by=case( [(ReceiverStats.version == null(), None)], else_=ReceiverStats.date).desc().nullslast()).label("version"), func.first_value(ReceiverStats.platform).over( partition_by=ReceiverStats.receiver_id, order_by=case([(ReceiverStats.platform == null(), None)], else_=ReceiverStats.date).desc().nullslast()).label( "platform"), ).order_by(ReceiverStats.receiver_id).subquery()) upd = (update(Receiver).where( and_(Receiver.id == receiver_stats.c.receiver_id)).values({ "firstseen": receiver_stats.c.firstseen, "lastseen": receiver_stats.c.lastseen, "location": receiver_stats.c.location_wkt, "altitude": receiver_stats.c.altitude, "version": receiver_stats.c.version, "platform": receiver_stats.c.platform, })) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.warn("Updated {} Receivers".format(update_counter)) return "Updated {} Receivers".format(update_counter)
def create_device_stats(session, date, logger=None): """Add/update device stats.""" if logger is None: logger = app.logger (start, end) = date_to_timestamps(date) # First kill the stats for the selected date deleted_counter = session.query(DeviceStats).filter( DeviceStats.date == date).delete() # Since "distinct count" does not work in window functions we need a work-around for receiver counting sq = (session.query( AircraftBeacon, func.dense_rank().over( partition_by=AircraftBeacon.device_id, order_by=AircraftBeacon.receiver_id).label("dr")).filter( and_(between(AircraftBeacon.timestamp, start, end), AircraftBeacon.device_id != null())).filter( or_(AircraftBeacon.error_count == 0, AircraftBeacon.error_count == null())).subquery()) # Calculate stats, firstseen, lastseen and last values != NULL device_stats = session.query( distinct(sq.c.device_id).label("device_id"), literal(date).label("date"), func.max( sq.c.dr).over(partition_by=sq.c.device_id).label("receiver_count"), func.max(sq.c.altitude).over( partition_by=sq.c.device_id).label("max_altitude"), func.count(sq.c.device_id).over( partition_by=sq.c.device_id).label("aircraft_beacon_count"), func.first_value(sq.c.name).over( partition_by=sq.c.device_id, order_by=case( [(sq.c.name == null(), None)], else_=sq.c.timestamp).asc().nullslast()).label("name"), func.first_value(sq.c.timestamp).over( partition_by=sq.c.device_id, order_by=case( [(sq.c.timestamp == null(), None)], else_=sq.c.timestamp).asc().nullslast()).label("firstseen"), func.first_value(sq.c.timestamp).over( partition_by=sq.c.device_id, order_by=case( [(sq.c.timestamp == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("lastseen"), func.first_value(sq.c.aircraft_type).over( partition_by=sq.c.device_id, order_by=case([(sq.c.aircraft_type == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "aircraft_type"), func.first_value(sq.c.stealth).over( partition_by=sq.c.device_id, order_by=case( [(sq.c.stealth == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("stealth"), func.first_value(sq.c.software_version).over( partition_by=sq.c.device_id, order_by=case([(sq.c.software_version == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "software_version"), func.first_value(sq.c.hardware_version).over( partition_by=sq.c.device_id, order_by=case([(sq.c.hardware_version == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "hardware_version"), func.first_value(sq.c.real_address).over( partition_by=sq.c.device_id, order_by=case([(sq.c.real_address == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "real_address"), ).subquery() # And insert them ins = insert(DeviceStats).from_select( [ DeviceStats.device_id, DeviceStats.date, DeviceStats.receiver_count, DeviceStats.max_altitude, DeviceStats.aircraft_beacon_count, DeviceStats.name, DeviceStats.firstseen, DeviceStats.lastseen, DeviceStats.aircraft_type, DeviceStats.stealth, DeviceStats.software_version, DeviceStats.hardware_version, DeviceStats.real_address, ], device_stats, ) res = session.execute(ins) insert_counter = res.rowcount session.commit() logger.debug("DeviceStats for {}: {} deleted, {} inserted".format( date, deleted_counter, insert_counter)) return "DeviceStats for {}: {} deleted, {} inserted".format( date, deleted_counter, insert_counter)
NsfHerdAcademicField('J03', 'J', 'Education', 'Non-STEM Fields', 'Education and Human Science'), NsfHerdAcademicField('J04', 'J', 'Humanities', 'Non-STEM Fields', 'Arts and Sciences'), NsfHerdAcademicField('J05', 'J', 'Law', 'Non-STEM Fields', 'Law'), NsfHerdAcademicField('J06', 'J', 'Social Work', 'Non-STEM Fields', 'Arts and Sciences'), NsfHerdAcademicField('J07', 'J', 'Visual and Performing Arts', 'Non-STEM Fields', 'Fine and Performing Arts'), NsfHerdAcademicField('J08', 'J', 'Other Non-STEM Fields', 'Non-STEM Fields', 'Architecture') ]) print("Reading date dimension data.") dates = pd.read_csv('data/date_dimension.csv') dates = dates.fillna(sql.null()) print("Reading state data") states = pd.read_csv('data/states.csv', encoding='utf-16') print("Reading custom peer list data") lists = pd.read_csv('data/ipeds_custom_peer_lists.csv', encoding='utf-8') session = Session() try: # bulk insert objects for federal agencies and academic fields (NSF) print('\nPopulating dimension tables.') session.bulk_save_objects(objects) print('\tFinished populating nsf_herd_federal_agencies.') print('\tFinished populating nsf_herd_academic_fields.')
def jobtype(jobtype_id): """ UI endpoint for a single jobtype. Allows showing and updating the jobtype """ jobtype = JobType.query.filter_by(id=jobtype_id).first() if not jobtype: return (render_template("pyfarm/error.html", error="Jobtype %s not found" % jobtype_id), NOT_FOUND) if request.method == "POST": with db.session.no_autoflush: jobtype.description = request.form["description"] new_version = JobTypeVersion(jobtype=jobtype) new_version.max_batch = request.form["max_batch"].strip() or\ sql.null() new_version.batch_contiguous =\ ("batch_contiguous" in request.form and request.form["batch_contiguous"] == "true") new_version.no_automatic_start_time =\ ("no_automatic_start_time" in request.form and request.form["no_automatic_start_time"] == "true") new_version.classname = request.form["classname"] new_version.code = request.form["code"] max_version, = db.session.query(func.max( JobTypeVersion.version)).filter_by(jobtype=jobtype).first() new_version.version = (max_version or 0) + 1 previous_version = JobTypeVersion.query.filter_by( jobtype=jobtype).order_by(desc( JobTypeVersion.version)).first() if previous_version: for requirement in previous_version.software_requirements: new_requirement = JobTypeSoftwareRequirement() new_requirement.jobtype_version = new_version new_requirement.software = requirement.software new_requirement.min_version = requirement.min_version new_requirement.max_version = requirement.max_version db.session.add(new_requirement) db.session.add(jobtype) db.session.add(new_version) db.session.commit() flash("Jobtype %s updated to version %s" % (jobtype.name, new_version.version)) return redirect( url_for("single_jobtype_ui", jobtype_id=jobtype.id), SEE_OTHER) else: latest_version = JobTypeVersion.query.filter_by( jobtype=jobtype).order_by(desc(JobTypeVersion.version)).first() if not latest_version: return (render_template("pyfarm/error.html", error="Jobtype %s has no versions" % jobtype_id), INTERNAL_SERVER_ERROR) return render_template("pyfarm/user_interface/jobtype.html", jobtype=jobtype, latest_version=latest_version, software_items=Software.query)
def test_reduce_aliased_union_2(self): metadata = MetaData() page_table = Table('page', metadata, Column('id', Integer, primary_key=True)) magazine_page_table = Table('magazine_page', metadata, Column('page_id', Integer, ForeignKey('page.id'), primary_key=True)) classified_page_table = Table('classified_page', metadata, Column('magazine_page_id', Integer, ForeignKey('magazine_page.page_id'), primary_key=True)) # this is essentially the union formed by the ORM's # polymorphic_union function. we define two versions with # different ordering of selects. # # the first selectable has the "real" column # classified_page.magazine_page_id pjoin = union( select([ page_table.c.id, magazine_page_table.c.page_id, classified_page_table.c.magazine_page_id ]). select_from( page_table.join(magazine_page_table). join(classified_page_table)), select([ page_table.c.id, magazine_page_table.c.page_id, cast(null(), Integer).label('magazine_page_id') ]). select_from(page_table.join(magazine_page_table)) ).alias('pjoin') eq_(util.column_set(sql_util.reduce_columns([pjoin.c.id, pjoin.c.page_id, pjoin.c.magazine_page_id])), util.column_set([pjoin.c.id])) # the first selectable has a CAST, which is a placeholder for # classified_page.magazine_page_id in the second selectable. # reduce_columns needs to take into account all foreign keys # derived from pjoin.c.magazine_page_id. the UNION construct # currently makes the external column look like that of the # first selectable only. pjoin = union(select([ page_table.c.id, magazine_page_table.c.page_id, cast(null(), Integer).label('magazine_page_id') ]). select_from(page_table.join(magazine_page_table)), select([ page_table.c.id, magazine_page_table.c.page_id, classified_page_table.c.magazine_page_id ]). select_from(page_table.join(magazine_page_table). join(classified_page_table)) ).alias('pjoin') eq_(util.column_set(sql_util.reduce_columns([pjoin.c.id, pjoin.c.page_id, pjoin.c.magazine_page_id])), util.column_set([pjoin.c.id]))
def reschedule_routers_from_down_agents(self): """Reschedule routers from down l3 agents if admin state is up.""" agent_dead_limit = self.agent_dead_limit_seconds() self.wait_down_agents('L3', agent_dead_limit) cutoff = self.get_cutoff_time(agent_dead_limit) context = n_ctx.get_admin_context() down_bindings = (context.session.query(RouterL3AgentBinding).join( agents_db.Agent).filter( agents_db.Agent.heartbeat_timestamp < cutoff, agents_db.Agent.admin_state_up ).outerjoin( l3_attrs_db.RouterExtraAttributes, l3_attrs_db.RouterExtraAttributes.router_id == RouterL3AgentBinding.router_id).filter( sa.or_( l3_attrs_db.RouterExtraAttributes.ha == sql.false(), l3_attrs_db.RouterExtraAttributes.ha == sql.null()))) try: agents_back_online = set() for binding in down_bindings: if binding.l3_agent_id in agents_back_online: continue else: agent = self._get_agent(context, binding.l3_agent_id) if agent.is_active: agents_back_online.add(binding.l3_agent_id) continue agent_mode = self._get_agent_mode(binding.l3_agent) if agent_mode == constants.L3_AGENT_MODE_DVR: # rescheduling from l3 dvr agent on compute node doesn't # make sense. Router will be removed from that agent once # there are no dvr serviceable ports on that compute node LOG.warn( _LW('L3 DVR agent on node %(host)s is down. ' 'Not rescheduling from agent in \'dvr\' ' 'mode.'), {'host': binding.l3_agent.host}) continue LOG.warn( _LW("Rescheduling router %(router)s from agent %(agent)s " "because the agent did not report to the server in " "the last %(dead_time)s seconds."), { 'router': binding.router_id, 'agent': binding.l3_agent_id, 'dead_time': agent_dead_limit }) try: self.reschedule_router(context, binding.router_id) except (l3agentscheduler.RouterReschedulingFailed, oslo_messaging.RemoteError): # Catch individual router rescheduling errors here # so one broken one doesn't stop the iteration. LOG.exception(_LE("Failed to reschedule router %s"), binding.router_id) except Exception: # we want to be thorough and catch whatever is raised # to avoid loop abortion LOG.exception( _LE("Exception encountered during router " "rescheduling."))
def upgrade(): bind = op.get_bind() session = orm.Session(bind=bind) blocks = [ { "blocknumber": 22477107, "txhash": "0xd16cd27f8635e84cbb6d02ecf6a5304bae998381cda1291d4cb690d9159196d9", "blockhash": "0x82ba068410ac71a70dfe95708b3c4ec08884355d8384423deb2337a6bcd64dd0", }, { "blocknumber": 22477427, "txhash": "0x135c236ebb4d02a1d2bc13bf946e982871ad59a29ed413bbe6aed2acb60a1f17", "blockhash": "0x63b16e801cb90f3497043a904e0180a8161d9bbb45add0a9380e277b0befbbba", }, { "blocknumber": 22477062, "txhash": "0x05771964bc0527268c330b78bb1819998d589d31a95e5f2f8aa846041de1dfee", "blockhash": "0x8e5f3edd55d1c7f3d6484a8fb7d2a9d825fb53ecb446c8959dd3887fa999d2e7", }, { "blocknumber": 22477832, "txhash": "0xda0b64477469390eaa3b17098c01f3e03e5b0e52959ffd493804538dda039a92", "blockhash": "0x705d01ed50cf8f1d83686acc19c9d0bdd52493b3552532543629cdf07fba6005", }, ] for block in blocks: block_query = (session.query(Block).filter( Block.blockhash == block["blockhash"], Block.number == block["blocknumber"], ).first()) if not block_query: # If the block is missing, do not continue # This migration is a production hotfix return session.flush() tracks = [ Track( track_id=470883, blocknumber=22477107, is_current=True, is_delete=False, txhash= "0xd16cd27f8635e84cbb6d02ecf6a5304bae998381cda1291d4cb690d9159196d9", blockhash= "0x82ba068410ac71a70dfe95708b3c4ec08884355d8384423deb2337a6bcd64dd0", owner_id=3538, route_id="", cover_art=None, cover_art_sizes="QmZFgQiupP2KWoqYZGqJ83zEwHtXMj6QhnNNfDCLpTax4q", title="???", tags="electro,house,2021", genre="Electro", mood="Fiery", credits_splits=None, remix_of=None, create_date=None, release_date="Fri Jul 30 2021 17:29:24 GMT+0200", length=None, file_type=None, description=None, license="All rights reserved", isrc=None, iswc=None, track_segments=[ { "multihash": "QmW7kQwqbZcYWQHtYdXK6KezSCqQ1vRdwhdWtWvTT79rnk", "duration": 6.016, }, { "multihash": "QmYQEkQvPGHgkMLGhvfYcZdd385KmoifAer5kxnAuYhf4E", "duration": 5.994667, }, { "multihash": "QmVj5uvFPBpa9635hL17EcYPJAQURm6rqJdvociR6Qk1Q2", "duration": 5.994667, }, { "multihash": "QmW5QyPo5kUSXs1v5oJTrLWdFB1JHciWKcMpw3GbY7a7rX", "duration": 5.994667, }, { "multihash": "QmZeRjqj9SC1depFGw5vLwdAiayBr7rdrtSiUKYtoFaJix", "duration": 6, }, { "multihash": "QmQ2dPUnNMay2Yxj6pj7Jw7WvpodXoQxZvUTdSKjMcoF56", "duration": 0.021333, }, ], download={ "is_downloadable": False, "requires_follow": False, "cid": None, }, updated_at="July-30-2021 10:29:40", created_at="July-30-2021 10:29:40", is_unlisted=False, field_visibility={ "genre": True, "mood": True, "tags": True, "share": True, "play_count": True, "remixes": True, }, stem_of=null(), ), Track( track_id=470884, blocknumber=22477427, is_current=True, is_delete=False, txhash= "0x135c236ebb4d02a1d2bc13bf946e982871ad59a29ed413bbe6aed2acb60a1f17", blockhash= "0x63b16e801cb90f3497043a904e0180a8161d9bbb45add0a9380e277b0befbbba", owner_id=3538, route_id="", cover_art=None, cover_art_sizes="QmZFgQiupP2KWoqYZGqJ83zEwHtXMj6QhnNNfDCLpTax4q", title="???", tags="electro,house,2021", genre="Electro", mood="Fiery", credits_splits=None, remix_of=None, create_date=None, release_date="Fri Jul 30 2021 16:55:42 GMT+0200", length=None, file_type=None, description=None, license="All rights reserved", isrc=None, iswc=None, track_segments=[ { "multihash": "QmW7kQwqbZcYWQHtYdXK6KezSCqQ1vRdwhdWtWvTT79rnk", "duration": 6.016, }, { "multihash": "QmYQEkQvPGHgkMLGhvfYcZdd385KmoifAer5kxnAuYhf4E", "duration": 5.994667, }, { "multihash": "QmVj5uvFPBpa9635hL17EcYPJAQURm6rqJdvociR6Qk1Q2", "duration": 5.994667, }, { "multihash": "QmW5QyPo5kUSXs1v5oJTrLWdFB1JHciWKcMpw3GbY7a7rX", "duration": 5.994667, }, { "multihash": "QmZeRjqj9SC1depFGw5vLwdAiayBr7rdrtSiUKYtoFaJix", "duration": 6, }, { "multihash": "QmQ2dPUnNMay2Yxj6pj7Jw7WvpodXoQxZvUTdSKjMcoF56", "duration": 0.021333, }, ], download={ "is_downloadable": False, "requires_follow": False, "cid": None, }, updated_at="July-30-2021 10:56:20", created_at="July-30-2021 10:56:20", is_unlisted=False, field_visibility={ "genre": True, "mood": True, "tags": True, "share": True, "play_count": True, "remixes": True, }, stem_of=null(), ), Track( track_id=470892, blocknumber=22477062, is_current=True, is_delete=False, txhash= "0x05771964bc0527268c330b78bb1819998d589d31a95e5f2f8aa846041de1dfee", blockhash= "0x8e5f3edd55d1c7f3d6484a8fb7d2a9d825fb53ecb446c8959dd3887fa999d2e7", owner_id=3538, route_id="", cover_art=None, cover_art_sizes="QmZFgQiupP2KWoqYZGqJ83zEwHtXMj6QhnNNfDCLpTax4q", title="???", tags="electro,house,2021", genre="Electro", mood="Fiery", credits_splits=None, remix_of=None, create_date=None, release_date="Fri Jul 30 2021 16:24:35 GMT+0200", length=None, file_type=None, description=None, license="All rights reserved", isrc=None, iswc=None, track_segments=[ { "multihash": "QmW7kQwqbZcYWQHtYdXK6KezSCqQ1vRdwhdWtWvTT79rnk", "duration": 6.016, }, { "multihash": "QmYQEkQvPGHgkMLGhvfYcZdd385KmoifAer5kxnAuYhf4E", "duration": 5.994667, }, { "multihash": "QmVj5uvFPBpa9635hL17EcYPJAQURm6rqJdvociR6Qk1Q2", "duration": 5.994667, }, { "multihash": "QmW5QyPo5kUSXs1v5oJTrLWdFB1JHciWKcMpw3GbY7a7rX", "duration": 5.994667, }, { "multihash": "QmZeRjqj9SC1depFGw5vLwdAiayBr7rdrtSiUKYtoFaJix", "duration": 6, }, { "multihash": "QmQ2dPUnNMay2Yxj6pj7Jw7WvpodXoQxZvUTdSKjMcoF56", "duration": 0.021333, }, ], download={ "is_downloadable": False, "requires_follow": False, "cid": None, }, updated_at="July-30-2021 10:25:55", created_at="July-30-2021 10:25:55", is_unlisted=False, field_visibility={ "genre": True, "mood": True, "tags": True, "share": True, "play_count": True, "remixes": True, }, stem_of=null(), ), Track( track_id=470910, blocknumber=22477832, is_current=True, is_delete=False, txhash= "0xda0b64477469390eaa3b17098c01f3e03e5b0e52959ffd493804538dda039a92", blockhash= "0x705d01ed50cf8f1d83686acc19c9d0bdd52493b3552532543629cdf07fba6005", owner_id=3538, route_id="", cover_art=None, cover_art_sizes="QmZFgQiupP2KWoqYZGqJ83zEwHtXMj6QhnNNfDCLpTax4q", title="???", tags="electro,house,2021", genre="Electro", mood="Fiery", credits_splits=None, remix_of=None, create_date=None, release_date="Fri Jul 30 2021 17:29:24 GMT+0200", length=None, file_type=None, description=None, license="All rights reserved", isrc=None, iswc=None, track_segments=[ { "multihash": "QmW7kQwqbZcYWQHtYdXK6KezSCqQ1vRdwhdWtWvTT79rnk", "duration": 6.016, }, { "multihash": "QmYQEkQvPGHgkMLGhvfYcZdd385KmoifAer5kxnAuYhf4E", "duration": 5.994667, }, { "multihash": "QmVj5uvFPBpa9635hL17EcYPJAQURm6rqJdvociR6Qk1Q2", "duration": 5.994667, }, { "multihash": "QmW5QyPo5kUSXs1v5oJTrLWdFB1JHciWKcMpw3GbY7a7rX", "duration": 5.994667, }, { "multihash": "QmZeRjqj9SC1depFGw5vLwdAiayBr7rdrtSiUKYtoFaJix", "duration": 6, }, { "multihash": "QmQ2dPUnNMay2Yxj6pj7Jw7WvpodXoQxZvUTdSKjMcoF56", "duration": 0.021333, }, ], download={ "is_downloadable": False, "requires_follow": False, "cid": None, }, updated_at="July-30-2021 11:30:05", created_at="July-30-2021 11:30:05", is_unlisted=False, field_visibility={ "genre": True, "mood": True, "tags": True, "share": True, "play_count": True, "remixes": True, }, stem_of=null(), ), ] session.add_all(tracks) track_routes = [ TrackRoute( slug="oWXPm", title_slug="oWXPm", collision_id=0, owner_id=3538, track_id=470883, is_current=True, txhash= "0xd16cd27f8635e84cbb6d02ecf6a5304bae998381cda1291d4cb690d9159196d9", blockhash= "0x82ba068410ac71a70dfe95708b3c4ec08884355d8384423deb2337a6bcd64dd0", blocknumber=22477107, ), TrackRoute( slug="6YBxG", title_slug="6YBxG", collision_id=0, owner_id=3538, track_id=470884, is_current=True, blocknumber=22477427, txhash= "0x135c236ebb4d02a1d2bc13bf946e982871ad59a29ed413bbe6aed2acb60a1f17", blockhash= "0x63b16e801cb90f3497043a904e0180a8161d9bbb45add0a9380e277b0befbbba", ), TrackRoute( slug="KV8xz", title_slug="KV8xz", collision_id=0, owner_id=3538, track_id=470892, is_current=True, blocknumber=22477062, txhash= "0x05771964bc0527268c330b78bb1819998d589d31a95e5f2f8aa846041de1dfee", blockhash= "0x8e5f3edd55d1c7f3d6484a8fb7d2a9d825fb53ecb446c8959dd3887fa999d2e7", ), TrackRoute( slug="Nl2xw", title_slug="Nl2xw", collision_id=0, owner_id=3538, track_id=470910, is_current=True, blocknumber=22477832, txhash= "0xda0b64477469390eaa3b17098c01f3e03e5b0e52959ffd493804538dda039a92", blockhash= "0x705d01ed50cf8f1d83686acc19c9d0bdd52493b3552532543629cdf07fba6005", ), ] session.add_all(track_routes) session.query(SkippedTransaction).filter( SkippedTransaction.txhash.in_([ "0x05771964bc0527268c330b78bb1819998d589d31a95e5f2f8aa846041de1dfee", "0xd16cd27f8635e84cbb6d02ecf6a5304bae998381cda1291d4cb690d9159196d9", "0x135c236ebb4d02a1d2bc13bf946e982871ad59a29ed413bbe6aed2acb60a1f17", "0xda0b64477469390eaa3b17098c01f3e03e5b0e52959ffd493804538dda039a92", ])).delete(synchronize_session="fetch") session.commit()
def create_receiver_stats(session, date, logger=None): """Add/update receiver stats.""" if logger is None: logger = app.logger (start, end) = date_to_timestamps(date) # First kill the stats for the selected date deleted_counter = session.query(ReceiverStats).filter( ReceiverStats.date == date).delete() # Select one day sq = session.query(ReceiverBeacon).filter( between(ReceiverBeacon.timestamp, start, end)).subquery() # Calculate stats, firstseen, lastseen and last values != NULL receiver_stats = session.query( distinct(sq.c.receiver_id).label("receiver_id"), literal(date).label("date"), func.first_value(sq.c.timestamp).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.timestamp == null(), None)], else_=sq.c.timestamp).asc().nullslast()).label("firstseen"), func.first_value(sq.c.timestamp).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.timestamp == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("lastseen"), func.first_value(sq.c.location).over( partition_by=sq.c.receiver_id, order_by=case([(sq.c.location == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "location_wkt"), func.first_value(sq.c.altitude).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.altitude == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("altitude"), func.first_value(sq.c.version).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.version == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("version"), func.first_value(sq.c.platform).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.platform == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("platform"), ).subquery() # And insert them ins = insert(ReceiverStats).from_select( [ ReceiverStats.receiver_id, ReceiverStats.date, ReceiverStats.firstseen, ReceiverStats.lastseen, ReceiverStats.location_wkt, ReceiverStats.altitude, ReceiverStats.version, ReceiverStats.platform, ], receiver_stats, ) res = session.execute(ins) insert_counter = res.rowcount session.commit() logger.warn("ReceiverStats for {}: {} deleted, {} inserted".format( date, deleted_counter, insert_counter)) # Update aircraft_beacon_count, aircraft_count and max_distance aircraft_beacon_stats = (session.query( AircraftBeacon.receiver_id, func.count(AircraftBeacon.timestamp).label("aircraft_beacon_count"), func.count(func.distinct( AircraftBeacon.device_id)).label("aircraft_count"), func.max(AircraftBeacon.distance).label("max_distance"), ).filter( and_(between(AircraftBeacon.timestamp, start, end), AircraftBeacon.error_count == 0, AircraftBeacon.quality <= MAX_PLAUSIBLE_QUALITY, AircraftBeacon.relay == null())).group_by( AircraftBeacon.receiver_id).subquery()) upd = (update(ReceiverStats).where( and_(ReceiverStats.date == date, ReceiverStats.receiver_id == aircraft_beacon_stats.c.receiver_id)).values({ "aircraft_beacon_count": aircraft_beacon_stats.c.aircraft_beacon_count, "aircraft_count": aircraft_beacon_stats.c.aircraft_count, "max_distance": aircraft_beacon_stats.c.max_distance })) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.warn("Updated {} ReceiverStats".format(update_counter)) return "ReceiverStats for {}: {} deleted, {} inserted, {} updated".format( date, deleted_counter, insert_counter, update_counter)
def create_jobtype(): if request.method == "GET": return render_template("pyfarm/user_interface/jobtype_create.html", jobtypes=JobType.query, software_items=Software.query) else: with db.session.no_autoflush: jobtype = JobType() jobtype.name = request.form["name"] jobtype.description = request.form["description"] jobtype_version = JobTypeVersion() jobtype_version.jobtype = jobtype jobtype_version.version = 1 jobtype_version.max_batch = request.form["max_batch"].strip() or\ sql.null() jobtype_version.batch_contiguous =\ ("batch_contiguous" in request.form and request.form["batch_contiguous"] == "true") jobtype_version.no_automatic_start_time =\ ("no_automatic_start_time" in request.form and request.form["no_automatic_start_time"] == "true") jobtype_version.classname = request.form["classname"] jobtype_version.code = request.form["code"] requirements = zip(request.form.getlist("software"), request.form.getlist("min_version"), request.form.getlist("min_version")) for requirement_tuple in requirements: software = Software.query.filter_by( id=int(requirement_tuple[0])).first() if not software: return (render_template("pyfarm/error.html", error="Software %s not found" % requirement_tuple[0]), NOT_FOUND) requirement = JobTypeSoftwareRequirement() requirement.software = software requirement.jobtype_version = jobtype_version if requirement_tuple[1] != "": minimum_version = SoftwareVersion.query.filter_by( id=int(requirement_tuple[1])).first() if not minimum_version: return (render_template( "pyfarm/error.html", error="Software version %s not " "found" % requirement_tuple[1]), NOT_FOUND) if minimum_version.software != software: return (render_template( "pyfarm/error.html", error="Software version %s " "does not belong to software %s" % (minimum_version.version, software.software)), BAD_REQUEST) requirement.min_version = minimum_version if requirement_tuple[2] != "": maximum_version = SoftwareVersion.query.filter_by( id=int(requirement_tuple[2])).first() if not maximum_version: return (render_template( "pyfarm/error.html", error="Software version %s not " "found" % requirement_tuple[2]), NOT_FOUND) if maximum_version.software != software: return (render_template( "pyfarm/error.html", error="Software version %s " "does not belong to software %s" % (maximum_version.version, software.software)), BAD_REQUEST) requirement.max_version = maximum_version db.session.add(requirement) db.session.add(jobtype) db.session.add(jobtype_version) db.session.commit() flash("Jobtype %s created" % jobtype.name) return redirect(url_for('jobtypes_index_ui'), SEE_OTHER)
def update_device_stats_jumps(session, date, logger=None): """Update device stats jumps.""" if logger is None: logger = app.logger (start, end) = date_to_timestamps(date) # speed limits in m/s (values above indicates a unplausible position / jump) max_horizontal_speed = 1000 max_vertical_speed = 100 max_jumps = 10 # threshold for an 'ambiguous' device # find consecutive positions for a device sq = (session.query( AircraftBeacon.device_id, AircraftBeacon.timestamp, func.lead(AircraftBeacon.timestamp).over( partition_by=AircraftBeacon.device_id, order_by=AircraftBeacon.timestamp).label("timestamp_next"), AircraftBeacon.location_wkt, func.lead(AircraftBeacon.location_wkt).over( partition_by=AircraftBeacon.device_id, order_by=AircraftBeacon.timestamp).label("location_next"), AircraftBeacon.altitude, func.lead(AircraftBeacon.altitude).over( partition_by=AircraftBeacon.device_id, order_by=AircraftBeacon.timestamp).label("altitude_next"), ).filter( and_(between(AircraftBeacon.timestamp, start, end), AircraftBeacon.error_count == 0)).subquery()) # calc vertial and horizontal speed between points sq2 = (session.query( sq.c.device_id, (func.st_distancesphere(sq.c.location_next, sq.c.location) / (func.extract("epoch", sq.c.timestamp_next) - func.extract("epoch", sq.c.timestamp))).label("horizontal_speed"), ((sq.c.altitude_next - sq.c.altitude) / (func.extract("epoch", sq.c.timestamp_next) - func.extract("epoch", sq.c.timestamp))).label("vertical_speed"), ).filter( and_(sq.c.timestamp != null(), sq.c.timestamp_next != null(), sq.c.timestamp < sq.c.timestamp_next)).subquery()) # ... and find and count 'jumps' sq3 = (session.query( sq2.c.device_id, func.sum( case([(or_( func.abs(sq2.c.horizontal_speed) > max_horizontal_speed, func.abs(sq2.c.vertical_speed) > max_vertical_speed), 1)], else_=0)).label("jumps")).group_by( sq2.c.device_id).subquery()) upd = update(DeviceStats).where( and_(DeviceStats.date == date, DeviceStats.device_id == sq3.c.device_id)).values({ "ambiguous": sq3.c.jumps > max_jumps, "jumps": sq3.c.jumps }) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.warn("Updated {} DeviceStats jumps".format(update_counter)) return "DeviceStats jumps for {}: {} updated".format(date, update_counter)
def add_jobtype_software_requirement(jobtype_id): with db.session.no_autoflush: jobtype = JobType.query.filter_by(id=jobtype_id).first() if not jobtype: return (render_template("pyfarm/error.html", error="Jobtype %s not found" % jobtype_id), NOT_FOUND) previous_version = JobTypeVersion.query.filter_by( jobtype=jobtype).order_by(desc(JobTypeVersion.version)).first() if not previous_version: return (render_template("pyfarm/error.html", error="Jobtype %s has no versions" % jobtype_id), INTERNAL_SERVER_ERROR) new_version = JobTypeVersion(jobtype=jobtype) new_version.max_batch = previous_version.max_batch or sql.null() new_version.batch_contiguous = previous_version.batch_contiguous new_version.no_automatic_start_time =\ previous_version.no_automatic_start_time new_version.classname = previous_version.classname new_version.code = previous_version.code new_version.version = previous_version.version + 1 for requirement in previous_version.software_requirements: retained_requirement = JobTypeSoftwareRequirement() retained_requirement.jobtype_version = new_version retained_requirement.software = requirement.software retained_requirement.min_version = requirement.min_version retained_requirement.max_version = requirement.max_version db.session.add(retained_requirement) new_requirement = JobTypeSoftwareRequirement() new_requirement.jobtype_version = new_version new_requirement_software = Software.query.filter_by( id=request.form["software"]).first() if not new_requirement_software: return (render_template("pyfarm/error.html", error="Software %s not found" % request.form["software"]), NOT_FOUND) new_requirement.software = new_requirement_software if request.form["minimum_version"] != "": min_version = SoftwareVersion.query.filter_by( id=request.form["minimum_version"]).first() if not min_version: return (render_template( "pyfarm/error.html", error="Software version %s not " "found" % request.form["minimum_version"]), NOT_FOUND) if min_version.software != new_requirement_software: return (render_template( "pyfarm/error.html", error="Software version %s does " "not belong to software %s" % (min_version.version, new_requirement_software.software)), BAD_REQUEST) new_requirement.min_version = min_version if request.form["maximum_version"] != "": max_version = SoftwareVersion.query.filter_by( id=request.form["maximum_version"]).first() if not max_version: return (render_template( "pyfarm/error.html", error="Software version %s not " "found" % request.form["maximum_version"]), NOT_FOUND) if max_version.software != new_requirement_software: return (render_template( "pyfarm/error.html", error="Software version %s does " "not belong to software %s" % (max_version.version, new_requirement_software.software)), BAD_REQUEST) new_requirement.max_version = max_version db.session.add(new_version) db.session.add(new_requirement) db.session.commit() flash("Software requirement has been added to jobtype %s" % jobtype.name) return redirect(url_for("single_jobtype_ui", jobtype_id=jobtype.id), SEE_OTHER)
def update_devices(session, logger=None): """Update devices with stats.""" if logger is None: logger = app.logger device_stats = (session.query( distinct(DeviceStats.device_id).label("device_id"), func.first_value(DeviceStats.name).over( partition_by=DeviceStats.device_id, order_by=case( [(DeviceStats.name == null(), None)], else_=DeviceStats.date).desc().nullslast()).label("name"), func.first_value(DeviceStats.firstseen).over( partition_by=DeviceStats.device_id, order_by=case( [(DeviceStats.firstseen == null(), None)], else_=DeviceStats.date).asc().nullslast()).label("firstseen"), func.max(DeviceStats.lastseen).over( partition_by=DeviceStats.device_id, order_by=case( [(DeviceStats.lastseen == null(), None)], else_=DeviceStats.date).desc().nullslast()).label("lastseen"), func.first_value(DeviceStats.aircraft_type).over( partition_by=DeviceStats.device_id, order_by=case([(DeviceStats.aircraft_type == null(), None)], else_=DeviceStats.date).desc().nullslast()).label( "aircraft_type"), func.first_value(DeviceStats.stealth).over( partition_by=DeviceStats.device_id, order_by=case( [(DeviceStats.stealth == null(), None)], else_=DeviceStats.date).desc().nullslast()).label("stealth"), func.first_value(DeviceStats.software_version).over( partition_by=DeviceStats.device_id, order_by=case([(DeviceStats.software_version == null(), None)], else_=DeviceStats.date).desc().nullslast()).label( "software_version"), func.first_value(DeviceStats.hardware_version).over( partition_by=DeviceStats.device_id, order_by=case([(DeviceStats.hardware_version == null(), None)], else_=DeviceStats.date).desc().nullslast()).label( "hardware_version"), func.first_value(DeviceStats.real_address).over( partition_by=DeviceStats.device_id, order_by=case([(DeviceStats.real_address == null(), None)], else_=DeviceStats.date).desc().nullslast()).label( "real_address"), ).order_by(DeviceStats.device_id).subquery()) upd = (update(Device).where( and_(Device.id == device_stats.c.device_id)).values({ "name": device_stats.c.name, "firstseen": device_stats.c.firstseen, "lastseen": device_stats.c.lastseen, "aircraft_type": device_stats.c.aircraft_type, "stealth": device_stats.c.stealth, "software_version": device_stats.c.software_version, "hardware_version": device_stats.c.hardware_version, "real_address": device_stats.c.real_address, })) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.warn("Updated {} Devices".format(update_counter)) return "Updated {} Devices".format(update_counter)
def parse_track_event(self, session, update_task, entry, event_type, track_record, block_timestamp): event_args = entry["args"] # Just use block_timestamp as integer block_datetime = datetime.utcfromtimestamp(block_timestamp) if event_type == track_event_types_lookup["new_track"]: track_record.created_at = block_datetime track_metadata_digest = event_args._multihashDigest.hex() track_metadata_hash_fn = event_args._multihashHashFn buf = multihash.encode(bytes.fromhex(track_metadata_digest), track_metadata_hash_fn) track_metadata_multihash = multihash.to_b58_string(buf) logger.info(f"track metadata ipld : {track_metadata_multihash}") # If the IPLD is blacklisted, do not keep processing the current entry # continue with the next entry in the update_track_events list if is_blacklisted_ipld(session, track_metadata_multihash): logger.info( f"Encountered blacklisted metadata CID {track_metadata_multihash} in indexing new track" ) return None owner_id = event_args._trackOwnerId handle = (session.query(User.handle).filter( User.user_id == owner_id, User.is_current == True).first())[0] track_record.owner_id = owner_id track_record.is_delete = False track_metadata = update_task.ipfs_client.get_metadata( track_metadata_multihash, track_metadata_format) track_record = populate_track_record_metadata(track_record, track_metadata, handle) track_record.metadata_multihash = track_metadata_multihash # if cover_art CID is of a dir, store under _sizes field instead if track_record.cover_art: # If CID is in IPLD blacklist table, do not continue with indexing if is_blacklisted_ipld(session, track_record.cover_art): logger.info( f"Encountered blacklisted cover art CID {track_record.cover_art} in indexing new track" ) return None logger.warning( f"tracks.py | Processing track cover art {track_record.cover_art}" ) track_record.cover_art_sizes = track_record.cover_art track_record.cover_art = None update_stems_table(session, track_record, track_metadata) update_remixes_table(session, track_record, track_metadata) if event_type == track_event_types_lookup["update_track"]: upd_track_metadata_digest = event_args._multihashDigest.hex() upd_track_metadata_hash_fn = event_args._multihashHashFn update_buf = multihash.encode(bytes.fromhex(upd_track_metadata_digest), upd_track_metadata_hash_fn) upd_track_metadata_multihash = multihash.to_b58_string(update_buf) logger.info( f"update track metadata ipld : {upd_track_metadata_multihash}") # If the IPLD is blacklisted, do not keep processing the current entry # continue with the next entry in the update_track_events list if is_blacklisted_ipld(session, upd_track_metadata_multihash): logger.info( f"Encountered blacklisted metadata CID {upd_track_metadata_multihash} in indexing update track" ) return None owner_id = event_args._trackOwnerId handle = (session.query(User.handle).filter( User.user_id == owner_id, User.is_current == True).first())[0] track_record.owner_id = owner_id track_record.is_delete = False track_metadata = update_task.ipfs_client.get_metadata( upd_track_metadata_multihash, track_metadata_format) track_record = populate_track_record_metadata(track_record, track_metadata, handle) track_record.metadata_multihash = upd_track_metadata_multihash # All incoming cover art is intended to be a directory # Any write to cover_art field is replaced by cover_art_sizes if track_record.cover_art: # If CID is in IPLD blacklist table, do not continue with indexing if is_blacklisted_ipld(session, track_record.cover_art): logger.info( f"Encountered blacklisted cover art CID {track_record.cover_art} in indexing update track" ) return None logger.info( f"tracks.py | Processing track cover art {track_record.cover_art}" ) track_record.cover_art_sizes = track_record.cover_art track_record.cover_art = None update_remixes_table(session, track_record, track_metadata) if event_type == track_event_types_lookup["delete_track"]: track_record.is_delete = True track_record.stem_of = null() track_record.remix_of = null() logger.info(f"Removing track : {track_record.track_id}") track_record.updated_at = block_datetime return track_record
def col(name, table): try: return colnamemaps[table][name] except KeyError: return sql.cast(sql.null(), types[name]).label(name)
def resolve_buildings(self, info): query = Building.get_query(info) return query.join(CityModel).filter( BuildingModel.is_active == 1, CityModel.latitude != null(), CityModel.longitude != null(), ).all()
def crawl_webpage(url): r = requests.get(url) rj = r.json() logger.debug(rj['rowCount']) url_melde = 'https://www.intensivregister.de/api/public/stammdaten/krankenhausstandort/{0}/meldebereiche' hospital_entries = [] hospital_entries_extended = [] hospital_beds_entries_extended = [] len_ = len(rj['data']) for i, x in enumerate(rj['data']): logger.debug(str(i) + ' / ' + str(len_)) name = x['krankenhausStandort']['bezeichnung'] address = str(x['krankenhausStandort']['strasse']) address += ' ' address += str(x['krankenhausStandort']['hausnummer']) address += ' ' address += str(x['krankenhausStandort']['plz']) address += ' ' address += str(x['krankenhausStandort']['ort']) state = x['krankenhausStandort']['bundesland'] location = '(' location += str(x['krankenhausStandort']['position']['longitude']) location += ' ' location += str(x['krankenhausStandort']['position']['latitude']) location += ')' icu_low_state = legends(x['bettenStatus']['statusLowCare']) icu_high_state = legends(x['bettenStatus']['statusHighCare']) ecmo_state = legends(x['bettenStatus']['statusECMO']) last_update = x['meldezeitpunkt'] hospital_id = int(x['id']) covid_cases = get_attr_if_exists(x, 'faelleCovidAktuell') if covid_cases is not None: covid_cases = int(covid_cases) else: covid_cases = null() tmp_url = url_melde.format(str(hospital_id)) r_melde = requests.get(tmp_url) rj_melde = r_melde.json() contact = '' for y in rj_melde: if len(y['ansprechpartner']): for c in y['ansprechpartner']: contact += str( c['zustaendigkeit']['bezeichnung']) + ' : ' + str( c['nachname']) + ' : Tel. ' + str( c['telefonnummer']) contact += ', ' else: for c in y['tags']: contact += c contact += ', ' hospital_id_beds = int(y['krankenhausStandort']['id']) name_beds = y['bezeichnung'] casesecmoyear_beds = int(y['faelleEcmoJahr']) available_beds_beds = int(y['bettenPlankapazitaet']) description_beds = y['bettenPlankapazitaet'] last_update_beds = y['letzteMeldung'] if last_update_beds is None: last_update_beds = null() beds_beds = y['krankenhausStandort'][ 'intensivmedizinischePlanbetten'] if beds_beds is not None: beds_beds = int(beds_beds) else: beds_beds = null() beds = [ hospital_id_beds, name_beds, available_beds_beds, casesecmoyear_beds, beds_beds, description_beds, last_update_beds ] hospital_beds_entries_extended.append(beds) contact = contact[:-2] if len(contact) > 255: contact = contact[:250] hospital_entry = [ name, address, contact, state, icu_low_state, icu_high_state, ecmo_state, last_update, location ] hospital_entries.append(hospital_entry) hospital_entry = [ hospital_id, name, address, contact, state, icu_low_state, icu_high_state, ecmo_state, last_update, location, covid_cases ] hospital_entries_extended.append(hospital_entry) return hospital_entries, hospital_entries_extended, hospital_beds_entries_extended
'toc_code': 'control', 'toi_code': 'academic_institution', 'med_sch_flag': 'medical_school_flag', '01.a': 'federal_government', '01.b': 'state_and_local_government', '01.c': 'business', '01.d': 'nonprofit_organizations', '01.e': 'institutional_funds', '01.f': 'other_sources', '01.g': 'total_rd_expenses', '04': 'medical_school_expenses', 'NA_01': 'arra_funds' }) # replace NaN with database-compliant nulls institutions = institutions.fillna(sql.null()) # insert data into dbo.survey_records session = Session() try: print('Attempting to insert {:,} rows for {} into {}.'.format( institutions.shape[0], year, NsfHerdInstitution.__tablename__)) record_deletes = session.query(NsfHerdInstitution).filter( NsfHerdInstitution.date_key == date_key).delete( synchronize_session=False) session.bulk_insert_mappings( mapper=NsfHerdInstitution, mappings=institutions.to_dict(orient='records'), render_nulls=True) except Exception as e:
def filter_query(self, query, view): return query.filter(( view.model.owner_id == self.get_request_credentials()) | (view.model.owner_id == sql.null()))
'agency_key', 'academic_field_key', 'ncses_inst_id', 'unitid', 'expenditure'] herd = herd[keepers].groupby(['inst_id', 'date_key', 'funding_type', 'agency_key', 'academic_field_key', 'ncses_inst_id', 'unitid']).sum().reset_index() herd['expenditure'] = herd.expenditure.fillna(0) herd = herd.fillna(sql.null()) # insert data into dbo.survey_records session = Session() try: print('Attempting to insert {:,} rows for {} into {}.'.format(herd.shape[0], year, NsfHerdDetail.__tablename__)) record_deletes = session.query(NsfHerdDetail).filter(NsfHerdDetail.date_key==date_key).delete(synchronize_session=False) session.bulk_insert_mappings(mapper = NsfHerdDetail, mappings = herd.to_dict(orient='records'), render_nulls = True) except Exception as e: session.rollback() print(str(e)) print('No data were altered due to error.') else:
def bm_node_get_associated(context, service_host=None): query = model_query(context, models.BareMetalNode, read_deleted="no").\ filter(models.BareMetalNode.instance_uuid != null()) if service_host: query = query.filter_by(service_host=service_host) return query.all()
def update_entries(session, date, logger=None): """Create receiver coverage stats for Melissas ognrange.""" if logger is None: logger = app.logger logger.info("Compute receiver coverages.") (start, end) = date_to_timestamps(date) # Filter aircraft beacons sq = (session.query( AircraftBeacon.location_mgrs_short, AircraftBeacon.receiver_id, AircraftBeacon.signal_quality, AircraftBeacon.altitude, AircraftBeacon.device_id).filter( and_(between(AircraftBeacon.timestamp, start, end), AircraftBeacon.location_mgrs_short != null(), AircraftBeacon.receiver_id != null(), AircraftBeacon.device_id != null())).subquery()) # ... and group them by reduced MGRS, receiver and date query = (session.query( sq.c.location_mgrs_short, sq.c.receiver_id, func.cast(date, Date).label("date"), func.max(sq.c.signal_quality).label("max_signal_quality"), func.min(sq.c.altitude).label("min_altitude"), func.max(sq.c.altitude).label("max_altitude"), func.count(sq.c.altitude).label("aircraft_beacon_count"), func.count(func.distinct(sq.c.device_id)).label("device_count"), ).group_by(sq.c.location_mgrs_short, sq.c.receiver_id).subquery()) # if a receiver coverage entry exist --> update it upd = (update(ReceiverCoverage).where( and_( ReceiverCoverage.location_mgrs_short == query.c.location_mgrs_short, ReceiverCoverage.receiver_id == query.c.receiver_id, ReceiverCoverage.date == date)).values({ "max_signal_quality": query.c.max_signal_quality, "min_altitude": query.c.min_altitude, "max_altitude": query.c.max_altitude, "aircraft_beacon_count": query.c.aircraft_beacon_count, "device_count": query.c.device_count, })) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.debug( "Updated receiver coverage entries: {}".format(update_counter)) # if a receiver coverage entry doesnt exist --> insert it new_coverage_entries = session.query(query).filter(~exists().where( and_( ReceiverCoverage.location_mgrs_short == query.c.location_mgrs_short, ReceiverCoverage.receiver_id == query.c.receiver_id, ReceiverCoverage.date == date))) ins = insert(ReceiverCoverage).from_select( ( ReceiverCoverage.location_mgrs_short, ReceiverCoverage.receiver_id, ReceiverCoverage.date, ReceiverCoverage.max_signal_quality, ReceiverCoverage.min_altitude, ReceiverCoverage.max_altitude, ReceiverCoverage.aircraft_beacon_count, ReceiverCoverage.device_count, ), new_coverage_entries, ) result = session.execute(ins) insert_counter = result.rowcount session.commit() finish_message = "ReceiverCoverage: {} inserted, {} updated".format( insert_counter, update_counter) logger.debug(finish_message) return finish_message