def get_by_random(self,
db: Session,
subject: Optional[str] = None) -> Optional[Item]:
if subject:
return (db.query(self.model).filter(
self.model.subject == subject).order_by(random()).first())
return db.query(self.model).order_by(random()).first()
def get_by_random_many(self,
db: Session,
amount: int,
subject: Optional[str] = None) -> list[Item]:
if subject:
return (db.query(
self.model).filter(self.model.subject == subject).order_by(
random()).limit(amount).all())
return db.query(self.model).order_by(random()).limit(amount).all()
def randomRooTrackAJAX(self):
track = Session.query(Track) \
.join(Album, Album.artists) \
.filter(Artist.mbid.in_(Bonnaroo.artist_mbids)) \
.order_by(random()) \
.first()
return simplejson.dumps([track.toPlaylistJSON()])
def next(constraints):
config = Config.default()
session = startup(config)
count, inc_tag, exc_tag, inc_artist, exc_artist, path = parse_constraints(constraints)
tracks = session.query(Track).join(Artist)
if inc_artist:
query = session.query(Artist.id).filter(or_(Artist.name.like(name) for name in inc_artist))
tracks = tracks.filter(Artist.id.in_(query))
if exc_artist:
query = session.query(Artist.id).filter(or_(Artist.name.like(name) for name in exc_artist))
tracks = tracks.filter(~Artist.id.in_(query))
if inc_tag:
query = session.query(Artist.id).join(Artist.tags).filter(or_(Tag.text.like(tag) for tag in inc_tag))
tracks = tracks.filter(Artist.id.in_(query))
if exc_tag:
query = session.query(Artist.id).join(Artist.tags).filter(or_(Tag.text.like(tag) for tag in exc_tag))
tracks = tracks.filter(~Artist.id.in_(query))
if path:
(path, file) = split(path)
track = session.query(Track).join(Album).filter(Album.path == path, Track.file == file).one()
neighbours = all_neighbours(session, track, config.max_links)
tracks = tracks.filter(Track.id.in_(neighbours))
for track in tracks.order_by(random()).limit(count):
print('file://%s' % join(track.album.path, track.file))
def _samp_compute_up(t, s, **kwargs):
if t.n is not None:
limit = t.n
else:
limit = sa.select([safuncs.count() * t.frac],
from_obj=s.alias()).as_scalar()
return s.order_by(safuncs.random()).limit(limit)
def _samp_compute_up(t, s, **kwargs):
if t.n is not None:
limit = t.n
else:
limit = sa.select([safuncs.count() * t.frac],
from_obj=s.alias()).as_scalar()
return s.order_by(safuncs.random()).limit(limit)
def collect_album(session, track, neighbours, max_links):
artist1, track1, track2, artist2 = map(aliased, [Artist, Track, Track, Artist])
return accumulate(neighbours, max_links, 'album',
session.query(artist1).join(track1, Album, track2, artist2)\
.filter(artist2.id == track.artist.id,
artist1.id != track.artist.id)\
.group_by(artist1.id).order_by(random()).all())
def index(self):
"""Handle the front-page."""
meetings = DBSession.query(Meeting).filter(
Meeting.date > datetime.datetime.now() -
datetime.timedelta(hours=3)).order_by(Meeting.date).limit(2)
banner = DBSession.query(Banner).order_by(functions.random()).first()
return dict(page='index', meetings=meetings, banner=banner)
def random_quote(bot, trigger):
''' Called when someone wants a random quote '''
session = bot.memory['session']
res = session.query(BucketFacts).order_by(random()).limit(1).one()
session.close()
if res:
bot.say(res.tidbit)
return
def __init__(self):
# select a few random words from the word cards
words = session.query(WordCard).order_by(random()).limit(6).all()
self.correct = words[0]
self.wrongs = words[1:]
self.words = words
shuffle(self.words)
def some_tips(self, super_secret_info=False, approved_only=True):
with no_tears(QueryTipError):
query = self.tip_query(super_secret_info) \
.order_by(random())
if approved_only:
query = query.filter(Tip.approved == approved_only)
return query.limit(self.CROAK_SIZE).all()
def some_tips(self, super_secret_info=False, approved_only=True):
with no_tears(QueryTipError):
query = self.tip_query(super_secret_info) \
.order_by(random())
if approved_only:
query = query.filter(Tip.approved == approved_only)
return query.limit(self.CROAK_SIZE).all()
def __init__(self):
# select a few random words from the word cards
words = session.query(WordCard).order_by(random()).limit(6).all()
self.correct = words[0]
self.wrongs = words[1:]
self.words = words
shuffle(self.words)
def random_quote(bot, trigger):
choice = trigger.group(1)
choice = choice.strip()
''' Called when someone wants a random quote '''
if choice == 'quote':
session = bot.memory['session']
res = session.query(BucketFacts).order_by(
random()).limit(1).one_or_none()
session.close()
if res:
return bot.say(res.tidbit)
else:
session = bot.memory['session']
res = session.query(BucketFacts).filter(
BucketFacts.fact == '%s quotes' % choice.strip()).order_by(
random()).limit(1).one_or_none()
session.close()
if res:
return bot.say(res.tidbit)
def get_random_fortune(db):
s = select(
[fortunes.c.id, fortunes.c.body]
).where(
char_length(fortunes.c.body) <= 128
).order_by(
random()
).limit(1)
return db.execute(s).fetchone()
def randomRooAlbumAJAX(self):
album = Session.query(Album) \
.join(Album.artists) \
.filter(Artist.mbid.in_(Bonnaroo.artist_mbids)) \
.order_by(random()) \
.first()
tracks = Session.query(Track) \
.filter_by(albumid=album.mbid)
json = map(lambda x: x.toPlaylistJSON(), tracks)
return simplejson.dumps(json)
def get(self, paper_id=''):
if paper_id == 'random':
from sqlalchemy.sql.functions import random
result = Paper.query.order_by(random()).first()
else:
result = Paper.query.filter_by(id=paper_id).first()
if result:
return result
else:
return error('Paper not found'), 400
def populate(self, bot):
''' Clears the inventory and fill it with random items '''
self.current_items.clear()
session = bot.memory['session']
res = session.query(BucketItems.what).order_by(random()).limit(
bot.config.bucket.inv_size).all()
session.close()
for item in res:
self.current_items.append(item[0])
return
def link_all(session, linker):
have_new = session.query(Artist).filter(Artist.new == True).count()
if have_new: warning('new artists; re-linking all')
for artist in session.query(Artist).order_by(random()).all():
if have_new or twice_monthly():
delete_src(session, artist)
linker.link(session, artist)
if have_new:
artist.new = False
session.commit()
debug('done!')
def add(count, names):
config = Config.default()
session = startup(config)
tracks = session.query(Track.id)
for name in names:
debug('filtering by %s' % name)
query = session.query(Track.id).join(Artist).join(Artist.tags)
if name.startswith('-'):
tracks = query.filter(Track.id.in_(tracks), not_(Artist.tags.any(Tag.text.like(name[1:]))))
else:
tracks = query.filter(Track.id.in_(tracks), Artist.tags.any(Tag.text.like(name)))
tracks = session.query(Track).filter(Track.id.in_(tracks)).order_by(random()).limit(count)
add_tracks(session, config, tracks.all())
def similarTrackAJAX(self):
trackid = request.params['id'].split('_')[1]
artists = Session.query(MBArtist).join(MBArtistCreditName, MBArtistCredit, MBRecording, AudioFile, AudioFile.track).filter(Track.id==trackid).all()
similarMbids = set([])
try:
for artist in artists:
similarMbids.update(similarartist.get_similar_artists(Session, self.lastfmNetwork, artist))
except (BadStatusLine, SocketTimeout) as e:
log.error('[lastfm] down? ' + e.__repr__())
return ''
artistMbidsWithAlbums = Session.query(Artist.mbid) \
.join(artist_albums) \
.filter(Artist.mbid.in_(similarMbids)) \
.distinct() \
.subquery()
randomSimilarArtist = Session.query(Artist) \
.filter(Artist.mbid.in_(artistMbidsWithAlbums)) \
.order_by(random()) \
.first()
randomAlbum = rand.choice(randomSimilarArtist.albums)
randomTrack = rand.choice(randomAlbum.tracks)
return simplejson.dumps([randomTrack.toPlaylistJSON()])
def queryDocsByIds(self, grouped_ids, in_constrain=True, limit_to=-1):
counter = 0
reverse_groups = dict()
for type_name, ids in grouped_ids.items():
for one_id in ids:
reverse_groups[one_id] = type_name
default_type = ''
if not in_constrain:
default_type = list(grouped_ids.keys())[0]
grouped_docs = {type_name: [] for type_name, id in grouped_ids.items()}
with self.dao.create_session() as session:
if not in_constrain:
doc_iter = session.query(Document).filter(
Document.DATASET_ID == self.dataset_id).order_by(
functions.random())
for doc in doc_iter:
if (doc.DOC_ID not in grouped_ids):
grouped_docs[default_type].append(
Document(doc.DOC_ID, doc.DATASET_ID, doc.BUNCH_ID,
doc.DOC_NAME, doc.TEXT, doc.DATE,
doc.REF_DATE, doc.META_DATA))
counter += 1
if 0 < limit_to <= counter:
break
else:
for type_name, ids in grouped_ids.items():
for doc_id in ids:
doc = session.query(Document).filter(
Document.DATASET_ID == self.dataset_id).filter(
Document.DOC_ID == doc_id).first()
if doc is None:
logError(
'Oops, it seems a DOC_ID ({}) does not exist in the database, you may need to rebuild the whoosh index'
.format(doc_id))
else:
grouped_docs[type_name].append(doc.clone())
return grouped_docs
def random(bot, pettype):
session = bot.db.session()
res = session.query(PetsDB).filter(PetsDB.type == pettype).order_by(
random()).first()
session.close()
return res
def get_random_insult(victim: str) -> str:
session = db_session()
insult = session.query(Insult).order_by(random()).first().insult
insult = victim + ' ' + insult
return insult.capitalize()
if run_block == 6:
# exists 查询 (不存在则为 ~exists() )
from sqlalchemy.sql import exists
db.query(User.name).filter(~exists().where(User.role_id == Role.id))
# 问题：User表无role_id字段 ************************************ pay attention to it !
# SELECT name AS users_name FROM users WHERE NOT EXISTS (SELECT * FROM roles WHERE users.role_id = roles.id)
# any 也可以表示 EXISTS
db.query(Role).filter(
Role.users.any()
) # 问题：Role表无users字段 ************************************ pay attention to it !
# ------------------------------------------------------------------------------------------------------------------
# 7. random
# ------------------------------------------------------------------------------------------------------------------
if run_block == 7:
from sqlalchemy.sql.functions import random
user_ = db.query(User).order_by(random()).first()
name_ = db.query(User).order_by(
random()).first().name # 随机从User表中获取一个User类的实例对象的名字
db.close()
# 方法参考:
# https://docs.sqlalchemy.org/en/latest/orm/internals.html?highlight=any#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any
def randomAlbumAJAX(self):
album = Session.query(Album).order_by(random())[0]
tracks = Session.query(Track) \
.filter_by(albumid=album.mbid)
json = map(lambda x: x.toPlaylistJSON(), tracks)
return simplejson.dumps(json)
def randomTrackAJAX(self):
track = Session.query(Track).order_by(random())[0]
return simplejson.dumps([track.toPlaylistJSON()])
def random_station():
station = session.query(Station).order_by(random()).limit(1).one()
return redirect(url_for('.station_detail', station_id=station.id))
def transfer_size(session, config, root, limit):
total = 0
albums = iter(session.query(Album).order_by(random()).all())
while total < limit:
total += transfer_album(config, root, next(albums))
debug("transferred %.1f GB" % (total / 1e9))
def random_track(session):
track = session.query(Track).order_by(random()).limit(1).one()
debug('random: %s / %s' % (track.name, track.artist.name))
return track
def random_idea(db: Session = Depends(get_db)):
return db.query(Idea).order_by(random()).limit(1).one_or_none()
def hints():
awake, mins = hintbot.is_awake()
if not awake:
session = Session()
query = session.query(Chattext).filter_by(usage="c_u_ltr")
text = query.order_by(sqlfunc.random()).first()
session.close()
if text is None:
return Response("Sorry, I have no response.",
status=503,
mimetype="text/plain")
else:
return text.ctext.format(minutes=mins)
if request.content_length < 1:
app.logger.info("-- recieved empty request or non-text mime type")
return Response("nope.", status=400, mimetype="text/plain")
# get user uuid from query string
try:
user_uuid = str(uuid.UUID(request.args.get("uuid")))
except ValueError:
return Response("Not a user.", status=400, mimetype="text/plain")
# get user's "chat"
try:
usrmsg = request.data.decode("utf-8")
except Exception:
return Response("I only speak ascii.",
status=400,
mimetype="text/plain")
# get current time
now = datetime.datetime.utcnow()
session = Session()
# check that user exists
user = session.query(User).filter_by(uuid=user_uuid).first()
if user is None:
session.close()
return Response("That user does not exist.",
status=400,
mimetype="text/plain")
# scan user chat for the name of a flag
flag = None
sols = session.query(Solution).all()
for sol in sols:
if usrmsg.find(sol.flag_name) >= 0:
if flag is None or len(sol.flag_name) > len(flag.flag_name):
flag = sol
if flag is None:
chatmsg = None
info = None
prev_reqs = session.query(Hintreq).filter_by(
user_id=user.user_id).all()
if len(prev_reqs) == 0:
query = session.query(Chattext).filter_by(usage="hello_start")
text1 = query.order_by(sqlfunc.random()).first()
query = session.query(Chattext).filter_by(usage="hello_end")
text2 = query.order_by(sqlfunc.random()).first()
if text1 is None or text2 is None:
chatmsg = None
else:
chatmsg = text1.ctext + text2.ctext
info = [user.uname]
else:
query = session.query(Chattext).filter_by(usage="convo")
text = query.order_by(sqlfunc.random()).first()
if text is None or random.random() >= 0.5:
fortune = subprocess.run(["/usr/games/fortune", "-s"],
capture_output=True)
chatmsg = fortune.stdout.decode("utf-8").strip()
else:
chatmsg = text.ctext
# get total points
query = session.query(sqlfunc.sum(solutions.columns.point_val))
query = query.filter(
correct.columns.user_id == user.user_id,
correct.columns.solu_id == solutions.columns.solu_id)
total_points = query.first()[0]
if total_points is None:
total_points = 0
# get first hint time
query = session.query(sqlfunc.min(hintreqs.columns.time))
first_hint = query.filter(
hintreqs.columns.user_id == user.user_id).first()[0]
# provide info for conversation
info = [user.uname, total_points, (now - first_hint).seconds // 60]
ureq = Hintreq(user_id=user.user_id, time=now, solu_id=None)
session.add(ureq)
session.commit()
session.close()
if chatmsg is None:
return Response("Sorry, I have no response.",
status=503,
mimetype="text/plain")
return chatmsg.format(info=info)
# if asked about a flag, link the hint request entry with it
ureq = Hintreq(user_id=user.user_id, time=now, solu_id=flag.solu_id)
session.add(ureq)
session.commit()
hint = hintbot.get_hint(flag.flag_name, user.user_id)
# record request on the mqtt server
if mclient is not None:
mclient.publish(
"hints",
payload=
f"{user.uname} has asked for a hint on flag {flag.flag_name}.",
qos=0,
retain=False)
session.close()
if hint is None:
return "Sorry, no hints are available for this flag."
return hint
def random(bot):
session = bot.memory['quotes_session']
res = session.query(QuotesDB).filter(QuotesDB.active == 1).order_by(
random()).first()
session.close()
return res
def select_question(cls, num: int, question_type: int):
return cls.query.filter_by(type=question_type).order_by(
random()).limit(num)
genres = ["Beginner", "Intermediate", "Advanced"]
images = [
"/static/img/python_crash_course.jpeg",
"/static/img/grokking_algorithms.jpeg",
"/static/img/the_quick_python_book.jpeg",
"/static/img/irresistible_APIs.jpeg",
"/static/img/intro_to_computer_science.jpg",
]
books = []
for _ in range(100):
author = session.query(Author).order_by(random()).first()
# add categories to import_books that way I get a category value
category = session.query(Category).order_by(random()).first()
book = Book(
title=fake.company(),
summary=fake.text(max_nb_chars=200, ext_word_list=None),
image_url=choice(images),
genre=choice(genres),
publication_year=fake.year(),
author=author,
category=category,
)
books.append(book)
session.add_all(books)
def search(text):
text = text or ""
Q = session.query(Quote)
Q = Q.filter(Quote.text.ilike("%" + text + "%")).order_by(random())
return Q.first(), Q.count()
# 5.7 给结果集的列取别名
users5 = session.query(User.name.label('user_name')).all()
for user in users5:
print(user.user_name)
# 5.8 去重查询（需要导入distinct方法
users6 = session.query(distinct(User.name).label('name')).all()
# 5.9 统计查询
user_count = session.query(User.name).order_by(User.name).count()
age_avg = session.query(func.avg(User.age)).first()
age_sum = session.query(func.sum(User.age)).first()
# 5.10 分组查询
users7 = session.query(func.count(User.name).label('count'),
User.age).group_by(User.age)
for user in users7:
print('age:{0}, count:{1}'.format(user.age, user.count))
# 6.1 exists查询(不存在则为~exists())
session.query(User.name).filter(~exists().where(User.id == Role.id))
# SELECT name AS users_name FROM users WHERE NOT EXISTS (SELECT * FROM roles WHERE users.role_id = roles.id)
# 6.2 除了exists，any也可以表示EXISTS
session.query(Role).filter(Role.users.any())
# 7 random
user = session.query(User).order_by(random()).first()
session.close()
def similarTrackAJAX(self):
id = request.params['id'].split('_')[1]
track = Session.query(Track).filter_by(id=id).one()
lastfmArtist = self.lastfmNetwork.get_artist(track.artist.name)
similarArtists = lastfmArtist.get_similar()
similarMbids = filter(lambda x: x is not None, map(lambda x: x.mbid, similarArtists))
randomSimilarArtist = Session.query(Artist).filter(Artist.mbid.in_(similarMbids)).order_by(random()).first()
return simplejson.dumps([rand.choice(randomSimilarArtist.tracks).toPlaylistJSON()]) \
if randomSimilarArtist \
else self.randomTrackAJAX()
def new(self):
try:
testsuite_id = int(h.escape(request.params.get('testsuite_id')))
except:
redirect(url(controller='attempt', action='index'))
return
testsuite = Session.query(TestSuite).get(testsuite_id)
if testsuite and testsuite.questions_per_test <= len(testsuite.questions):
first_name = h.escape(request.params.get('first_name'))
middle_name = h.escape(request.params.get('middle_name'))
last_name = h.escape(request.params.get('last_name'))
group = h.escape(request.params.get('group'))
if len(first_name) and len(middle_name) and len(last_name) and len(group):
attempt = Session.query(Attempt).filter((Attempt.first_name==first_name) &
(Attempt.middle_name==middle_name) &
(Attempt.last_name==last_name) &
(Attempt.group==group) &
(Attempt.testsuite_id==testsuite_id)).order_by(desc(Attempt.date)).first()
attempt_delay = timedelta(seconds=int(config['attempt_delay']))
delta = attempt_delay
if attempt:
delta = datetime.now() - attempt.date
if attempt is None or delta >= attempt_delay:
questions_q = Session.query(Question).filter(Question.testsuite_id==testsuite.id).order_by(random()).limit(testsuite.questions_per_test)
question_encoder = QuestionEncoder()
test_dict = {'name': testsuite.name,
'questions': {question.id: question_encoder.default(question) for question in questions_q}}
test = json.dumps(test_dict)
new_attempt = Attempt(first_name=first_name,
middle_name=middle_name,
last_name=last_name,
group=group,
testsuite_id=testsuite.id,
test=test)
Session.add(new_attempt)
Session.commit()
redirect(url(controller='attempt', action='test', id=new_attempt.id))
return
elif not attempt.is_attempted:
redirect(url(controller='attempt', action='test', id=attempt.id))
return
else:
if attempt.is_attempted_correct:
message = u"Вы уже успешно прошли этот тест"
else:
before_repeat = attempt_delay - delta
before_repeat = timedelta(days=before_repeat.days, seconds=before_repeat.seconds)
message = u"Вы сможете повтороно пройти тест через " + unicode(before_repeat)
redirect(url(controller='attempt', action='index', message=message))
else:
redirect(url(controller='attempt', action='index'))
else:
redirect(url(controller='attempt', action='index'))
def collect_dst(session, track, neighbours, max_links):
return accumulate(neighbours, max_links, 'dst',
(link.dst for link in
session.query(Link).filter(Link.src == track.artist,
Link.dst != track.artist).order_by(random()).all()))