def get_words(term, quantity=1): # if we haven`t received a term to search, randomize the words PopularDescription = Text popular_description_id = db.session.query(PopularDescription.id)\ .filter(PopularDescription.word_id == Word.id)\ .limit(1)\ .correlate(Word)\ .as_scalar() get_words = db.session.query(Word, Text, Language, Lexicographer)\ .join(Text, Text.id == popular_description_id)\ .join(Language, Language.id == Text.language_id)\ .join(Lexicographer, Lexicographer.id == Text.created_by_id)\ .with_entities(Word.id, Word.word, Text.timestamp, Text.text, Text.id.label("text_id"), \ Language.name, Language.code, Word.created_by_id, Lexicographer.username, \ Text.num_ratings, Language.color) if term is None: return get_words.offset( func.floor(func.random() * db.session.query(func.count(Word.id)))).limit(quantity) # otherwise use our term to search else: return get_words.filter(Word.word.ilike("%" + term + "%"))
async def show_random_async(user_id: int) -> schemas.Product: count = products.count().where(products.c.user_id == user_id) query = products.select().where(products.c.user_id == user_id).offset( func.floor(func.random() * count)).limit(1) item = await database.fetch_one(query) if not item: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=f"has no items.") return schemas.Product(**item)
def find_random_media(count: int) -> list: return session.query(Img) \ .filter(Img.archived_at == None) \ .options(load_only('telegram_file_id', 'media_type')) \ .offset( func.floor( func.random() * session.query(func.count(Img.telegram_file_id)) ) ).limit(count).all()
def show_random(db: Session, user: schemas.LoginUser) -> schemas.ShowProduct: item = db.query( models.Product).filter(models.Product.user_id == user.id).offset( func.floor( func.random() * db.query(func.count(models.Product.user_id)).filter( models.Product.user_id == user.id))).limit(1).first() if not item: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=f"has no items.") return item
def plotScatter (self, xkey, ykey, skey = None, ckey = None, binkey = None, bins = 10, binMin = None, binMax = None, clsType = DumpFileEntry, errorbars = False, resize = False, xscale = 1.0, yscale = 1.0, **kwargs): query = self.query # If binning is intended (binkey is not None), group the data into bins if binkey is not None: # Calculate the min and max of the bin if binMin and binMax are not specified if binMax is None: query = clsType.cacheQuery (binkey, query, function = "max", label = "max") if binMin is None: query = clsType.cacheQuery (binkey, query, function = "min", label = "min") if query != self.query: res = query.first () binMax = res.max if binMax is None else binMax binMin = res.min if binMin is None else binMin if binMax is None or binMin is None: raise ValueError ("Nothing matches query") # Add the binning procedure to the query # TODO It might be nice if this could bin by non-numerical data too if isinstance (binkey, str): binkey = getattr (DumpFileEntry, binkey) query = self.query.add_column (func.floor (binkey / (binMax - binMin) * bins + binMin).label ("bin")).group_by ("bin").filter (binkey >= binMin, binkey <= binMax) # Add the keys to the query, using the cacheQuery method for key, name in zip ([xkey, ykey, skey, ckey], ["xkey", "ykey", "skey", "ckey"]): if key is not None: query = clsType.cacheQuery (key, query, function = None if binkey is None else "avg", label = name) if binkey is not None and errorbars: query = clsType.cacheQuery (key, query, function = "stddev", label = name + "std") # Execute the query results = query.all () # Plot the data if skey is not None: kwargs ["s"] = np.array ([res.skey for res in results]) if resize: minimum = np.min (kwargs ["s"]) maximum = np.max (kwargs ["s"]) if minimum != maximum: kwargs ["s"] = (kwargs ["s"] - minimum) / (maximum - minimum) * 100 + 40 else: kwargs ["s"] = 200 if ckey is not None: kwargs ["c"] = [res.ckey for res in results] if binkey is None or not errorbars: return self.ax.scatter ([res.xkey * xscale for res in results], [res.ykey * yscale for res in results], **kwargs) else: return self.ax.errorbar ([res.xkey * xscale for res in results], [res.ykey * yscale for res in results], xerr = [res.xkeystd * xscale for res in results], yerr = [res.ykeystd * yscale for res in results], linestyle = "None", **kwargs)
def obshist(plateid, session): # Set initial variables sn2, vdone = 0.0, 0 # Get global S/N and visit information try: #find all the APOGEE plates with the same location_id and version as the current plate locver_plates = getLocVerPlates(session,plateid) sndata = session.query(func.floor(Exposure.start_time/86400.0+0.3),\ func.sum(Quickred.snr_standard * Quickred.snr_standard),\ func.count(Quickred.snr_standard),\ func.sum(Reduction.snr * Reduction.snr),\ func.count(Reduction.snr)).\ join(ExposureFlavor, Observation, PlatePointing, Plate).\ outerjoin(Quickred, Reduction).\ filter(Plate.plate_id.in_(locver_plates)).\ filter(ExposureFlavor.label=='Object').\ filter(or_(Quickred.snr_standard >= 10.0, Reduction.snr >= 10.0)).\ group_by(func.floor(Exposure.start_time/86400.0+0.3)).distinct() if sndata.count() > 0: good_days = [x[0] for x in sndata] else: good_days = [] for i in range(sndata.count()): # Determine which S/N to use, QL or reduction if sndata[i][3] != None: sn = float(sndata[i][3]) sncnt = float(sndata[i][4]) else: sn = float(sndata[i][1]) sncnt = float(sndata[i][2]) # If 2+ visits with S/N > 10, add visit to total if sncnt >= 2: vdone += 1 # Always add S/N^2 to total sn2 += sn # Get individual exposure S/N information expdata = session.query(Exposure.pk,\ (Quickred.snr_standard * Quickred.snr_standard),\ (Reduction.snr * Reduction.snr),\ func.floor(Exposure.start_time/86400.0+0.3)).\ join(ExposureFlavor, Observation, PlatePointing, Plate).\ outerjoin(Quickred, Reduction).\ filter(Plate.plate_id.in_(locver_plates)).\ filter(ExposureFlavor.label=='Object').\ filter(or_(Quickred.snr_standard >= 10.0, Reduction.snr >= 10.0)).\ order_by(Exposure.pk).distinct() # Mark whether exposures are good or bad exposures = [] for e in expdata: if int(e[3]) in good_days: this_good = True else: this_good = False exposures.append([e[0], e[1], e[2], this_good]) # Get vplan information from the design value table dvdata = session.query(DesignValue.value).\ join(DesignField, Design, Plate).\ filter(Plate.plate_id==plateid).distinct() except: raise RuntimeError("ERROR: unable to find observation history in database for plate: %s\n%s" %\ (plateid, sys.exc_info())) try: vplan = int(dvdata.filter(DesignField.label=='apogee_n_design_visits').first()[0]) except: vplan = 0 try: cadence_flag = dvdata.filter(DesignField.label=='apogee_design_type').first()[0] except: cadence_flag = '' return sn2, vdone, vplan, cadence_flag, exposures
def random_quote(): # I got this from https://stackoverflow.com/questions/60805/getting-random-row-through-sqlalchemy result = quotes.query.options(load_only('id')).offset( func.floor(func.random() * db.session.query(func.count(quotes.id)))).limit(1).all() return (result[0].quote)
c = [ ## date/time functions ## #func.timeofday(), # for postgresql func.localtime(), func.current_timestamp(), #func.date_part("month", func.now()), # for postgresql func.now(), ## mathematical functions ## func.pow(4, 2), func.sqrt(441), func.pi(), func.floor(func.pi()), func.ceil(func.pi()), ## string functions ## func.lower("ABC"), func.upper("abc"), func.length("abc"), func.trim(" ab c "), #func.chr(65), # for postgresql ] s = select(c) rs = conn.execute(s) print(rs.keys()) print(rs.fetchall())
def _optimized_random(model_name): from wuaiwow import db return model_name.query.options(load_only('id')).offset( func.floor( func.random() * db.session.query(func.count(model_name.id)))).limit(1).all()
def optimized_question_random(): return Question.query.filter.options(load_only('id')).offset( func.floor(func.random() * db.session.query(func.count(Question.id)))).limit(1).all()