def recover(self): logger.info('Recovering from %s', self.args.working_set_file) with open(self.args.working_set_file, 'r') as file, \ new_session() as session: query = insert(Result) values = [] for line_num, line in enumerate(file): doc = pickle.loads(base64.b64decode(line)) values.append({ 'project_id': doc['project_id'], 'shortcode': doc['shortcode'], 'url': doc['url'], 'encoding': doc['encoding'], 'datetime': doc['datetime'], }) if line_num % 10000 == 0: logger.info('Recover progress: %d', line_num) session.execute(query, values) session.commit() values = [] logger.info('Finishing up...') session.execute(query, values) session.commit() logger.info('Done!')
def recordset(collection, user, user_agent, recordset_info): "Create a record set from the records matched by a query." spquery = recordset_info['fromquery'] tableid = spquery['contexttableid'] with models.session_context() as session: recordset = models.RecordSet() recordset.timestampCreated = datetime.now() recordset.version = 0 recordset.collectionMemberId = collection.id recordset.dbTableId = tableid recordset.name = recordset_info['name'] if 'remarks' in recordset_info: recordset.remarks = recordset_info['remarks'] recordset.type = 0 recordset.createdByAgentID = user_agent.id recordset.SpecifyUserID = user.id session.add(recordset) session.flush() new_rs_id = recordset.recordSetId model = models.models_by_tableid[tableid] id_field = getattr(model, model._id) field_specs = field_specs_from_json(spquery['fields']) query, __ = build_query(session, collection, user, tableid, field_specs) query = query.with_entities(id_field, literal(new_rs_id)).distinct() RSI = models.RecordSetItem ins = insert(RSI).from_select((RSI.recordId, RSI.RecordSetID), query) session.execute(ins) return new_rs_id
def add(self, new_urls, **kwargs): assert not isinstance(new_urls, (str, bytes)) referrer = kwargs.pop('referrer', None) top_url = kwargs.pop('top_url', None) url_strings = list(new_urls) if referrer: url_strings.append(referrer) if top_url: url_strings.append(top_url) with self._session() as session: URLStrDBRecord.add_many(session, url_strings) url_id_map = URLStrDBRecord.get_map(session, url_strings) for url in new_urls: values = dict(status=Status.todo) values.update(**kwargs) values['url_str_id'] = url_id_map[url] if referrer: values['referrer_id'] = url_id_map[referrer] if top_url: values['top_url_str_id'] = url_id_map[top_url] session.execute( insert(URLDBRecord).prefix_with('OR IGNORE'), values )
def check_in(self, url, new_status, increment_try_count=True, url_result=None): with self._session() as session: values = { QueuedURL.status: new_status.value } if url_result: values.update(url_result.database_items()) if increment_try_count: values[QueuedURL.try_count] = QueuedURL.try_count + 1 # TODO: rewrite as a join for clarity subquery = select([URLString.id]).where(URLString.url == url)\ .limit(1) query = update(QueuedURL).values(values)\ .where(QueuedURL.url_string_id == subquery) session.execute(query) if new_status == Status.done and url_result and url_result.filename: query = insert(QueuedFile).prefix_with('OR IGNORE').values({ 'queued_url_id': subquery }) session.execute(query)
def obtainRbacId(self, identifier): ''' @see: RbacServiceAlchemy.obtainRbacId ''' assert isinstance(identifier, int), 'Invalid user id %s' % identifier sql = self.session().query(RbacUserMapped.rbacId) sql = sql.filter(RbacUserMapped.userId == identifier) try: rbacUserId, = sql.one() except NoResultFound: sql = self.session().query(UserMapped.Id) sql = sql.filter(UserMapped.Id == identifier) if not sql.count(): raise IdError(UserMapped) rbac = Rbac() self.session().add(rbac) self.session().flush((rbac, )) rbacUserId = rbac.id self.session().execute( insert( tableFor(RbacUserMapped), { RbacUserMapped.userId: identifier, RbacUserMapped.rbacId: rbacUserId })) return rbacUserId
def infoTimeout(info): print "Server timed out!", info serverKey = "%s:%s" % info if serverKey in allTimeouts: del allTimeouts[serverKey] if not serverKey in numRetries or numRetries[serverKey] > 0: numRetries[serverKey] -= 1 serverlistCallback(info) else: print "Server retry limit reached:",info serverid = getServerId(info[0],info[1]) db.execute(insert(tbl_server_history).prefix_with('IGNORE'), id=serverid, date=tm, status=0, ) db.execute( update(tbl_servers) .where ( tbl_servers.c.id==serverid) .values( last_sample=tm ) )
def generate_mock(self): with new_session() as session: items = [] for i in range(self.args.count): if i % 100 == 0: print(i) if self.args.projects == 1: project_id = "test" else: project_id = "test_{}".format(random.randint(1, self.args.projects)) items.append( { "project_id": project_id, "shortcode": self.generate_shortcode(), "url": self.generate_url(), "encoding": "ascii", "datetime": datetime.datetime.utcnow(), } ) print("Running insertion") session.execute(insert(Result), items)
def populate_files(self): with self._session() as session: datetime_ago = datetime.datetime.utcnow() - datetime.timedelta(days=3) query = session.query(IAItem.id).filter( or_( IAItem.refresh_date.is_(None), IAItem.public_date > datetime_ago )) for row in query: identifier = row[0] _logger.info('Populating item %s.', identifier) files = yield self._api.get_item_files(identifier) query = insert(File).prefix_with('OR IGNORE') values = [] for filename, size in files: values.append({ 'ia_item_id': identifier, 'filename': filename, 'size': size, }) session.execute(query, values) query = update(IAItem).where(IAItem.id == identifier) session.execute( query, {'refresh_date': datetime.datetime.utcnow()} ) session.commit()
def populate_daily_stats(self): with self._session() as session: query = delete(DailyStat) session.execute(query) query = session.query(IAItem.id, IAItem.public_date) for ia_item_id, public_date in query: date = public_date.date() total_size = 0 rows = session.query(File.size)\ .filter_by(ia_item_id=ia_item_id)\ .filter(File.job_id.isnot(None)) for size, in rows: total_size += size session.execute( insert(DailyStat).prefix_with('OR IGNORE'), {'date': date} ) query = update(DailyStat)\ .values({'size': DailyStat.size + total_size})\ .where(DailyStat.date == date) session.execute(query)
def get_post(subreddit: str, server: str, server_id: int, channel: str, channel_id: int, username: str, username_id: int, dupe: bool, engine, setup): for submission in get_subreddit(subreddit, setup).hot(limit=25): if not check_duplicate(submission.id, server, dupe, engine): with engine.begin() as conn: conn.execute( insert(link_history).values(server=server, server_id=server_id, channel=channel, channel_id=channel_id, username=username, username_id=username_id, datetime_sent=datetime.now(), subreddit=subreddit, link=submission.url, reddit_id=submission.id) ) return submission
def insert_codes(codes): rows = [{ "code": code, "description": description } for code, description in codes.items()] ins = insert(RevenueCode.__table__, rows) db.session.execute(ins) db.session.commit()
def add_visits(self, visits): with self._session() as session: for url, warc_id, payload_digest in visits: session.execute( insert(Visit).prefix_with('OR IGNORE'), dict(url=url, warc_id=warc_id, payload_digest=payload_digest))
def insert(self, table, data): if isinstance(table, str): table = self.get_table(table) data = preprocess_table_data(table, data) stmt = insert(table, list(data), returning=table.primary_key.columns) return self.execute(stmt)
def checkin_item(item_id, tamper_key, results): item_stat = { 'project': '', 'username': '', 'scanned': 0, 'found': len(results) } with new_session() as session: row = session.query( Item.project_id, Item.username, Item.upper_sequence_num, Item.lower_sequence_num, Item.ip_address, Item.datetime_claimed ) \ .filter_by(id=item_id, tamper_key=tamper_key).first() if not row: raise InvalidClaim() (project_id, username, upper_sequence_num, lower_sequence_num, ip_address, datetime_claimed) = row item_stat['project'] = project_id item_stat['username'] = username item_stat['scanned'] = upper_sequence_num - lower_sequence_num + 1 item_stat['started'] = datetime_claimed.replace( tzinfo=datetime.timezone.utc).timestamp() query_args = [] # tz instead of utcnow() for Unix timestamp in UTC instead of local time = datetime.datetime.now(datetime.timezone.utc) item_stat['finished'] = time.timestamp() for shortcode in results.keys(): url = results[shortcode]['url'] encoding = results[shortcode]['encoding'] query_args.append({ 'project_id': project_id, 'shortcode': shortcode, 'url': url, 'encoding': encoding, 'datetime': time }) if len(query_args) > 0: query = insert(Result) session.execute(query, query_args) session.execute(delete(Item).where(Item.id == item_id)) Budget.check_in(project_id, ip_address) if Stats.instance: Stats.instance.update(item_stat) return item_stat
def add_systematik_document_relation(self, systematik_id, document_id): ''' Does what the method name says. ''' insert_statement = insert(self.dsref_table).\ values(systematik=systematik_id.node_id, roemisch=systematik_id.roman, sub=systematik_id.subfolder, hauptnr=document_id) self._get_connection().execute(insert_statement)
def add_player(tid, name, corp_id, runner_id): db = get_db() player = metadata.tables["player"] with db.begin() as conn: conn.execute( insert(player).values(p_name=name, tid=tid, corp_id=corp_id, runner_id=runner_id)) return name
def getServerId(ip,port): res = db.execute(select([tbl_servers],and_(tbl_servers.c.ip==ip,tbl_servers.c.port==port))) if res.rowcount == 0: res = db.execute(insert(tbl_servers),ip=ip,port=port) serverid = res.inserted_primary_key[0] else: row = res.first(); serverid = row['id'] return serverid
def create_legacy_version(datastores_table, datastore_versions_table, image_id): insert( table=datastores_table, values=dict(id=LEGACY_DATASTORE_ID, name="Legacy MySQL") ).execute() insert( table=datastore_versions_table, values=dict(id=LEGACY_VERSION_ID, datastore_id=LEGACY_DATASTORE_ID, name="Unknown Legacy Version", image_id=image_id, packages="", active=False, manager="mysql") ).execute() return LEGACY_VERSION_ID
def add_visits(self, visits): with self._session() as session: for url, warc_id, payload_digest in visits: session.execute( insert(Visit).prefix_with('OR IGNORE'), dict( url=url, warc_id=warc_id, payload_digest=payload_digest ) )
def sqlite_merge(merge_stmt, compiler, **kwargs): dummy = insert(merge_stmt.table, merge_stmt.values) values, table = dummy.parameters, dummy.table primary_key = table.primary_key unique_columns = next( c.columns for c in table.constraints if isinstance(c, (PrimaryKeyConstraint, UniqueConstraint)) and all(c.name in values[0].keys() for c in c.columns) ) other_columns = tuple(c for c in table.c if c.name not in values[0]) def make_select(column, value): return sa.select((column,)).where( and_(uc == value[uc.name] for uc in unique_columns) ) values = tuple( {**v, **{c.name: make_select(c, v) for c in other_columns}} for v in values ) stmt = insert(table, values).prefix_with('OR REPLACE') return compiler.process(stmt, **kwargs)
def _store_transform(transformation: str, origin: str, input: str, output: str) -> str: if input != output: engine.execute( insert(__CpeNorm__), { 'id': uuid(), 'transformation': transformation, 'origin': origin, 'input': input, 'output': output }) return output
def test_merge(session, initial_items, items_to_merge): items = { 1: {'id': 1, 'change': '1', 'a': 'a', 'b': 'b'}, } session.execute(insert(Foo, initial_items.values())) session.execute(Merge(Foo, items.values())) session.commit() foos = { foo.id: foo._asdict() for foo in session.query(Foo.id, Foo.change, Foo.a, Foo.b).all() } assert dict(initial_items, **items) == foos
def sqlite_merge(merge_stmt, compiler, **kwargs): dummy = insert(merge_stmt.table, merge_stmt.values) values, table = dummy.parameters, dummy.table primary_key = table.primary_key unique_columns = next( c.columns for c in table.constraints if isinstance(c, (PrimaryKeyConstraint, UniqueConstraint)) and all( c.name in values[0].keys() for c in c.columns)) other_columns = tuple(c for c in table.c if c.name not in values[0]) def make_select(column, value): return sa.select((column, )).where( and_(uc == value[uc.name] for uc in unique_columns)) values = tuple({ **v, **{c.name: make_select(c, v) for c in other_columns} } for v in values) stmt = insert(table, values).prefix_with('OR REPLACE') return compiler.process(stmt, **kwargs)
def add_items(cls, project_id, sequence_list): with new_session() as session: query = insert(Item) query_args = [] for lower_num, upper_num in sequence_list: query_args.append({ 'project_id': project_id, 'lower_sequence_num': lower_num, 'upper_sequence_num': upper_num, }) session.execute(query, query_args)
def __fetch_or_insert(self, conn, ref_tbl, id_col_name, val_col_name, val): whereclause = getattr(ref_tbl.c, val_col_name) == val sel_proxy = conn.execute(select([getattr(ref_tbl.c, id_col_name)], whereclause)) result = sel_proxy.fetchall() if len(result) == 1: # Found related entry - return found ID. ref_id = result[0][id_col_name] else: # Not found - insert new and return new ID. ins_proxy = conn.execute(insert(ref_tbl, values={val_col_name:val})) ref_id = ins_proxy.inserted_primary_key[0] return ref_id
def __fetch_or_insert(self, conn, ref_tbl, id_col_name, val_col_name, val): whereclause = getattr(ref_tbl.c, val_col_name) == val sel_proxy = conn.execute( select([getattr(ref_tbl.c, id_col_name)], whereclause)) result = sel_proxy.fetchall() if len(result) == 1: # Found related entry - return found ID. ref_id = result[0][id_col_name] else: # Not found - insert new and return new ID. ins_proxy = conn.execute( insert(ref_tbl, values={val_col_name: val})) ref_id = ins_proxy.inserted_primary_key[0] return ref_id
def _insert(table, value): """ Return a SQLAlchemy insert statement based on :param table: The table we are inserting to :param value: An object representing the object we are inserting to the table :return: A SQLAlchemy insert statement """ values_dict = {} for i in table.__table__.columns: name = i.name column_value = getattr(value, i.name) if type(column_value) == str: column_value = column_value.replace("'", "''") values_dict[name] = column_value return insert(table, values=values_dict)
def import_papers(): """ Import papers from bibjson file """ with open(join(__here__, '..', 'input', 'bibjson')) as f: data = load(f) run_query('create_papers_table') table = reflect_table('ignimbrite_paper') for i in data: i['docid'] = i.pop('_gddid') __ = insert(table).values(**i) session.execute(__) session.commit()
def apply_classifier(request): job = request.params['job'] cid = request.matchdict['classifier_id'] session = DBSession() if request.params['submit'] == 'apply': classifiers = session.query(TestClassifier) if cid != "all": classifiers = classifiers.filter_by(id=cid) classifiers = classifiers.all() for classifier in classifiers: col = getattr(Run, str(classifier.column)) matched_runs = session.query(str(classifier.id), Run.id) \ .filter(Run.job_id == job) \ .filter(col.op('~')(classifier.pattern)) query = expression.insert(TestRunClassification).from_select([ TestRunClassification.classifier_id, TestRunClassification.run_id ], matched_runs) session.execute(query) elif request.params['submit'] == 'delete': subquery = session.query(TestRunClassification.run_id) \ .join(Run).filter(Run.job_id == job) query = session.query(TestRunClassification) \ .filter(TestRunClassification.run_id.in_(subquery)) if cid != "all": query = query.filter(TestRunClassification.classifier_id == cid) query.delete(synchronize_session=False) mark_changed(session) if cid == 'all': return HTTPFound(location=request.route_url('list_classifiers')) else: return HTTPFound( location=request.route_url('view_classifier', classifier_id=cid))
def test_merge(session, initial_items, items_to_merge): items = { 1: { 'id': 1, 'change': '1', 'a': 'a', 'b': 'b' }, } session.execute(insert(Foo, initial_items.values())) session.execute(Merge(Foo, items.values())) session.commit() foos = { foo.id: foo._asdict() for foo in session.query(Foo.id, Foo.change, Foo.a, Foo.b).all() } assert dict(initial_items, **items) == foos
def commit_records(self, spider): spider.logger.info("Commiting {} records".format( getattr(self, "record_count", 0))) self.save_stats() for t in getattr(self, "records", {}): table = self.tables[t] cols = [c.name for c in table.columns] pks = [c.name for c in table.primary_key] vals = [] if self.engine.name == 'postgresql': insert_statement = postgresql.insert(table) upsert_statement = insert_statement.on_conflict_do_update( constraint=table.primary_key, set_={c: insert_statement.excluded.get(c) for c in cols}) elif self.engine.name == 'mysql': insert_statement = mysql.insert(table) upsert_statement = insert_statement.on_duplicate_key_update( **{c: insert_statement.excluded.get(c) for c in cols}) elif self.engine.name == 'sqlite': upsert_statement = insert(table).prefix_with("OR REPLACE") for r in self.records[t]: if "scrape_id" in cols: r['scrape_id'] = self.crawl_id if "spider" in cols: r['spider'] = self.spider_name if self.engine.name == 'postgresql': vals.append({c: r.get(c) for c in cols}) else: vals.append({ c: str(r.get(c)) if type(r.get(c)) in (list, dict) else r.get(c) for c in cols }) if vals: self.conn.execute(upsert_statement, vals) self.records = {t: [] for t in self.tables} self.record_count = 0
def create_account(self, balance: float = 0, score: float = 0) -> model.Account: '''创建并返回新账户''' with self._scoped_session_maker() as session: # 插入新账户 inserter = expression.insert(model.Account).values(balance=balance, score=score) res = session.execute(inserter) account_id = res.inserted_primary_key #查询新插入的对象 account = session.query( model.Account).filter(model.Account.id == account_id).first() if account is None: raise NoRows() return account
def create_user(self, account_id: int, name: str = "", phone: str = "") -> model.User: '''创建并返回新账户''' with self._scoped_session_maker() as session: # 插入新账户 inserter = expression.insert(model.User).values( name=name, phone=phone, account_id=account_id) res = session.execute(inserter) user_id = res.inserted_primary_key #查询新插入的对象 user = session.query(model.User).filter_by(id=user_id).first() if user is None: raise NoRows() return user
def apply_classifier(request): job = request.params['job'] cid = request.matchdict['classifier_id'] session = DBSession() if request.params['submit'] == 'apply': classifiers = session.query(TestClassifier) if cid != "all": classifiers = classifiers.filter_by(id=cid) classifiers = classifiers.all() for classifier in classifiers: col = getattr(Run, str(classifier.column)) matched_runs = session.query(str(classifier.id), Run.id) \ .filter(Run.job_id == job) \ .filter(col.op('~')(classifier.pattern)) query = expression.insert(TestRunClassification).from_select( [TestRunClassification.classifier_id, TestRunClassification.run_id], matched_runs) session.execute(query) elif request.params['submit'] == 'delete': subquery = session.query(TestRunClassification.run_id) \ .join(Run).filter(Run.job_id == job) query = session.query(TestRunClassification) \ .filter(TestRunClassification.run_id.in_(subquery)) if cid != "all": query = query.filter(TestRunClassification.classifier_id == cid) query.delete(synchronize_session=False) mark_changed(session) if cid == 'all': return HTTPFound(location=request.route_url('list_classifiers')) else: return HTTPFound(location=request.route_url('view_classifier', classifier_id=cid))
def populate_jobs(self): with self._session() as session: query = session.query(File.ia_item_id, File.filename, File.size)\ .filter_by(job_id=None) for row in query: ia_item_id, filename, size = row filename_info = parse_filename(filename) if not filename_info: continue job_ident = filename_info['ident'] or \ '{}{}'.format(filename_info['date'], filename_info['time']) query = insert(Job).prefix_with('OR IGNORE') value = { 'id': job_ident, 'domain': filename_info['domain'], } session.execute(query, [value]) values = {} if filename_info['aborted']: values['aborts'] = Job.aborts + 1 if filename_info['extension'] == 'warc.gz': values['warcs'] = Job.warcs + 1 values['size'] = Job.size + size elif filename_info['extension'] == 'json': values['jsons'] = Job.jsons + 1 if values: query = update(Job).values(values)\ .where(Job.id == job_ident) session.execute(query) query = update(File)\ .values({'job_id': job_ident})\ .where(File.ia_item_id == ia_item_id)\ .where(File.filename == filename) session.execute(query)
async def i_track_root_analysis(self, root: RootAnalysis) -> bool: """Tracks the given root to the given RootAnalysis uuid.""" version = root.version if version is None: version = str(uuid.uuid4()) try: async with self.get_db() as db: await db.execute( insert(RootAnalysisTracking).values( uuid=root.uuid, version=version, json_data=root.to_json(exclude_analysis_details=True) ) ) await db.commit() root.version = version return True except sqlalchemy.exc.IntegrityError: return False
def pair_round(tid, rnd): plrs = get_active_players(tid) if len(plrs) % 2 == 1: plrs = add_bye_player(tid) pairings = make_pairings(plrs) match_list = make_matches(pairings) db = get_db() table_match = metadata.tables["match"] with db.begin() as conn: for i, match in enumerate(match_list): conn.execute( insert(table_match).values( corp_id=match[0], runner_id=match[1], tid=tid, rnd=rnd, match_num=i + 1, )) score_byes(tid, rnd)
def make_recordset(request): try: recordset_info = json.load(request) except ValueError as e: return HttpResponseBadRequest(e) spquery = recordset_info['fromquery'] tableid = spquery['contexttableid'] with models.session_context() as session: recordset = models.RecordSet() recordset.timestampCreated = datetime.now() recordset.version = 0 recordset.collectionMemberId = request.specify_collection.id recordset.dbTableId = tableid recordset.name = recordset_info['name'] if 'remarks' in recordset_info: recordset.remarks = recordset_info['remarks'] recordset.type = 0 recordset.createdByAgentID = request.specify_user_agent.id recordset.SpecifyUserID = request.specify_user.id session.add(recordset) session.flush() new_rs_id = recordset.recordSetId model = models.models_by_tableid[tableid] id_field = getattr(model, model._id) field_specs = [ QueryField.from_spqueryfield(EphemeralField.from_json(data)) for data in sorted(spquery['fields'], key=lambda field: field['position']) ] query, __ = build_query(session, request.specify_collection, request.specify_user, tableid, field_specs) query = query.with_entities(id_field, literal(new_rs_id)).distinct() RSI = models.RecordSetItem ins = insert(RSI).from_select((RSI.recordId, RSI.RecordSetID), query) session.execute(ins) return HttpResponseRedirect(uri_for_model('recordset', new_rs_id))
def _create_sequence( sequence_id, start_value, range_size, alert_threshold, updater_id, session): insert_stmt = insert( Sequence.__table__, values={ "sequence_id": sequence_id, "start_value": start_value, "current_value": start_value, "range_size": range_size, "alert_threshold": alert_threshold, "updater": updater_id }, returning=[ Sequence.__table__.c.current_value, Sequence.__table__.c.updater ] ) result = session.execute(insert_stmt).first() return result
def make_recordset(request): if settings.RO_MODE or request.specify_user.usertype not in ('Manager', 'FullAccess'): return HttpResponseForbidden() try: recordset_info = json.load(request) except ValueError as e: return HttpResponseBadRequest(e) spquery = recordset_info['fromquery'] tableid = spquery['contexttableid'] with models.session_context() as session: recordset = models.RecordSet() recordset.timestampCreated = datetime.now() recordset.version = 0 recordset.collectionMemberId = request.specify_collection.id recordset.dbTableId = tableid recordset.name = recordset_info['name'] if 'remarks' in recordset_info: recordset.remarks = recordset_info['remarks'] recordset.type = 0 recordset.createdByAgentID = request.specify_user_agent.id recordset.SpecifyUserID = request.specify_user.id session.add(recordset) session.flush() new_rs_id = recordset.recordSetId model = models.models_by_tableid[tableid] id_field = getattr(model, model._id) field_specs = [QueryField.from_spqueryfield(EphemeralField.from_json(data)) for data in sorted(spquery['fields'], key=lambda field: field['position'])] query, __ = build_query(session, request.specify_collection, request.specify_user, tableid, field_specs) query = query.with_entities(id_field, literal(new_rs_id)).distinct() RSI = models.RecordSetItem ins = insert(RSI).from_select((RSI.recordId, RSI.RecordSetID), query) session.execute(ins) return HttpResponseRedirect(uri_for_model('recordset', new_rs_id))
def check_set_sample_type(target, value, oldvalue, initiator): # pylint: disable=W0613 if isinstance(target, Sample) \ and value == SAMPLE_TYPES.STOCK \ and oldvalue != SAMPLE_TYPES.STOCK: sess = object_session(target) if target.id is None: # We need a sample ID for the following execute statement to work. sess.flush() mdp = target.molecule_design_pool if mdp.id is None: sess.add(type(mdp), mdp) sess.flush() ss_tbl = class_mapper(StockSample).local_table sess.execute( insert(ss_tbl, values=dict( sample_id=target.sample_id, molecule_design_set_id=target.molecule_design_pool.id, supplier_id=target.supplier.id, molecule_type_id=target.molecule_type.id, concentration=target.concentration)))
def mknod ( self, path, mode, dev ): print '*** mknod', path, oct(mode), dev if not self.layout(path): return -errno.ENOENT if getDepth(path) >= 2: pe = getParts(path) else: return -errno.ENOSYS if getDepth(path) == 4 and ( pe[-2] == 'data' ): a=pe[-1].split('#_') table = Table(pe[-3], self.meta, schema=pe[-4], autoload=True) print a, pe[-1].split('#_')[0] ," == ", table.columns.keys()[0] if pe[-1].split('#_')[0] == table.columns.keys()[0]: values = [a[1]] values.extend(["" for e in table.columns.keys() if e != a[0]]) dv=dict(zip(table.columns.keys(), values)) #dbg() self.engine.execute(insert(table,values=dv, whereclause=table.c[str(a[0])]==a[1]) ) return 0
def checkin_item(item_id, tamper_key, results): item_stat = { 'project': '', 'username': '', 'scanned': 0, 'found': len(results) } with new_session() as session: item = session.query(Item).filter_by(id=item_id, tamper_key=tamper_key).first() item_stat['project'] = item.project_id item_stat['username'] = item.username item_stat['scanned'] = item.upper_sequence_num - item.lower_sequence_num + 1 query_args = [] time = datetime.datetime.utcnow() for shortcode in results.keys(): url = results[shortcode]['url'] encoding = results[shortcode]['encoding'] query_args.append({ 'project_id': item.project_id, 'shortcode': shortcode, 'url': url, 'encoding': encoding, 'datetime': time }) if len(query_args) > 0: query = insert(Result) session.execute(query, query_args) session.delete(item) if Stats.instance: Stats.instance.update(item_stat) return item_stat
def fset(self, value): session = object_session(self) session.flush() oldValue = [i[0] for i in fget(self)] oldValue.sort() if value: self.super_kind_gid = value[0] else: self.super_kind_gid = None newValue = list(value) newValue.sort() toRemove, toAdd = compareSorted(oldValue, newValue) if toRemove: session.execute( delete(info_kind_parents).where( and_(info_kind_parents.c.gid == self.gid, authors_users.c.user_id.in_(toRemove)))) if toAdd: session.execute( insert(info_kind_parents).values( [info_kind_parent(self.gid, gid) for gid in toAdd])) session.commit()
def ages(): run_query('create_ages_table') table = reflect_table('global_geology_age') res = session.query(nlp).filter(nlp.c.lemmas.overlap(age_terms)) age_range = re.compile( "(\d+(?:\.\d+)?)(?: ± (\d+(?:\.\d+)?))?(?: ?(-*|to|and) ?(\d+(?:\.\d+)?))? ?([Mk]a)" ) for row in res: sentence = Sentence(row) __ = age_range.findall(str(sentence)) for match in __: (age, error, sep, end_age, unit) = match stmt = insert(table).values(docid=sentence.document, sentid=sentence.id, age=fix_age(age, unit), error=fix_age(error, unit), end_age=fix_age(end_age, unit)) session.execute(stmt) session.commit()
def save_weird_years(cls, source_table: Type[declarative_base] = Vehicle) -> sase.Insert: """ This function generates the statement to insert weird build_years with accompanying primary keys to this table. The `prefix_with("IGNORE")` is to run the query even though some strings cannot be converted to integers. The result is still as expected. :param source_table: Main table where all unsanitized data is stored, is. vehicles :return: insert statement object that can be executed against the database """ stmt = sase.insert(cls).prefix_with("IGNORE").from_select( inspect(cls).columns.keys(), sase.select( [source_table.country, source_table.vehicle_id, source_table.licence, source_table.build_year] ).where( sase.or_( sase.cast(source_table.build_year, Integer) < constants.MIN_YEAR, sase.cast(source_table.build_year, Integer) > constants.MAX_YEAR) ) ) return stmt
def generate_mock(self): with new_session() as session: items = [] for i in range(self.args.count): if i % 100 == 0: print(i) if self.args.projects == 1: project_id = 'test' else: project_id = 'test_{}'.format(random.randint(1, self.args.projects)) items.append({ 'project_id': project_id, 'shortcode': self.generate_shortcode(), 'url': self.generate_url(), 'encoding': 'ascii', 'datetime': datetime.datetime.utcnow() }) print('Running insertion') session.execute(insert(Result), items)
def populate_json_files(self): with self._session() as session: query = session.query(File.ia_item_id, File.filename, File.job_id)\ .filter(File.filename.endswith('.json'))\ .filter(File.job_id.isnot(None)) for identifier, filename, job_id in query: json_id = filename.replace('.json', '') if session.query(JSONMetadata.id).filter_by(id=json_id).scalar(): continue response = yield self._api.download_item_file(identifier, filename) try: doc = json.loads(response.body.decode('utf-8', 'replace')) except ValueError: _logger.exception('JSON error!') continue url = doc.get('url') query = insert(JSONMetadata) values = { 'id': json_id, 'job_id': job_id, 'url': url, 'started_by': doc.get('started_by') } session.execute(query, [values]) if job_id and url: query = update(Job)\ .values({'url': url}).where(Job.id == job_id) session.execute(query) session.commit()
def check_set_sample_type(target, value, oldvalue, initiator): # pylint: disable=W0613 if isinstance(target, Sample) \ and value == SAMPLE_TYPES.STOCK \ and oldvalue != SAMPLE_TYPES.STOCK: sess = object_session(target) if target.id is None: # We need a sample ID for the following execute statement to work. sess.flush() mdp = target.molecule_design_pool if mdp.id is None: sess.add(type(mdp), mdp) sess.flush() ss_tbl = class_mapper(StockSample).local_table sess.execute(insert(ss_tbl, values=dict(sample_id=target.sample_id, molecule_design_set_id= target.molecule_design_pool.id, supplier_id=target.supplier.id, molecule_type_id= target.molecule_type.id, concentration= target.concentration) ) )
def chunk_insertPeoplePlayLink(db, chunk_size=5000): people_plays = db.execute( """select pitcher_id,id from plays;""").fetchall() people_plays = {tuple(x.values()) for x in people_plays} print('count down:') print(3) existing = db.execute("""select * from people_plays""").fetchall() existing = {tuple(x.values()) for x in existing} print(2) people_plays = list(people_plays - existing) print(1) count = 1 chunks = sql_alch_schema.chunk(chunk_size, people_plays) for chunk_ in chunks: print(f"chunk {count} of {len(chunks)}") insert_stmt = insert(PersonPlayLink, chunk_) db.execute(insert_stmt) count += 1
def add_many(cls, session, urls): query = insert(URLStrDBRecord).prefix_with('OR IGNORE') session.execute(query, [{'url': url} for url in urls])
def add_many(self, new_urls, **kwargs): assert not isinstance(new_urls, (str, bytes)), \ 'Expected a list-like. Got {}'.format(new_urls) referrer = kwargs.pop('referrer', None) top_url = kwargs.pop('top_url', None) new_urls = tuple(new_urls) if not new_urls: return () assert isinstance(new_urls[0], dict), type(new_urls[0]) url_strings = list(item['url'] for item in new_urls) if referrer: url_strings.append(referrer) if top_url: url_strings.append(top_url) with self._session() as session: query = insert(URLString).prefix_with('OR IGNORE') session.execute(query, [{'url': url} for url in url_strings]) bind_values = dict(status=Status.todo) bind_values.update(**kwargs) bind_values['url_str_id'] = select([URLString.id])\ .where(URLString.url == bindparam('url')) if referrer: bind_values['referrer_id'] = select([URLString.id])\ .where(URLString.url == bindparam('referrer')) if top_url: bind_values['top_url_str_id'] = select([URLString.id])\ .where(URLString.url == bindparam('top_url')) query = insert(URL).prefix_with('OR IGNORE').values(bind_values) all_row_values = [] for item in new_urls: assert 'url' in item assert 'referrer' not in item assert 'top_url' not in item row_values = item if referrer: row_values['referrer'] = referrer if top_url: row_values['top_url'] = top_url all_row_values.append(row_values) last_primary_key = session.query(func.max(URL.id)).scalar() or 0 session.execute(query, all_row_values) query = select([URLString.url]).where( and_(URL.id > last_primary_key, URL.url_str_id == URLString.id) ) added_urls = [row[0] for row in session.execute(query)] return added_urls
def insert_rows(db, rows_data): ins = insert(Contrato.__table__, rows_data) db.session.execute(ins) db.session.commit()
def infoCallback(serverinfo): serverKey = "%s:%s" % (serverinfo['server_ip'],serverinfo['query_port']) if serverKey in allTimeouts: allTimeouts[serverKey].cancel() del allTimeouts[serverKey] if serverKey in numRetries: del numRetries[serverKey] #print serverinfo serverid = getServerId(serverinfo['server_ip'],serverinfo['query_port']) serverversion = serverinfo['version'] if serverversion == '1.0.0.0': gd = serverinfo['gamedata'].split('|') serverversion = gd[0] db.execute(insert(tbl_server_history).prefix_with('IGNORE'), id=serverid, date=tm, map=serverinfo['map'], numplayers=serverinfo['numplayers'], maxplayers=serverinfo['maxplayers'], numbots=serverinfo['numbots'], password=serverinfo['password'], tickrate=0, version=serverversion, status=1, ) db.execute( update(tbl_servers) .where ( tbl_servers.c.id==serverid) .values( last_sample=tm ) ) qc.startRulesQuery((serverinfo['server_ip'],serverinfo['query_port'])) donameinsert = False res = db.execute( select([tbl_server_name_history], and_( tbl_server_name_history.c.id==serverid, tbl_server_name_history.c.date_end=='0000-00-00 00:00:00' ) ) ) if res.rowcount == 0: donameinsert = True else: row = res.first() if row['server_name'].lower().strip() != serverinfo['server_name'].lower().strip(): donameinsert = True db.execute( update(tbl_server_name_history) .where( and_( tbl_server_name_history.c.id==serverid, tbl_server_name_history.c.date_end=='0000-00-00 00:00:00' ) ) .values( date_end=datetime.datetime.now() ) ) if donameinsert: db.execute(insert(tbl_server_name_history), id=serverid, date_end='0000-00-00 00:00:00', date_start=datetime.datetime.now(), server_name=serverinfo['server_name'], )