def createApplication(manifestText, manifestSrc, manifestObj = None):
try:
if not manifestObj:
manifestObj = json.loads(manifestSrc)
name = manifestObj['name']
baseURL = manifestObj['base_url']
icon96 = None
if 'icons' in manifestObj:
if '96' in manifestObj['icons']:
icon96 = manifestObj['icons']['96']
else:
key = manifestObj['icons'].keys()[0]
icon96 = manifestObj['icons'][key]
description = manifestObj['description']
a = Application(name, manifestSrc, manifestText, baseURL, datetime.now(), icon96, description)
session.add(a)
session.commit()
return a
except sqlalchemy.exc.IntegrityError:
session.rollback()
raise ValueError("An application is already registered for that launch URL.")
except Exception, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to create application")
def make_user(login_session):
new_user = User(name=login_session['username'],
email=login_session['email'])
session.add(new_user)
session.commit()
user = session.query(User).filter_by(email=login_session['email']).one()
return user.id
def createApplication(manifestText, manifestSrc, manifestObj=None):
try:
if not manifestObj:
manifestObj = json.loads(manifestSrc)
name = manifestObj["name"]
baseURL = manifestObj["base_url"]
icon96 = None
if "icons" in manifestObj:
if "96" in manifestObj["icons"]:
icon96 = manifestObj["icons"]["96"]
else:
key = manifestObj["icons"].keys()[0]
icon96 = manifestObj["icons"][key]
description = manifestObj["description"]
a = Application(name, manifestSrc, manifestText, baseURL, datetime.now(), icon96, description)
session.add(a)
session.commit()
return a
except sqlalchemy.exc.IntegrityError:
session.rollback()
raise ValueError("An application is already registered for that launch URL.")
except Exception, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to create application")
def post_data(data):
check = session.query(User).filter_by(
public_display_name=data['public_display_name'])
if check == None:
raise Exception(
'This username is in use. Please, enter another username')
else:
public_display_name = data['public_display_name']
last_user = session.query(User).order_by(User.id.desc()).first()
session.add(
User(first_name=data["first_name"],
last_name=data['last_name'],
public_display_name=public_display_name,
about_me=data['about_me'],
profile_link=data['profile_link'],
email=data['email'],
password=data['password']))
session.commit()
last_user = session.query(User).order_by(User.id.desc()).first()
session.add(
PurchaseSettings(id=last_user.id,
credit_card='',
id_user=last_user.id,
id_plan=1))
session.commit()
def update_user():
form = UpdateUserForm(request.form)
if form.validate():
uid = form.uid.data
name = form.name.data
email = form.email.data
phone = form.phone.data
is_super = form.is_super.data
user = db_session.query(Admin).filter(Admin.admin_uuid == uid).first()
required_phone = db_session.query(Admin).filter(
Admin.admin_phone == phone)[:]
required_email = db_session.query(Admin).filter(
Admin.admin_email == email)[:]
if len(required_phone) > 1: # 判断是否本人的手机号码
return '该手机号已被使用'
elif len(required_email) > 1:
return '该邮箱已被使用'
elif len(required_phone) == 1 and user.admin_phone != phone:
return '该手机号已被使用'
elif len(required_email) == 1 and user.admin_email != email:
return '该邮箱已被使用'
else:
user.admin_name = name
user.admin_email = email
user.admin_phone = phone
user.admin_is_super = is_super
db_session.commit()
return "success"
else:
errors = form.errors
error = errors.popitem()[1][0]
return error
def todo(self):
# met les devoirs dans ToDo
self.connectTodo()
# charge le fichier avec les devoirs deja ajouté
lineDev = session.query(Config).one()
if lineDev.devoirs != None:
devoirs = json.loads(lineDev.devoirs)
else:
devoirs = []
if self.client.logged_in and self.todo_client and self.listeToDo:
# récupère la liste todo dans laquelle il faut mettre les devoirs
lists = self.todo_client.get_lists()
task_list = None
for list in lists:
if list.displayName == self.listeToDo:
task_list = list
break
if task_list == None:
self.todo_client.create_list(name=self.listeToDo)
# récupére tous les devoirs pour les 30 prochains jours
homeworks = self.client.homework(date.today(),
date.today() + timedelta(days=30))
for homework in homeworks:
# création de l'id
id = homework.description[0:10] + \
homework.date.strftime("%Y-%m-%dT%H:%M:%S")
# si le devoir n'est pas fini est qu'il n'est pas encore dans todo on l'ajoute
if homework.done == False and id not in devoirs:
self.todo_client.create_task(
title=
f'{homework.subject.name[0:5]} {homework.description[0:100]}',
list_id=task_list.list_id,
due_date=homework.date,
body_text=
f'{homework.subject.name} : {homework.description}')
devoirs.append(id)
# Si il est fait on le coche sur todo
else:
tasks = self.todo_client.get_tasks(
list_id=task_list.list_id, status='notCompleted')
for task in tasks:
if task.dueDateTime == homework.date and task.body == homework.description:
self.todo_client.complete_task(
task_id=task.task_id,
list_id=task_list.list_id)
break
# récupére tous les devoirs fait
tasks = self.todo_client.get_tasks(list_id=task_list.list_id,
status='completed')
# on coche le devoirs sur pronote
for task in tasks:
homeworks = self.client.homework(
date.today(),
date.today() + timedelta(days=30))
for homework in homeworks:
if task.dueDateTime == homework.date and task.body == homework.description:
homework.set_done(status=True)
self.line.devoirs = json.dumps(devoirs)
session.commit()
def update_student():
form = UpdateStudentForm(request.form)
if form.validate():
id = form.id.data
name = form.name.data
email = form.email.data
phone = form.phone.data
class_ = form.class_.data
user = db_session.query(Student).filter(
Student.student_id == id).first()
required_phone = db_session.query(Student).filter(
Student.student_phone == phone)[:]
required_email = db_session.query(Student).filter(
Student.student_email == email)[:]
if len(required_phone) > 1: # 判断是否本人的手机号码
return '该手机号已被使用'
elif len(required_email) > 1:
return '该邮箱已被使用'
elif len(required_phone) == 1 and user.student_phone != phone:
return '该手机号已被使用'
elif len(required_email) == 1 and user.student_email != email:
return '该邮箱已被使用'
else:
user.student_name = name
user.student_email = email
user.student_phone = phone
user.student_class = Class(class_name=class_)
db_session.commit()
return "success"
else:
errors = form.errors
error = errors.popitem()[1][0]
return error
def EditItem(item_id):
if 'username' not in login_session:
return redirect(url_for('Login'))
item = session.query(Item).filter_by(id=item_id).one()
if item.user_id != userid_by_email(login_session['email']):
return "You are not the owner of this item."
if request.method == 'GET':
categories = session.query(Category).all()
item_to_edit = session.query(Item).filter_by(id=item_id).one()
return render_template('edititem.html',
categories=categories, item=item_to_edit)
if request.method == 'POST':
item_to_edit = session.query(Item).filter_by(id=item_id).one()
file = request.files['new-item-image']
filename = save_image(file)
add_photo_to_database(filename, item_to_edit)
submitted_category = request.form['category']
item_category = potential_new_category(submitted_category)
item_to_edit.name = request.form['new-item-name']
item_to_edit.description = request.form['new-item-description']
item_to_edit.category = item_category
session.add(item_to_edit)
session.commit()
flash('Item updated.')
return redirect(url_for('MyItems'))
def update(id):
try:
a = session.query(Employee).get(id)
if a != None:
firstname = request.json['firstname']
lastname = request.json['lastname']
# import pdb
# pdb.set_trace()
age = request.json['age']
skills = request.json['skills']
skill_obj = session.query(Skills).filter_by(emp_id=id).first()
if firstname.islower():
firstname = firstname.upper()
if lastname.islower():
lastname = lastname.upper()
a.firstname = firstname
a.lastname = lastname
a.age = age
skill_obj.tech = skills['tech']
session.commit()
updated_user = {
"firstname": firstname,
"lastname": lastname,
"age": age,
"skills": skills["tech"]
}
return updated_user
else:
return ("UserID incorrect")
except Exception as e:
return ("Cannot update entry")
def remove_purchase_for_user_app(userid, appid):
try:
session.query(Purchase).filter(Purchase.user_id == userid).filter(Purchase.app_id == appid).delete()
session.commit()
except Exception, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to remove purchase")
def destroy(self):
try:
session.delete(self)
session.commit()
return True
except IntegrityError, e:
self.errors['SQL'] = e.message
return False
def store(Model, data):
if not len(data):
return
for item in data:
session.execute(Model.__table__.insert().prefix_with('IGNORE'), item)
# i = Model(**item)
# session.add(i)
session.commit()
def actualizar_puntos(self, puntos, userid):
stmt = (
update(User).
where(User.user_id == userid).
values(puntaje = puntos)
)
session.execute(stmt)
session.commit()
def save(obj):
try:
session.add(obj)
session.commit()
except Exception, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to save object")
def save(obj):
try:
session.add(obj)
session.commit()
except Exception, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to save object")
def remove_purchase_for_user_app(userid, appid):
try:
session.query(Purchase).filter(Purchase.user_id == userid).filter(Purchase.app_id == appid).delete()
session.commit()
except Exception, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to remove purchase")
def post(self):
form = AddTestPagerForm(request.form)
if form.validate():
# 接收前台数据
choiceSize = int(request.form["choiceSize"])
choiceScore = int(request.form["choiceScore"])
shortAnswerSize = int(request.form["shortAnswerSize"])
shortAnswerScore = int(request.form["shortAnswerScore"])
programSize = int(request.form["programSize"])
programScore = int(request.form["programScore"])
type = request.form["type"]
name = request.form['name'].strip()
# 试卷的总分等于 =
# 选择器的个数*单个选择题的分值 + 简答题的个数*单个简答题的分值 + 程序设计题的个数*单个程序设计提的分值
totalscore = (choiceSize*choiceScore) + (shortAnswerSize*shortAnswerScore) + (programSize*programScore)
count_choice = db_session.query(Choice).filter(Choice.choice_type==type).count()
count_short_answer = db_session.query(ShortAnswer).filter(ShortAnswer.short_answer_type==type).count()
count_program = db_session.query(Program).filter(Program.program_type==type).count()
# 如果数据库选择题的个数小于需要生成的个数
if count_choice < choiceSize:
# 把所有的题全部抽出来
choices = db_session.query(Choice).all()
message1 = '题库中选择题不够%s个, 现已全部引用' % choiceSize
# 如果需要生成的选择题的个数小于数据库中存储的数量
else:
choices = db_session.query(Choice).order_by(func.rand(Choice.choice_id))[0:choiceSize]
if count_short_answer < shortAnswerSize:
short_answers = db_session.query(ShortAnswer).all()
message2 = '题库中简答题不够%s个, 现已全部引用' % shortAnswerSize
else:
short_answers = db_session.query(ShortAnswer).order_by(func.rand(ShortAnswer.short_answer_id))[0:shortAnswerSize]
if count_program < programSize:
programs = db_session.query(Program).all()
message3 = '题库中简答题不够%s个, 现已全部引用' % programSize
else:
programs = db_session.query(Program).order_by(func.rand(Program.program_id))[0:programSize]
# 创建试卷对象
test_pager = TestPager(test_pager_name=name, test_pager_type=type, test_pager_choice_score=choiceScore,
test_pager_short_answer_score=shortAnswerScore, test_pager_program_score=programScore,
test_pager_choice_num=choiceSize, test_pager_short_answer_num=shortAnswerSize,
test_pager_program_num=programSize, test_pager_total_score=totalscore)
# 选择题，简答题，程序设计题绑定到试卷中
test_pager.test_pager_choices.extend(choices)
test_pager.test_pager_short_answers.extend(short_answers)
test_pager.test_pager_programs.extend(programs)
db_session.add(test_pager)
db_session.commit()
content = {
# 'message1': message1,
# 'message2': message2,
# 'message3': message3,
'totalScore': totalscore
}
return render_template('front/front_add_test_page.html', **content)
else:
message = form.errors.popitem()[1][0]
return render_template('front/front_add_test_page.html', message=message)
def delete_user():
if request.method == 'POST':
uid = request.form['uid']
admin = db_session.query(Admin).filter(Admin.admin_uuid == uid).first()
if admin: # 如果存在该用户
admin.admin_is_delete = True
db_session.commit()
return 'success'
else:
return '不存在该用户'
def potential_new_category(name):
""" Create a new category unless one by that name exists """
category = session.query(Category).filter_by(name=name).one_or_none()
if category:
return category
if category is None:
category = Category(name=name)
session.add(category)
session.commit()
return category
def update_program():
form = request.form
try:
program = db_session.query(Program).filter(Program.program_id==form.get('id')).first()
program.program_content = form.get("content")
program.program_result = form.get("result")
db_session.commit()
return 'success'
except Exception as err:
return 'fail'
def post(self):
params = self.reqparse.parse_args()
new_event = None
if session.query(Event).filter_by(event_key = params['eventKey']).first() is None:
new_event = Event(params['eventKey'], params['name'])
session.add(new_event)
else:
abort(400, 'event already exists!')
session.commit()
return jsonify({"eventAdded": new_event.as_dict()})
def update_short_answer():
form = request.form
try:
short_answer = db_session.query(ShortAnswer).filter(ShortAnswer.short_answer_id==form.get('id')).first()
short_answer.short_answer_content = form.get("content")
short_answer.short_answer_result = form.get("result")
db_session.commit()
return 'success'
except Exception as err:
return 'fail'
def opt_out_foreigners():
from constant import foreign_teams
f_team_query = session.query(Team.team_id).filter(
Team.team_name.in_(foreign_teams)).all()
f_team_ids = [t.team_id for t in f_team_query]
# リストでフィルターをかけているが、deleteの引数synchronize_sessionのデフォルト値'evaluate'ではこれをサポートしていない(らしい)からFalseを指定する
count = session.query(Record).filter(
Record.team_id.in_(f_team_ids)).delete(synchronize_session=False)
session.commit()
notify_line(f'{len(f_team_ids)}件の外国籍チームを検出。{count}件の記録を削除')
def createPurchaseForUserApp(uid, appid):
try:
p = Purchase(uid, appid, datetime.now())
session.add(p)
session.commit()
return p
except Exception, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to create purchase")
def add_records_wrapper(date_min, date_max):
target_meets = session.query(Meet.meet_id).filter(
Meet.start >= date_min,
Meet.start <= date_max).order_by(Meet.start).all()
target_meets_ids = [m.meet_id for m in target_meets]
if not_up_to_date := imperfect_meets(target_meets_ids):
count = session.query(Record).filter(
Record.meet_id.in_(not_up_to_date)).delete(
synchronize_session=False)
session.commit()
notify_line(f'大会ID:{not_up_to_date}、記録未納の可能性あり。{count}件の記録を削除')
def addIdentity(uid, identifier, displayName, email):
try:
id = Identity(uid, identifier, displayName, email, datetime.now())
session.add(id)
session.commit()
return id
except sqlalchemy.exc.IntegrityError, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to create identity")
def createUser():
try:
u = User()
session.add(u)
session.commit()
return u
except sqlalchemy.exc.IntegrityError, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to create user")
def createCategory(name, parent=None):
try:
if parent and (len(parent) == 0 or parent == 0):
parent = None
c = Category(name, parent=parent)
session.add(c)
session.commit()
return c
except Exception, e:
session.rollback()
raise ValueError("Error while creating category: %s" % e)
def delete_teacher():
if request.method == 'POST':
id = request.form['id']
teacher = db_session.query(Teacher).filter(
Teacher.teacher_id == id).first()
if teacher: # 如果存在该用户
teacher.teacher_is_delete = True
db_session.commit()
return 'success'
else:
return '不存在该用户'
def save(self):
self.before_save()
try:
if 'SQL' in self.errors: del self.errors['SQL']
if len(self.errors) > 0: return False
session.add(self)
session.commit()
return True
except IntegrityError, e:
self.errors['SQL'] = e.message
return False
def addIdentity(uid, identifier, displayName, email):
try:
id = Identity(uid, identifier, displayName, email, datetime.now())
session.add(id)
session.commit()
return id
except sqlalchemy.exc.IntegrityError, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to create identity")
def createPurchaseForUserApp(uid, appid):
try:
p = Purchase(uid, appid, datetime.now())
session.add(p)
session.commit()
return p
except Exception, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to create purchase")
def createCategory(name, parent=None):
try:
if parent and (len(parent) == 0 or parent == 0):
parent = None
c = Category(name, parent=parent)
session.add(c)
session.commit()
return c
except Exception, e:
session.rollback()
raise ValueError("Error while creating category: %s" % e)
def createUser():
try:
u = User()
session.add(u)
session.commit()
return u
except sqlalchemy.exc.IntegrityError, e:
logging.exception(e)
session.rollback()
raise ValueError("Unable to create user")
def delete_student():
if request.method == 'POST':
id = request.form['id']
student = db_session.query(Student).filter(
Student.student_id == id).first()
if student: # 如果存在该用户
student.student_is_delete = True
db_session.commit()
return 'success'
else:
return '不存在该用户'
def delete_user(id):
try:
user = session.query(Employee).get(id)
user2 = session.query(Skills).filter_by(emp_id=id).first()
#if user user2 !=None:
session.delete(user)
session.delete(user2)
session.commit()
final = {"firstname": user.firstname, "lastname": user.lastname}
return final
except Exception as e:
return e
session.rollback()
def initialize_stats_table():
session.query(Stats).delete()
session.commit()
for pool in [0, 1]:
for sex in [1, 2]:
for style_and_distance in [
11, 12, 13, 14, 15, 16, 17, 21, 22, 23, 24, 31, 32, 33, 34,
41, 42, 43, 44, 53, 54, 55, 63, 64, 65, 66, 73, 74, 75
]:
for grade in range(20):
event = sex * 100 + style_and_distance
session.add(Stats(pool=pool, event=event, grade=grade))
session.commit()
def post(self):
form = AddShortAnswerForm(request.form)
if form.validate():
type = form.type.data
content = form.content.data
result = form.result.data
short_answer = ShortAnswer(short_answer_type=type, short_answer_content=content, short_answer_result=result)
db_session.add(short_answer)
db_session.commit()
return self.__render('success')
else:
message = form.errors.popitem()[1][0]
return self.__render(message=message)
def post(self):
form = AddProgramForm(request.form)
if form.validate():
type = form.type.data
content = form.content.data
result = form.result.data
program = Program(program_type=type, program_content=content, program_result=result)
db_session.add(program)
db_session.commit()
return self.__render('success')
else:
message = form.errors.popitem()[1][0]
return self.__render(message=message)
def run(self):
parser = argparse.ArgumentParser(
description='a sms console helper to manage sms'
)
parser.add_argument('--create', '-c',
action='store_true',
help='create a new database')
parser.add_argument('--list-messages', '-l',
action='store_true',
help='list all messages')
parser.add_argument('--update-contact', '-u',
action='store_true',
help='update sms that contact is none')
parser.add_argument('--import-best-message-storer', '-B',
type=argparse.FileType('r'),
metavar='FILE',
nargs='+',
help='import messages from Best MessageStorer backups')
parser.add_argument('--import-sms-backup-and-restore', '-S',
type=argparse.FileType('r'),
metavar='FILE',
nargs='+',
help='import messages from Sms Backup & Restore')
args = parser.parse_args()
if args.create:
self.create_all()
elif args.list_messages:
self.list_messages()
elif args.update_contact:
update_sms()
elif args.import_best_message_storer != None:
for text in args.import_best_message_storer:
best_message_storer_parser = Best_message_storer_parser(
text.read().decode('utf-16le')
)
smses = best_message_storer_parser.parse()
session.add_all(smses)
session.commit()
best_message_storer_parser.print_result()
elif not args.import_sms_backup_and_restore is None:
for xml in args.import_sms_backup_and_restore:
sms_backup_restore_parser = SmsBackupAndRestoreParser(
xml.read().decode('utf-8')
)
smses = sms_backup_restore_parser.parse()
session.add_all(smses)
session.commit()
sms_backup_restore_parser.print_result()
else:
parser.print_help()
def update_sms():
"""更新短信联系人为空的短信"""
number_rows = session.execute("""
select distinct number, phone_id, contact_id
from sms
where contact_id is null;""")
for row in number_rows.fetchall():
number = row['number']
sms = session.query(Sms).filter(
and_(Sms.number.like('%' + number), Sms.contact_id != None)
).first()
if sms is not None:
session.execute(
"""
update sms
set contact_id = :contact_id, phone_id = :phone_id
where number =:number;""",
{'contact_id': sms.contact_id,
'phone_id': sms.phone_id,
'number': sms.number}
)
session.commit()
def scan(self):
# init scan model
scan = Scan()
scan.scan_started_at = datetime.now()
scan.scan_by = User.current_user()
for key, value in self.selectMapper.iteritems():
for plug in value['plugins']:
options = getFormValue(plug.options)
for ret in plug.execute(options):
finding = Finding(**ret)
if not finding.name:
finding.name = plug.name
finding.scan = scan
finding.plugin_name = plug.name
finding.plugin_group = plug.group
finding.plugin_options = str(options)
scan.findings.append(finding)
scan.scan_finished_at = datetime.now()
try:
session.add(scan)
session.add_all(scan.findings)
session.commit()
except IntegrityError, e:
QMessageBox.information(self, u"SQL错误", u"扫描结果储存失败\n" + e.message)
def save_sms(self, type, name, number, content, create_at):
sms = session.query(Sms).filter_by(number=number,
create_at=create_at).first()
if not sms is None: # 重复的短信
raise DuplicateError
sms = Sms()
sms.create_at = create_at
sms.number = number
sms.type = type
sms.content = content
if not name is None:
name = name.replace(' ', '')
name = name.replace('!', '')
name = name.replace(u'！', '')
if not name is None: # 保存联系人
contact_phone_items = session.query(Contact, Phone). \
filter(Contact.id == Phone.contact_id). \
filter(Phone.number == number).all()
if len(contact_phone_items) > 0: # 已有联系人
contact, phone = contact_phone_items[0]
else: # 新建联系人
contact = session.query(Contact).filter_by(name=name).first()
if contact is None:
contact = Contact(name=name)
session.add(contact)
phone = Phone(number)
phone.contact = contact
contact.adjust_name()
session.commit()
sms.contact = contact
sms.phone = phone
self.smses.append(sms)
for li in ul:
try:
data['name'] = li.xpath(".//a/text()")[0].strip()
except Exception, e:
data['name'] = ""
try:
data['url'] = li.xpath(".//a/@href")[0].strip()
except Exception, e:
data['url'] = ""
try:
sg = SegmentFault(**data)
session.add(sg)
session.commit()
spider_logger.info("Data: INSERT SUCCESS")
except Exception, e:
spider_logger.error("Data: INSERT ERROR: %s" % str(e))
return data
def douban_movie_parse(html_content):
if html_content == '':
return ""
else:
try:
dom = fromstring(html_content)
except Exception, e:
spider_logger.error("Function: parse_data, INFO: %s" % str(e))
def save(self):
session.commit()