def beats_data(id_num, con_time, current_time):
"""
:param id_num:
:param con_time:
:param current_time:
:return: data = {
'id_num': id_num,
'station_alarms': station_alarms,
'plc_alarms': plc_alarms,
'station_info': info,
'data_alarms': alarm_data
}
"""
r = ConnDB()
# 获取心跳间隔时间内产生的报警
alarm_data = r.get('alarm_info')
r.set('alarm_info', None)
# print(alarm_data)
# 获取
station_alarms = list()
plc_alarms = list()
session = Session()
try:
if con_time:
station = session.query(StationAlarm).filter(con_time <= StationAlarm.time). \
filter(StationAlarm.time < current_time).all()
for s in station:
station_alarms.append(serialize(s))
plc = session.query(PLCAlarm). \
filter(con_time <= PLCAlarm.time).filter(PLCAlarm.time < current_time).all()
for p in plc:
plc_alarms.append(serialize(p))
finally:
session.close()
# 获取设备信息
info = station_info()
data = {
'id_num': id_num,
's_a': station_alarms,
'p_a': plc_alarms,
'info': info,
'd_a': alarm_data
}
return data
def loginUser(self, User):
isNoob = 0
std_query = db.GqlQuery("SELECT * FROM Student WHERE user = :1",
User).fetch(1)
student = {}
if len(std_query) == 0:
try:
url = "https://mcommunity.umich.edu/mcPeopleService/people/" + User.nickname(
)
result = urlfetch.fetch(url)
info = json.loads(result.content)
studentName = info['person']['displayName']
except:
studentName = User.nickname()
student = models.Student(user=User,
courses=[],
reminders=[],
major="",
advisor_email="",
name=studentName,
calID="")
logging.warning(student)
student.put()
isNoob = 1
else:
student = std_query[0]
logging.warning(models.serialize(student))
context = {
'name': student.name,
'email': student.user.email(),
'major': student.major,
'advisor_email': student.advisor_email,
'logoutUrl': users.create_logout_url("/"),
'achievement': 89,
'noob': isNoob,
'admin': users.is_current_user_admin()
}
self.render_response('index.jade', **context)
# create the courses calendar asynchronously
if student.calID is None or student.calID == "":
logging.warning('student calID is empty')
calendar = utils.createCal()
request = service.calendars().insert(body=calendar)
created_cal = request.execute(http=decorator.http())
student.calID = created_cal["id"]
else:
logging.warning('student cal id already exists, it is %s' %
student.calID)
student.put()
def get(self):
announcements = db.GqlQuery("SELECT * FROM Announcement")
output = []
for x in announcements:
output.append(models.serialize(x))
self.response.out.write(json.dumps(output))
def get(self):
announcements = db.GqlQuery("SELECT * FROM Announcement")
output = []
for x in announcements :
output.append(models.serialize(x))
self.response.out.write(json.dumps(output))
def get_restaurants(borough):
s = Session()
q = s.query(Restaurant)
if borough != 0:
q = q.filter(Restaurant.borough == borough)
ret = serialize(q.all())
resp = jsonify(ret)
resp.headers['Access-Control-Allow-Origin'] = '*'
return resp
def get_inspection():
ids = request.args.get('ids').split(',')
s = Session()
ret = serialize(
s.query(Inspection).filter(Inspection.restaurant.in_(ids)).order_by(
Inspection.restaurant.asc()))
resp = jsonify(ret)
resp.headers['Access-Control-Allow-Origin'] = '*'
return resp
def get(self, id=None):
try:
if not id:
customers = Customers.return_all()
return jsonify({'customers': customers})
else:
customer = Customers.return_one(id)
return jsonify({'customer': serialize(customer)})
except:
return {'message': 'Something went wrong'}, 500
def post(self):
User = users.get_current_user()
if User:
student = db.GqlQuery("SELECT * FROM Student WHERE user = :1",
User).get()
#logging.warning("going to get credentials")
#credentials = decorator.credentials
#logging.warning("going to refresh credentials")
#credentials.refresh(http=decorator.http())
#logging.warning("refreshed credentials")
postData = json.loads(self.request.body)
logging.warning(postData)
newReminder = models.Reminder(parent=models.student_key(User))
newReminder.type = postData['type']
newReminder.title = postData['title']
newReminder.course_str = postData['course_str']
newReminder.completed = False
newReminder.date = postData['date']
newReminder.start_time = postData['start_time']
newReminder.end_time = postData['end_time']
newReminder.course = postData['course']
newReminder.note = postData['note']
newReminder.id = int(time.time()) # do we need to rand here too?
newReminder.eventid = ""
newReminder.eventseq = -1
newReminder.alert_min = int(postData['alert_min'])
newReminder.deleted = False
newReminder.semester_id = "SP13" # eventually not hardcoded
if postData['add_to_cal'] == True:
newReminder.add_to_cal = True
event = utils.createReminderEvent(postData)
logging.warning(event)
request = service.events().insert(calendarId=student.calID,
body=event)
response = request.execute(http=decorator.http())
if response is not None and response != "":
logging.warning(json.dumps(response))
newReminder.eventid = response["id"]
newReminder.eventseq = response["sequence"]
else:
newReminder.add_to_cal = False
newReminder.put()
logging.warning("added reminder to db")
self.response.out.write(json.dumps(models.serialize(newReminder)))
else:
self.response.out.write("['auth':'fail']")
def post(self):
User = users.get_current_user()
if User:
student = db.GqlQuery("SELECT * FROM Student WHERE user = :1", User).get()
#logging.warning("going to get credentials")
#credentials = decorator.credentials
#logging.warning("going to refresh credentials")
#credentials.refresh(http=decorator.http())
#logging.warning("refreshed credentials")
postData = json.loads(self.request.body)
logging.warning(postData)
newReminder = models.Reminder(parent=models.student_key(User))
newReminder.type = postData['type']
newReminder.title = postData['title']
newReminder.course_str = postData['course_str']
newReminder.completed = False
newReminder.date = postData['date']
newReminder.start_time = postData['start_time']
newReminder.end_time = postData['end_time']
newReminder.course = postData['course']
newReminder.note = postData['note']
newReminder.id = int(time.time()) # do we need to rand here too?
newReminder.eventid = ""
newReminder.eventseq = -1
newReminder.alert_min = int(postData['alert_min'])
newReminder.deleted = False
newReminder.semester_id = "SP13" # eventually not hardcoded
if postData['add_to_cal'] == True :
newReminder.add_to_cal = True
event = utils.createReminderEvent(postData)
logging.warning(event)
request = service.events().insert(calendarId=student.calID, body=event)
response = request.execute(http=decorator.http())
if response is not None and response != "" :
logging.warning(json.dumps(response))
newReminder.eventid = response["id"]
newReminder.eventseq = response["sequence"]
else :
newReminder.add_to_cal = False
newReminder.put()
logging.warning("added reminder to db")
self.response.out.write(json.dumps(models.serialize(newReminder)))
else :
self.response.out.write("['auth':'fail']")
def loginUser(self, User):
isNoob = 0
std_query = db.GqlQuery("SELECT * FROM Student WHERE user = :1", User).fetch(1)
student = {}
if len(std_query) == 0:
try:
url = "https://mcommunity.umich.edu/mcPeopleService/people/" + User.nickname()
result = urlfetch.fetch(url)
info = json.loads(result.content)
studentName = info["person"]["displayName"]
except:
studentName = User.nickname()
student = models.Student(
user=User, courses=[], reminders=[], major="", advisor_email="", name=studentName, calID=""
)
logging.warning(student)
student.put()
isNoob = 1
else:
student = std_query[0]
logging.warning(models.serialize(student))
context = {
"name": student.name,
"email": student.user.email(),
"major": student.major,
"advisor_email": student.advisor_email,
"logoutUrl": users.create_logout_url("/"),
"achievement": 89,
"noob": isNoob,
"admin": users.is_current_user_admin(),
}
self.render_response("index.jade", **context)
# create the courses calendar asynchronously
if student.calID is None or student.calID == "":
logging.warning("student calID is empty")
calendar = utils.createCal()
request = service.calendars().insert(body=calendar)
created_cal = request.execute(http=decorator.http())
student.calID = created_cal["id"]
else:
logging.warning("student cal id already exists, it is %s" % student.calID)
student.put()
def get(self):
User = users.get_current_user()
if User:
courses = db.GqlQuery("SELECT * FROM Course WHERE ANCESTOR IS :1", models.student_key(User))
output = []
for x in courses :
output.append(models.serialize(x))
self.response.out.write(json.dumps(output))
else:
self.response.out.write("['auth':'fail']")
def get(self):
User = users.get_current_user()
if User:
courses = db.GqlQuery("SELECT * FROM Course WHERE ANCESTOR IS :1",
models.student_key(User))
output = []
for x in courses:
output.append(models.serialize(x))
self.response.out.write(json.dumps(output))
else:
self.response.out.write("['auth':'fail']")
def get(self):
User = users.get_current_user()
if User:
reminders = db.GqlQuery("SELECT * FROM Reminder WHERE ANCESTOR IS :1", models.student_key(User))
output = []
for x in reminders :
#if(self.request.get('showAll') == "true" or x.completed == False):
output.append(models.serialize(x))
self.response.out.write(json.dumps(output))
else:
self.response.out.write("['auth':'fail']")
def get(self):
User = users.get_current_user()
if User:
reminders = db.GqlQuery(
"SELECT * FROM Reminder WHERE ANCESTOR IS :1",
models.student_key(User))
output = []
for x in reminders:
#if(self.request.get('showAll') == "true" or x.completed == False):
output.append(models.serialize(x))
self.response.out.write(json.dumps(output))
else:
self.response.out.write("['auth':'fail']")
def post(self):
User = users.get_current_user()
if User:
student = db.GqlQuery("SELECT * FROM Student WHERE user = :1",
User).get()
info = json.loads(self.request.body)
logging.warning(info)
if student == None:
self.response.out.write("student is null")
return
dup_course = db.GqlQuery(
"SELECT * FROM Course WHERE ANCESTOR IS :1 AND id = :2",
models.student_key(User), int(info["courseId"])).get()
if dup_course != None:
logging.warning("duplicate course")
self.response.out.write("already in this course")
return
# create the courses calendar if it doesn't already exist
if student.calID is None or student.calID == "":
logging.warning('student calID is in fact empty')
self.response.out.write(
"student calendar is empty in api, not adding course")
return
else:
logging.warning('student id is something else, it is %s' %
student.calID)
courseInfo = utils.createClassEvent(info)
logging.warning(courseInfo)
event = courseInfo["event"]
request = service.events().insert(calendarId=student.calID,
body=event)
response = request.execute(http=decorator.http())
logging.warning(json.dumps(response))
newCourse = models.Course(parent=models.student_key(User))
newCourse.id = info["courseId"]
newCourse.code = info["code"]
newCourse.number = info["number"]
newCourse.section = info["section"]
newCourse.type = info["type"]
newCourse.title = info["title"]
newCourse.days = courseInfo["days"]
newCourse.start_time = info["start_time"]
newCourse.end_time = info["end_time"]
newCourse.start = info["start"]
newCourse.end = info["end"]
newCourse.location = info["location"]
newCourse.instructor = info["instructor"]
newCourse.prof_email = info["prof_email"]
newCourse.site_link = info["site_link"]
newCourse.eventid = response["id"]
newCourse.eventseq = response["sequence"]
newCourse.semester_id = "SP13" # should not be hardcoded in the future
newCourse.put()
# respond with changes so backbone knows the id
self.response.out.write(json.dumps(models.serialize(newCourse)))
else:
self.response.out.write("['auth':'fail']")
def get(self):
par = sqlparm.parse_args()
name = par.get('name', '')
type = par.get('type', '')
conn = pymysql.connect(host='127.0.0.1',
port=3306,
user='******',
passwd='threaten',
db='threaten')
cursor = conn.cursor()
if type == 'safe_orm1':
# 正确示例1：以下使用sqlalchemy - orm方式，不会存在sql注入【首推荐】
data = serialize(Test.query.filter(Test.name == name).all())
return data
elif type == 'safe_orm2':
# 正确示例2：sqlalchemy - orm方式，不会存在sql注入【首推荐】
data = db.session.query(Test.name,
Test.action).filter(Test.name == name)
elif type == 'safe_api1':
# 正确示例3：api_orm使用预编译【首推荐】
cursor.execute("select name,action from test where name=%s",
(name))
data = cursor.fetchall()
elif type == 'safe_api2':
# 正确示例4：内置函数转义特殊符号
name = pymysql.escape_string(name)
sql = "select name,action from test where name='%s'" % (name)
cursor.execute(sql)
data = cursor.fetchall()
elif type == 'safe_api3':
# 正确示例5：name=:name为预编译模式【首推荐】
stmt = text("SELECT name,action FROM test where name=:name")
# query = db.session.query(Test.name, Test.action).from_statement(stmt).params(name=name) # 用法1-使用db
query = SQLAlchemy().session.query(
Test.name, Test.action).from_statement(stmt).params(
name=name) # 用法2-使用Test类
data = query.all()
elif type == 'safe_api4':
# 正确示例6：自定义函数转义特殊符号
name = self.safe_sql_escape(name)
sql = "select name,action from test where name='%s'" % (name)
cursor.execute(sql)
data = cursor.fetchall()
elif type == 'notsafe_1':
# 错误示例1：拼接用户输入，使用xx和xx' or '1'='1可验证
sql = "select name,action from test where name='%s'" % (name)
cursor.execute(sql)
data = cursor.fetchall()
elif type == 'notsafe_2':
# 错误示例2：拼接用户输入
sql = "select name,action from test where name='" + name + "'"
cursor.execute(sql)
data = cursor.fetchall()
elif type == 'notsafe_3':
# 错误示例3：拼接用户输入
sql = "select name,action from test where name='{0}'".format(name)
cursor.execute(sql)
data = cursor.fetchall()
elif type == 'notsafe_4':
# 错误示例4：拼接用户输入
sql = f"select name,action from test where name='{name}'"
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
conn.close()
return dict(data)
def post(self):
User = users.get_current_user()
if User:
student = db.GqlQuery("SELECT * FROM Student WHERE user = :1", User).get()
info = json.loads(self.request.body)
logging.warning(info)
if student == None :
self.response.out.write("student is null")
return
dup_course = db.GqlQuery("SELECT * FROM Course WHERE ANCESTOR IS :1 AND id = :2",
models.student_key(User), int(info["courseId"])).get()
if dup_course != None :
logging.warning("duplicate course")
self.response.out.write("already in this course")
return
# create the courses calendar if it doesn't already exist
if student.calID is None or student.calID == "":
logging.warning('student calID is in fact empty')
self.response.out.write("student calendar is empty in api, not adding course")
return
else:
logging.warning('student id is something else, it is %s' % student.calID)
courseInfo = utils.createClassEvent(info)
logging.warning(courseInfo)
event = courseInfo["event"]
request = service.events().insert(calendarId=student.calID, body=event)
response = request.execute(http=decorator.http())
logging.warning(json.dumps(response))
newCourse = models.Course(parent=models.student_key(User))
newCourse.id = info["courseId"]
newCourse.code = info["code"]
newCourse.number = info["number"]
newCourse.section = info["section"]
newCourse.type = info["type"]
newCourse.title = info["title"]
newCourse.days = courseInfo["days"]
newCourse.start_time = info["start_time"]
newCourse.end_time = info["end_time"]
newCourse.start = info["start"]
newCourse.end = info["end"]
newCourse.location = info["location"]
newCourse.instructor = info["instructor"]
newCourse.prof_email = info["prof_email"]
newCourse.site_link = info["site_link"]
newCourse.eventid = response["id"]
newCourse.eventseq = response["sequence"]
newCourse.semester_id = "SP13" # should not be hardcoded in the future
newCourse.put()
# respond with changes so backbone knows the id
self.response.out.write(json.dumps(models.serialize(newCourse)))
else:
self.response.out.write("['auth':'fail']")
