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 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 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 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 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)
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 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'],
)
