def undiscovered_topic_stats (board_str):
"""
returns a JSON undiscovered topic stats object for a board defined by board_str
(structure {unreadCount, firstUnreadID}) this function is called via ajax in index.js
"""
board_id = int(board_str.split("-")[0])
board = Board.query.get(board_id)
if not board:
return "boardnotfound", 404
if current_user.is_anonymous():
return jsonify(undiscoveredCount = 0, firstUndiscoveredID = None)
topic_id_generator = Topic.query.filter(Topic.board_id == board_id).values(Topic.id)
topic_ids = [oneple[0] for oneple in topic_id_generator]
discovered_ids_generator = DiscoveredTopic.query.filter(DiscoveredTopic.user_id == current_user.id,
DiscoveredTopic.topic_id.in_(topic_ids)).values(DiscoveredTopic.topic_id)
discovered_ids = [oneple[0] for oneple in discovered_ids_generator]
undiscovered_count = Topic.query.filter(Topic.board_id == board_id, not_(Topic.id.in_(discovered_ids))).count()
first_undiscovered = Topic.query.filter(Topic.board_id == board_id, not_(Topic.id.in_(discovered_ids))).first()
if not first_undiscovered:
first_undiscovered_id = None
else:
first_undiscovered_id = first_undiscovered.id
return jsonify(undiscoveredCount = undiscovered_count, firstUndiscoveredID = first_undiscovered_id)
def post_process_video(session: Session, debug):
vids = session.query(Video)
vids = vids.filter(Video.processing_done)
vids = vids.filter(not_(Video.post_processing_done))
vids = vids.filter(or_(and_(Video.do_twitch, Video.done_twitch), not_(Video.do_twitch)))
vids = vids.filter(or_(and_(Video.do_youtube, Video.done_youtube), not_(Video.do_youtube)))
vids = vids.order_by(Video.id.asc())
vid = vids.first()
if not vid:
print("No video in need of processing found")
return 0
out_dir = get_setting(session, "output_directory")
final_dir = get_setting(session, "final_output_directory")
out_prefix = get_setting(session, "output_video_prefix")
out_ext = get_setting(session, "output_video_extension")
out_fname = "%s%s%s" % (out_prefix, vid.id, out_ext)
out_path = os.path.join(out_dir, out_fname)
final_path = os.path.join(final_dir, out_fname)
if out_path != final_path:
shutil.move(out_path, final_path)
vid.post_processing_status = "Moved %s to %s" % (out_path, final_path)
else:
vid.post_processing_status = "Nothing to do"
vid.post_processing_done = True
session.commit()
return 0
def get_related(table, attr, title):
if attr == "bra":
data_attr = category
else:
data_attr = attr
if data_attr == "hs" or data_attr == "wld":
dataset = "secex"
elif data_attr == "cnae" or data_attr == "cbo":
dataset = "rais"
elif data_attr == "university" or data_attr == "course_hedu":
dataset = "hedu"
elif data_attr == "course_sc":
dataset = "sc"
else:
return
q = table.query.filter(getattr(table, "{}_id".format(category)) == id) \
.filter(getattr(table, "year") == parse_year(__year_range__[dataset][1]))
if category in ("bra"):
q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("0xx")))
elif category in ("cnae", "cbo", "wld"):
q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("xx")))
elif category in ("hs"):
q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("22")))
elif category in ("course_hedu", "course_sc"):
q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("00")))
q = q.order_by(getattr(table, "prox_{}".format(attr)).desc()).limit(40).all()
if len(q) > 0:
m = globals()[category.capitalize()]
q = [(m.query.get(getattr(a, "{}_id_target".format(category))), getattr(a, "prox_{}".format(attr))) for a in q]
related.append({"title": title, "pages": q})
def randomProfile(category = None):
ids = db.session.query(Search.id).filter(Search.kind == category)
if category in ("bra"):
ids = ids.filter(not_(Search.id.startswith("0xx")))
elif category in ("cnae", "cbo", "wld"):
ids = ids.filter(not_(Search.id.startswith("xx")))
elif category in ("hs"):
ids = ids.filter(not_(Search.id.startswith("22")))
elif category in ("course_hedu", "course_sc"):
ids = ids.filter(not_(Search.id.startswith("00")))
if category == "bra":
ids = ids.filter(func.length(Search.id) == 9)
elif category in ("cnae", "hs", "course_hedu"):
ids = ids.filter(func.length(Search.id) == 6)
elif category == ("wld", "course_sc"):
ids = ids.filter(func.length(Search.id) == 5)
elif category == "cnae":
ids = ids.filter(func.length(Search.id) == 4)
ids = ids.order_by(Search.weight.desc()).limit(100).all()
rand = random.randrange(0, len(ids))
id = ids[rand][0]
return redirect(url_for("profiles.profiles", category=category, id=id))
def get_metadata_counts(self, params, project, property):
if "query_id" in params:
values = db.session.query(
Property.value.label("value"),
func.count(Property.unit_id.distinct()).label("unit_count")
).\
join(
PropertyOfSentence,
PropertyOfSentence.property_id == Property.id).\
join(SentenceInQuery,
PropertyOfSentence.sentence_id ==
SentenceInQuery.sentence_id).\
filter(SentenceInQuery.query_id == params["query_id"][0]).\
filter(not_(Property.name.contains("_set"))).\
filter(Property.name == property.property_name).\
group_by(Property.value)
else:
values = db.session.query(
Property.value.label('value'),
PropertyCount.sentence_count.label("unit_count")
).filter(Property.project_id == project.id).\
filter(not_(Property.name.contains("_set"))).\
filter(Property.name == property.property_name).\
filter(PropertyCount.property_value == Property.value).\
order_by(desc("unit_count")).\
group_by(Property.value).\
limit(20)
return values.all()
def get(self):
ensure_allowed('read', 'user')
parser = reqparse.RequestParser()
parser.add_argument('login', type=str, location='values')
parser.add_argument('first_name', type=str, location='values')
parser.add_argument('last_name', type=str, location='values')
args = parser.parse_args()
query = request.dbs.query(User).join(model.Entity)\
.filter(not_(model.User.deleted))\
.filter(not_(model.Entity.deleted))
# Optional filters
if args.get('login', None):
query = query.filter(User.login == args['login'])
if args.get('first_name', None):
name_search = '%' + args['first_name'] + '%'
query = query.filter(User.first_name.like(name_search))
if args.get('last_name', None):
name_search = '%' + args['last_name'] + '%'
query = query.filter(User.first_name.like(name_search))
return {'users': map(lambda u: marshal(u.to_dict(), user_fields), query.all())}
def unread_post_stats (topic_str):
"""
returns a JSON unread post stats object for a topic defined by topic_str
(structure {unreadCount, firstUnreadID}) this function is called via ajax
in board.js
"""
topic_id = int(topic_str.split("-")[0])
topic = Topic.query.get(topic_id)
if not topic:
return "topicnotfound", 404
if current_user.is_anonymous():
return jsonify(unreadCount = 0, firstUnreadID = None)
post_id_generator = Post.query.filter(Post.topic_id == topic_id).values(Post.id)
post_ids = [oneple[0] for oneple in post_id_generator]
read_ids_generator = ReadPost.query.filter(ReadPost.user_id == current_user.id, ReadPost.post_id.in_(post_ids)).values(ReadPost.post_id)
read_ids = [oneple[0] for oneple in read_ids_generator]
unread_count = Post.query.filter(Post.topic_id == topic_id, not_(Post.id.in_(read_ids))).count()
first_unread = Post.query.filter(Post.topic_id == topic_id, not_(Post.id.in_(read_ids))).first()
if not first_unread:
first_unread_id = None
else:
first_unread_id = first_unread.id
return jsonify(unreadCount = unread_count, firstUnreadID = first_unread_id)
def __updateMachinesTable(self, connection, uuids = []):
"""
Remove all rows in the Machines table that are no more needed
if a list of uuids is given, only ghost for the given computers are
looking for.
"""
# Get all Machines id that are not a foreign key in Results
if uuids:
todelete = connection.execute(
select([Machines.id], and_(
Machines.uuid.in_(uuids),
not_(
or_(
Machines.id.in_(select([Results.FK_machines])),
Machines.id.in_(select([ProfilesResults.FK_machines]))
)
)
))).fetchall()
else:
todelete = connection.execute(
select([Machines.id], not_(
or_(
Machines.id.in_(select([Results.FK_machines])),
Machines.id.in_(select([ProfilesResults.FK_machines]))
)
))).fetchall()
todelete = [{"id" : x[0]} for x in todelete]
# Delete them if any
if todelete:
connection.execute(self.machines.delete(Machines.id == bindparam("id")), todelete)
def get_cv_count_by_loc(self, fold_count=10):
'''
Returns a list of pairs
'''
# Get list of dates and assign to folds of the same size
all_dates = [date[0].strftime('%Y-%m-%d') for date in self.session.query(sqlalchemy.distinct(Rank.date))]
random.shuffle(all_dates)
fold_size = int(math.floor(len(all_dates) / fold_count))
fold_dates = [all_dates[i:i+fold_size] for i in range(0, len(all_dates), fold_size)]
folds = []
for i in range(0, fold_count):
# Query ranks for all videos
ranks = self.session.query(Rank.video_id, Rank.loc, sqlalchemy.sql.func.count('*').label('entries')).\
filter(sqlalchemy.not_(Rank.loc.like('%all_%'))).\
filter_by(source='view').\
group_by(Rank.video_id, Rank.loc).\
filter(Rank.date.in_(sum([fold_dates[f] for f in range(0,fold_count) if f != i], [])))
training = self._query_to_count_by_loc(ranks)
ranks = self.session.query(Rank.video_id, Rank.loc, sqlalchemy.sql.func.count('*').label('entries')).\
filter(sqlalchemy.not_(Rank.loc.like('%all_%'))).\
filter_by(source='view').\
group_by(Rank.video_id, Rank.loc).\
filter(Rank.date.in_(fold_dates[i]))
test = self._query_to_count_by_loc(ranks)
folds.append((training, test))
return folds
def delete(self, user_id):
"""
Deletes a user.
"""
# Check permission
resource = 'own_user' if user_id == request.ws_session.user_id else 'user'
ensure_allowed('delete', resource)
try:
user = request.dbs.query(model.User).join(model.Entity)\
.filter(model.User.id == user_id)\
.filter(not_(model.User.deleted))\
.filter(not_(model.Entity.deleted)).one()
user.deleted = True
app.logger.info('User {} deleted'.format(user_id))
return {'user_deleted': True}, 200
except NoResultFound:
app.logger.warning('DELETE Request on non existing user {}'.format(user_id))
return 404
except MultipleResultsFound:
app.logger.error('Multiple results found for user {} on DELETE UserAPI'.format(user_id))
return 500
def getTestSeries(branches, start_date, test_names, last_run=None):
# Find all the Branch/OS/Test combinations
if len(test_names) > 0:
test_clause = db.tests.pretty_name.in_(test_names)
else:
test_clause = True
q = sa.select(
[db.branches.id.label('branch_id'), db.branches.name.label('branch_name'), db.os_list.id.label('os_id'), db.os_list.name.label('os_name'), db.tests.id.label('test_id'), db.tests.pretty_name, db.tests.name.label('test_name')],
sa.and_(
db.test_runs.machine_id == db.machines.id,
db.builds.id == db.test_runs.build_id,
db.os_list.id == db.machines.os_id,
db.tests.id == db.test_runs.test_id,
db.test_runs.date_run > start_date,
db.branches.name.in_(branches),
goodNameClause,
sa.not_(db.machines.name.like('%stage%')),
sa.not_(db.tests.pretty_name.like("%NoChrome%")),
sa.not_(db.tests.pretty_name.like("%Fast Cycle%")),
test_clause,
))
if last_run:
q = q.where(db.test_runs.id > last_run)
q = q.distinct()
retval = []
for row in q.execute():
retval.append(TestSeries(*row))
return retval
def test_in_filtering(self):
"""test the behavior of the in_() function."""
users.insert().execute(user_id=7, user_name="jack")
users.insert().execute(user_id=8, user_name="fred")
users.insert().execute(user_id=9, user_name=None)
s = users.select(users.c.user_name.in_([]))
r = s.execute().fetchall()
# No username is in empty set
assert len(r) == 0
s = users.select(not_(users.c.user_name.in_([])))
r = s.execute().fetchall()
# All usernames with a value are outside an empty set
assert len(r) == 2
s = users.select(users.c.user_name.in_(["jack", "fred"]))
r = s.execute().fetchall()
assert len(r) == 2
s = users.select(not_(users.c.user_name.in_(["jack", "fred"])))
r = s.execute().fetchall()
# Null values are not outside any set
assert len(r) == 0
def db_fix_fields_attrs(session):
"""
Ensures that the current store and the field_attrs.json file correspond.
The content of the field_attrs dict is used to add and remove all of the
excepted forms of field_attrs for FieldAttrs in the db.
"""
field_attrs = read_json_file(Settings.field_attrs_file)
std_lst = [u'inputbox', u'textarea', u'checkbox', u'tos', u'date']
for field_type, attrs_dict in field_attrs.items():
attrs_to_keep_for_type = attrs_dict.keys()
if field_type in std_lst:
# Ensure that the standard field attrs do not have extra attr rows
_filter = not_(models.FieldAttr.name.in_(attrs_to_keep_for_type)), \
models.FieldAttr.field_id == models.Field.id, \
models.Field.type == field_type, \
models.Field.template_id == None
else:
# Look for dropped attrs in non-standard field_groups like whistleblower_identity
_filter = not_(models.FieldAttr.name.in_(attrs_to_keep_for_type)), \
models.FieldAttr.field_id == models.Field.id, \
models.Field.template_id == field_type
for res in session.query(models.FieldAttr).filter(*_filter):
session.delete(res)
# Add keys to the db that have been added to field_attrs
for field in session.query(models.Field):
type = field.type if field.template_id is None else field.template_id
attrs = field_attrs.get(type, {})
db_update_fieldattrs(session, field.id, attrs, None)
def update_commutes(session, dests, modes, chunksize=50, delay=5, verbose=True, **kwargs):
"""
Look up commute distances and times from Google Maps API for posts in the
database that are missing commute information.
"""
query = (
session
.query(ApartmentPost)
.outerjoin(ApartmentPost.commutes)
.filter(not_(ApartmentPost.commutes.any()))
.filter(not_(ApartmentPost.latitude == None)))
num_updates = query.count()
num_processed = 0
for posts in grouper(chunksize, query):
_process_batch(session, posts, dests, modes, **kwargs)
num_processed += len(posts)
print "{}/{} commutes processed".format(num_processed, num_updates)
_random_pause(delay)
try:
session.commit()
except:
session.rollback()
raise
finally:
session.close()
def _query_citing_records(self, show_duplicates=False):
"""Returns records which cites this one."""
index_ref = self._get_index_ref()
if not index_ref:
raise Exception("There is no index_ref for this object")
citation_query = RecordMetadata.query.with_entities(RecordMetadata.id,
RecordMetadata.json['control_number'])
citation_filter = referenced_records(RecordMetadata.json).contains([index_ref])
filter_deleted_records = or_(not_(type_coerce(RecordMetadata.json, JSONB).has_key('deleted')), # noqa: W601
not_(RecordMetadata.json['deleted'] == cast(True, JSONB)))
only_literature_collection = type_coerce(RecordMetadata.json, JSONB)['_collections'].contains(['Literature'])
filter_superseded_records = or_(
not_(type_coerce(RecordMetadata.json, JSONB).has_key('related_records')), # noqa: W601
not_(type_coerce(RecordMetadata.json, JSONB)['related_records'].contains([{'relation': 'successor'}]))
)
citations = citation_query.filter(citation_filter,
filter_deleted_records,
filter_superseded_records,
only_literature_collection)
if not show_duplicates:
# It just hides duplicates, and still can show citations
# which do not have proper PID in PID store
# Duplicated data should be removed with the CLI command
citations = citations.distinct(RecordMetadata.json['control_number'])
return citations
def check_permission_action_internal(session_id, action, resource, user_id):
try:
session = request.dbs.query(model.Session).join(model.User)\
.join(model.Entity)\
.filter(not_(model.User.deleted))\
.filter(not_(model.Entity.deleted))\
.filter(model.Session.id == session_id)\
.filter(model.Session.user_id == user_id).one()
# Check that the session is still active
if not session.is_active:
app.logger.info('Session {} expired, unable to check permission'.format(session_id))
return {'error': 'session_expired'}, 404
# Refresh the session
session.refreshed = datetime.datetime.now()
# Check permission
allowed = bool(acl.is_allowed(session.role, action, resource))
app.logger.info('Permission {} for action {} on {} for user {} in session {}'.format(
'granted' if allowed else 'denied', action, resource, user_id, session_id))
return {'allowed': allowed}, 200
except NoResultFound:
app.logger.warning('No result found for user {} and session {}'.format(user_id, session_id))
return {'error': 'invalid_session'}, 404
except MultipleResultsFound:
app.logger.error('Multiple results found for user {} and session {}'.format(user_id, session_id))
return '', 500
except AssertionError:
app.logger.error('Request on non existing resource: {}'.format(resource))
return {'error': 'invalid_resource'}, 404
def session_info_action(session_id, user_id):
try:
# Find the session
session = request.dbs.query(model.Session).join(model.User)\
.join(model.Entity)\
.filter(not_(model.User.deleted))\
.filter(not_(model.Entity.deleted))\
.filter(model.Session.id == session_id)\
.filter(model.Session.user_id == user_id).one()
# Check that the session is still active
if not session.is_active:
app.logger.info('Session {} expired, unable to get session data'.format(session_id))
return '', 404
resp_data = {
'session_id': session.id,
'user_id': session.user_id,
'opened': session.opened.isoformat(),
'refreshed': session.refreshed.isoformat(),
'expires': session.expires.isoformat()
}
app.logger.info('Informed about session {} for user {}'.format(session_id, user_id))
return resp_data, 200
except NoResultFound:
app.logger.warning('Try to check NON existing session {} for user {}'.format(session_id, user_id))
return '', 404
except MultipleResultsFound:
app.logger.error('Multiple results found on query for session with session:{} and user:{}in v1:logout'.format(session_id, user_id))
return '', 500
def _ibm_volume_get_all_except_key_query(context,
key=None,
session=None):
# Find all the ids in volumes that has is_boot_volume key in
# volume_metadata table. Create a subquery.
if not session:
# it takes ~ 4s to get_session for the first time.
session = cinder_db.get_session()
with session.begin():
boot_vols = model_query(context,
cinder_models.Volume.id,
session=session).\
join("volume_metadata").\
filter(cinder_models.VolumeMetadata.key == key).\
subquery()
# Filter out all the boot volumes
if cinder_db.is_admin_context(context):
return model_query(context,
cinder_models.Volume,
session=session).\
options(joinedload('volume_metadata')).\
options(joinedload('volume_admin_metadata')).\
filter(not_(cinder_models.Volume.id.in_(boot_vols)))
else:
return model_query(context,
cinder_models.Volume,
session=session).\
options(joinedload('volume_metadata')).\
filter(not_(cinder_models.Volume.id.in_(boot_vols)))
def find_matching_subscriptions(s3_path):
subscription_daos = SubscriptionDao.query\
.join(DatasetDao, DatasetDao.id == SubscriptionDao.data['dataset_id'].astext)\
.filter(SubscriptionDao.data['state'].astext == SubscriptionState.ACTIVE)\
.filter(
or_(
SubscriptionDao.data['s3_path_start_prefix_inclusive'].astext <= s3_path,
SubscriptionDao.data['s3_path_start_prefix_inclusive'] == 'null',
not_(SubscriptionDao.data.has_key('s3_path_start_prefix_inclusive')),
).self_group()
)\
.filter(
or_(
SubscriptionDao.data['s3_path_end_prefix_exclusive'].astext > s3_path,
SubscriptionDao.data['s3_path_end_prefix_exclusive'] == 'null',
not_(SubscriptionDao.data.has_key('s3_path_end_prefix_exclusive')),
).self_group()
)\
.filter(
or_(
literal(s3_path).op('~')(cast(SubscriptionDao.data['s3_path_regex_filter'].astext, String)),
SubscriptionDao.data['s3_path_regex_filter'] == 'null',
not_(SubscriptionDao.data.has_key('s3_path_regex_filter')),
).self_group()
)\
.filter(literal(s3_path).like(DatasetDao.data['location'].astext + '%'))\
.all()
return [s.to_model() for s in subscription_daos]
def match_obsolete_refs(args):
with open(args.data_file(args.version, 'obsolete_refs.json')) as fp:
refs = json.load(fp)
matched = args.data_file(args.version, 'obsolete_refs_matched.json')
if matched.exists():
with open(matched) as fp:
matched = json.load(fp)
else:
matched = {}
#
# TODO: optionally re-evaluate known-unmatched refs!
#
count = 0
f, m = 0, 0
for id_ in refs:
if id_ in matched:
continue
count += 1
if count > 1000:
print '1000 obsolete refs processed!'
break
ref = Ref.get(id_)
found = False
if ref.description and len(ref.description) > 5:
for match in DBSession.query(Ref)\
.filter(not_(Source.id.in_(refs)))\
.filter(Source.description.contains(ref.description))\
.filter(or_(Source.author == ref.author, Source.year == ref.year))\
.limit(10):
print '++', ref.id, '->', match.id, '++', ref.author, '->', match.author, '++', ref.year, '->', match.year
matched[ref.id] = match.id
found = True
break
if not found and ref.name and len(ref.name) > 5:
for match in DBSession.query(Ref)\
.filter(not_(Source.id.in_(refs)))\
.filter(Source.name == ref.name)\
.limit(10):
try:
if match.description and ref.description and slug(match.description) == slug(ref.description):
print '++', ref.id, '->', match.id, '++', ref.description, '->', match.description
matched[ref.id] = match.id
found = True
break
except AssertionError:
continue
if not found:
m += 1
print '--', ref.id, ref.name, ref.description
matched[ref.id] = None
else:
f += 1
print f, 'found'
print m, 'missed'
with open(args.data_file(args.version, 'obsolete_refs_matched.json'), 'w') as fp:
json.dump(matched, fp)
def main():
ini_file = sys.argv[1]
conf_parser = ConfigParser.ConfigParser( {'here': os.getcwd()} )
conf_parser.read( ini_file )
configuration = {}
for key, value in conf_parser.items( "app:main" ):
configuration[key] = value
database_connection = configuration['database_connection']
file_path = configuration['file_path']
app = TestApplication( database_connection=database_connection, file_path=file_path )
jobs = {}
try:
for job in app.model.Job.filter( sa.and_( app.model.Job.table.c.create_time.between( '2008-05-23', '2008-11-29' ),
app.model.Job.table.c.state == 'ok',
sa.or_( sa.and_( sa.or_( app.model.Job.table.c.tool_id == 'gops_intersect_1',
app.model.Job.table.c.tool_id == 'gops_subtract_1',
app.model.Job.table.c.tool_id == 'gops_coverage_1' ),
sa.not_( app.model.Job.table.c.command_line.like( '%-2 1,2,3%' ) ) ),
sa.and_( sa.or_( app.model.Job.table.c.tool_id == 'gops_complement_1',
app.model.Job.table.c.tool_id == 'gops_merge_1' ),
sa.not_( app.model.Job.table.c.command_line.like( '%-1 1,2,3%' ) ) ) ) ) ).all():
print "# processing job id %s" % str( job.id )
for jtoda in job.output_datasets:
print "# --> processing JobToOutputDatasetAssociation id %s" % str( jtoda.id )
hda = app.model.HistoryDatasetAssociation.get( jtoda.dataset_id )
print "# ----> processing HistoryDatasetAssociation id %s" % str( hda.id )
if not hda.deleted:
# Probably don't need this check, since the job state should suffice, but...
if hda.dataset.state == 'ok':
history = app.model.History.get( hda.history_id )
print "# ------> processing history id %s" % str( history.id )
if history.user_id:
cmd_line = str( job.command_line )
new_output = tempfile.NamedTemporaryFile('w')
if job.tool_id in ['gops_intersect_1', 'gops_subtract_1', 'gops_coverage_1']:
new_cmd_line = " ".join(map(str, cmd_line.split()[:4])) + " " + new_output.name + " " + " ".join(map(str, cmd_line.split()[5:]))
job_output = cmd_line.split()[4]
else:
new_cmd_line = " ".join(map(str, cmd_line.split()[:3])) + " " + new_output.name + " " + " ".join(map(str, cmd_line.split()[4:]))
job_output = cmd_line.split()[3]
try:
os.system(new_cmd_line)
except:
pass
diff_status = os.system('diff %s %s >> /dev/null' % (new_output.name, job_output))
if diff_status == 0:
continue
print "# --------> Outputs differ"
user = app.model.User.get( history.user_id )
jobs[ job.id ] = {}
jobs[ job.id ][ 'hda_id' ] = hda.id
jobs[ job.id ][ 'hda_name' ] = hda.name
jobs[ job.id ][ 'hda_info' ] = hda.info
jobs[ job.id ][ 'history_id' ] = history.id
jobs[ job.id ][ 'history_name' ] = history.name
jobs[ job.id ][ 'history_update_time' ] = history.update_time
jobs[ job.id ][ 'user_email' ] = user.email
except Exception, e:
print "# caught exception: %s" % str( e )
def get_log_entries():
# Limits and offsets
if 'limit' in request.args:
limit = int(request.args['limit'])
if limit > 100:
limit = 100
else:
limit = 10
if 'offset' in request.args:
offset = int(request.args['offset'])
else:
offset = 0
if 'organization_id' in request.args:
query = LogEntry.query\
.filter(LogEntry.organization_id == request.args['organization_id'])\
.filter(sa.not_(LogEntry.retired))
else:
query = LogEntry.query.filter(sa.not_(LogEntry.retired))
if 'search' in request.args:
if request.args["search"]:
slist = []
clist=[]
for s in request.args["search"].split():
slist.append(sa.or_(sa.func.upper(LogEntry.entry_title).like("%" + s.upper() + "%")))
slist.append(sa.or_(sa.func.upper(LogEntry.entry_description).like("%" + s.upper() + "%")))
clist.append(sa.or_(sa.func.upper(LogEntryComment.comment).like("%" + s.upper() + "%")))
query=LogEntry.query\
.filter(sa.or_(*slist) | LogEntry.id.in_(LogEntryComment.query.with_entities("log_entry_id")\
.filter(sa.or_(*clist))\
.filter(LogEntryComment.retired == False)))\
.filter (LogEntry.user_id.in_(User.query.with_entities("id")\
.filter(sa.func.upper(User.first_name).in_(request.args["search"].upper().split()) | sa.func.upper(User.last_name).in_(request.args["search"].upper().split()))))\
.filter(LogEntry.organization_id == request.args['organization_id'])\
.filter(sa.not_(LogEntry.retired))
if query.count()==0:
query=LogEntry.query\
.filter(sa.or_(*slist) | LogEntry.id.in_(LogEntryComment.query.with_entities("log_entry_id")\
.filter(sa.or_(*clist))\
.filter(LogEntryComment.retired == False)))\
.filter(LogEntry.organization_id == request.args['organization_id'])\
.filter(LogEntry.retired == False)
if 'daterange' in request.args and request.args["daterange"]:
rdates=request.args["daterange"].split('_')
query.whereclause.append(sa.between(LogEntry.entry_time,rdates[0] + " 00:00:00.000",rdates[1] + " 11:59:59.000"))
log_entries = query.order_by(sa.desc(LogEntry.entry_time)).limit(limit).offset(offset).all()
if not log_entries:
return jsonify({}), 204
log_entries = [l.to_json() for l in log_entries]
return jsonify(log_entries=log_entries)
def empty_category(session):
q = session.query(Category)
q = q.outerjoin(Snippet)
q = q.filter(sql.not_(Category.children.any(Category.parent_id != None)))
# q = q.group_by(Category.id)
# q = q.having(sql.func.count(Snippet.id) == 0)
q = q.filter(sql.not_(Category.snippets.any(Snippet.id != None)))
return q
def _find_addresses_to_be_unlocked(context, network_ids, addresses):
addresses = [_to_int(address) for address in addresses]
query = context.session.query(models.IPAddress)
query = query.filter(models.IPAddress.network_id.in_(network_ids))
if addresses:
query = query.filter(not_(models.IPAddress.address.in_(addresses)))
query = query.filter(not_(models.IPAddress.lock_id.is_(None)))
return query.all()
def receive_list(self, session_key, page=1, page_length=20):
'''
@type session_key: string
@param session_key: 사용자 Login Session
@type page: int
@param page: Page Number
@type page_length: int
@param page_length: Number of Messages to get in one page
@rtype: ttypes.MessageList
@return:
1. 리스트 읽어오기 성공: MessageList
2. 리스트 읽어오기 실패:
1. 로그인되지 않은 사용자: NotLoggedIn Exception
2. 데이터베이스 오류: InternalError Exception
'''
# TODO: ArticleManager 참고하여 싹 뜯어고치기
# TODO: page 관련 정보 처리하는거 ArticleManager 에서 아예 util 로 옮기기
ret_dict = {}
user_info = self.engine.login_manager.get_session(session_key)
session = model.Session()
to_user = self.engine.member_manager._get_user(session, user_info.username)
blacklist_dict_list = self.engine.blacklist_manager.get_blacklist(session_key)
blacklist_users = set()
for blacklist_item in blacklist_dict_list:
if blacklist_item.block_message:
blacklist_users.add(blacklist_item.blacklisted_user_username)
received_messages_count = session.query(model.Message).filter(
and_(model.Message.to_id==to_user.id,
not_(model.Message.received_deleted==True)
)).count()
received_new_messages_count = session.query(model.Message).filter(
and_(model.Message.to_id==to_user.id,
model.Message.read_status==u'N',
not_(model.Message.received_deleted==True)
)).count()
last_page = int(received_messages_count / page_length)
page = int(page)
if received_messages_count % page_length != 0:
last_page += 1
elif received_messages_count == 0:
last_page += 1
if page > last_page:
session.close()
raise InvalidOperation('wrong pagenum')
offset = page_length * (page - 1)
last = offset + page_length
received_messages = session.query(model.Message).filter(
and_(model.Message.to_id==to_user.id,
not_(model.Message.received_deleted==True)
)).order_by(model.Message.id.desc())[offset:last]
received_messages_dict_list = self._get_dict_list(received_messages, MESSAGE_WHITELIST, blacklist_users)
ret_dict['hit'] = received_messages_dict_list
ret_dict['last_page'] = last_page
ret_dict['new_message_count'] = received_new_messages_count
ret_dict['results'] = received_messages_count
session.close()
return MessageList(**ret_dict)
def outstanding():
# XXX: discounted /2
outstanding = db.session.query(models.Attendance) \
.join(models.Course, models.Applicant) \
.filter(not_(models.Attendance.waiting),
not_(models.Applicant.discounted),
models.Attendance.has_to_pay,
models.Attendance.amountpaid < models.Course.price)
return dict(outstanding=outstanding)
def visible_in_request(self):
if session.get('edit_mode'):
return sa.text('1=1')
else:
return sa.and_(
self.visible,
self.photo.has(visible=True),
sa.not_(self.trashed),
sa.not_(self.photo.has(trashed=True)),
)
def _build_resource_map(self):
import ckan.model as model
# Find all non .csv/.xls links for gov.uk
resources = model.Session.query(model.Resource).\
filter(model.Resource.url.like("%/www.gov.uk/%")).\
filter(not_(model.Resource.url.ilike("%.csv"))).\
filter(not_(model.Resource.url.ilike("%.xls"))).\
filter(not_(model.Resource.url.ilike("%.xlsx"))).\
filter(not_(model.Resource.url.ilike("%.pdf"))).\
filter(not_(model.Resource.url.ilike("%.rdf"))).\
filter(not_(model.Resource.url.ilike("%.json"))).\
filter(not_(model.Resource.url.ilike("%.doc"))).\
filter(not_(model.Resource.resource_type=='documentation')).\
filter(not_(model.Resource.resource_type=='timeseries'))
#filter(model.Resource.state=='active')
log.info("Found %d resources for www.gov.uk links" % resources.count())
for r in resources:
pkg = r.resource_group.package
# If we only want one, then skip the others
if self.options.single and not pkg.name == self.options.single:
continue
if pkg.state == 'active':
self.local_resource_map[pkg].append(r)
def _apply_extra_criteria(q_obj, parsed_query):
if parsed_query.get('q', ''):
query = parsed_query.get('q')
q_obj = q_obj.filter(or_(Song.name.ilike('%{0}%'.format(query)), Song.artist.ilike('%{0}%'.format(query))))
for exc in parsed_query.get('exclude', []):
q_obj = q_obj.filter(and_(not_(Song.name.ilike('%{0}%'.format(exc))), not_(Song.artist.ilike('%{0}%'.format(exc)))))
for ats in parsed_query.get('artist', []):
q_obj = q_obj.filter(Song.artist.ilike('%{0}%'.format(ats)))
for sng in parsed_query.get('song', []):
q_obj = q_obj.filter(Song.name.ilike('%{0}%'.format(sng)))
return q_obj
def put(self, user_id):
"""
Updates a user.
"""
# Check permission
resource = 'own_user' if user_id == request.ws_session.user_id else 'user'
ensure_allowed('update', resource)
try:
user = request.dbs.query(model.User).join(model.Entity)\
.filter(model.User.id == user_id)\
.filter(not_(model.User.deleted))\
.filter(not_(model.Entity.deleted)).one()
app.logger.info('Request on user {}'.format(user_id))
except NoResultFound:
app.logger.warning('PUT Request on non existing user {}'.format(user_id))
return {}, 404
except MultipleResultsFound:
app.logger.error('Multiple results found for user {} on PUT UserAPI'.format(user_id))
return {}, 500
args = self.reqparse.parse_args()
if args['login'] is not None:
# We check if another non deleted user has the same login
login = args['login']
q = request.dbs.query(model.User).join(model.Entity)\
.filter(not_(model.User.deleted))\
.filter(not_(model.Entity.deleted))\
.filter(model.User.login == login)
if request.dbs.query(q.exists()).scalar():
app.logger.warning('Tried to create an user with already existing name')
return {'error': 'User with the same login exists.'}, 412
user.login = login
if args['entity_id'] is not None:
user.entity_id = args['entity_id']
if args['password_hash'] is not None:
user.password_hash = args['password_hash']
if args['role'] is not None:
role = args['role']
if role in roles:
user.role = role
else:
return {'error': 'Role \'' + role + '\' is not allowed'}, 412
if args['first_name'] is not None:
user.first_name = args['first_name']
if args['last_name'] is not None:
user.last_name = args['last_name']
app.logger.info('User {} successfully updated'.format(user_id))
return marshal(user.to_dict(), user_fields)
def recipes(self, recipes, action='.', *args, **kw):
recipes = recipes.join(Recipe.recipeset)\
.join(RecipeSet.job)\
.filter(not_(Job.is_deleted))
recipes_return = self._recipes(recipes, **kw)
searchvalue = None
search_options = {}
if recipes_return:
if 'recipes_found' in recipes_return:
recipes = recipes_return['recipes_found']
if 'searchvalue' in recipes_return:
searchvalue = recipes_return['searchvalue']
if 'simplesearch' in recipes_return:
search_options['simplesearch'] = recipes_return['simplesearch']
PDC = widgets.PaginateDataGrid.Column
recipes_grid = myPaginateDataGrid(fields=[
PDC(name='id',
getter=lambda x: make_link(url='./%s' % x.id, text=x.t_id),
title='ID',
options=dict(sortable=True)),
PDC(name='whiteboard',
getter=lambda x: x.whiteboard,
title='Whiteboard',
options=dict(sortable=True)),
PDC(name='distro_tree.arch.arch',
getter=lambda x: x.arch,
title='Arch',
options=dict(sortable=True)),
PDC(name='resource.fqdn',
getter=lambda x: x.resource and x.resource.link,
title='System',
options=dict(sortable=True)),
PDC(name='distro_tree.distro.name',
getter=lambda x: x.distro_tree and x.distro_tree.link,
title='Distro Tree',
options=dict(sortable=False)),
PDC(name='progress',
getter=lambda x: x.progress_bar,
title='Progress',
options=dict(sortable=False)),
PDC(name='status',
getter=_custom_status,
title='Status',
options=dict(sortable=True)),
PDC(name='result',
getter=_custom_result,
title='Result',
options=dict(sortable=True)),
PDC(name='action',
getter=lambda x: self.action_cell(x),
title='Action',
options=dict(sortable=False)),
])
search_bar = SearchBar(
name='recipesearch',
label=_(u'Recipe Search'),
simplesearch_label='Lookup ID',
table=search_utility.Recipe.search.create_complete_search_table(),
search_controller=url("/get_search_options_recipe"),
quick_searches=[('Status-is-Queued', 'Queued'),
('Status-is-Running', 'Running'),
('Status-is-Completed', 'Completed')])
return dict(title="Recipes",
grid=recipes_grid,
list=recipes,
search_bar=search_bar,
action=action,
options=search_options,
searchvalue=searchvalue)
def delete_not_in_view_types(cls, view_types):
'''Delete the Resource Views not in the received view types list'''
query = meta.Session.query(ResourceView) \
.filter(sa.not_(ResourceView.view_type.in_(view_types)))
return query.delete(synchronize_session='fetch')
def get_sqla_criterion_expr(column, criterion, negate=False):
"""Create a SQLAlchemy criterion expression
**Parameters:**
* **column** - (*SQLAlchemy column*) A SQLAlchemy column object to be used
in the expression
* **criterion** - (*3-item iterable*) A 3-item tuple or list of the format
[field, operation, value(s)]. See `core.CRITERIA_OPERATIONS` for supported
operations. The value item may take on different formats depending on the
operation. In most cases passing an iterable will result in multiple
criteria of that operation being formed. For example, ("my_field", "=",
[1,2,3]) would logically or 3 conditions of equality to the 3 values in the
list. The "between" operations expect each value to be a 2-item iterable
representing the lower and upper bound of the criterion.
* **negate** - (*bool, optional*) Negate the expression
**Returns:**
(*SQLAlchemy expression*) - A SQLALchemy expression representing the
criterion
**Notes:**
Postgresql "like" is case sensitive, but mysql "like" is not. Postgresql
also supports "ilike" to specify case insensitive, so one option is to look
at the dialect to determine the function, but that is not supported yet.
"""
field, op, values = criterion
op = op.lower()
if not isinstance(values, (list, tuple)):
values = [values]
use_or = True
has_null = any([v is None for v in values])
if op == "=":
clauses = [column == v if v is not None else column.is_(None) for v in values]
elif op == "!=":
clauses = [column != v if v is not None else column.isnot(None) for v in values]
elif op == ">":
clauses = [column > v for v in values]
elif op == "<":
clauses = [column < v for v in values]
elif op == ">=":
clauses = [column >= v for v in values]
elif op == "<=":
clauses = [column <= v for v in values]
elif op == "in":
if has_null:
clauses = [
column == v if v is not None else column.is_(None) for v in values
]
else:
clauses = [column.in_(values)]
elif op == "not in":
use_or = False
if has_null:
clauses = [
column != v if v is not None else column.isnot(None) for v in values
]
else:
clauses = [sa.not_(column.in_(values))]
elif op == "between":
raiseifnot(len(values) == 2, "Between clause value must have length of 2")
clauses = [column.between(values[0], values[1])]
elif op == "not between":
raiseifnot(len(values) == 2, "Between clause value must have length of 2")
clauses = [sa.not_(column.between(values[0], values[1]))]
elif op == "like":
clauses = [column.like(v) for v in values]
elif op == "not like":
use_or = False
clauses = [sa.not_(column.like(v)) for v in values]
else:
raise ZillionException("Invalid criterion operand: %s" % op)
if use_or:
clause = sa.or_(*clauses)
else:
clause = sa.and_(*clauses)
if negate:
clause = sa.not_(clause)
return clause
def delete_old_records(cls,
task_id: str,
dag_id: str,
num_to_keep=conf.getint(
"core",
"max_num_rendered_ti_fields_per_task",
fallback=0),
session: Session = None):
"""
Keep only Last X (num_to_keep) number of records for a task by deleting others
:param task_id: Task ID
:param dag_id: Dag ID
:param num_to_keep: Number of Records to keep
:param session: SqlAlchemy Session
"""
if num_to_keep <= 0:
return
tis_to_keep_query = session \
.query(cls.dag_id, cls.task_id, cls.execution_date) \
.filter(cls.dag_id == dag_id, cls.task_id == task_id) \
.order_by(cls.execution_date.desc()) \
.limit(num_to_keep)
if session.bind.dialect.name in ["postgresql", "sqlite"]:
# Fetch Top X records given dag_id & task_id ordered by Execution Date
subq1 = tis_to_keep_query.subquery('subq1')
session.query(cls) \
.filter(
cls.dag_id == dag_id,
cls.task_id == task_id,
tuple_(cls.dag_id, cls.task_id, cls.execution_date).notin_(subq1)) \
.delete(synchronize_session=False)
elif session.bind.dialect.name in ["mysql"]:
# Fetch Top X records given dag_id & task_id ordered by Execution Date
subq1 = tis_to_keep_query.subquery('subq1')
# Second Subquery
# Workaround for MySQL Limitation (https://stackoverflow.com/a/19344141/5691525)
# Limitation: This version of MySQL does not yet support
# LIMIT & IN/ALL/ANY/SOME subquery
subq2 = (session.query(subq1.c.dag_id, subq1.c.task_id,
subq1.c.execution_date).subquery('subq2'))
session.query(cls) \
.filter(
cls.dag_id == dag_id,
cls.task_id == task_id,
tuple_(cls.dag_id, cls.task_id, cls.execution_date).notin_(subq2)) \
.delete(synchronize_session=False)
else:
# Fetch Top X records given dag_id & task_id ordered by Execution Date
tis_to_keep = tis_to_keep_query.all()
filter_tis = [
not_(
and_(cls.dag_id == ti.dag_id, cls.task_id == ti.task_id,
cls.execution_date == ti.execution_date))
for ti in tis_to_keep
]
session.query(cls) \
.filter(and_(*filter_tis)) \
.delete(synchronize_session=False)
repr(
session.query(Customers).filter(
or_(Customers.town == 'Peterbrugh',
Customers.town == 'Norfolk')).all()))
print(
repr(
session.query(Customers).filter(
and_(Customers.first_name == 'John',
Customers.town == 'Norfolk')).all()))
print(
repr(
session.query(Customers).filter(
and_(Customers.first_name == 'John',
not_(Customers.town == 'Peterbrugh', ))).all()))
print(repr(session.query(Orders).filter(Orders.date_shipped == None).all()))
print(repr(session.query(Orders).filter(Orders.date_shipped != None).all()))
print(
repr(
session.query(Customers).filter(
Customers.first_name.in_(['Toby', 'Sarah'])).all()))
print(
repr(
session.query(Customers).filter(
Customers.first_name.notin_(['Toby', 'Sarah'])).all()))
print(repr(
session.query(Items).filter(Items.cost_price.between(10, 50)).all()))
print(
def _negate(t, expr):
op = expr.op()
arg, = map(t.translate, op.args)
return sa.not_(arg) if isinstance(expr, ir.BooleanValue) else -arg
def _find_scheduled_tasks(
self,
dag_run: DagRun,
session: Session,
check_execution_date=False) -> Optional[List[TI]]:
"""
Make scheduling decisions about an individual dag run
``currently_active_runs`` is passed in so that a batch query can be
used to ask this for all dag runs in the batch, to avoid an n+1 query.
:param dag_run: The DagRun to schedule
:return: scheduled tasks
"""
if not dag_run or dag_run.get_state() in State.finished:
return
try:
dag = dag_run.dag = self.dagbag.get_dag(dag_run.dag_id,
session=session)
except SerializedDagNotFound:
self.log.exception("DAG '%s' not found in serialized_dag table",
dag_run.dag_id)
return None
if not dag:
self.log.error("Couldn't find dag %s in DagBag/DB!",
dag_run.dag_id)
return None
currently_active_runs = session.query(TI.execution_date, ).filter(
TI.dag_id == dag_run.dag_id,
TI.state.notin_(list(State.finished)),
).all()
if check_execution_date and dag_run.execution_date > timezone.utcnow(
) and not dag.allow_future_exec_dates:
self.log.warning("Execution date is in future: %s",
dag_run.execution_date)
return None
if dag.max_active_runs:
if (len(currently_active_runs) >= dag.max_active_runs
and dag_run.execution_date not in currently_active_runs):
self.log.info(
"DAG %s already has %d active runs, not queuing any tasks for run %s",
dag.dag_id,
len(currently_active_runs),
dag_run.execution_date,
)
return None
self._verify_integrity_if_dag_changed(dag_run=dag_run, session=session)
schedulable_tis, callback_to_run = dag_run.update_state(
session=session, execute_callbacks=False)
dag_run.schedule_tis(schedulable_tis, session)
session.commit()
query = (session.query(TI).outerjoin(TI.dag_run).filter(
or_(DR.run_id.is_(None),
DR.run_type != DagRunType.BACKFILL_JOB)).join(
TI.dag_model).filter(not_(DM.is_paused)).filter(
TI.state == State.SCHEDULED).options(
selectinload('dag_model')))
scheduled_tis: List[TI] = with_row_locks(
query,
of=TI,
**skip_locked(session=session),
).all()
return scheduled_tis
def get_filtered_samples(response: Response,
dataset_id: int,
page: Optional[int] = None,
limit: Optional[int] = None,
labels: Optional[List[int]] = Query(None),
users: Optional[List[int]] = Query(None),
labeled: Optional[bool] = None,
free_text: Optional[Union[str, bytes]] = None,
divided_labels: Optional[bool] = None,
user: User = Depends(get_current_active_user)):
"""
NOT for usage in connection with Active Learning!
:param response: gets Response Header Object from FastAPI, dont fill\\
:param dataset_id: dataset_id for dataset\\
:param limit: number of samples per page\\
:param page: number of page that should be fetched (beginning with 0) \\
both limit and page need to be filled for paging, returns Total number of elements in the Header in X-Total \\
:param labeled: return only labeled samples (true) / unlabeled samples (false)\\
:param labels: list of label_ids to filter for add each label with label = label_id\\
:param divided_labels: search only for samples, which different users labeled differently\\
:param users: list of user_ids to filter for add each user with users = user_id\\
:param free_text: freetext search (only one word)\\
:param user: the currently active user -> needed for authentication-check\\
:return: list of samples
"""
# return only current associations, if changed code needs to be adapted
only_current_associations = True
dataset = db.query(Dataset).filter(Dataset.id == dataset_id)
if not dataset:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail="Dataset not found for id: {}.".format(dataset_id))
query = db.query(Sample).filter(Sample.dataset_id == dataset_id)
# JOIN table association for later use
if labels or users:
query = query.join(Association, Sample.id == Association.sample_id)
# filter for labels
if labels:
for label_id in labels:
label = db.query(Label).get(label_id)
if not label:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail="Label not found for id: {}.".format(label_id),
)
query = query.filter(Association.label_id.in_(labels))
# filter for users who labeled the sample
if users:
for user_id in users:
user = db.query(User).get(user_id)
if not user:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail="User not found for id: {}.".format(user_id),
)
query = query.filter(Association.user_id.in_(users))
# filter for only labeled or unlabeled datasets
if labeled is not None:
if labeled:
if not (labels or users):
query = query.join(Association,
Sample.id == Association.sample_id)
else:
if users or labels or divided_labels:
raise HTTPException(
status_code=status.HTTP_400_BAD_REQUEST,
detail=
"Cannot process unlabeled Samples if filters for Labels or Users are set.",
)
query = query.filter(Sample.dataset_id == dataset_id,
~Sample.associations.any())
# text search
if free_text:
# prepare text
free_text = free_text.replace(" ", " & ")
sample = db.query(Sample).filter(
Sample.dataset_id == dataset_id).first()
content_type = sample.type
# text search only for content type 'text' and 'table'
if content_type == "text":
matched_tables = select([Text.id]).where(
Text.content.match('{}'.format(free_text)))
query = query.join(Text).filter(Text.id.in_(matched_tables))
elif content_type == "table":
matched_tables = select([Table.id]).where(
Table.content.match('{}'.format(free_text)))
query = query.join(Table).filter(Table.id.in_(matched_tables))
else:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail=
"The Dataset with id {} does not have text to search as content."
.format(dataset_id),
)
# filter for divided labels (sample has more than 1 label)
if divided_labels:
# rebuild base query, join association 2x with alias
association1 = aliased(Association)
association2 = aliased(Association)
base_query = db.query(Sample) \
.filter(Sample.dataset_id == dataset_id) \
.join(association1, Sample.id == association1.sample_id) \
.join(association2, Sample.id == association2.sample_id) \
.filter(association1.is_current == only_current_associations) \
.filter(association1.is_current == only_current_associations)
# use query as subquery to apply other filters (eg. for labels or users)
sub_query = query.with_entities(Sample.id).subquery()
# build new query
query = base_query \
.filter(not_(association1.label_id == association2.label_id)) \
.filter(Sample.id.in_(sub_query)) \
.group_by(Sample.id).having(func.count(association1.label_id) > 1) \
.order_by(func.count(association1.label_id).desc())
# only return samples with no label or a current label
# All Samples with a current label
with_current_association = db.query(Sample.id)\
.join(Association, Sample.id == Association.sample_id)\
.filter(Association.is_current == only_current_associations)
# All Samples with a label
with_association = db.query(Sample.id)\
.join(Association, Sample.id == Association.sample_id)\
.subquery()
# All Samples without any labels
without_association = db.query(Sample.id)\
.filter(Sample.id.notin_(with_association))
valid_samples = with_current_association.union(without_association)
query = query.filter(Sample.id.in_(valid_samples))
# limit number of returned elements and paging, return total_elements in header
if page is not None and limit:
if page < 0:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail=
"Page number needs to be 0 or greater. Page number was: {}.".
format(page),
)
total_elements = query.count()
response.headers["X-Total"] = "{}".format(total_elements)
lower_limit = page * limit
upper_limit = page * limit + limit
query = query.order_by(Sample.id).slice(lower_limit, upper_limit)
samples = query.all()
return samples
def main():
ini_file = sys.argv[1]
conf_parser = ConfigParser.ConfigParser({'here': os.getcwd()})
conf_parser.read(ini_file)
configuration = {}
for key, value in conf_parser.items("app:main"):
configuration[key] = value
database_connection = configuration['database_connection']
file_path = configuration['file_path']
app = TestApplication(database_connection=database_connection,
file_path=file_path)
jobs = {}
try:
for job in app.model.Job.filter(
sa.and_(
app.model.Job.table.c.create_time.between(
'2008-05-23', '2008-11-29'),
app.model.Job.table.c.state == 'ok',
sa.or_(
sa.and_(
sa.or_(
app.model.Job.table.c.tool_id ==
'gops_intersect_1',
app.model.Job.table.c.tool_id ==
'gops_subtract_1',
app.model.Job.table.c.tool_id ==
'gops_coverage_1'),
sa.not_(
app.model.Job.table.c.command_line.like(
'%-2 1,2,3%'))),
sa.and_(
sa.or_(
app.model.Job.table.c.tool_id ==
'gops_complement_1',
app.model.Job.table.c.tool_id ==
'gops_merge_1'),
sa.not_(
app.model.Job.table.c.command_line.like(
'%-1 1,2,3%')))))).all():
print("# processing job id %s" % str(job.id))
for jtoda in job.output_datasets:
print("# --> processing JobToOutputDatasetAssociation id %s" %
str(jtoda.id))
hda = app.model.HistoryDatasetAssociation.get(jtoda.dataset_id)
print("# ----> processing HistoryDatasetAssociation id %s" %
str(hda.id))
if not hda.deleted:
# Probably don't need this check, since the job state should suffice, but...
if hda.dataset.state == 'ok':
history = app.model.History.get(hda.history_id)
print("# ------> processing history id %s" %
str(history.id))
if history.user_id:
cmd_line = str(job.command_line)
new_output = tempfile.NamedTemporaryFile('w')
if job.tool_id in [
'gops_intersect_1', 'gops_subtract_1',
'gops_coverage_1'
]:
new_cmd_line = " ".join(
map(str,
cmd_line.split()[:4])
) + " " + new_output.name + " " + " ".join(
map(str,
cmd_line.split()[5:]))
job_output = cmd_line.split()[4]
else:
new_cmd_line = " ".join(
map(str,
cmd_line.split()[:3])
) + " " + new_output.name + " " + " ".join(
map(str,
cmd_line.split()[4:]))
job_output = cmd_line.split()[3]
try:
os.system(new_cmd_line)
except Exception:
pass
diff_status = os.system(
'diff %s %s >> /dev/null' %
(new_output.name, job_output))
if diff_status == 0:
continue
print("# --------> Outputs differ")
user = app.model.User.get(history.user_id)
jobs[job.id] = {}
jobs[job.id]['hda_id'] = hda.id
jobs[job.id]['hda_name'] = hda.name
jobs[job.id]['hda_info'] = hda.info
jobs[job.id]['history_id'] = history.id
jobs[job.id]['history_name'] = history.name
jobs[job.id][
'history_update_time'] = history.update_time
jobs[job.id]['user_email'] = user.email
except Exception as e:
print("# caught exception: %s" % e)
print("\n\n# Number of incorrect Jobs: %d\n\n" % (len(jobs)))
print(
"#job_id\thda_id\thda_name\thda_info\thistory_id\thistory_name\thistory_update_time\tuser_email"
)
for jid in jobs:
print('%s\t%s\t"%s"\t"%s"\t%s\t"%s"\t"%s"\t%s' %
(str(jid), str(jobs[jid]['hda_id']), jobs[jid]['hda_name'],
jobs[jid]['hda_info'], str(
jobs[jid]['history_id']), jobs[jid]['history_name'],
jobs[jid]['history_update_time'], jobs[jid]['user_email']))
sys.exit(0)
def token_per_realm_count(self, realm, status=None):
"""
Give the number of tokens per realm
:return a dict with the keys: active, inactive,
assigned, unassigned, total
"""
result = {}
# if no realm or empty realm is specified
if realm.strip() == '' or realm.strip() == '/:no realm:/':
# get all tokenrealm ids
token_id_tuples = Session.query(TokenRealm.token_id).all()
token_ids = set()
for token_tuple in token_id_tuples:
token_ids.add(token_tuple[0])
# all tokens, which are not references in TokenRealm
r_condition = and_(not_(Token.LinOtpTokenId.in_(token_ids)))
else:
# otherwise query all items with realm references
r_condition = and_(TokenRealm.realm_id == Realm.id,
Realm.name == u'' + realm,
TokenRealm.token_id == Token.LinOtpTokenId)
result['total'] = Session.query(Token).\
filter(r_condition).distinct().count()
if not status:
return result
for stat in status:
conditions = (and_(r_condition), )
# handle combinations like:
# status=unassigned&active,unassigned&inactive
if '&' in stat:
stati = stat.split('&')
if 'assigned' in stati:
conditions += (and_(Token.LinOtpUserid != u''), )
else:
conditions += (and_(Token.LinOtpUserid == u''), )
if 'active' in stati:
conditions += (and_(Token.LinOtpIsactive == True), )
else:
conditions += (and_(Token.LinOtpIsactive == False), )
else:
# handle single expressions like
# status=unassigned,active
if 'assigned' == stat:
conditions += (and_(Token.LinOtpUserid != u''), )
elif 'unassigned' == stat:
conditions += (and_(Token.LinOtpUserid == u''), )
elif 'active' == stat:
conditions += (and_(Token.LinOtpIsactive == True), )
elif 'inactive' == stat:
conditions += (and_(Token.LinOtpIsactive == False), )
# create the final condition as AND of all conditions
condition = and_(*conditions)
result[stat] = Session.query(TokenRealm, Realm, Token).\
filter(condition).count()
return result
def get_instruments(self, month_end):
issue_dt = [t for t in get_dates(const.FreqEnum.D)
if t < month_end][-self.issue]
with get_session() as ss:
filters = [
# date
fund.Description.setup_date <= issue_dt,
fund.Description.redemption_start_dt <= month_end,
fund.Description.maturity_date >= month_end,
# not connect fund
fund.Description.wind_code.notin_(
ss.query(fund.Connections.child_code)),
# issue reset after convert happened.
sa.not_(
ss.query(fund.Converted.wind_code).filter(
fund.Converted.chg_date > issue_dt,
fund.Converted.chg_date <= month_end,
fund.Converted.wind_code ==
fund.Description.wind_code).exists())
]
if self.open_only:
filters.append(fund.Description.fund_type == '契约型开放式')
if self.initial_only:
filters.append(fund.Description.is_initial == 1)
if self.size > 0:
filters.append(
ss.query(fund.PortfolioAsset.wind_code).filter(
fund.PortfolioAsset.net_asset >= self.size * 1e8,
fund.PortfolioAsset.end_date == (
month_end - pd.Timedelta(days=22) - QuarterEnd()),
fund.PortfolioAsset.wind_code ==
fund.Description.wind_code).exists())
# if self.manager:
#
fund_list = {
code
for (code, ) in ss.query(fund.Description.wind_code).filter(
*filters).all()
}
if self.include or self.exclude:
sector_type = pd.concat((
get_sector(const.AssetEnum.CMF,
valid_dt=month_end,
sector_prefix='2001'),
get_sector(
const.AssetEnum.CMF,
sector_prefix='1000',
valid_dt=max((t for t in get_dates(const.FreqEnum.Q)
if t < month_end)),
),
))
if self.include:
in_fund = sector_type.loc[
lambda df: df['sector_code'].isin(self.include),
'wind_code']
fund_list = fund_list & {*in_fund}
if self.exclude:
ex_fund = sector_type.loc[
lambda df: df['sector_code'].isin(self.exclude),
'wind_code']
fund_list = fund_list - {*ex_fund}
return fund_list
query = session.query(Employee).filter(
and_(Employee.emp_no > 12, Employee.emp_no < 40))
show(query)
print('=' * 100)
print('---------------or------------------')
query = session.query(Employee).filter((Employee.emp_no < 20)
| (Employee.emp_no >= 40))
show(query)
print('*' * 30)
query = session.query(Employee).filter(
or_(Employee.emp_no < 20, Employee.emp_no >= 40))
show(query)
print('*' * 30)
query = session.query(Employee).filter(not_(Employee.emp_no < 20))
show(query)
print('*' * 30)
query = session.query(Employee).filter(~(Employee.emp_no < 20))
show(query)
print('---------------in------------------')
query = session.query(Employee).filter(Employee.emp_no.in_([30, 40]))
show(query)
print('*' * 100)
print('---------------not in------------------')
query = session.query(Employee).filter(~Employee.emp_no.in_([30, 40]))
show(query)
print('*' * 100)
print('--------------like------------------')
def plans_to_teams(plan_results,
debug=True,
group=None,
include=None,
exclude=None):
guestentry = """
%(time)s Uhr - %(roundname)s bei "%(host)s"
%(address)s
Klingeln bei: %(bell)s
Bei Problemen erreicht ihr dieses Team unter: %(phone)s
Routenlink: %(link)s"""
hostentry = """
%(time)s Uhr - %(roundname)s wird von Euch - "%(teamname)s" - zubereitet.
Tipp: Es lohnt sich, für jeden Namen in der Runde etwas Kreppband bereit zu halten!
Zu Gast sind bei Euch:
%(guests)s"""
hostguests = """
Team "%(guestname)s"
(Allergien: %(allergies)s)
(Vegetarier dabei: %(vegetarians)s)
Telefon: %(guestphone)s
"""
subject = "meet&eat - Abendplanung Team %s"
round_datas = ({
"time": "18:00",
"name": "Vorspeise"
}, {
"time": "20:00",
"name": "Hauptgericht"
}, {
"time": "21:30",
"name": "Dessert"
})
print "Preprocess plan..."
guestmap = defaultdict(list)
for team in plan_results:
for host in plan_results[team]:
if team != host:
guestmap[host].append(team)
template = get_template("plan")
sender = "meet&eat Orga <%s>" % config.MAIL_DEFAULT_SENDER
envelope = config.MAIL_DEFAULT_SENDER
print "Fetch data..."
teams = {}
qry = db.session.query(Team).filter_by(deleted=False,
confirmed=True,
backup=False)
if include is not None:
qry = qry.filter(Team.id.in_(include))
if exclude is not None:
qry = qry.filter(not_(Team.id.in_(exclude)))
if group is not None:
qry = qry.filter_by(groups=group)
for team in qry:
teams[str(team.id)] = team
event_date = pretty_date(parse_cfg_date(config.EVENT_DATE), show_year=True)
i = 0
print "Connect to smtp..."
with smtp_session() as session:
print "Send mails..."
for team in plan_results:
plan_detail = []
start_point = MapPoint.from_team(teams[team])
for (round_idx, host) in enumerate(plan_results[team]):
round_data = round_datas[round_idx]
end_point = MapPoint.from_team(teams[host])
# route = openroute_link([start_point, end_point])
route = gmaps_link([start_point, end_point])
start_point = end_point
if team != host:
detail = guestentry % {
"time": round_data["time"],
"roundname": round_data["name"],
"host": teams[host].name,
"address": teams[host].location.street,
"bell": teams[host].location.extra,
"phone": teams[host].phone,
"link": route
}
plan_detail.append(detail)
else:
guest_details = []
for guest in guestmap[team]:
guest_detail = hostguests % {
"guestname": teams[guest].name,
"allergies": teams[guest].allergies,
"vegetarians": teams[guest].vegetarians,
"guestphone": teams[guest].phone
}
guest_details.append(guest_detail)
detail = hostentry % {
"time": round_data["time"],
"roundname": round_data["name"],
"teamname": teams[team].name,
"guests": "\n".join(guest_details)
}
plan_detail.append(detail)
plan = "\n\n".join(plan_detail)
text = template.render(eventdate=event_date,
teamname=teams[team].name,
volume=config.VOLUME,
contact_email=config.CONTACT_EMAIL,
contact_phone=config.CONTACT_PHONE,
plan=plan)
msg = MIMEText(text, "plain", "utf8")
rcpt = teams[team].email
if debug:
rcpt = config.MAIL_DEFAULT_SENDER
msg['Subject'] = subject % teams[team].name
msg['From'] = sender
msg['To'] = rcpt
msg['Date'] = formatdate(localtime=True)
msg['Message-ID'] = make_msgid()
session.sendmail(envelope, [rcpt] + [config.CONTACT_EMAIL],
msg.as_string())
i += 1
print "Mails sent: %d" % i
# 定义自定义操作返回类型为bool
# somecolumn.bool_op('-->')('some value')
"""
where 条件使用连接符
目前没有实现 order by,group by,having操作
"""
from sqlalchemy import and_, or_, not_
# and, or, not 连接符
print(
and_(
users.c.name.like('j%'), users.c.id == addresses.c.user_id,
or_(
addresses.c.email_address == '*****@*****.**',
addresses.c.email_address == '*****@*****.**',
), not_(users.c.id > 5)))
# 使用位运算符表示and,or,not
print(
users.c.name.like('j%') & (users.c.id == addresses.c.user_id)
& ((addresses.c.email_address == '*****@*****.**')
| (addresses.c.email_address == '*****@*****.**'))
& ~(users.c.id > 5))
# as,between的使用
s = select([(users.c.fullname +
", " + addresses.c.email_address).
label('title')]). \
where(
and_(
users.c.id == addresses.c.user_id,
users.c.name.between('m', 'z'),
or_(
def _not_contains(t, expr):
return sa.not_(_contains(t, expr))
def decorated(*args, **kwargs): return sqlalchemy.not_(operation(*args, **kwargs))
def add_comment(json):
if (not json or not ('postId' in json) or not ('userId' in json)
or not ('commentText' in json)):
abort(400, 'Not all fields were received.')
post_query = db.session.query(MoveDetails).filter(
and_(MoveDetails.id == json['postId'],
not_(MoveDetails.deleted))).first()
if not post_query:
abort(400, 'Post with given id does not exist.')
user_query = db.session.query(User).filter(
and_(User.id == json['userId'], not_(User.deleted))).first()
if not user_query:
abort(400, 'User with given id does not exist.')
if 'parentCommentId' in json:
comment_query = db.session.query(Comment).filter(
Comment.id == json['parentCommentId']).first()
if not comment_query:
abort(400, 'Comment with given parentCommentId does not exist.')
comment = Comment(poster=user_query.id,
parent_post=post_query.id,
creation_datetime=datetime.now(),
text=json['commentText'],
parent_comment=json['parentCommentId'],
is_offer=False)
else:
comment = Comment(poster=user_query.id,
parent_post=post_query.id,
creation_datetime=datetime.now(),
text=json['commentText'],
is_offer=False)
post_query.comments.append(comment)
db.session.commit()
if 'parentCommentId' in json and comment_query.poster != user_query.id:
update = Update(update_type='comment_reply',
updated_movee_id=comment_query.poster,
concerning_movee_id=user_query.id,
description=json['commentText'],
move_id=post_query.id,
update_time=datetime.now())
db.session.add(update)
db.session.commit()
if post_query.movee_id != user_query.id and not 'parentCommentId' in json:
update = Update(update_type='comment',
updated_movee_id=post_query.movee_id,
concerning_movee_id=user_query.id,
description=json['commentText'],
move_id=post_query.id,
update_time=datetime.now())
db.session.add(update)
db.session.commit()
if user_query.user_type == 'Removalist':
post_record = db.session.query(PostRecord).filter(
and_(PostRecord.move_id == post_query.id,
PostRecord.user_id == user_query.id)).first()
if not post_record:
post_record = PostRecord(move_id=post_query.id,
user_id=user_query.id,
last_updated=datetime.now())
user_query.post_records.append(post_record)
else:
post_record.last_updated = datetime.now()
db.session.commit()
resp = jsonify({
'comment': comment.to_dict(),
'commenter': user_query.to_dict()
})
resp.status_code = 200
return resp
def inner_wrapper(self,
column,
mostly=None,
result_format=None,
*args,
**kwargs):
if result_format is None:
result_format = self.default_expectation_args["result_format"]
result_format = parse_result_format(result_format)
if result_format['result_format'] == 'COMPLETE':
warnings.warn(
"Setting result format to COMPLETE for a SqlAlchemyDataset can be dangerous because it will not limit the number of returned results."
)
unexpected_count_limit = None
else:
unexpected_count_limit = result_format[
'partial_unexpected_count']
expected_condition = func(self, column, *args, **kwargs)
# FIXME Temporary Fix for counting missing values
# Added to compensate when an ignore_values argument is added to the expectation
ignore_values = [None]
if func.__name__ in [
'expect_column_values_to_not_be_null',
'expect_column_values_to_be_null'
]:
ignore_values = []
# Counting the number of unexpected values can be expensive when there is a large
# number of np.nan values.
# This only happens on expect_column_values_to_not_be_null expectations.
# Since there is no reason to look for most common unexpected values in this case,
# we will instruct the result formatting method to skip this step.
result_format['partial_unexpected_count'] = 0
count_query = sa.select([
sa.func.count().label('element_count'),
sa.func.sum(
sa.case(
[(
sa.or_(
sa.column(column).in_(ignore_values),
# Below is necessary b/c sa.in_() uses `==` but None != None
# But we only consider this if None is actually in the list of ignore values
sa.column(column).is_(None)
if None in ignore_values else False),
1)],
else_=0)).label('null_count'),
sa.func.sum(
sa.case([(sa.and_(
sa.not_(expected_condition),
sa.case([(sa.column(column).is_(None), False)],
else_=True)
if None in ignore_values else True), 1)],
else_=0)).label('unexpected_count')
]).select_from(self._table)
count_results = dict(self.engine.execute(count_query).fetchone())
# Handle case of empty table gracefully:
if "element_count" not in count_results or count_results[
"element_count"] is None:
count_results["element_count"] = 0
if "null_count" not in count_results or count_results[
"null_count"] is None:
count_results["null_count"] = 0
if "unexpected_count" not in count_results or count_results[
"unexpected_count"] is None:
count_results["unexpected_count"] = 0
# Retrieve unexpected values
unexpected_query_results = self.engine.execute(
sa.select([sa.column(column)]).select_from(self._table).where(
sa.and_(
sa.not_(expected_condition),
sa.or_(
# SA normally evaluates `== None` as `IS NONE`. However `sa.in_()`
# replaces `None` as `NULL` in the list and incorrectly uses `== NULL`
sa.case([(sa.column(column).is_(None), False)],
else_=True)
if None in ignore_values else False,
# Ignore any other values that are in the ignore list
sa.column(column).in_(ignore_values) ==
False))).limit(unexpected_count_limit))
nonnull_count = count_results['element_count'] - \
count_results['null_count']
if "output_strftime_format" in kwargs:
output_strftime_format = kwargs["output_strftime_format"]
maybe_limited_unexpected_list = []
for x in unexpected_query_results.fetchall():
if isinstance(x[column], string_types):
col = parse(x[column])
else:
col = x[column]
maybe_limited_unexpected_list.append(
datetime.strftime(col, output_strftime_format))
else:
maybe_limited_unexpected_list = [
x[column] for x in unexpected_query_results.fetchall()
]
success_count = nonnull_count - count_results['unexpected_count']
success, percent_success = self._calc_map_expectation_success(
success_count, nonnull_count, mostly)
return_obj = self._format_map_output(
result_format,
success,
count_results['element_count'],
nonnull_count,
count_results['unexpected_count'],
maybe_limited_unexpected_list,
None,
)
if func.__name__ in [
'expect_column_values_to_not_be_null',
'expect_column_values_to_be_null'
]:
# These results are unnecessary for the above expectations
del return_obj['result']['unexpected_percent_nonmissing']
try:
del return_obj['result']['partial_unexpected_counts']
del return_obj['result']['partial_unexpected_list']
except KeyError:
pass
return return_obj
def load(self, agent, check_odbc_state=True):
# pylint: disable=too-many-locals
# pylint: disable=too-many-statements
envid = self.server.environment.envid
if check_odbc_state and not self.server.odbc_ok():
return {"error": "Cannot run command while in state: %s" % \
self.server.state_manager.get_state()}
stmt = \
'SELECT system_users.name, system_users.email, ' +\
' system_users.hashed_password, system_users.salt, ' +\
' system_users.friendly_name, system_users.admin_level, ' +\
' system_users.created_at, system_users.id ' +\
'FROM system_users'
excludes = ['guest', '_system']
data = agent.odbc.execute(stmt)
# Send tableau readonly password-related events if appropriate.
self._eventit(agent, data)
if failed(data):
return data
session = meta.Session()
names = ['palette']
cache = self.load_users(agent)
system_key = SystemKeys.ALERTS_NEW_USER_ENABLED
alerts_new_user_enabled = self.system[system_key]
if alerts_new_user_enabled == 'yes':
default_email_level = 1
else:
default_email_level = 0
user_count = UserProfile.user_count(envid)
if user_count <= 1:
first_load = True
else:
first_load = False
for row in data['']:
name = row[0]
if name.lower() in excludes:
continue
sysid = row[7]
names.append(name)
entry = UserProfile.get_by_name(envid, name)
if not entry:
entry = UserProfile(envid=envid, name=name)
entry.email_level = default_email_level
session.add(entry)
entry.email = row[1]
entry.hashed_password = row[2]
entry.salt = row[3]
entry.friendly_name = row[4]
entry.system_admin_level = row[5]
entry.system_created_at = row[6]
entry.system_user_id = sysid
if sysid in cache:
obj = cache[sysid]
entry.login_at = obj.login_at
entry.user_admin_level = obj.admin_level
entry.licensing_role_id = obj.licensing_role_id
entry.publisher = obj.publisher
# On first user table import, Tableau Server Administrators
# are set to Palette Super Admins.
if first_load and entry.system_admin_level == 10:
entry.roleid = Role.SUPER_ADMIN
session.commit()
# deleted entries no longer found in Tableau are marked inactive.
session.query(UserProfile).\
filter(not_(UserProfile.name.in_(names))).\
update({'active': False}, synchronize_session='fetch')
timestamp = datetime.now().strftime(DATEFMT)
self.system.save(SystemKeys.AUTH_TIMESTAMP, timestamp)
d = {u'status': 'OK', u'count': len(data[''])}
logger.debug("auth load returning: %s", str(d))
return d
def prepare_filter(self, req, path, relation, values):
table = req.get_table(path)
# assoc = req.get_table(path, self.assoc_table, include_tail=True)
# req.join(assoc, table.c.id == assoc.c.parent_id)
# TODO: Make these all assert that the entities on the other side are _active.
# Right now it is likely that this will be okay, since the events/scanner
# should keep the association up to date.
if relation in ('is', 'is_not', 'in', 'not_in', 'type_is',
'type_is_not'):
# We could easily do this, but lets match Shotgun!
if 'is' in relation and len(values) > 1:
raise ClientFault('more than one value for %s' % relation)
check_empty = False
by_type = dict()
if 'type' in relation:
if values[0] is None:
check_empty = True
else:
by_type = {values[0]: None}
else:
for e in values:
if e is None:
check_empty = True
continue
try:
by_type.setdefault(e['type'], []).append(e['id'])
except (TypeError, KeyError, IndexError):
raise ClientFault(
'multi_entity %s value must be an entity or null' %
relation)
clauses = []
for type_, ids in by_type.iteritems():
assoc = self.assoc_table.alias() # Must be clean.
type_path = FieldPath(list(path) + [(type_, 'id')])
type_table = req.get_table(type_path)
query = sa.select([sa.literal(1)]).select_from(
assoc.join(
type_table,
sa.and_(
assoc.c.child_id == type_table.c.id,
# Ugly shortcut here.
True
if type_ is None else assoc.c.child_type == type_,
))).where(
sa.and_(
table.c.id == assoc.c.parent_id,
# Ugly shortcut here.
True
if ids is None else type_table.c.id.in_(ids),
))
clauses.append(sa.exists(query))
if check_empty:
assoc = self.assoc_table.alias() # Must be clean.
query = sa.select([
sa.literal(1)
]).select_from(assoc).where(table.c.id == assoc.c.parent_id)
clauses.append(sa.not_(sa.exists(query)))
clause = clauses[0] if len(clauses) == 1 else sa.or_(*clauses)
clause = sa.not_(clause) if 'not' in relation else clause
return clause
raise FilterNotImplemented('%s on %s' % (relation, self.type_name))
def make_query(search, option, order, genre=None, server_id_mod=None):
query = db.session.query(ModelBotDownloaderKtvItem)
if search is not None and search != '':
if search.find('|') != -1:
tmp = search.split('|')
conditions = []
for tt in tmp:
if tt != '':
conditions.append(
ModelBotDownloaderKtvItem.filename.like(
'%' + tt.strip() + '%'))
query = query.filter(or_(*conditions))
elif search.find(',') != -1:
tmp = search.split(',')
for tt in tmp:
if tt != '':
query = query.filter(
ModelBotDownloaderKtvItem.filename.like(
'%' + tt.strip() + '%'))
else:
query = query.filter(
or_(
ModelBotDownloaderKtvItem.filename.like('%' + search +
'%'),
ModelBotDownloaderKtvItem.daum_title == search))
if genre is not None and genre != '':
if genre.find('|') != -1:
tmp = genre.split('|')
conditions = []
for tt in tmp:
if tt != '':
conditions.append(
ModelBotDownloaderKtvItem.daum_genre.like(
'%' + tt.strip() + '%'))
query = query.filter(or_(*conditions))
elif genre.find(',') != -1:
tmp = genre.split(',')
for tt in tmp:
if tt != '':
query = query.filter(
ModelBotDownloaderKtvItem.daum_genre.like(
'%' + tt.strip() + '%'))
else:
query = query.filter(
or_(
ModelBotDownloaderKtvItem.daum_genre.like('%' + genre +
'%'),
ModelBotDownloaderKtvItem.daum_genre == genre))
if option == 'request_True':
query = query.filter(
ModelBotDownloaderKtvItem.download_status.like('True%'))
elif option == 'request_False':
query = query.filter(
ModelBotDownloaderKtvItem.download_status.like('False%'))
elif option == 'by_plex_on':
query = query.filter(ModelBotDownloaderKtvItem.plex_key != None)
elif option == 'by_plex_off':
query = query.filter(ModelBotDownloaderKtvItem.plex_key == None)
elif option == 'by_plex_episode_off':
query = query.filter(ModelBotDownloaderKtvItem.plex_key != None)
query = query.filter(
not_(ModelBotDownloaderKtvItem.plex_key.like('E%')))
#실패. 아래 동작 안함.
#elif option == 'torrent_incomplted':
# query = query.filter(ModelBotDownloaderKtvItem.downloader_item_id != None)
#elif option == 'torrent_completed':
# from downloader.model import ModelDownloaderItem
# query = query.filter(ModelBotDownloaderKtvItem.downloader_item_id != None).filter(ModelBotDownloaderKtvItem.downloader_item_id == ModelDownloaderItem.id).filter(ModelDownloaderItem.completed_time != None)
elif option == 'share_received':
query = query.filter(ModelBotDownloaderKtvItem.folderid != None)
elif option == 'share_no_received':
query = query.filter(ModelBotDownloaderKtvItem.folderid == None)
elif option == 'share_request_incompleted':
query = query.filter(
ModelBotDownloaderKtvItem.share_copy_time != None).filter(
ModelBotDownloaderKtvItem.share_copy_completed_time ==
None)
elif option == 'share_request_completed':
query = query.filter(
ModelBotDownloaderKtvItem.share_copy_time != None).filter(
ModelBotDownloaderKtvItem.share_copy_completed_time != None
)
if order == 'desc':
query = query.order_by(desc(ModelBotDownloaderKtvItem.id))
else:
query = query.order_by(ModelBotDownloaderKtvItem.id)
if server_id_mod is not None and server_id_mod != '':
tmp = server_id_mod.split('_')
if len(tmp) == 2:
query = query.filter(ModelBotDownloaderKtvItem.server_id %
int(tmp[0]) == int(tmp[1]))
return query
def make_query(data_table, url_args, lang, **kwargs):
from dataviva import db
from dataviva.attrs.models import Bra, Cnae, Cbo, Hs, Wld, University, Course_hedu, Course_sc
ops = {">": operator.gt,
">=": operator.ge,
"<": operator.lt,
"<=": operator.le}
check_keys = ["bra_id", "cnae_id", "cbo_id", "hs_id", "wld_id", "university_id", "course_hedu_id", "course_sc_id"]
unique_keys = []
download = url_args.get("download", None)
raw = True if "raw" in kwargs else None
order = url_args.get("order", None)
offset = url_args.get("offset", None)
limit = url_args.get("limit", None)
cols = url_args.get("cols", None)
if type(cols) == str or type(cols) == unicode:
cols = cols.split(".")
excluding = url_args.get("excluding", None)
if offset:
offset = float(offset)
limit = limit or 50
filter = url_args.get("filter", None)
if filter:
filter = re.split("(>=|>|<=|<)", filter)
join = kwargs["join"] if "join" in kwargs else False
show_id = None
cache_id = request.path
ret = {}
# first lets test if this query is cached (be sure we are not paginating
# results) as these should not get cached
if limit is None and download is None and raw is None and cols is None:
cached_q = cached_query(cache_id)
if cached_q:
return cached_q
query = db.session.query(data_table)
if join:
for j in join:
query = query.add_entity(j["table"])
for col in j["on"]:
query = query.filter(getattr(data_table, col) == getattr(j["table"], col))
query = query.group_by(data_table.year)
# handle year (if specified)
if "year" in kwargs:
ret["year"] = parse_years(kwargs["year"])
query = query \
.filter(data_table.year.in_(ret["year"]))
# parse all filters
for key in check_keys:
if key in kwargs:
if key != "bra_id":
unique_keys.append(key)
if "show" in kwargs[key]:
show_id = key
parse_results = parse_filter(kwargs,key,query,data_table,ret)
query = parse_results["query"]
ret = parse_results["ret"]
if filter:
query = query.filter(ops[filter[1]](getattr(data_table, filter[0]), float(filter[2])))
if excluding:
for e in excluding:
query = query.filter(not_(getattr(data_table, e).startswith(excluding[e])))
# lastly we want to get the actual data held in the table requested
if "aggregate" not in ret:
# handle ordering
if order:
direction = "asc"
if "." in order:
o, direction = order.split(".")
else:
o = order
order_table = None
if join:
for j in join:
if o in j["columns"]:
order_table = j["table"]
if order_table == None:
order_table = data_table
all_col_names = data_table.__table__.columns.keys() + order_table.__table__.columns.keys()
if o in all_col_names:
if direction == "asc":
query = query.order_by(asc(getattr(order_table,o)))
elif direction == "desc":
query = query.order_by(desc(getattr(order_table,o)))
if limit:
query = query.limit(limit).offset(offset)
# raise Exception(compile_query(query))
if join:
ret["data"] = []
items = query.all()
for row in items:
datum = row[0].serialize()
join_data = []
for i, r in enumerate(row):
if i != 0:
serialized = r.serialize()
for k in serialized:
if k in join[i-1]["columns"]:
datum[k] = serialized[k]
ret["data"].append(datum)
elif raw:
return query.all()
else:
ret["data"] = [d.serialize() for d in query.all()]
if "aggregate" in ret:
agg_data = []
ret["data"] = sorted(ret["data"],key=lambda x: x["year"])
if "bra" not in ret:
ret["bra"] = {}
for bra in ret["bra"]:
if "aggregates" in bra:
filtered_objs = []
for key, group in groupby(ret["data"],lambda x: x["year"]):
year_data = []
for obj in group:
if obj["bra_id"] in bra["aggregates"]:
year_data.append(obj)
if len(unique_keys) > 0:
def check_filter(d,keys,i):
if i == len(keys):
merged_data = merge_objects(d)
merged_data["year"] = key
merged_data["bra_id"] = bra["id"]
agg_data.append(merged_data)
else:
d = sorted(d,key=lambda x: x[keys[i]])
for x, g in groupby(d,lambda x: x[keys[i]]):
new_array = []
for o in g:
new_array.append(o)
check_filter(new_array,keys,i+1)
check_filter(year_data,unique_keys,0)
else:
merged_data = merge_objects(year_data)
merged_data["year"] = key
merged_data["bra_id"] = bra["id"]
agg_data.append(merged_data)
else:
bra_data = [obj for obj in ret["data"] if obj["bra_id"] == bra["id"]]
agg_data = agg_data + bra_data
ret["data"] = agg_data
# handle ordering
if order:
direction = "asc"
if "." in order:
o, direction = order.split(".")
else:
o = order
if direction == "asc":
ret["data"].sort(key=lambda x: x[o] if o in x else None)
elif direction == "desc":
ret["data"].sort(key=lambda x: x[o] if o in x else None, reverse=True)
if limit:
ret["data"] = ret["data"][int(offset):int(offset)+int(limit)]
if cols:
cols = ["year","bra_id"]+unique_keys+cols
new_return = []
attrs = None
if ("name" or "id_ibge" or "id_mdic" in cols) and show_id:
attr_table = locals()[show_id[:-3].capitalize()]
attrs = [x.serialize() for x in attr_table.query.all()]
attrs = {x["id"]:x or None for x in attrs}
for d in ret["data"]:
new_obj = {}
for k in d:
if k in cols:
new_obj[k] = d[k]
if attrs:
if "name" in cols and "name_{0}".format(lang) in attrs[d[show_id]]:
new_obj["name"] = attrs[d[show_id]]["name_{0}".format(lang)]
if "id_ibge" in cols and "id_ibge" in attrs[d[show_id]]:
new_obj["id_ibge"] = attrs[d[show_id]]["id_ibge"]
if "id_mdic" in cols and "id_mdic" in attrs[d[show_id]]:
new_obj["id_mdic"] = attrs[d[show_id]]["id_mdic"]
new_return.append(new_obj)
ret["data"] = new_return
if order:
for i, d in enumerate(ret["data"]):
r = i+1
if offset:
r = r+offset
d["rank"] = int(r)
if download is not None:
header = [str(c).split(".")[1] for c in data_table.__table__.columns]
if cols:
stickies = [c for c in header if c in unique_keys]
header = stickies+cols
def generate():
for i, data_dict in enumerate(ret["data"]):
row = [str(data_dict[c]) if c in data_dict else '' for c in header]
if i == 0:
yield ';'.join(header) + '\n' + ';'.join(row) + '\n'
yield ';'.join(row) + '\n'
content_disposition = "attachment;filename=%s.csv" % (cache_id[1:-1].replace('/', "_"))
if sys.getsizeof(ret["data"]) > 10485760:
resp = Response(['Unable to download, request is larger than 10mb'],
mimetype="text/csv;charset=UTF-8",
headers={"Content-Disposition": content_disposition})
else:
resp = Response(generate(), mimetype="text/csv;charset=UTF-8",
headers={"Content-Disposition": content_disposition})
return resp
# gzip and jsonify result
ret = gzip_data(jsonify(ret).data)
if limit is None and download is None and raw is None and cols is None:
cached_query(cache_id, ret)
return ret
def _save_tags(self, tags, post_id, conn):
tags = self.normalize_tags(tags)
tag_ids = []
for tag in tags: # iterate over given tags
try:
# check if the tag exists
statement = self._tag_table.select().where(
self._tag_table.c.text == tag)
tag_result = conn.execute(statement).fetchone()
if tag_result is None:
# insert if it is a new tag
tag_insert_statement = self._tag_table.insert().\
values(text=tag)
result = conn.execute(tag_insert_statement)
tag_id = result.inserted_primary_key[0]
else:
# tag already exists
tag_id = tag_result[0]
except sqla.exc.IntegrityError as e:
# some database error occurred;
tag_id = None
self._logger.exception(str(e))
except Exception as e:
# unknown exception occurred
tag_id = None
self._logger.exception(str(e))
if tag_id is not None:
# for a valid tag_id
tag_ids.append(tag_id)
try:
# check if given post has tag given by tag_id
statement = self._tag_posts_table.select().where(
sqla.and_(self._tag_posts_table.c.tag_id == tag_id,
self._tag_posts_table.c.post_id == post_id))
tag_post_result = conn.execute(statement).fetchone()
if tag_post_result is None:
# if tag_id not present for the post given by post_id
tag_post_statement = self._tag_posts_table.insert().\
values(tag_id=tag_id, post_id=post_id)
conn.execute(tag_post_statement)
except sqla.exc.IntegrityError as e:
self._logger.exception(str(e))
except Exception as e:
self._logger.exception(str(e))
try:
# remove tags that have been deleted
statement = self._tag_posts_table.delete().where(
sqla.and_(
sqla.not_(self._tag_posts_table.c.tag_id.in_(tag_ids)),
self._tag_posts_table.c.post_id == post_id))
conn.execute(statement)
except Exception as e:
self._logger.exception(str(e))
def parse_sqla_operator(raw_key, *operands):
key = raw_key.lower().strip()
if not operands:
raise APIError("Missing arguments for '%s'." % (key))
if key in ["and"]:
query = and_(*operands)
return query
elif key in ["or"]:
query = or_(*operands)
return query
elif key in ["not"]:
x = operands[0]
return not_(parse_condition(x))
else:
if len(operands) != 2:
raise APIError(
"Wrong number of arguments for '%s'. Expected: 2 Got: %s"
% (key, len(operands))
)
x, y = operands
if x is None:
x = null()
if key in ["equals", "="]:
return x == y
if key in ["greater", ">"]:
return x > y
if key in ["lower", "<"]:
return x < y
if key in ["notequal", "<>", "!="]:
return x != y
if key in ["notgreater", "<="]:
return x <= y
if key in ["notlower", ">="]:
return x >= y
if key in ["add", "+"]:
return x + y
if key in ["substract", "-"]:
return x - y
if key in ["multiply", "*"]:
return x * y
if key in ["divide", "/"]:
return x / y
if key in ["concatenate", "||"]:
return fun.concat(x, y)
if key in ["is"]:
return x is y
if key in ["is not"]:
return x.isnot(y)
if key in ["like"]:
return x.like(y)
if key in ["<->"]:
return x.distance_centroid(y)
if key in ["getitem"]:
if isinstance(y, Slice):
return x[parse_single(y.start, int) : parse_single(y.stop, int)]
else:
return x[read_pgid(y)]
if key in ["in"]:
return x.in_(y)
raise APIError("Operator '%s' not supported" % key)
def _generate_simple_operator_clause(
ctx, clause: 'FilterClause',
taxon_model_info_map: Dict[str, TaxonModelInfo]) -> ClauseElement:
"""
:param ctx: Husky query context
:param clause: Filter clause
:param taxon_model_info_map: map of taxons and model infos
:return: SQL clause element
"""
taxon_model_info: TaxonModelInfo = taxon_model_info_map[clause.taxon]
if isinstance(clause, TaxonTaxonFilterClause):
# Always referring to the full column name, never relying on alchemy.column reference to not be ambiguous.
right_taxon_info = taxon_model_info_map[clause.right_taxon]
right_operand = literal_column(right_taxon_info.taxon_sql_accessor)
else:
right_operand = literal(
clause.value) if clause.value is not None else None
# Always referring to the full column name, never relying on alchemy.column reference to not be ambiguous.
left_operand = literal_column(taxon_model_info.taxon_sql_accessor)
if clause.operator == SimpleFilterOperator.EQ and taxon_model_info.is_array:
# Otherwise in SF, if taxon is an array, instead of using equal sign(=),
# we check if the value is obtained in the taxon value (list)
# First argument to ARRAY_CONTAINS needs to be of type VARIANT
return func.array_contains(
right_operand.op('::')(text('VARIANT')), left_operand)
if clause.operator in SIMPLE_OPERATORS_FUNCTIONS:
if right_operand is None: # support "IS NULL" and " IS NOT NULL"
if clause.operator is SimpleFilterOperator.EQ:
return left_operand.is_(None)
elif clause.operator is SimpleFilterOperator.NEQ:
return not_(left_operand.is_(None))
else:
raise UnknownOperator(clause)
return SIMPLE_OPERATORS_FUNCTIONS[clause.operator](left_operand,
right_operand)
else:
# when using LIKE/NOT LIKE operators, we need right operand
if right_operand is None:
raise UnknownOperator(clause)
# When using *like, always cast operand to string.
left_operand = sql_cast(left_operand, String)
# LIKE operator is handled differently
# We should not call escape_special_character_in_like_pattern, coz Fe already does that.
if clause.operator is SimpleFilterOperator.LIKE:
return left_operand.like(right_operand,
escape=LIKE_PATTERN_ESCAPE_CHAR)
elif clause.operator is SimpleFilterOperator.NOT_LIKE:
return not_(
left_operand.like(right_operand,
escape=LIKE_PATTERN_ESCAPE_CHAR))
elif clause.operator is SimpleFilterOperator.ILIKE:
return left_operand.ilike(right_operand,
escape=LIKE_PATTERN_ESCAPE_CHAR)
elif clause.operator is SimpleFilterOperator.NOT_ILIKE:
return not_(
left_operand.ilike(right_operand,
escape=LIKE_PATTERN_ESCAPE_CHAR))
raise UnknownOperator(clause)
def token_count(self, realm_list, status=None):
"""
Give the number of tokens (with status) per realm
if multiple tokens are given, give summary for all tokens
tokens which are in multiple realms are only counted once!
:param realm_list: list of realms which must be queried
:param status: string which contains requested token status
:return: dict with the keys: active, inactive,
assigned, unassigned, total
"""
if not isinstance(realm_list, (list, tuple)):
realms = [realm_list]
else:
# copy realms so that we can delete items safely
realms = realm_list[:]
if len(realms) < 1:
realms = ['/:no realm:/']
result = {}
cond = tuple()
for realm in realms:
realm = realm.strip()
if '/:no realm:/' in realm or realm == '':
# get all tokenrealm ids
token_id_tuples = db.session.query(TokenRealm.token_id).all()
token_ids = set()
for token_tuple in token_id_tuples:
token_ids.add(token_tuple[0])
# all tokens, which are not references in TokenRealm
cond += (and_(not_(Token.LinOtpTokenId.in_(token_ids))),)
if '/:no realm:/' in realm:
realms.remove('/:no realm:/')
else:
cond += (and_(TokenRealm.realm_id == Realm.id,
Realm.name == realm,
TokenRealm.token_id == Token.LinOtpTokenId),)
# realm condition:
r_condition = or_(*cond)
if 'total' in status:
# count all tokens in an realm
token_query = db.session.query(TokenRealm, Realm, Token)
token_query = token_query.filter(r_condition)
token_query = token_query.distinct(Token.LinOtpTokenId)
result['total'] = token_query.count()
if 'total users' in status:
# according to the token users license spec, we count only
# the distinct users of all assigned and active tokens of an realm
user_query = db.session.query(TokenRealm, Realm, Token)
user_query = user_query.filter(r_condition)
user_query = user_query.filter(Token.LinOtpUserid != '')
user_query = user_query.filter(Token.LinOtpIsactive == True)
user_query = user_query.distinct(
Token.LinOtpUserid, Token.LinOtpIdResClass)
result['total users'] = user_query.count()
for stat in status:
if stat in ['total users', 'total']:
continue
conditions = (and_(r_condition),)
# handle combinations like:
# status=unassigned & active, unassigned & inactive
if '&' in stat:
stati = stat.split('&')
if 'assigned' in stati:
conditions += (and_(Token.LinOtpUserid != ''),)
else:
conditions += (and_(Token.LinOtpUserid == ''),)
if 'active' in stati:
conditions += (and_(Token.LinOtpIsactive == True),)
else:
conditions += (and_(Token.LinOtpIsactive == False),)
else:
# handle single expressions like
# status=unassigned,active
if 'assigned' == stat:
conditions += (and_(Token.LinOtpUserid != ''),)
elif 'unassigned' == stat:
conditions += (and_(Token.LinOtpUserid == ''),)
elif 'active' == stat:
conditions += (and_(Token.LinOtpIsactive == True),)
elif 'inactive' == stat:
conditions += (and_(Token.LinOtpIsactive == False),)
# create the final condition as AND of all conditions
condition = and_(*conditions)
result[stat] = db.session.query(TokenRealm, Realm, Token).\
filter(condition).count()
return result
def _create(wlk, root, session):
query = session.query(DatabaseEntry)
for key, value in root.attrs.iteritems():
typ = key[0]
if typ == 'tag':
criterion = TableTag.name.in_([value])
# `key[1]` is here the `inverted` attribute of the tag. That means
# that if it is True, the given tag must not be included in the
# resulting entries.
if key[1]:
query = query.filter(~DatabaseEntry.tags.any(criterion))
else:
query = query.filter(DatabaseEntry.tags.any(criterion))
elif typ == 'fitsheaderentry':
key, val, inverted = value
key_criterion = TableFitsHeaderEntry.key == key
value_criterion = TableFitsHeaderEntry.value == val
if inverted:
query = query.filter(
not_(
and_(
DatabaseEntry.fits_header_entries.any(
key_criterion),
DatabaseEntry.fits_header_entries.any(
value_criterion))))
else:
query = query.filter(
and_(
DatabaseEntry.fits_header_entries.any(key_criterion),
DatabaseEntry.fits_header_entries.any(
value_criterion)))
elif typ == 'download time':
start, end, inverted = value
if inverted:
query = query.filter(
~DatabaseEntry.download_time.between(start, end))
else:
query = query.filter(
DatabaseEntry.download_time.between(start, end))
elif typ == 'path':
path, inverted = value
if inverted:
query = query.filter(
or_(DatabaseEntry.path != path,
DatabaseEntry.path == None))
else:
query = query.filter(DatabaseEntry.path == path)
elif typ == 'wave':
wavemin, wavemax, waveunit = value
query = query.filter(
and_(DatabaseEntry.wavemin >= wavemin,
DatabaseEntry.wavemax <= wavemax))
elif typ == 'time':
start, end, near = value
query = query.filter(
and_(DatabaseEntry.observation_time_start < end,
DatabaseEntry.observation_time_end > start))
else:
if typ.lower() not in SUPPORTED_SIMPLE_VSO_ATTRS.union(
SUPPORTED_NONVSO_ATTRS):
raise NotImplementedError(
"The attribute {0!r} is not yet supported to query a database."
.format(typ))
query = query.filter_by(**{typ: value})
return query.all()
def build_facts_query(self,
db: Session,
*,
user: models.User,
filters: schemas.FactSearch = schemas.FactSearch()):
visible_decks = (db.query(models.Deck.id).join(
models.User_Deck).filter(
models.User_Deck.owner_id == user.id).subquery())
user_facts = (db.query(models.Fact).join(
visible_decks, models.Fact.deck_id == visible_decks.c.id).filter(
models.Fact.user_id == user.id))
deck_owners = (db.query(
models.User_Deck.deck_id,
models.User_Deck.owner_id).outerjoin(visible_decks).filter(
models.User_Deck.permissions ==
schemas.Permission.owner).subquery())
filtered_facts = (db.query(models.Fact).join(
visible_decks, models.Fact.deck_id == visible_decks.c.id).join(
deck_owners,
and_(models.Fact.deck_id == deck_owners.c.deck_id,
models.Fact.user_id == deck_owners.c.owner_id)))
facts_query = (user_facts.union(filtered_facts))
# Don't allow Jeopardy facts
facts_query = facts_query.filter(models.Fact.deck_id != 2)
if filters.studyable:
facts_query = (facts_query.outerjoin(
models.Deleted,
and_(models.Fact.fact_id == models.Deleted.fact_id,
models.Deleted.user_id == user.id)
).filter(models.Deleted.user_id == None).outerjoin(
models.Reported,
and_(models.Fact.fact_id == models.Reported.fact_id,
models.Reported.user_id == user.id)).filter(
models.Reported.user_id == None).outerjoin(
models.Suspended,
and_(
models.Fact.fact_id ==
models.Suspended.fact_id,
models.Suspended.user_id == user.id)).filter(
models.Suspended.user_id == None))
else:
facts_query = (facts_query.outerjoin(
models.Deleted,
and_(models.Fact.fact_id == models.Deleted.fact_id,
models.Deleted.user_id == user.id)).filter(
models.Deleted.user_id == None))
if filters.suspended is not None:
if filters.suspended:
facts_query = facts_query.join(models.Suspended).filter(
models.Suspended.user_id == user.id)
else:
facts_query = (facts_query.outerjoin(
models.Suspended,
and_(models.Fact.fact_id == models.Suspended.fact_id,
models.Suspended.user_id == user.id)).filter(
models.Suspended.user_id == None))
if filters.reported is not None:
if filters.reported:
facts_query = facts_query.join(models.Reported)
if not user.is_superuser:
facts_query = facts_query.filter(
models.Reported.user_id == user.id)
else:
facts_query = (facts_query.outerjoin(
models.Reported,
and_(models.Fact.fact_id == models.Reported.fact_id,
models.Reported.user_id == user.id)).filter(
models.Reported.user_id == None))
if filters.all:
facts_query = facts_query.filter(
models.Fact.__ts_vector__.op('@@')(func.plainto_tsquery(
'english', filters.all)))
if filters.text:
facts_query = facts_query.filter(
models.Fact.text.ilike(filters.text))
if filters.answer:
facts_query = facts_query.filter(
models.Fact.answer.ilike(filters.answer))
if filters.category:
facts_query = facts_query.filter(
models.Fact.category.ilike(filters.category))
if filters.identifier:
facts_query = facts_query.filter(
models.Fact.identifier.ilike(filters.identifier))
if filters.deck_ids:
facts_query = facts_query.filter(
models.Fact.deck_id.in_(filters.deck_ids))
if filters.deck_id:
facts_query = facts_query.filter(
models.Fact.deck_id == filters.deck_id)
if filters.marked is not None:
if filters.marked:
facts_query = facts_query.filter(
models.Fact.markers.any(id=user.id))
else:
facts_query = facts_query.filter(
not_(models.Fact.markers.any(id=user.id)))
if filters.randomize:
facts_query = facts_query.order_by(func.random())
return facts_query
def sel_stu():
# 查询所有操作
students = Student.query.all()
# 第一种方式：查询id=1的学生信息
students = Student.query.filter(Student.id == 1)
# 第二种方式：查询id=1的学生信息
students = Student.query.filter_by(id=1)
# 第三种方式：查询id=1的学生信息
sql = 'select * from student where id=1;'
students = db.session.execute(sql)
# 查询所有学生数据
sql = 'select * from student;'
students = db.session.execute(sql)
# 模糊查询, 查询姓名中包含妲己的学生信息
# 在django中 filter(s_name__contains='妲己')
# 语法：filter(模型名.属性.运算符('xx'))
students = Student.query.filter(Student.s_name.contains('妲己'))
# 以什么开始，startswith
students = Student.query.filter(Student.s_name.startswith('校'))
# 以什么结束，endswith
students = Student.query.filter(Student.s_name.endswith('9'))
# 查询年龄大于等于16的学生信息
students = Student.query.filter(Student.s_age.__ge__(16))
# 查询id在10到12之间的学生信息
students = Student.query.filter(Student.id.in_([10, 11, 12]))
# 查询年龄小于15的学生信息
students = Student.query.filter(Student.s_age.__lt__(15))
# 模糊查询，使用like，查询姓名中第二位为花的学生信息
# like '_花%'
students = Student.query.filter(Student.s_name.like('_花%'))
# 按照id降序,升序
students = Student.query.order_by('id')
students = Student.query.order_by('-id')
students = Student.query.order_by(desc('id'))
students = Student.query.order_by(asc('id'))
students = Student.query.order_by('id desc')
students = Student.query.order_by('id asc')
# 查询数据，获取第五个到第十个的数据
students = Student.query.all()[4:10]
page = 3
students = Student.query.offset((page - 1) * 2).limit(2)
# 使用get，获取id=1的学生信息
# get拿不到值，不会报错，只会返回一个空
students = Student.query.get(1)
# and_, not_, or_
students = Student.query.filter(Student.s_age == 16,
Student.s_name.contains('花'))
students = Student.query.filter(
and_(Student.s_age == 16, Student.s_name.contains('花')))
students = Student.query.filter(not_(Student.s_age == 16),
not_(Student.s_name.contains('花')))
students = Student.query.filter(
or_(Student.s_age == 16, Student.s_name.contains('花')))
return render_template('stus.html', stus=students)
def packages_json_generate():
yield '{{"name":"{}","packages":['.format(REPO_NAME)
cached_packages = db_session.query(Package) \
.filter(Package.last_updated.isnot(None),
Package.last_update_successful,
Package.last_updated >= datetime.utcnow() - timedelta(hours=24)) \
.options(load_only(Package.owner,
Package.name,
Package.description,
Package.filename,
Package.date,
Package.version,
Package.download_url,
Package.homepage))
iter_cached_packages = iter(cached_packages)
package = next(iter_cached_packages, None)
if package:
yield json_dump_package(package)
for package in iter_cached_packages:
yield "," + json_dump_package(package)
update_packages = db_session.query(Package) \
.filter(or_(Package.last_updated.is_(None),
and_(Package.last_update_successful,
Package.last_updated < datetime.utcnow() - timedelta(hours=24)),
and_(not_(Package.last_update_successful),
Package.last_updated < datetime.utcnow() - timedelta(hours=4)))) \
.options(load_only(Package.owner,
Package.repo,
Package.path,
Package.ptype,
Package.date))
loop = asyncio.new_event_loop()
asyncio.set_event_loop(loop)
update_tasks = [
asyncio.ensure_future(update_package(package))
for package in update_packages
]
iter_update_tasks = asyncio.as_completed(update_tasks)
if not package:
update_task = next(iter_update_tasks, None)
if update_task:
updated_package = None
try:
updated_package = loop.run_until_complete(update_task)
except Exception as ex:
LOGGER.error(ex)
LOGGER.debug(traceback.format_exc())
if updated_package:
yield json_dump_package(updated_package)
for update_task in iter_update_tasks:
try:
updated_package = loop.run_until_complete(update_task)
except Exception as ex:
LOGGER.error(ex)
LOGGER.debug(traceback.format_exc())
continue
if updated_package:
yield "," + json_dump_package(updated_package)
loop.close()
if update_tasks:
last_updated_prop = Property("last_updated",
date_val=datetime.utcnow())
last_updated_prop = db_session.merge(last_updated_prop)
db_session.commit()
last_updated = last_updated_prop.date_val
else:
last_updated = db_session.query(Property.date_val).filter(
Property.identifier == "last_updated").scalar()
yield '],"last_updated":"{}"}}'.format(
last_updated.isoformat() if last_updated else "")
