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