def login(request):
"""Returns a tuple of (authenticated) uid, and a message string.
If the user could not be authenticated (or they didn't try), the uid
will be None. If the user successfully logged in, sets a session
cookie for this session."""
session = DBSession()
uid = authenticated_userid(request)
if uid:
return session.query(User).get(uid), None
if 'user' in request.params and 'passwd' in request.params:
uid = request.params['user']
passwd = request.params['passwd']
user = session.query(User).get(uid)
if not user:
return None, "Invalid user or password."
if user.check_pw(passwd):
headers = remember(request, uid)
request.response.headerlist.extend(headers)
return user, "Logged in Successfully"
else:
return None, "Invalid user or password."
else:
return None, None
def return_records(self): # pylint: disable=R0201
"""Returns all records in the database."""
logger.info("Return records")
if not self.request.params:
logger.info("Returns all records")
records = DBSession.query(Record).order_by(
Record.timestamp.desc()).all()
else:
logger.info("Searching records by params")
for key in self.request.params.keys():
logger.info("Invalid Query")
if key not in Record.search_queries:
return Response(status=400)
logger.info("Processing date queries")
start_date = self.request.params.get("start_date", Record.min_date)
start_date = Record.get_timestamp(start_date)
end_date = self.request.params.get("end_date", Record.max_date)
end_date = Record.get_timestamp(end_date)
records = (DBSession.query(Record).filter(
Record.timestamp.between(start_date, end_date)).order_by(
Record.timestamp.desc()).all())
if records:
records_json = []
for record in records:
records_json.append(record.to_dict())
return records_json
return Response(status=204)
def _cleanup(self):
now = time()
session = DBSession()
session.query(Data).filter(
now - Data.timestamp > TIMEOUT_THRESHOLD).delete()
session.commit()
session.close()
def edit(self):
uid = self.request.matchdict['uid']
transaction.commit()
page = DBSession.query(User).filter_by(id=uid).first()
edit_form = self.edit_form
if 'submit' in self.request.params:
controls = self.request.POST.items()
try:
appstruct = edit_form.validate(controls)
except deform.ValidationFailure as e:
return dict(page=page, form=e.render())
# Change the content and redirect to the view
page.First_Name = appstruct['First_Name']
page.Age = appstruct['Age']
page.Address = appstruct['Address']
page.Last_Name = appstruct['Last_Name']
transaction.commit()
url = self.request.route_url('home', uid=page.id)
return HTTPFound(url)
form = self.edit_form.render(
dict(uid=page.id,
First_Name=page.First_Name,
Address=page.Address,
Last_Name=page.Last_Name,
Age=page.Age))
users = DBSession.query(User).all()
return dict(results=users, form=form)
def get_menu_desc(menu, for_calendar):
desc = ''
json_result = []
if len(menu.menus) < 1:
return ''
for menu_item_id in menu.menus.split(' '):
menu_item = DBSession.query(MenuItem).filter(MenuItem.id==menu_item_id).one()
allergens = DBSession.query(Allergen).filter(Allergen.menu_item_id==menu_item.id).all()
allergen_string = ', '.join([a.allergen for a in allergens])
desc = '\n' + desc
menu_item_name = menu_item.name.decode('utf8')
desc = desc + menu_item_name
if menu_item.healthy:
if for_calendar:
desc = desc + '\n' + health_icon[menu_item.healthy-1].decode('utf8') + ' ' + HEALTHY_FACTOR[menu_item.healthy-1] + ' '
else:
desc = desc + '\n' + '(' + HEALTHY_FACTOR[menu_item.healthy-1] + ') '
desc = desc + '\n'
menu_item_desc = ''
if len(menu_item.description.decode('utf8')):
menu_item_desc = menu_item.description.decode('utf8')
desc = desc + menu_item_desc + '\n'
if len(allergen_string):
desc = desc + '(' + allergen_string + ')\n\n'
json_result.append((menu_item_name, menu_item_desc))
return desc.strip('\n')
def getUser(request):
username = request.headers["X-Webauth-User"]
#now get the uuid
uuid = ""
print "UserName:"******"UUID:", uuid
if uuid == "":
user = DBSession.query(Owners).filter_by(username=username).all()
else:
user = DBSession.query(Owners).filter_by(uuid=uuid).all()
if len(user) == 0:
cur = Owners(username, uuid)
DBSession.add(cur)
DBSession.commit()
user = DBSession.query(Owners).filter_by(username=username).all()
print "Made"
if len(user) == 1:
print "Found"
cur = user[0]
# if user == "zemon1":
# user = "******"
return cur
def attach_pictures(request):
menu_query = DBSession.query(Menu).filter(Menu.id==request.matchdict['menu_id'])
images_id = menu_query.one().images_id
images_id = images_id.split(' ') if images_id else []
if images_id:
images = DBSession.query(Image).filter(Image.id.in_(images_id)).all()
added_thumbs = []
if "data" in request.params:
data = json.loads(request.params["data"])
for image_info in data:
image, thumb = image_info
if not image or not thumb:
continue
ext = os.path.splitext(image)[-1].lower()
if ext in (".jpg", ".jpeg", ".png"):
new_image = Image(image_url=image, thumb_url=thumb)
added_thumbs.append(thumb)
DBSession.add(new_image)
DBSession.flush()
DBSession.refresh(new_image)
images_id.append(new_image.id)
menu_query.update({
"images_id": ' '.join([str(i) for i in images_id]),
})
return json.dumps(added_thumbs)
def get(self, grid):
session = DBSession()
q = session.query(Person).get(int(grid))
group = session.query(PersonGroup).get(q.group_grid)
setattr(q, 'group_name', group.name)
return q
def user_get(request):
dbsession = DBSession()
name = clean_matchdict_value(request, 'user')
user = dbsession.query(User).filter(User.name == name).one()
user_data = dbsession.query(UserData).filter(UserData.user_id == user.id).one()
user_json = convert.decodeUser(request, dbsession, user, user_data)
return {'status': 'success', 'user': user_json}
def job_archive():
"""
Returns the job archive from the database
:return:
"""
jobs_stmt = DBSession.query(Job).order_by(Job.updatetime.desc()).all()
jobs_dict = []
for jobs in jobs_stmt:
job_outputs = DBSession.query(JobOutput).filter(JobOutput.jobid == jobs.id).all()
jobs = jobs.__dict__
if 'jobinfo' in jobs and jobs['jobinfo'] is not None:
obj = jsonpickle.decode(jobs['jobinfo'])
try:
jobs.update(obj)
except BaseException as e:
print(str(e))
if 'jobdetails' in jobs and jobs['jobdetails'] is not None:
obj = jsonpickle.decode(jobs['jobdetails'])
try:
jobs.update(obj)
except BaseException as e:
print(str(e))
jobs['number_of_joboutputs'] = len(job_outputs)
jobs['joboutputs'] = []
for outputs in job_outputs:
jobs['joboutputs'].append(outputs.__dict__)
jobs_dict.append(jobs)
return jobs_dict
def is_allowed(request, routename, method='ALL', check_route=True):
"""
Given a request_object, routename and method; returns True if current user has access to that route,
otherwise returns False.
If check_route if False, does not check the DB to see if the route is in the list of protected routes
"""
if check_route:
route = DBSession.query(RoutePermission).filter_by(route_name=routename).first()
if not route:
return True
if not isinstance(method, list):
method = [method, ]
user_permissions = request.session.get('auth_user_permissions', [])
if request.session.get('auth_static_permission', None):
user_permissions.append(request.session.get('auth_static_permission', None))
has_permission = DBSession.query(func.count(RoutePermission.permission)).filter(
RoutePermission.route_name == routename).filter(
RoutePermission.method.in_(method)).filter(
RoutePermission.permission.in_(user_permissions)).scalar()
if has_permission > 0:
return True
else:
return False
def delete(self):
uid = self.request.matchdict['uid']
form = ReusableForm()
DBSession.query(User).filter_by(id=uid).delete()
transaction.commit()
users = DBSession.query(User).all()
return dict(form=form, results=users)
def update_menu_item_desc(request):
menu_item_id = request.matchdict['menu_item_id']
menuItem = DBSession.query(MenuItem).filter(MenuItem.id==menu_item_id)
new_desc = urllib.unquote(request.json_body).encode('utf8')
menuItem.update({"description":new_desc})
menu = DBSession.query(Menu).filter(Menu.id==menuItem.one().menu_id).one()
update_gcalendar(menu)
return 'ok'
def update(self, grid, data):
session = DBSession()
session.query(Person).filter(Person.grid == grid).update(data)
try:
session.commit()
except Exception as e:
log.debug("Ocorreu um erro ao salvar as configurações !\n" + e)
session.rollback()
def stop_unblock(context, request):
id = request.matchdict.get('id', None)
session = DBSession()
session.query(Stop).filter(Stop.id == id).update({
Stop.is_block: False,
Stop.user_block_id: None
})
return Response()
def auth_request(request):
response = None
# ignore static routes for authentication
if 'static' in request.path.split('/'):
return handler(request)
logged_in_user = request.session.get('logged_in_user', None)
protected_routes = []
routes = DBSession.query(RoutePermission.route_name).distinct().all()
for R in routes:
protected_routes.append(R[0])
#print(protected_routes)
matched_routename = None
for r in request.registry.introspector.get_category('routes'):
R = r['introspectable']
matcher, generator = _compile_route(R['pattern'])
if type(matcher(request.path)) == types.DictionaryType:
#print(R['name'] + ':' + R['pattern'])
matched_routename = R['name']
break
# Check routes from protected routes here.
if matched_routename and matched_routename in protected_routes:
# first check if there is any static permission given and if yes then validate routes against that permission
user_permissions = []
if request.session.get('auth_static_permission', None):
user_permissions.append(request.session.get('auth_static_permission', None))
else:
if not logged_in_user:
return HTTPForbidden()
# get user permissions
user_permissions = request.session.get('auth_user_permissions', [])
# get route permissions for the current route
# match if there are any common permissions and check for all matching request methods
has_permission = DBSession.query(func.count(RoutePermission.permission)).filter(
RoutePermission.route_name == matched_routename).filter(
or_(RoutePermission.method == 'ALL',
RoutePermission.method == request.method)).filter(
RoutePermission.permission.in_(user_permissions)).scalar()
if has_permission > 0:
return handler(request)
else:
return HTTPForbidden()
else:
return handler(request)
def make_session(user, sessionid=None, expires_in=3600):
if not sessionid:
sessionid = str(uuid.uuid4())
DBSession.query(Session).filter(Session.sessionid == sessionid).delete()
logging.debug("making session for %s with sessionid %s" % (user.username, sessionid))
s = Session(user_id=user.id, sessionid=sessionid, expires=datetime.datetime.now() + datetime.timedelta(0, expires_in))
DBSession.add(s)
DBSession.flush()
DBSession.expunge(s)
return s
def get_entries(request): #todo this searching is terrible but it's a POC if not any(param == 'search' for param in request.params) or request.GET['search'] == '': data = DBSession.query(m.Entry).order_by(m.Entry.start_datetime.desc()).slice(0, 10).all() else: searchExpression = '%' + request.GET['search'] + '%' data = DBSession.query(m.Entry) \ .filter((m.Entry.content.ilike(searchExpression) | m.Entry.title.ilike(searchExpression))) \ .all() return utilities.serialize(data)
def refresh_menu(menu):
menu_items = []
allergens = []
for menu_item_id in menu.menus and menu.menus.split(' '):
menu_item = DBSession.query(MenuItem).filter(MenuItem.id==menu_item_id).one()
allergen_array = DBSession.query(Allergen).filter(Allergen.menu_item_id==menu_item.id).all()
menu_items.append(menu_item)
allergens.append([a.allergen for a in allergen_array])
return dict(menu_items=menu_items, allergens=allergens, menu=menu, three_meals=THREE_MEALS)
def get_houses(context, request):
box = leaflet_box_to_WKT_polygon(request.params.getall('box')[0])
session = DBSession()
houses_in_box = session.query(House) \
.filter(House.geo.within(box)) \
.join(House.policeman) \
.all()
houses = []
policemen = dict()
for house in houses_in_box:
geometry = loads(str(house.geo.geom_wkb))
feature = Feature(
id=house.osm_id,
geometry=geometry,
properties={
'address': house.address,
'pm_id': house.policeman_id
}
)
houses.append(feature)
policeman = house.policeman
if not policeman.id in policemen:
photo_url_parsed = policeman.photo_url.split('/')
photo_name = photo_url_parsed[-2] + photo_url_parsed[-1].replace('img', '_file')
policemen[policeman.id] = {
'id': policeman.id,
'name': policeman.name,
'type': policeman.type,
'rank': policeman.rank,
'phone': policeman.phone,
'url': policeman.url,
'photo_url': request.route_url('home') + 'static/images/' + photo_name,
'color': policeman.color
}
subdivisions_in_box = session.query(Subdivision) \
.filter(Subdivision.geo.within(box)) \
.all()
subdivisions = {}
for subdivision_in_box in subdivisions_in_box:
subdivision = row2dict(subdivision_in_box, ['id', 'name', 'phone', 'address', 'hours', 'url'])
subdivision['geo'] = loads(str(subdivision_in_box.geo.geom_wkb))
subdivisions[subdivision['id']] = subdivision
result = {
'houses': FeatureCollection(houses),
'policemen': policemen,
'subdivisions': subdivisions
}
return Response(dumps(result))
def changeUserPassword(user,password):
mySession = DBSession()
try:
transaction.begin()
mySession.query(User).filter_by(user_name = user).update({"user_password": encodeData(password)})
transaction.commit()
mySession.close()
return True
except:
transaction.abort()
mySession.close()
return False
def get_menu_json(menu):
json_result = []
if len(menu.menus) < 1:
return ''
for menu_item_id in menu.menus.split(' '):
menu_item = DBSession.query(MenuItem).filter(MenuItem.id==menu_item_id).one()
allergens = DBSession.query(Allergen).filter(Allergen.menu_item_id==menu_item.id).all()
allergen_string = ', '.join([a.allergen for a in allergens])
menu_item_name = menu_item.name.decode('utf8')
if len(menu_item.description.decode('utf8')):
menu_item_desc = menu_item.description.decode('utf8')
json_result.append((menu_item_name, menu_item_desc))
return json.dumps(json_result)
def delete(self, grid):
session = DBSession()
session.query(Person).filter(Person.grid == grid).update({'flag': 'D'})
try:
session.commit()
log.info("Comando executado com sucesso !")
log.info(session)
return session
except Exception as e:
log.debug("Ocorreu um erro ao salvar as configurações !\n" +
e.__str__())
session.rollback()
return None
def put(self, catid):
session = DBSession()
category = session.query(models.Category).filter_by(id=catid).first()
args = parser.parse_args()
if args['name'] and len(args['name']):
category.name = args['name']
if args['order'] and len(args['order']):
neworder = args['order']
other = session.query(models.Category).filter_by(order=neworder).first()
if other:
other.order = category.order
category.order = neworder
session.commit()
def delete_picture(request):
menu_query = DBSession.query(Menu).filter(Menu.id==request.matchdict['menu_id'])
images_id = menu_query.one().images_id
images_id = images_id.split(' ') if images_id else []
if "data" in request.params:
thumb_url = json.loads(request.params["data"])
image = DBSession.query(Image).filter(Image.thumb_url==thumb_url).filter(Image.id.in_(images_id)).one()
images_id.remove(str(image.id))
images_id = ' '.join([str(i) for i in images_id])
menu_query.update({"images_id": images_id}, synchronize_session=False)
DBSession.delete(image)
return thumb_url
def start_shuffle():
random.seed()
session = DBSession()
groups = session.query(Group).filter(
Group.date_shuffle <= datetime.datetime.now().date(),
Group.shuffle_done == False, Group.active == True).all()
for group in groups:
if len(group.members) == 0:
group.shuffle_done = True
session.commit()
send_message(
group.owner.telegramid,
'К сожалению, в вашей группе "{}" нет ни одного участника. Некому высылать подарки'
.format(group.name))
elif len(group.members) == 1:
group.shuffle_done = True
session.commit()
send_message(
group.owner.telegramid,
'К сожалению в вашей группе "{}" всего один учатник. Некому высылать подарки.'
.format(group.name))
send_message(
group.members[0].user.telegramid,
'Вы единственный участник группы {}. Подарите себе что-нибудь приятное'
.format(group.name))
elif len(group.members) > 1:
member_list = group.members[:]
random.shuffle(member_list)
member_list[-1].send_to = member_list[0].user
for i in range(len(member_list) - 1):
member_list[i].send_to = member_list[i + 1].user
group.shuffle_done = True
session.commit()
for member in group.members:
to_member = session.query(Member).filter(
Member.group == group,
Member.user == member.send_to).first()
text = '''Распределение получателей для группу {} завершено!\nВы Санта для {}. Пожелания к подарку: {}
подарок высылать по следующему адресу: {} {}. На имя {}'''.format(
group.name, to_member.user.name, to_member.suggestions,
to_member.user.index, to_member.user.address,
to_member.user.fio)
send_message(member.user.telegramid, text)
send_message(
group.owner.telegramid,
'Распределение получателей для группу {} завершено! Участников: {}.\
Всем участникам разосланы их получатели.'.format(
group.name, len(group.members)))
session.close()
save_to_log('system', comment_text="Shuffle done")
def make_session(user, sessionid=None, expires_in=3600):
if not sessionid:
sessionid = str(uuid.uuid4())
DBSession.query(Session).filter(Session.sessionid == sessionid).delete()
logging.debug("making session for %s with sessionid %s" %
(user.username, sessionid))
s = Session(user_id=user.id,
sessionid=sessionid,
expires=datetime.datetime.now() +
datetime.timedelta(0, expires_in))
DBSession.add(s)
DBSession.flush()
DBSession.expunge(s)
return s
def populate(comps='comps-f16', do_dependencies=True):
from yum.comps import Comps
session = DBSession()
c = Comps()
c.add('comps/%s.xml' % comps)
for group in c.groups:
g = Group(id=group.groupid,
name=group.name,
description=group.description)
session.add(g)
for package in group.packages:
p = session.query(Package).filter_by(
name=to_unicode(package)).first()
if not p:
p = Package(name=package)
session.add(p)
p.group = g
session.flush()
root = Root(name=u'Fedora')
session.add(root)
session.flush()
for category in c.categories:
c = Category(id=category.categoryid,
name=category.name,
description=category.description)
session.add(c)
root.categories.append(c)
for group in category.groups:
g = session.query(Group).filter_by(
group_id=to_unicode(group)).first()
if not g:
print "Cannot find group: %s" % group
else:
g.category = c
session.flush()
if do_dependencies:
for package in session.query(Package).all():
add_dependencies(package, session)
session.commit()
def update_stop(context, request):
stop = json.loads(request.POST["stop"])
session = DBSession()
session.query(Stop).filter(Stop.id == stop["id"]).update(
{
Stop.name: stop["name"].encode("UTF-8"),
Stop.is_bench: None if stop["is_bench"] == "None" else stop["is_bench"],
Stop.is_shelter: None if stop["is_shelter"] == "None" else stop["is_shelter"],
Stop.comment: stop["comment"].encode("UTF-8"),
Stop.panorama_link: stop["panorama_link"],
Stop.check_status_type_id: stop["is_check"],
Stop.is_help: str_to_boolean(stop["is_help"]),
},
synchronize_session=False,
)
# raw sql about https://groups.google.com/forum/?fromgroups=#!topic/geoalchemy/vSAlsuhwWfo
sql = "UPDATE stops SET geom=GeomFromText(:wkt, 4326) WHERE id = :stop_id"
session.execute(sql, {"wkt": "POINT(%s %s)" % (stop["lon"], stop["lat"]), "stop_id": stop["id"]})
sql_routes = sql_generate_for_many_to_many(
session,
stop["routes"],
["stop_id", "route_id"],
{"col": "stop_id", "id": stop["id"]},
"stops_routes",
["stop_id", "route_id"],
)
if sql_routes:
session.execute(sql_routes)
sql_types = sql_generate_for_many_to_many(
session,
stop["stop_types"],
["stop_id", "stop_type_id"],
{"col": "stop_id", "id": stop["id"]},
"stops_stop_types",
["stop_id", "stop_type_id"],
)
if sql_types:
session.execute(sql_types)
log = LogStops()
log.stop_id = stop["id"]
log.user_id = request.session["u_id"]
log.time = datetime.now()
session.add(log)
transaction.commit()
return Response(json.dumps(stop))
def update_menu_item_allergen(request):
menu_item_query = DBSession.query(MenuItem).filter(MenuItem.id==request.matchdict['menu_item_id'])
allergen_to_add = request.params.get('menu_item_allergen_on', None)
allergen_to_remove = request.params.get('menu_item_allergen_off', None)
if allergen_to_add:
#create a new allergen for the allergen that's turned on
new_allergen = Allergen(menu_item_id=request.matchdict['menu_item_id'], allergen = allergen_to_add)
DBSession.add(new_allergen)
elif allergen_to_remove:
#delete the allergen that was turned off
allergen_to_remove = DBSession.query(Allergen).filter(Allergen.menu_item_id==request.matchdict['menu_item_id']).filter(Allergen.allergen==allergen_to_remove).one()
DBSession.delete(allergen_to_remove)
menu = DBSession.query(Menu).filter(Menu.id==menu_item_query.one().menu_id).one()
update_gcalendar(menu)
return 'ok'
def updateProfile(user,data):
mySession = DBSession()
try:
transaction.begin()
mySession.query(User).filter_by(user_name = user).update({"user_fullname": data["user_fullname"],"user_organization": data["user_organization"],
"user_email": data["user_email"],"user_cnty": data["user_cnty"],"user_sector": data["user_sector"],
"user_about": data["user_about"]})
transaction.commit()
mySession.close()
return True
except Exception, e:
print str(e)
transaction.abort()
mySession.close()
return False
def clear_db():
"""On teardown, remove all the db stuff"""
db = DBSession()
db.query(Payment).delete()
db.query(Trade).delete()
db.query(Product).delete()
db.query(LogTrade).delete()
db.commit()
def additional_changes_stats(request):
db_session = DBSession()
d = request.GET.get('date')
if not d:
return HTTPNotFound()
try:
d = d.split('-')
d = date(year=int(d[0]), month=int(d[1]), day=int(d[2]))
except Exception:
return HTTPNotFound()
spiders = db_session.query(Spider, Account).join(Account).filter(Spider.enabled == True,
Account.enabled == True)
final_stats = []
for s in spiders.yield_per(50):
try:
crawl = db_session.query(Crawl).filter(Crawl.spider_id == s.Spider.id,
Crawl.status == 'upload_finished',
Crawl.crawl_date == d).order_by(desc(Crawl.id)).first()
if crawl:
stats = get_crawl_stats(crawl.id, '/home/innodev/product-spiders')
row = {'account': s.Account.name, 'spider': s.Spider.name,
'total products': str(crawl.products_count),
'changes percentage': get_perc(crawl.products_count, stats['total changes'])}
for k in stats:
row[k] = str(stats[k])
final_stats.append(row)
except Exception:
continue
final_stats.sort(cmp=lambda x, y: cmp(int(x['total changes']), int(y['total changes'])), reverse=True)
f = NamedTemporaryFile(suffix='.csv', delete=False)
header = ['account', 'spider', 'total products',
'total changes', 'changes percentage', 'name', 'url',
'image_url', 'category', 'brand', 'sku', 'stock',
'shipping_cost', 'dealer', 'identifier']
writer = csv.DictWriter(f, header)
writer.writeheader()
for row in final_stats:
writer.writerow(row)
f.close()
r = FileResponse(f.name, request=request, content_type='text/csv')
return r
class LagouPipeline(object):
def __init__(self):
self.session = DBSession()
self.pool = redis.Redis(host='raspberrypi', port=6379, db=2)
def process_item(self, item, spider):
if isinstance(item, LagouItem):
if self.session.query(Jobs).filter(
Jobs.positionId == item['positionId'],
Jobs.companyId == item['companyId']).first():
pass
else:
obj = Jobs(
companyId=item['companyId'],
positionId=item['positionId'],
jobNature=item['jobNature'],
companyName=item['companyName'],
financeStage=item['financeStage'],
companyFullName=item['companyFullName'],
companySize=item['companySize'],
industryField=item['industryField'],
positionName=item['positionName'],
city=item['city'],
createTime=item['createTime'],
salary_low=item['salary_low'],
salary_high=item['salary_high'],
workYear=item['workYear'],
education=item['education'],
positionAdvantage=item['positionAdvantage'],
district=item['district'],
# uid=item['uid'],
companyLabelList=item['companyLabelList'],
)
self.session.add(obj)
try:
self.session.commit()
except Exception, e:
print e
self.session.rollback()
# self.session.close()
elif isinstance(item, CompanyItem):
# 公司信息存入mysql数据库
'''
obj=Company(
companyId=item['companyId'],
companyName=item['companyFullName']
)
self.session.add(obj)
try:
self.session.commit()
except Exception, e:
print e
self.session.rollback()
'''
# 公司的数据存入redis
self.pool.set(item['companyId'], item['companyFullName'])
def make_admin(config_path):
from intranet3.models import User
user_login = sys.argv[-1]
if len(sys.argv) < 4:
print u"Provide user login"
return
session = DBSession()
user = session.query(User).filter(User.email == user_login).first()
if not user:
print u"No such user: %s" % user_login
return
if 'admin' in user.groups:
print u'Removing %s from group admin' % user.name
groups = list(user.groups)
groups.remove('admin')
user.groups = groups
else:
print u'Adding %s to group admin' % user.name
groups = list(user.groups)
groups.append('admin')
user.groups = groups
session.add(user)
transaction.commit()
def get_all_colleague_data_by_id(self, id):
"""
Get all colleague associated data filter by colleague_id
"""
colleague = DBSession.query(Colleague).filte(
Colleague.colleague_id == id).first()
return colleague
def my_view(request):
try:
one = DBSession.query(Note).filter(Note.note_id == 1).first()
except DBAPIError:
return Response(conn_err_msg, content_type='text/plain',
status_int=500)
return {'one': one, 'project': 'PyramidNotes'}
def get_all_colleague_relation_by_id(self, id):
"""
Get all colleague_relation data filter by colleague_id
"""
colleague_relation = DBSession.query(ColleagueRelation).filter(
ColleagueRelation.colleague_id == id).all()
return colleague_relation
def sentiment_data(self, url, common_list):
"""
Does the sentiment analysis with wit.ai
and saves the sentiment data into the database.
"""
query_str = urllib.parse.quote_plus(' '.join(common_list)[:280])
resp = yield self.fetch_url(
'https://api.wit.ai/message?v=20180703&q={}'.format(query_str),
headers={
'Authorization':
'Bearer {WIT_AUTH_KEY}'.format(WIT_AUTH_KEY=WIT_AUTH_KEY)
})
ents = json.loads(resp['body'])['entities']
sentiment = ents and ents.get('sentiment')
if sentiment:
sentiment = sentiment[0]['value']
s_obj = DBSession.query(UrlSentiment).filter(
UrlSentiment.sentiment == sentiment)
if s_obj.count() == 0:
s_obj = UrlSentiment(url=url, sentiment=sentiment)
DBSession.add(s_obj)
DBSession.commit()
raise gen.Return(s_obj)
raise gen.Return(False)
def update_menu_item_name(request):
menu_item_query = DBSession.query(MenuItem).filter(MenuItem.id==request.matchdict['menu_item_id'])
menu_item = menu_item_query.one()
old_name = menu_item.name
new_name = urllib.unquote(request.json_body)
menu_item_query.update({"name":new_name.encode('utf8')})
#update the menu name if the menu item is the first menu item, the menu name is the same as the old menu item name or there's no menu name
menu_query = DBSession.query(Menu).filter(Menu.id==menu_item.menu_id)
menu = menu_query.one()
if int(menu.menus.split(' ')[0]) == menu_item.id:
if not menu.name or old_name == menu.name:
menu_query.update({"name":new_name.encode('utf8')})
update_gcalendar(menu)
#update the menu title with the new name
return json.dumps(menu.name.decode('utf8'))
def get_spending_item(request): id = request.matchdict['id'] data = DBSession.query(m.Spending).filter(m.Spending.spending_id == id).first() if data is None: raise 'Not implemented - need a 404 here' else: return utilities.serialize(data)
def test_create_success(self):
user_data = {'email': '*****@*****.**', 'password': '******'}
create_object(User, **user_data)
self.signin(**user_data)
self.testapp.post('/pads/create/', {'name': 'pad'}, status=302)
self.assertEquals(1, DBSession.query(Pad).count())
class Controller(object):
def __init__(self):
self.session = DBSession()
def selUser(self):
get_data = self.session.query(User).all()
return showAllView(get_data)
def update_menu_name(request):
menu_query = DBSession.query(Menu).filter(Menu.id==request.matchdict['menu_id'])
new_name = urllib.unquote(request.json_body)
menu_query.update({"name":new_name.encode('utf8')})
update_gcalendar(menu_query.one())
return json.dumps(menu_query.one().name.decode('utf8'))
def write_jobs_to_database(jobs):
"""
Writes the retrieved jobs to the database
:param jobs: list of all jobs
:return:
"""
log = logging.getLogger(__name__)
with transaction.manager:
for (classname, classmembers) in jobs.items():
if len(classmembers) == 0:
continue
for element in classmembers:
if 'error' in element:
continue
if 'JobID' not in element:
log.debug('Row didn''t match specified criteria {0}'.format(element))
continue
if not re.search('[0-9]*', element['JobID']):
log.debug('Row didn''t match specified criteria {0}'.format(element))
continue
dbrow = DBSession.query(Job).filter(Job.id == element['JobID']).all()
json_str = jsonpickle.encode(element)
if len(dbrow) == 0:
j = Job(element['JobID'], json_str)
DBSession.add(j)
elif len(dbrow) == 1:
dbrow[0].jobinfo = json_str
else:
log.error('More than one entry for jobid: {0}'.format(json_str))
DBSession.commit()
def event_delete(request):
dbsession = DBSession()
id = clean_matchdict_value(request, 'event')
event = dbsession.query(Event).filter(Event.id == id).one()
dbsession.delete(event)
transaction.commit()
return {'status': 'success'}
def get_logs(context, request):
session = DBSession()
user_stops_count_sbq = session \
.query(LogStops.user_id.label('user_id'), func.count(LogStops.stop_id.distinct()).label('count_stops')) \
.group_by(LogStops.user_id) \
.subquery()
user_stops_log = session.query(User, user_stops_count_sbq.c.count_stops) \
.outerjoin(user_stops_count_sbq, User.id == user_stops_count_sbq.c.user_id) \
.order_by(asc(User.display_name))
count_editable_stops = session.query(func.count(LogStops.stop_id.distinct())).scalar()
count_all_stops = session.query(func.count(Stop.id)).scalar()
results = {'count': {'all': count_all_stops, 'editable': count_editable_stops},
'stops_by_users': []}
for user_stops_log in user_stops_log:
results['stops_by_users'].append({'user_name': user_stops_log[0].display_name, 'count_stops': user_stops_log[1]})
return Response(json.dumps(results))
def get_all_collegue_locus_by_id(self, id):
"""
Get all colleague_locus data filter by colleague_id
"""
colleague_locus = DBSession.query(ColleagueLocus).filter(
ColleagueLocus.colleague_id == id).all()
return colleague_locus
def create_message(request):
data = json.loads(request.body)
cur_user = data['username']
user_id = DBSession.query(User).filter_by(uid=cur_user).one().id
data["owner_id"] = user_id
target_query = DBSession.query(User).filter(
User.uid == cur_user)
if target_query.count() > 0:
target = target_query.first()
if target.uid == cur_user or cur_user.admin:
m = Message.from_dict(data)
#m = Message()
DBSession.add(m)
DBSession.commit()
return m.to_dict()
raise HTTPUnauthorized
def scoreboard():
board = DBSession.query(Scoreboard).order_by(Scoreboard.score.desc()).all()
rankings = get_rankings(board)
return render_template('scoreboard.html',
scoreboard=board,
count=len(board),
rankings=rankings)
def crawl_plat_first_letter(shuju_date="2020-01-062020-01-12"):
"""
平台成交数据 https://shuju.wdzj.com/platdata-1.html
"""
url = "https://shuju.wdzj.com/plat-data-custom.html"
form_data = {
"type": 0,
"shujuDate": shuju_date
}
response = requests.post(url, data=form_data)
status = response.status_code
if status != 200:
print("crawl failed. (status is not 200)")
raise CrawlFailed('crawl failed')
plats_data = response.json()
for plat_data in plats_data:
plat_id = plat_data.get('platId')
wdzj_id = plat_data.get('wdzjPlatId')
first_letter = plat_data.get('firstLetter')
session = DBSession()
if wdzj_id != 0:
product = session.query(Product).filter_by(plat_id=plat_id).first()
product.first_letter = first_letter
session.commit()
session.close()
def scoreboard(request):
offset = int(request.matchdict['offset'])
userLimit = 1000
users = DBSession.query(User).\
order_by(User.cur_question.desc()).order_by(User.last_submit_time.asc()).\
limit(userLimit).offset(offset).\
all()
rank = offset + 1
userRanks = []
for user in users:
tempUser = {}
tempUser['id'] = user.id
tempUser['rank'] = rank
tempUser['name'] = user.name
tempUser['nick'] = user.nick
tempUser['cur_question'] = user.cur_question
tempUser['score'] = user.score
tempUser['last_submit_time'] = user.last_submit_time
if tempUser['nick'] != 'Admin':
userRanks.append(tempUser)
rank += 1
return dict(scores = userRanks)
def getUserName(request):
currentUser = int(authenticated_userid(request))
user = DBSession.query(User).filter(User.id == currentUser).first()
userName = user.nick
return {'user' : userName}
def on_set_base_info(cls, company_id, company_name=None, country=None, city=None, address=None, timezone=None,
currency=None, note=None):
""" 设置公司基础信息 """
session = DBSession()
com = session.query(cls).filter(cls.company_id == company_id).first()
if com:
if company_name:
com.company_name = company_name
if country:
com.country = country
if city:
com.city = city
if address:
com.address = address
if timezone:
com.timezone = timezone
if currency:
com.currency = currency
if note:
com.note = note
try:
session.add(com)
session.commit()
except Exception as why:
session.rollback()
message = "companies.on_set_base_info: {0}".format(why)
logging.info(message)
return -1
return 0
def instantiate(self, **extra_attrs):
"""
Create an instance of a SQLAlchemy model class corresponding to
my OFX tag, with attributes given by my contained OFX elements.
If an instance that matches the given primary key signature has
already been given, return that instead of creating a new one.
"""
self.extra_attributes = extra_attrs
# SECID needs to instantiate as SECINFO
if self.tag == 'SECID':
SubClass = models.SECINFO
else:
SubClass = getattr(models, self.tag)
self._preflatten()
self.attributes = self._flatten()
self._postflatten()
self.attributes.update(self.extra_attributes)
self.extra_attributes = {}
try:
fingerprint = SubClass._fingerprint(**self.attributes)
instance = DBSession.query(SubClass).filter_by(**fingerprint).one()
except NoResultFound:
instance = SubClass(**self.attributes)
DBSession.add(instance)
return instance
def view_note(request):
note_id = request.matchdict['note_id']
note = DBSession.query(Note).filter(
Note.id == note_id,
Note.user_id == get_current_user(request).id
).first() or raise_404(text='Note not found')
return _response_dict(request, note=note)
def addLocationDB(locName, roomNum, locOwner):
exists = DBSession.query(Location).filter_by(owner=locOwner.id,
name=locName,
roomNumber=roomNum).all()
if len(exists) == 0:
tempLoc = Location(owner.id, name, roomNum)
DBSession.add(cur)
DBSession.commit()
loc = DBSession.query(Location).filter_by(id=tempLoc.id).all()
print "Made Loc"
if len(exists) == 1:
print "Found Loc"
loc = exists[0]
return loc
def test_create_success(self):
user_data = {'email': '*****@*****.**', 'password': '******'}
create_object(User, **user_data)
self.signin(**user_data)
self.testapp.post('/pads/create/', {'name': 'pad'}, status=302)
self.assertEquals(1, DBSession.query(Pad).count())
