我们从Python开源项目中，提取了以下8个代码示例，用于说明如何使用sqlalchemy.sql.func.min()。
def fetch_buy_or_sell_item_market_stats(item_id, is_buy_order): stats = MarketOrder \ .query \ .filter(MarketOrder.expire_time > datetime.utcnow()) \ .filter_by(item_id=item_id, is_buy_order=is_buy_order) \ .with_entities( func.sum(MarketOrder.amount).label('total_volume'), func.avg(MarketOrder.price).label('price_average'), func.min(MarketOrder.price).label('price_minimum'), func.max(MarketOrder.price).label('price_maximum'), func.count(MarketOrder.id).label('order_count'), ).one() return { 'total_volume': stats.total_volume if stats.total_volume else 0, 'price_average': round(float(stats.price_average), 2) if stats.price_average else 0, 'price_minimum': stats.price_minimum if stats.price_minimum else 0, 'price_maximum': stats.price_maximum if stats.price_maximum else 0, 'order_count': stats.order_count, }
def random_data(): rng = settings.Session.query(func.min(Comment163.id), func.max(Comment163.id)).all()[0] data = [] for i in range(12): v = random.uniform(rng[0], rng[1]) d = settings.engine.execute("select txt,liked,a.author,song_name,a.song_id,b.author from comment163 a inner join music163 b on a.song_id= b.song_id where a.id>" +str(v) + " limit 1").fetchone() data.append({"txt": d[0],"like": d[1] ,"author": d[2], "song" :{"name":d[3], "author": d[5], "id": d[4]}}) return data
def get_date_range_from_analysis(self): result = None #log.debug("get_date_range_from_analysis()") try: result = self.session.query(func.max(Exercise_Analysis.date).\ label("end_date"), func.min(Exercise_Analysis.date).\ label("start_date")).one() except Exception as e: log.error("---------------------------------------") log.error("Error - get_date_range_from_analysis(): %s", e) log.debug("get_date_range_from_analysis(): %s", result) return result
def service_get_minimum_version(context, binary): min_version = context.session.query( func.min(models.Service.version)).\ filter(models.Service.binary == binary).\ filter(models.Service.forced_down == false()).\ scalar() return min_version
def _get_associated_fixed_ips_query(context, network_id, host=None): # NOTE(vish): The ugly joins here are to solve a performance issue and # should be removed once we can add and remove leases # without regenerating the whole list vif_and = and_(models.VirtualInterface.id == models.FixedIp.virtual_interface_id, models.VirtualInterface.deleted == 0) inst_and = and_(models.Instance.uuid == models.FixedIp.instance_uuid, models.Instance.deleted == 0) # NOTE(vish): This subquery left joins the minimum interface id for each # instance. If the join succeeds (i.e. the 11th column is not # null), then the fixed ip is on the first interface. subq = context.session.query( func.min(models.VirtualInterface.id).label("id"), models.VirtualInterface.instance_uuid).\ group_by(models.VirtualInterface.instance_uuid).subquery() subq_and = and_(subq.c.id == models.FixedIp.virtual_interface_id, subq.c.instance_uuid == models.VirtualInterface.instance_uuid) query = context.session.query( models.FixedIp.address, models.FixedIp.instance_uuid, models.FixedIp.network_id, models.FixedIp.virtual_interface_id, models.VirtualInterface.address, models.Instance.hostname, models.Instance.updated_at, models.Instance.created_at, models.FixedIp.allocated, models.FixedIp.leased, subq.c.id).\ filter(models.FixedIp.deleted == 0).\ filter(models.FixedIp.network_id == network_id).\ join((models.VirtualInterface, vif_and)).\ join((models.Instance, inst_and)).\ outerjoin((subq, subq_and)).\ filter(models.FixedIp.instance_uuid != null()).\ filter(models.FixedIp.virtual_interface_id != null()) if host: query = query.filter(models.Instance.host == host) return query
def update_next_year(): """ ?????????????????? """ step = 100 # ???100??? session = get_db_session() # 2016??? mappings2016 = {} min_id, max_id = session.query(func.min(Pinkunhu2016.id), func.max(Pinkunhu2016.id)).one() while min_id <= max_id: objs = session.query(Pinkunhu2016).filter(Pinkunhu2016.id >= min_id, Pinkunhu2016.id <= min_id + step).all() for item in objs: mappings2016[item.card_number] = True mappings2016[item.card_number+'year_total_income'] = item.year_total_income mappings2016[item.card_number+'person_year_total_income'] = item.person_year_total_income min_id += step print 'min_id:%s' % min_id # 2015??? mappings2015 = {} min_id, max_id = session.query(func.min(Pinkunhu2015.id), func.max(Pinkunhu2015.id)).one() while min_id <= max_id: objs = session.query(Pinkunhu2015).filter(Pinkunhu2015.id >= min_id, Pinkunhu2015.id <= min_id + step).all() for item in objs: # ??2015??? mappings2015[item.card_number] = True mappings2015[item.card_number+'year_total_income'] = item.year_total_income mappings2015[item.card_number+'person_year_total_income'] = item.person_year_total_income # ??????? if mappings2016.get(item.card_number): item.ny_is_poor = 1 item.ny_total_income = mappings2016[item.card_number+'year_total_income'] item.ny_person_income = mappings2016[item.card_number+'person_year_total_income'] else: print item.id, '???' min_id += step print 'min_id:%s' % min_id # 2014??? min_id, max_id = session.query(func.min(Pinkunhu2014.id), func.max(Pinkunhu2014.id)).one() while min_id <= max_id: objs = session.query(Pinkunhu2014).filter(Pinkunhu2014.id >= min_id, Pinkunhu2014.id <= min_id + step).all() for item in objs: # ??????? if mappings2015.get(item.card_number): item.ny_is_poor = 1 item.ny_total_income = mappings2015[item.card_number+'year_total_income'] item.ny_person_income = mappings2015[item.card_number+'person_year_total_income'] else: print item.id, '???' min_id += step print 'min_id:%s' % min_id session.flush() session.commit()
def init_table(table, year, kind): """ ???? :param table: :param year: :param kind: :return: """ step = 100 # ???100??? session = get_db_session() mongo = get_db() # 2016??? mappings2016 = {} min_id, max_id = session.query(func.min(table.id), func.max(table.id)).one() if not min_id: return min_id -= 1 min_id = 2543595 while min_id < max_id: objs = session.query(table).filter(table.id > min_id).limit(step) for item in objs: min_id = item.id fid = item.sid data = {} for col in inspect(table).attrs: data[col.key] = getattr(item, col.key) print fid print '-------------------------' if kind in ['bangfuren', 'pinkunhu']: mongo.families.update( {'family_id': fid}, {'$set': { '%s.%s' % (year, kind): data } }, upsert=True ) elif kind in ['pinkunjiating']: mongo.families.update( {'family_id': fid}, {'$push': { '%s.%s' % (year, kind): data } }, upsert=True ) print 'min_id:%s' % min_id
