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 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 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()))