def autocomplete(request):
objName = dictObj[request.matchdict['obj']]
session = request.dbsession
criteria = request.params['term']
prop = asInt(request.matchdict['prop'])
try:
NameValReturn = request.matchdict['valReturn']
except:
NameValReturn = None
if isinstance(prop, int):
table = Base.metadata.tables[objName + 'DynPropValuesNow']
query = select([table.c['ValueString'].label('label'),
table.c['ValueString'].label('value')]
).distinct(table.c['ValueString']
).where(table.c['FK_' + objName + 'DynProp'] == prop)
query = query.where(table.c['ValueString'].like('%' + criteria + '%')
).order_by(asc(table.c['ValueString']))
else:
if NameValReturn is None:
NameValReturn = prop
table = Base.metadata.tables[objName]
query = select([table.c[NameValReturn].label('value'),
table.c[prop].label('label')]
).distinct(table.c[prop])
query = query.where(table.c[prop].like(
'%' + criteria + '%')).order_by(asc(table.c[prop]))
return [dict(row) for row in session.execute(query).fetchall()]
def showCollections():
movies = session.query(Movie).order_by(asc(Movie.name))
collections = session.query(Collection).order_by(asc(Collection.name))
if 'username' not in login_session:
return render_template('publicmovies.html', movies = movies, collections = collections)
else:
return render_template('movies.html', movies = movies, collections = collections)
def index():
if app.mixer.disp_count == 1:
disp = db.session.query(Dispenser) \
.filter(Dispenser.id == 1) \
.one()
return render_template("shotbot", booze=disp.booze.name, title="ShotBot")
can_make = app.mixer.get_available_drink_list()
can_make_dict = {}
for drink in can_make:
can_make_dict[drink] = 1
top_drinks = db.session.query(Drink) \
.join(DrinkName) \
.filter(Drink.name_id == DrinkName.id) \
.filter(Drink.popular == 1) \
.filter(Drink.available == 1) \
.order_by(asc(func.lower(DrinkName.name))).all()
top_drinks = filter_drink_list(can_make_dict, top_drinks)
process_ingredients(top_drinks)
other_drinks = db.session.query(Drink) \
.join(DrinkName) \
.filter(Drink.name_id == DrinkName.id) \
.filter(Drink.popular == 0) \
.filter(Drink.available == 1) \
.order_by(asc(func.lower(DrinkName.name))).all()
other_drinks = filter_drink_list(can_make_dict, other_drinks)
process_ingredients(other_drinks)
user = root_menu.index_layout()
return render_template("index",
top_drinks=top_drinks,
other_drinks=other_drinks,
title="Bartendro", user=user)
def addItem():
"""
Adds a new Catalog item to a specific category
"""
if 'username' not in login_session:
logged_in = False
return redirect('/login')
else:
logged_in = True
if request.method == 'POST':
if request.form['name']:
new_item = Item(name=request.form['name'], description=request.form['description'], category_id=request.form['category'])
session.add(new_item)
session.commit()
return redirect(url_for('catalog'))
else:
categories = session.query(Category).order_by(asc(Category.name))
return render_template('add-item.html', categories=categories
, logged_in=logged_in)
else:
categories = session.query(Category).order_by(asc(Category.name))
return render_template('add-item.html', categories=categories
, logged_in=logged_in)
def getTop(num=100, highlight_funcs=[], remove_funcs=[]):
"""
Get the top [amount] filtered URLS for a case. This has been optimised as one
query with a subquery.
"""
filter = getFilter(highlight_funcs, remove_funcs)
url2 = aliased(URL)
subq = session.query(url2.domain, func.count(url2.domain).label('domain_count'))\
.join(filter).filter(url2.domain != None)\
.group_by(url2.domain).order_by(desc(func.count(url2.domain)))
if num != "all":
subq = subq.limit(num)
subq = subq.subquery()
q = session.query(URL.netloc, func.count(1), subq.c.domain, subq.c.domain_count)\
.join((subq, URL.domain == subq.c.domain))\
.join(filter)\
.group_by(URL.netloc)\
.order_by(desc(subq.c.domain_count), asc(subq.c.domain), asc(URL.netloc))
domains = []
for netloc, netloc_count, domain, domain_count in q:
if len(domains) == 0 or domains[-1][0] != domain:
domains.append((domain, domain_count, []))
domains[-1][2].append((netloc, netloc_count))
return domains
def showCatalog():
'''Method to display contents of the home page of SnapShare application!'''
print "in show catalog"
albums = session.query(Album).order_by(asc(Album.name))
photos = session.query(Photo).order_by(asc(Photo.year))
#return render_template('catalog.html', albums=albums, photos = photos)
return jsonify(Albums =[a.serialize for a in albums], Photos = [p.serialize for p in photos])
def _subnet_find_ordered_by_used_ips(context, net_id, lock_subnets=True,
order="asc", unused=False, **filters):
count = sql_func.count(models.IPAddress.address).label("count")
size = (models.Subnet.last_ip - models.Subnet.first_ip)
query = context.session.query(models.Subnet, count)
if lock_subnets:
query = query.with_lockmode("update")
query = query.filter_by(do_not_use=False)
query = query.outerjoin(models.Subnet.generated_ips)
query = query.group_by(models.Subnet.id)
query = query.order_by(asc(models.Subnet.ip_version))
if unused: # find unused subnets
query = query.having(count == 0)
else: # otherwise, order used subnets
if order == "desc":
query = query.order_by(desc(size - count))
else: # default acending
query = query.order_by(asc(size - count))
query = query.filter(models.Subnet.network_id == net_id)
if "ip_version" in filters:
query = query.filter(models.Subnet.ip_version == filters["ip_version"])
if "segment_id" in filters and filters["segment_id"]:
query = query.filter(models.Subnet.segment_id == filters["segment_id"])
query = query.filter(models.Subnet.next_auto_assign_ip != -1)
if "subnet_id" in filters and filters["subnet_id"]:
query = query.filter(models.Subnet.id.in_(filters["subnet_id"]))
return query
def editCatalogItem(item_name):
if 'username' not in login_session:
return redirect('/login')
editedItem = session.query(CatalogItem).filter_by(name=item_name).one()
if request.method == 'POST':
#We have to be carefull about having 2 Items with the same name
#First we check if the name is the same.
if request.form['name'] != editedItem.name:
#If name changes, we look to see if it already existes
if not session.query(CatalogItem).filter_by(name=request.form['name']).count():
editedItem.name = request.form['name']
else:
flash('An item with the name %s already exist!' % (request.form['name']))
categories = session.query(Category).order_by(asc(Category.name))
return render_template('editcatalogitem.html', item=editedItem,
categories=categories,
login_session=login_session)
if request.form['description']:
editedItem.description = request.form['description']
if request.form['category_id']:
editedItem.category_id = request.form['category_id']
session.add(editedItem)
session.commit()
flash('Menu Item Successfully Edited')
return redirect(url_for('showHome'))
else:
categories = session.query(Category).order_by(asc(Category.name))
return render_template('editcatalogitem.html', item=editedItem,
categories=categories,
login_session=login_session)
def _changeOrderBy(self, widget, fieldsString):
""" Changes results order
RITOCCO fatto per SA 0.6.3 ...ancora da approfondire
"""
if sqlalchemy.__version__ > "0.6.0":
if (self.orderBy is not None) == (fieldsString[1] is not None):
if self.flag == False:
self.orderBy = asc(fieldsString[1])
self.flag = True
else:
self.orderBy = desc(fieldsString[1])
self.flag = False
else:
self.orderBy = fieldsString[1]
if fieldsString[0] is not None:
self.join = fieldsString[0]
else:
if self.orderBy == fieldsString[1]:
if self.flag == False:
self.orderBy = asc(fieldsString[1])
self.flag = True
else:
self.orderBy = desc(fieldsString[1])
self.flag = False
else:
self.orderBy = fieldsString[1]
if fieldsString[0]:
self.join = fieldsString[0]
self.refresh()
def parse_order_by(self, order_by_string, default=None):
"""Return an ORM compatible order_by using the given string"""
# TODO: generalize into class
# TODO: general (enough) columns
if order_by_string in ('create_time', 'create_time-dsc'):
return desc(self.model_class.create_time)
if order_by_string == 'create_time-asc':
return asc(self.model_class.create_time)
if order_by_string in ('update_time', 'update_time-dsc'):
return desc(self.model_class.update_time)
if order_by_string == 'update_time-asc':
return asc(self.model_class.update_time)
if order_by_string in ('name', 'name-asc'):
return asc(self.model_class.name)
if order_by_string == 'name-dsc':
return desc(self.model_class.name)
# TODO: history columns
if order_by_string in ('size', 'size-dsc'):
return desc(self.model_class.disk_size)
if order_by_string == 'size-asc':
return asc(self.model_class.disk_size)
# TODO: add functional/non-orm orders (such as rating)
if default:
return self.parse_order_by(default)
raise glx_exceptions.RequestParameterInvalidException('Unkown order_by', order_by=order_by_string,
available=['create_time', 'update_time', 'name', 'size'])
def user_permissions(session, user):
user_groups = get_all_groups_by_user(session, user)
non_np = [grp.name for grp, role in user_groups if role != GROUP_EDGE_ROLES.index("np-owner")]
now = datetime.utcnow()
permissions = session.query(
Permission.name,
PermissionMap.argument,
PermissionMap.granted_on,
Group,
).filter(
PermissionMap.permission_id == Permission.id,
PermissionMap.group_id == Group.id,
GroupEdge.group_id == Group.id,
GroupEdge.active == True,
user.enabled == True,
Group.enabled == True,
Group.name.in_(non_np),
or_(
GroupEdge.expiration > now,
GroupEdge.expiration == None
)
).order_by(
asc("name"), asc("argument"), asc("groupname")
).all()
return permissions
def test_paginate_query_attribute_error(self):
sqlalchemy.asc('user').AndReturn('asc')
self.query.order_by('asc').AndReturn(self.query)
self.mox.ReplayAll()
self.assertRaises(exception.InvalidSortKey,
utils.paginate_query, self.query,
self.model, 5, ['user_id', 'non-existent key'])
def test_paginate_query_value_error(self):
sqlalchemy.asc('user').AndReturn('asc_1')
self.query.order_by('asc_1').AndReturn(self.query)
self.mox.ReplayAll()
self.assertRaises(ValueError, utils.paginate_query,
self.query, self.model, 5, ['user_id', 'project_id'],
marker=self.marker, sort_dirs=['asc', 'mixed'])
def prepare(self):
conditions = []
if self.host_id is not None:
conditions.append(Attribute.host_id == self.host_id)
if self.alarmed_only:
conditions.append(EventState.internal_state != State.UP)
attributes = DBSession.query(Attribute).join(Host, EventState).\
filter(and_(*conditions)).\
order_by(asc(Host.display_name), asc(Attribute.display_name))
if attributes.count() == 0:
flash('No Attributes Found', 'alert')
self.map_groups = None
else:
for attribute in attributes:
astate, state_desc = self.attribute_state(attribute)
try:
atype = attribute.attribute_type.display_name
except AttributeError:
atype = 'Unknown'
att_fields = [('Host', attribute.host.display_name),
('Type', atype),
('Status', state_desc), ]
for k, v in attribute.description_dict().items():
if v != '':
att_fields.append((k, v))
self.add_item(attribute.host_id, attribute.host.display_name,
[('Address', attribute.host.mgmt_address)],
{'name': attribute.display_name,
'state': astate,
'url': url('/attributes/'+str(attribute.id)),
'fields': att_fields,
})
super(AttributeMap, self).prepare()
def list_project_searches(project_external_id):
project = get_project_by_id_or_404(project_external_id)
page = get_valid_page_or_1()
searches = DirectAwardSearch.query.filter(DirectAwardSearch.project_id == project.id)
if 'latest-first' in request.args:
if convert_to_boolean(request.args.get('latest-first')):
searches = searches.order_by(desc(DirectAwardSearch.created_at), desc(DirectAwardSearch.id))
else:
searches = searches.order_by(asc(DirectAwardSearch.created_at), asc(DirectAwardSearch.id))
else:
searches = searches.order_by(asc(DirectAwardSearch.id))
if convert_to_boolean(request.args.get('only-active', False)):
searches = searches.filter(DirectAwardSearch.active == True) # noqa
pagination_params = request.args.to_dict()
pagination_params['project_external_id'] = project.external_id
return paginated_result_response(
result_name="searches",
results_query=searches,
page=page,
per_page=current_app.config['DM_API_PROJECTS_PAGE_SIZE'],
endpoint='.list_project_searches',
request_args=pagination_params,
), 200
def prepare(self):
conditions = []
conditions.append(Host.show_host == True) # noqa
if self.zone_id is not None:
conditions.append(Host.zone_id == self.zone_id)
hosts = DBSession.query(Host).join(Zone).filter(
and_(*conditions)).order_by(asc(Zone.display_name),
asc(Host.display_name))
if hosts.count() == 0:
flash('No Hosts Found', 'alert')
self.map_groups = None
else:
for host in hosts:
vendor, device = host.snmp_type()
hstate, state_desc = self.host_state(host)
if self.alarmed_only and hstate == 'ok':
continue
host_fields = [('Zone', host.zone.display_name),
('Status', state_desc),
('Vendor', vendor),
('Device', device),
('Address', host.mgmt_address)]
self.add_item(host.zone_id, host.zone.display_name,
[],
{'name': host.display_name,
'state': hstate,
'url': url('/attributes/map/', {'h': host.id}),
'fields': host_fields,
})
super(HostMap, self).prepare()
def user_permissions(session, user):
# TODO: Make this walk the tree, so we can get a user's entire set of permissions.
now = datetime.utcnow()
permissions = session.query(
Permission.name,
PermissionMap.argument,
PermissionMap.granted_on,
Group,
).filter(
PermissionMap.permission_id == Permission.id,
PermissionMap.group_id == Group.id,
GroupEdge.group_id == Group.id,
GroupEdge.member_pk == user.id,
GroupEdge.member_type == 0,
GroupEdge.active == True,
user.enabled == True,
Group.enabled == True,
or_(
GroupEdge.expiration > now,
GroupEdge.expiration == None
)
).order_by(
asc("name"), asc("argument"), asc("groupname")
).all()
return permissions
def showGenres():
songs = session.query(Song).order_by(asc(Song.name))
genres = session.query(Genre).order_by(asc(Genre.name))
if 'username' not in login_session:
return render_template('publicsongs.html', songs = songs, genres = genres)
else:
return render_template('songs.html', songs = songs, genres = genres)
def showCategory(category_label):
"""Show Category.
Displays the category and category items.
Returns:
The rendered Category view
"""
try:
category = _DBH.query(Category).filter_by(label=category_label).one()
except NoResultFound:
return redirect(url_for('category.showCategoryMasterDetail')), 404
categories = (
_DBH.query(Category)
.order_by(asc(Category.label))
.all()
)
items = (
_DBH.query(Item)
.filter_by(category_id=category.id)
.order_by(asc(Item.label))
.all()
)
if 'name' in _login_session:
template_file = 'showCategory.html'
else:
template_file = 'showPublicCategory.html'
return render_template(
template_file, categories=categories, category=category, items=items)
def test_paginate_query_attribute_error(self):
sqlalchemy.asc("user").AndReturn("asc")
self.query.order_by("asc").AndReturn(self.query)
self.mox.ReplayAll()
self.assertRaises(
exception.InvalidSortKey, utils.paginate_query, self.query, self.model, 5, ["user_id", "non-existent key"]
)
def get_tests_query(filter_object=None):
# get the candidate tests to page through
query = tst.CandidateTest.query
# leave out alta tests
query = query.join('candidate_profile','account')
#query = query.filter(tst.Account.id!=352)
query = query.filter(tst.Account.id==957)
# leave out archived
query = query.join('test','test','type')
query = query.join('version')
query = query.filter(and_(tst.AccountTest.archived==False, tst.Test.archived==False, tst.TestVersion.archived==False))
# get only reading comprehension
query = query.filter(tst.TestType.id==6)
# ensure there are responses
query = query.join('sections','items')
query = query.filter(and_(tst.CandidateTestItem.response!=None, tst.CandidateTestItem.response!=''))
# order by test version
query = query.order_by(asc(tst.Test.id),asc(tst.TestVersion.id),asc(tst.CandidateTest.local_date))
# eagerload our responses
query = query.options(eagerload_all('sections.items'), eagerload('sections.items.item.type.has_response'))
return query.distinct()
def showRestaurants():
restaurants = session.query(Restaurant).order_by(asc(Restaurant.name))
cuisine = session.query(Restaurant).order_by(asc(Restaurant.cuisine_cat))
food = ['Asian', 'Italian', 'American', 'Greek', 'Vegetarian', 'Mexican', \
'All']
return render_template('restaurantlarge.html', restaurants=restaurants, \
food=food)
def get_monitor_data(cls, copr):
subquery = (
db.session.query(
db.func.max(Build.id).label("max_build_id_for_chroot"),
Build.package_id,
BuildChroot.mock_chroot_id)
.join(BuildChroot, and_(Build.id == BuildChroot.build_id))
.filter(Build.copr_id == copr.id)
.filter(BuildChroot.status != 2)
.group_by(Build.package_id, BuildChroot.mock_chroot_id)
.subquery("max_build_ids_for_a_chroot")
)
query = (
db.session.query(Package, Build, BuildChroot, MockChroot)
.join(subquery, and_(Package.id == subquery.c.package_id))
.join(Build, and_(Build.id == subquery.c.max_build_id_for_chroot))
.join(BuildChroot, and_(
BuildChroot.mock_chroot_id == subquery.c.mock_chroot_id,
BuildChroot.build_id == subquery.c.max_build_id_for_chroot
))
.join(MockChroot, and_(MockChroot.id == subquery.c.mock_chroot_id))
.order_by(
asc(Package.name),
asc(Package.id), # in case there are two packages with the same name
asc(MockChroot.os_release),
asc(MockChroot.os_version),
asc(MockChroot.arch)
)
)
return query.all()
def build_mpd_string(base_url, video, last_requested_segment_id=None):
segments = []
if last_requested_segment_id is not None:
segments = session \
.query(VideoSegment) \
.filter((VideoSegment.video_id == video.video_id) &
(VideoSegment.segment_id > last_requested_segment_id) &
(VideoSegment.status == 'OK')) \
.order_by(asc(VideoSegment.segment_id)) \
.all()
else:
segments = session \
.query(VideoSegment) \
.filter((VideoSegment.video_id == video.video_id) &
(VideoSegment.status == 'OK')) \
.order_by(asc(VideoSegment.segment_id)) \
.all()
repr_list = [video.repr_1, video.repr_2, video.repr_3]
new_last_segment_id = None
if segments and len(segments) > 0:
new_last_segment_id = segments[-1].segment_id
return gen_mpd(base_url=base_url,
repr_list=repr_list,
segment_duration_millis=video.segment_duration,
segment_list=segments), new_last_segment_id
def editItem():
# If not logged in redirect to login page, flash message
if 'username' not in login_session:
flash('You must be logged in to do that!')
return redirect('/login')
# Select and pass to template only items created by the current
items = session.query(Item).filter_by(
user_id=login_session['user_id']).order_by(asc(Item.name))
categories = session.query(Category).order_by(asc(Category.name))
if request.method == 'POST':
editedItem = session.query(Item).filter_by(
name=request.form['old_name']).one()
if request.form['name']:
editedItem.name = request.form['name']
if request.form['description']:
editedItem.description = request.form['description']
if request.form['category']:
newCategory = session.query(Category).filter_by(
name=request.form['category']).one()
editedItem.category_id = newCategory.id
session.add(editedItem)
session.commit()
flash("Item edited!")
return redirect(url_for('showCatalog'))
else:
return render_template('edititem.html',
items=items, categories=categories)
def test_paginate_query_null(self):
self.mox.StubOutWithMock(self.model.user_id, 'isnot')
self.model.user_id.isnot(None).AndReturn('asc_null_1')
sqlalchemy.desc('asc_null_1').AndReturn('asc_null_2')
self.query.order_by('asc_null_2').AndReturn(self.query)
sqlalchemy.asc(self.model.user_id).AndReturn('asc_1')
self.query.order_by('asc_1').AndReturn(self.query)
self.mox.StubOutWithMock(self.model.project_id, 'is_')
self.model.project_id.is_(None).AndReturn('desc_null_1')
sqlalchemy.desc('desc_null_1').AndReturn('desc_null_2')
self.query.order_by('desc_null_2').AndReturn(self.query)
sqlalchemy.desc(self.model.project_id).AndReturn('desc_1')
self.query.order_by('desc_1').AndReturn(self.query)
self.mox.StubOutWithMock(sqlalchemy.sql, 'and_')
sqlalchemy.sql.and_(mock.ANY).AndReturn('some_crit')
sqlalchemy.sql.and_(mock.ANY, mock.ANY).AndReturn('another_crit')
self.mox.StubOutWithMock(sqlalchemy.sql, 'or_')
sqlalchemy.sql.or_('some_crit', 'another_crit').AndReturn('some_f')
self.query.filter('some_f').AndReturn(self.query)
self.query.limit(5).AndReturn(self.query)
self.mox.ReplayAll()
utils.paginate_query(self.query, self.model, 5,
['user_id', 'project_id'],
marker=self.marker,
sort_dirs=['asc-nullslast', 'desc-nullsfirst'])
def retrieve(self):
objName = self.parent.item.model.__tablename__
criteria = self.request.params['term']
prop = self.attribute
if self.integers(prop):
table = Base.metadata.tables[objName + 'DynPropValuesNow']
query = select([table.c['ValueString'].label('label'),
table.c['ValueString'].label('value')]
).distinct(table.c['ValueString']
).where(table.c['FK_' + objName + 'DynProp'] == prop)
query = query.where(table.c['ValueString'].like('%' + criteria + '%')
).order_by(asc(table.c['ValueString']))
else:
NameValReturn = prop
if self.targetValue:
NameValReturn = self.targetValue
table = Base.metadata.tables[objName]
query = select([table.c[NameValReturn].label('value'),
table.c[prop].label('label')]
).distinct(table.c[prop])
query = query.where(table.c[prop].like(
'%' + criteria + '%')).order_by(asc(table.c[prop]))
return [dict(row) for row in self.session.execute(query).fetchall()]
def distance(self,id):
c.distance='''{
label: 'Distance(km)',
data:['''
q=model.Session.query(model.trackpoint).filter(model.trackpoint.id==id)
c.infomarker = q.one()
q = model.Session.query(model.track).filter(model.track.id==c.infomarker.track_id)
c.track = q.one()
q=model.Session.query(model.trackpoint).filter(model.trackpoint.track_id==c.track.id)
trackpoint=q.order_by(asc(model.trackpoint.timestamp)).first()
oldlat=float(trackpoint.latitude)
oldlon=float(trackpoint.longitude)
distance=0
trackpoints=q.order_by(asc(model.trackpoint.timestamp)).all()
for trackpoint in trackpoints:
newlat=float(trackpoint.latitude)
newlon=float(trackpoint.longitude)
if oldlat==newlat and oldlon==newlon:
pass
else:
try:
distance=distance+h.distance_on_unit_sphere(oldlat, oldlon, newlat, newlon)*6373
except typeError:
return oldlat,oldlon,newlat,newlon
oldlat=newlat
oldlon=newlon
timestamp=trackpoint.timestamp
c.distance=c.distance+'''['''+str(calendar.timegm(timestamp.timetuple()) * 1000)+''', '''+str(distance)+'''], '''
c.distance=c.distance+''']}'''
return c.distance
def statistics(mode=1, **kw):
order = kw.get("order")
sort = kw.get("sort")
if mode == 1:
query = db.session.query(Price)
else:
query = Price.query
if order:
query = query.filter_by(order=order)
if sort:
if sort == "ID":
query = query.order_by(desc(Price.id))
elif sort == "TOTAL-DESC":
query = query.order_by(desc(Price.total))
elif sort == "TOTAL-ASC":
query = query.order_by(asc(Price.total))
elif sort == "DATE-DESC":
query = query.order_by(desc(Price.reg_date))
elif sort == "DATE-ASC":
query = query.order_by(asc(Price.reg_date))
else:
query = query.order_by(Price.id)
else:
query = query.order_by(desc(Price.reg_date))
total = query.count()
items = [
(ob.id, ob.reg_date, ob.documentID, ob.documentDate, ob.priceTypeID, ob.currency, ob.total, ob.get_price())
for ob in query.all()
]
return (total, order and order.id, items, query)
def list(self, **kwargs):
"""Get a list of valid projects"""
try:
limit = kwargs['limit'] if 'limit' in kwargs else 10
page = kwargs['page'] if 'page' in kwargs else 0
filters = self.get_filters(**kwargs)
# In case of requiring languages, a LEFT JOIN must be generated
if 'lang' in kwargs and kwargs['lang'] is not None:
ret = []
for u in ProjectLang.get_query(kwargs['lang']) \
.filter(*filters) \
.order_by(asc(self.created)) \
.offset(page * limit) \
.limit(limit):
ret.append(ProjectLang.get_translated_object(
u._asdict(),
kwargs['lang'],
u.lang))
return ret
# No langs, normal query
return self.query.distinct().filter(*filters) \
.order_by(asc(self.created)) \
.offset(page * limit) \
.limit(limit).all()
except NoResultFound:
return []
def showRestaurants():
restaurants = session.query(Restaurant).order_by(asc(Restaurant.name))
if 'username' not in login_session:
return render_template('publicrestaurants.html', restaurants = restaurants)
else:
return render_template('restaurants.html', restaurants = restaurants)
def showCakes():
cakes = session.query(Cake).order_by(asc(Cake.name))
return render_template('main.html', cakes=cakes)
#!/usr/bin/python3
""" lists all State objects that contain the letter a """
from sys import argv
from model_city import City
from sqlalchemy.orm import sessionmaker
from sqlalchemy import asc, update
from model_state import Base, State
from sqlalchemy import (create_engine)
if __name__ == "__main__":
engine = create_engine('mysql+mysqldb://{}:{}@localhost/{}'.format(
argv[1], argv[2], argv[3]),
pool_pre_ping=True)
start = sessionmaker()
start.configure(bind=engine)
sta = start()
for new, new2 in sta.query(State,
City).join(City).order_by(asc(City.id)).all():
print("{}: ({}) {}".format(new.name, new2.id, new2.name))
sta.close()
def events(namespace_id, event_public_id, calendar_public_id, title,
description, location, busy, starts_before, starts_after,
ends_before, ends_after, limit, offset, view, expand_recurring,
show_cancelled, db_session):
query = db_session.query(Event)
if not expand_recurring:
if view == 'count':
query = db_session.query(func.count(Event.id))
elif view == 'ids':
query = db_session.query(Event.public_id)
filters = [
namespace_id, event_public_id, calendar_public_id, title, description,
location, busy
]
query = filter_event_query(query, Event, *filters)
event_criteria = []
if starts_before is not None:
event_criteria.append(Event.start < starts_before)
if starts_after is not None:
event_criteria.append(Event.start > starts_after)
if ends_before is not None:
event_criteria.append(Event.end < ends_before)
if ends_after is not None:
event_criteria.append(Event.end > ends_after)
if not show_cancelled:
if expand_recurring:
event_criteria.append(Event.status != 'cancelled')
else:
# It doesn't make sense to hide cancelled events
# when we're not expanding recurring events,
# so don't do it.
# We still need to show cancelled recurringevents
# for those users who want to do event expansion themselves.
event_criteria.append(
(Event.discriminator == 'recurringeventoverride')
| ((Event.status != 'cancelled')
& (Event.discriminator != 'recurringeventoverride')))
event_predicate = and_(*event_criteria)
query = query.filter(event_predicate)
if expand_recurring:
expanded = recurring_events(filters,
starts_before,
starts_after,
ends_before,
ends_after,
db_session,
show_cancelled=show_cancelled)
# Combine non-recurring events with expanded recurring ones
all_events = query.filter(Event.discriminator == 'event').all() + \
expanded
if view == 'count':
return {"count": len(all_events)}
all_events = sorted(all_events, key=lambda e: e.start)
if limit:
offset = offset or 0
all_events = all_events[offset:offset + limit]
else:
if view == 'count':
return {"count": query.one()[0]}
query = query.order_by(asc(Event.start)).limit(limit)
if offset:
query = query.offset(offset)
# Eager-load some objects in order to make constructing API
# representations faster.
all_events = query.all()
if view == 'ids':
return [x[0] for x in all_events]
else:
return all_events
def default():
"""Default page route."""
categories = session.query(Category).order_by(asc(Category.id))
latest = session.query(Product).order_by(
Product.created_at.desc()).limit(8).all()
return render_template('index.html', categories=categories, latest=latest)
def ShowCategories_all():
all_categories = session.query(Category).order_by(asc(Category.name))
return render_template('all_categories.html',
all_categories=all_categories,
login_session=login_session)
def show_all_lists():
lists = session.query(TodoList).order_by(asc(TodoList.name))
return render_template('todoLists.html', lists=lists)
def all_tags_selection():
lista_tags = Tag.query.order_by(asc(Tag.name)).all()
selection = []
for t in lista_tags:
selection.append((t, t))
return selection
def showCatalogs():
catalogs = session.query(Catalog).order_by(asc(Catalog.name))
if 'username' not in login_session:
return render_template('publicCatalogs.html', catalogs=catalogs)
else:
return render_template('catalogs.html', catalogs=catalogs)
def show_catalog():
categories = session.query(Category).order_by(asc(Category.name))
items = session.query(CategoryItem).order_by(desc(
CategoryItem.id)).limit(10)
return render_template('catalog.html', categories=categories, items=items)
def get_by_page(cls, order, page, per_page=10):
sort = desc(Task.id) if order == 'desc' else asc(Task.id)
return Task.query.order_by(sort).paginate(page, per_page).items
def showAccounts():
'''Show all accounts'''
accounts = session.query(Account).order_by(asc(Account.accountType))
stocks = session.query(Stock).order_by(asc(Stock.account_id))
return render_template('stocks.html', accounts=accounts, stocks=stocks)
def extract_alerts_from_db(self):
print("Extracting records from db")
self.alert_list_lock.acquire()
try:
start_from = datetime.now()
if None == self.prev_expirty_time:
self.prev_expirty_time = get_time_plus_minutes(self.alert_expiry_time)
else:
start_from = self.prev_expirty_time;
self.prev_expirty_time = time_plus_min(self.prev_expirty_time, self.alert_expiry_time)
# Pull records from db which are sorted in ascending order
alerts = self.session.query(RescueAlert).filter(RescueAlert.adventure_end_time > start_from, RescueAlert.adventure_end_time < self.prev_expirty_time).order_by(asc(RescueAlert.adventure_end_time)).all()
self.alerts_buff[len(self.alerts_buff): len(self.alerts_buff)] = alerts
for al in self.alerts_buff:
print("\t Adventure Id {} Adventure Name is {}, end-time {}".format(al.id, al.adventure_name, al.adventure_end_time))
finally:
self.alert_list_lock.release()
def event_log_get_oldest(self):
query = model_query(models.EventLog)
result = query.order_by(asc(models.EventLog.created_at)).limit(1).one()
return result
def get_programs_for_owner(session, owner_name):
return session.query(Program).filter(
Program.owner_name == owner_name).order_by(asc(
Program.prog_order)).all()
def get_programs_for_hardware(session, hardware):
return session.query(Program).filter(
Program.hardware == hardware).order_by(asc(
Program.prog_order)).all()
def index():
users = User.query.order_by(asc(User.username)).all()
return render_template('index.html', users = users)
def shownewPlaces():
place = session.query(Places).order_by(asc(Places.name))
return render_template('newPlace.html', p=place)
def showSkishops():
skishops = session.query(Parent).order_by(asc(Parent.name))
return render_template('skishops.html', skishops=skishops)
def list_audits():
page = get_valid_page_or_1()
try:
per_page = int(
request.args.get('per_page',
current_app.config['DM_API_SERVICES_PAGE_SIZE']))
except ValueError:
abort(400, 'invalid page size supplied')
audits = AuditEvent.query.order_by(
desc(AuditEvent.created_at) if convert_to_boolean(
request.args.get('latest_first')) else asc(AuditEvent.created_at))
audit_date = request.args.get('audit-date', None)
if audit_date:
if is_valid_date(audit_date):
audit_datetime = datetime.strptime(audit_date, DATE_FORMAT)
audits = audits.filter(
AuditEvent.created_at.between(
audit_datetime, audit_datetime + timedelta(days=1)))
else:
abort(400, 'invalid audit date supplied')
audit_type = request.args.get('audit-type')
if audit_type:
if AuditTypes.is_valid_audit_type(audit_type):
audits = audits.filter(AuditEvent.type == audit_type)
else:
abort(400, "Invalid audit type")
acknowledged = request.args.get('acknowledged', None)
if acknowledged and acknowledged != 'all':
if is_valid_acknowledged_state(acknowledged):
if convert_to_boolean(acknowledged):
audits = audits.filter(AuditEvent.acknowledged == true())
elif not convert_to_boolean(acknowledged):
audits = audits.filter(AuditEvent.acknowledged == false())
else:
abort(400, 'invalid acknowledged state supplied')
object_type = request.args.get('object-type')
object_id = request.args.get('object-id')
if object_type:
if object_type not in AUDIT_OBJECT_TYPES:
abort(400, 'invalid object-type supplied')
if not object_id:
abort(400, 'object-type cannot be provided without object-id')
model = AUDIT_OBJECT_TYPES[object_type]
id_field = AUDIT_OBJECT_ID_FIELDS[object_type]
ref_object = model.query.filter(id_field == object_id).first()
if ref_object is None:
abort(404,
"Object with given object-type and object-id doesn't exist")
audits = audits.filter(AuditEvent.object == ref_object)
elif object_id:
abort(400, 'object-id cannot be provided without object-type')
audits = audits.paginate(page=page, per_page=per_page)
return jsonify(auditEvents=[audit.serialize() for audit in audits.items],
links=pagination_links(audits, '.list_audits',
request.args))
def weight_order():
# Query all puppies by ascending weight
puppyWeights = session.query(Puppy).order_by(asc(Puppy.weight)).all()
for puppyWeight in puppyWeights:
print puppyWeight.weight
def test_track_tag_mat_view(app):
"""Tests that genre metrics can be queried"""
with app.app_context():
db = get_db()
test_entities = {
"tracks": [
{
"track_id": 1,
"tags": "",
"owner_id": 1
},
{
"track_id": 2,
"owner_id": 1,
"tags": "pop,rock,electric"
},
{
"track_id": 3,
"owner_id": 2
},
{
"track_id": 4,
"owner_id": 2,
"tags": "funk,pop"
},
{
"track_id": 5,
"owner_id": 2,
"tags": "funk,pop"
},
{
"track_id": 6,
"owner_id": 2,
"tags": "funk,Funk,kpop"
},
]
}
populate_mock_db(db, test_entities)
with db.scoped_session() as session:
session.execute("REFRESH MATERIALIZED VIEW tag_track_user")
user_1_tags = (session.query(TagTrackUserMatview).filter(
TagTrackUserMatview.owner_id == 1).order_by(
asc(TagTrackUserMatview.tag),
asc(TagTrackUserMatview.track_id)).all())
user_2_tags = (session.query(TagTrackUserMatview).filter(
TagTrackUserMatview.owner_id == 2).order_by(
asc(TagTrackUserMatview.tag),
asc(TagTrackUserMatview.track_id)).all())
user_4_tags = (session.query(TagTrackUserMatview).filter(
TagTrackUserMatview.owner_id == 4).all())
assert len(user_1_tags) == 3
assert user_1_tags[0].tag == "electric"
assert user_1_tags[0].track_id == 2
assert user_1_tags[1].tag == "pop"
assert user_1_tags[1].track_id == 2
assert user_1_tags[2].tag == "rock"
assert user_1_tags[2].track_id == 2
assert len(user_2_tags) == 6
assert user_2_tags[0].tag == "funk"
assert user_2_tags[0].track_id == 4
assert user_2_tags[1].tag == "funk"
assert user_2_tags[1].track_id == 5
assert user_2_tags[2].tag == "funk"
assert user_2_tags[2].track_id == 6
assert user_2_tags[3].tag == "kpop"
assert user_2_tags[3].track_id == 6
assert user_2_tags[4].tag == "pop"
assert user_2_tags[4].track_id == 4
assert user_2_tags[5].tag == "pop"
assert user_2_tags[5].track_id == 5
assert not user_4_tags
def reviews():
if request.method == 'GET':
sort = request.args.get('sort', 'asc')
limit = request.args.get('limit')
stat = request.args.get('stat')
if stat != None:
if sort == 'desc':
results = Review.query.order_by(desc('reviews.' + stat)).limit(limit).all()
elif sort == 'asc':
results = Review.query.order_by(asc('reviews.' + stat)).limit(limit).all()
else:
results = Review.query.limit(limit).all()
json_results = []
for result in results:
d = { 'id': result.id,
'created': result.created,
'user_id': result.user_id,
'beer_id': result.beer_id,
'aroma': result.aroma,
'appearance': result.appearance,
'taste': result.taste,
'palate': result.palate,
'bottle_style': result.bottle_style,
'overall': result.overall }
json_results.append(d)
return jsonify(review=json_results)
if request.method == 'POST':
user_id = request.get_json().get('user_id')
beer_id = request.get_json().get('beer_id')
aroma = request.get_json().get('aroma')
appearance = request.get_json().get('appearance')
taste = request.get_json().get('taste')
palate = request.get_json().get('palate')
bottle_style = request.get_json().get('bottle_style')
if user_id == "" or user_id == None:
abort(500)
if beer_id == "" or beer_id == None:
abort(500)
if aroma == "" or aroma == None:
abort(500)
if appearance == "" or appearance == None:
abort(500)
if taste == "" or taste == None:
abort(500)
if palate == "" or palate == None:
abort(500)
if bottle_style == "" or bottle_style == None:
abort(500)
errors = []
error_flag = False
if float(aroma) < 0 or float(aroma) > 5:
error = { 'error' : "Aroma must be between 0.0 and 5.0" }
errors.append(error)
if float(appearance) < 0 or float(appearance) > 5:
error = { 'error' : "Appearance must be between 0.0 and 5.0" }
errors.append(error)
if float(taste) < 0 or float(taste) > 10:
error = { 'error' : "Taste must be between 0.0 and 10.0" }
errors.append(error)
if float(palate) < 0 or float(palate) > 5:
error = { 'error' : "Palate must be between 0.0 and 5.0" }
errors.append(error)
if float(bottle_style) < 0 or float(bottle_style) > 5:
error = { 'error' : "Bottle Style must be between 0.0 and 5.0" }
errors.append(error)
if len(errors) > 0:
return jsonify(response = errors)
usercheck = User.query.filter_by(id = user_id).first()
if usercheck == None:
error = "The user does not exist"
return jsonify(response = error)
beercheck = Beer.query.filter_by(id = beer_id).first()
if beercheck == None:
error = "The beer does not exist"
return jsonify(response = error)
weeklycheck = Review.query.filter_by(user_id = user_id, beer_id = beer_id, posted_this_week = 1).first()
if weeklycheck != None:
error = "This user has already reviewed this beer this week"
return jsonify(response = error)
ratings = [float(aroma), float(appearance), float(taste), float(palate), float(bottle_style)]
overall = sum(ratings) / float(len(ratings))
newReview = Review(
user_id = user_id,
beer_id = beer_id,
aroma = aroma,
appearance = appearance,
taste = taste,
palate = palate,
bottle_style = bottle_style,
overall = overall,
posted_this_week = 1)
dbsession = db.session()
dbsession.add(newReview)
dbsession.commit()
success = "Review Created"
return jsonify(response = success)
def six_months():
# Query all of the puppies that are less than 6 months old organized by the youngest first
puppies_six_months = session.query(Puppy).filter(
Puppy.dateOfBirth >= "2015-06-01").order_by(asc(Puppy.dateOfBirth))
for puppies in puppies_six_months:
print puppies.dateOfBirth
def ShowCategories_all_JSON():
all_categories = session.query(Category).order_by(asc(Category.name))
return jsonify(all_categories=[i.serialize for i in all_categories])
def beers():
if request.method == 'GET':
sort = request.args.get('sort', 'asc')
limit = request.args.get('limit')
stat = request.args.get('stat')
if stat != None:
if sort == 'desc':
results = Beer.query.order_by(desc('beers.' + stat)).limit(limit).all()
elif sort == 'asc':
results = Beer.query.order_by(asc('beers.' + stat)).limit(limit).all()
else:
results = Beer.query.limit(limit).all()
json_results = []
for result in results:
d = { 'id': result.id,
'name': result.name,
'ibu': result.ibu,
'calories': result.calories,
'abv': result.abv,
'style': result.style,
'brewery_location': result.brewery_location,
'glass_type': result.glass_type }
json_results.append(d)
return jsonify(beers=json_results)
if request.method == 'POST':
name = request.get_json().get('name')
ibu = request.get_json().get('ibu')
calories = request.get_json().get('calories')
abv = request.get_json().get('abv')
style = request.get_json().get('style')
brewery_location = request.get_json().get('brewery_location')
glass_type = request.get_json().get('glass_type')
user_id = request.get_json().get('user_id')
if name == "" or name == None:
abort(500)
if ibu == "" or ibu == None:
abort(500)
if calories == "" or calories == None:
abort(500)
if abv == "" or abv == None:
abort(500)
if style == "" or style == None:
abort(500)
if brewery_location == "" or brewery_location == None:
abort(500)
if glass_type == "" or glass_type == None:
abort(500)
result = User.query.filter_by(id = user_id).first()
if result.beer_added_today == 1:
error = "This user has already added a beer today"
return jsonify(response = error)
glasscheck = Glass.query.filter_by(id = glass_type).first()
if glasscheck == None:
error = "Invalid beer glass type"
return jsonify(response = error)
dbsession = db.session()
query = dbsession.query(User)
query = query.filter(User.id == user_id)
record = query.one()
record.beer_added_today = 1
dbsession.commit()
newBeer = Beer(
name = name,
ibu = ibu,
calories = calories,
abv = abv,
style = style,
brewery_location = brewery_location,
glass_type = glass_type)
dbsession = db.session()
dbsession.add(newBeer)
dbsession.commit()
success = "Beer Created"
return jsonify(response = success)
def showRestaurants():
restaurants = session.query(Restaurant).order_by(asc(Restaurant.name))
return render_template('restaurants.html', restaurants=restaurants)
def categoryJSON():
#query category table from database
category = session.query(Category).order_by(asc(Category.name)).all()
return jsonify(categorys=[c.serialize for c in category])
def find_all_by_store_wo_page(cls, store_id):
return cls.query.filter(cls.store_id == store_id).order_by(
asc(cls.type)).all()
def showCategories():
categories = session.query(Category).order_by(asc(Category.name))
return render_template('categories.html', categories=categories)
