def get_extreme_partners():
reviews = session.execute(
"select * from reviews where percentage>=50 and repartner=1 and grade>=3"
)
review = namedtuple('review', reviews.keys())
reviews = [review(*r) for r in reviews.fetchall()]
best = [review.__dict__ for review in reviews]
for review in best:
reviewer = get_name(review['reviewerid'])
partner = get_name(review['partnerid'])
review['reviewerid'] = reviewer
review['partnerid'] = partner
reviews = session.execute(
"select * from reviews where percentage<50 and repartner=0 and grade<3"
)
review = namedtuple('review', reviews.keys())
reviews = [review(*r) for r in reviews.fetchall()]
worst = [review.__dict__ for review in reviews]
for review in worst:
reviewer = get_name(review['reviewerid'])
partner = get_name(review['partnerid'])
review['reviewerid'] = reviewer
review['partnerid'] = partner
return render_template('wall.html', best=best, worst=worst)
def land():
form = ProfileForm(request.form)
if request.method == 'POST' and form.validate():
studentid = form.data['studentid']
name = form.data['name']
gender = form.data['gender']
gradyear = form.data['gradyear']
try:
session.execute(
"insert into students (studentid, name, gender, gradyear) values (:studentid, :name, :gender, :gradyear)",
{
'studentid': studentid,
'name': name,
'gender': gender,
'gradyear': gradyear
})
session.commit()
except exc.SQLAlchemyError:
return render_template(
'error.html',
error='Uh oh - it looks like that RUID is already registered.',
link="/create",
destination='back')
return render_template(
'student.html',
name=name,
ruid=studentid,
reviews=[],
confirmation='Partner has been added to database!')
return render_template('create_profile.html', form=form)
def display_role(searchrole):
get_role_name = session.execute(
"SELECT * FROM roleplayer WHERE name='%s';" % (searchrole))
get_role_namecount = session.execute(
"SELECT * FROM roleplayer WHERE name='%s';" % (searchrole)).scalar()
if get_role_namecount is not None:
return render_template('display_roleplayer.html',
display=get_role_name)
else:
return "role player doesn't exist in our database"
def displayrole_api(searchrole):
get_role_name = session.execute(
"SELECT * FROM roleplayer WHERE name='%s';" % (searchrole))
get_role_name_count = session.execute(
"SELECT * FROM roleplayer WHERE name='%s';" % (searchrole)).scalar()
if get_role_name_count > 0: #if the count is greater than 0, then result exists and extract the movie based on the role player acted columnresult
json_string = {"Role Player": [], "Movies acted": []}
for i in get_role_name:
json_string["Role Player"].append([{
"Name":
i.name,
"Birthyear":
i.birthyear,
"primaryprofession":
i.primaryprofession
}])
get_movies_acted = session.execute(
"SELECT movies_acted FROM roleplayer WHERE name='%s';" %
(searchrole))
for k in get_movies_acted:
res = k[0].split(',')
for i in res:
acted_movies_count = session.execute(
"SELECT * FROM movie1 WHERE movieimdbid='%s';" %
(i)).scalar()
if acted_movies_count > 0:
acted_movies = session.execute(
"SELECT * FROM movie1 WHERE movieimdbid='%s';" %
(i.encode("utf-8")))
for cols in acted_movies:
json_string["Movies acted"].append([{
'Title':
cols.movie_title,
'imdb_id':
cols.movieimdbid,
'Released_on':
cols.movie_releaseyear,
'Ratings':
cols.movie_rating,
'votes':
cols.movie_votes
}])
return jsonify(json_string)
else:
return "role player doesn't exist in our database"
def get_diversity(classID):
women = session.execute(
"select count(studentid) from taken where classID=:val and studentid in (select studentid from students where gender=:gender)",
{
'val': classID,
'gender': 'female'
})
women = int(women.fetchone()[0]) * 1.0
men = session.execute(
"select count(studentid) from taken where classID=:val and studentid in (select studentid from students where gender=:gender)",
{
'val': classID,
'gender': 'male'
})
men = int(men.fetchone()[0]) * 1.0
return (women / (women + men))
def create_post():
data = request.get_json()
user = data['user']
post = data['post']
id_user = check_user(user['login'], user['password'])
if id_user == -1:
return make_response(f'User is not logged in', 401)
query = insert(Post).values(id_user=id_user,
header=post['header'],
text=post['text'])
try:
session.execute(query)
session.commit()
except IntegrityError:
return make_response('Server error', 500)
return make_response('Post created!')
def student_exists(studentid):
result = session.execute("select name from students where studentid=:val",
{
'val': studentid
}).fetchall()
students = list(r for r in result)
student = list(r for r in result)
return students
def register():
data = request.get_json()
query = insert(User).values(
login=data['login'],
password=md5(data['password'].encode('utf-8')).hexdigest())
try:
session.execute(query)
session.commit()
except IntegrityError:
resp = make_response(f'<h2>User {data["login"]} already exists</h2>',
400)
resp.headers['Content-Type'] = 'text/plain'
return resp
resp = make_response(
f'<h2>User {data["login"]} was created successfully</h2>', 200)
resp.headers['Content-Type'] = 'text/plain'
return resp
def get_profs(name):
result = session.execute(
"select classID, prof from taken where prof=:val group by classID",
{'val': name})
profs = list(r for r in result.fetchall())
if len(profs) != 0:
return render_template('results.html', profs=profs)
else:
return redirect(url_for('land'))
def taken_class(studentid, classID):
result = session.execute(
"select studentid from taken where studentid=:val and classID = :classID",
{
'val': studentid,
'classID': classID
}).fetchall()
student = list(r for r in result)
return len(student)
def get_student_profile(id):
reviews = session.execute("select * from reviews where partnerid=:val",
{'val': id})
review = namedtuple('review', reviews.keys())
reviews = [review(*r) for r in reviews.fetchall()]
reviews = [review.__dict__ for review in reviews]
name = session.execute("select name from students where studentid = :val",
{'val': id})
name = name.fetchone()[0]
for review in reviews:
r_name = session.execute(
"select name from students where studentid=:val",
{'val': review['reviewerid']})
review['reviewerid'] = r_name.fetchone()[0]
p_name = session.execute(
"select name from students where studentid=:val",
{'val': review['partnerid']})
review['partnerid'] = p_name.fetchone()[0]
return render_template('student.html', name=name, ruid=id, reviews=reviews)
def displaymovie_api(searchmovie):
get_movie = session.execute(
"SELECT * FROM movie1 WHERE movie_title='%s';" % (searchmovie))
get_no_of_movies = session.execute(
"SELECT * FROM movie1 WHERE movie_title='%s';" %
(searchmovie)).scalar()
json_string = {"Movie details": []}
if get_no_of_movies > 0:
for i in get_movie:
json_string['Movie details'].append([{
'Title': i.movie_title,
'imdb_id': i.movieimdbid,
'Released_on': i.movie_releaseyear,
'Ratings': i.movie_rating,
'votes': i.movie_votes
}])
return jsonify(json_string)
else:
return "Movie doesn't exist in the database"
def get_students(name):
#if request.method == 'POST':
# return redirect(url_for('get_profile'))
result = session.execute("select * from students where name=:val",
{'val': name})
Student = namedtuple('Student', result.keys())
students = [Student(*r) for r in result.fetchall()]
students = [student.__dict__ for student in students]
if len(students) != 0:
return render_template('results.html', students=students)
return redirect(url_for('land'))
def get_average_grade(classID):
average = session.execute(
"select avg (grade) from reviews where classID=:val", {
'val': classID
}).fetchone()[0]
average = round(average, 2)
if average < 2:
return ['F', average]
if average < 3:
return ['C', average]
if average < 4:
return ['B', average]
return ['A', average]
def get_professor_profile(id, course):
reviews = session.execute(
"select * from reviews where prof=:val and classID=:course", {
'val': id,
'course': course
})
review = namedtuple('review', reviews.keys())
reviews = [review(*r) for r in reviews.fetchall()]
reviews = [review.__dict__ for review in reviews]
for review in reviews:
r_name = session.execute(
"select name from students where studentid=:val",
{'val': review['reviewerid']})
review['reviewerid'] = r_name.fetchone()[0]
p_name = session.execute(
"select name from students where studentid=:val",
{'val': review['partnerid']})
review['partnerid'] = p_name.fetchone()[0]
#average_grade = get_average_grade(reviews)
return render_template('prof.html',
reviews=reviews,
prof=id,
course=course)
def get_class(classID):
reviews = session.execute("select * from reviews where classID=:val",
{'val': classID})
review = namedtuple('review', reviews.keys())
reviews = [review(*r) for r in reviews.fetchall()]
reviews = [review.__dict__ for review in reviews]
for review in reviews:
reviewer = get_name(review['reviewerid'])
partner = get_name(review['partnerid'])
review['reviewerid'] = reviewer
review['partnerid'] = partner
average = get_average_grade(classID)
diversity = round(get_diversity(classID), 4) * 100
#best = get_extreme_partners(classID, True)
#worst = get_extreme_partners(classID, False)
return render_template('class.html',
diversity=diversity,
classID=classID,
average=average,
reviews=reviews)
def get_name(studentid):
name = session.execute("select name from students where studentid=:val", {
'val': studentid
}).fetchone()[0]
return name
查询主键为 10004 的员工的所有年薪，需 Employees，Salaries 两个表联合查询。
结果是： 返回字段为 emp_no, birth_date, first_name, last_name, gender, hire_date, times, salary
'''
'''使用 sql 语句方式进行查询'''
sql = """
SELECT
emp.*, CONCAT_WS('--', s.from_date, s.to_date) AS 'times',
s.salary
FROM
employees emp
JOIN salaries s ON emp.emp_no = s.emp_no
WHERE
emp.emp_no = 10004
"""
sql_data = [(d.emp_no, d.birth_date, d.first_name, d.last_name, d.gender,
d.hire_date, d.times, d.salary) for d in session.execute(sql)]
'''使用 sqlalchemy 方式进行查询'''
alchemy_data = session.query(Employee.emp_no, Employee.birth_date, Employee.first_name,
Employee.last_name, Employee.gender, Employee.hire_date,
func.concat_ws('--', Salary.from_date, Salary.to_date).label('times'), Salary.salary).\
filter(Employee.emp_no==10004, Salary.emp_no==10004).all()
'''比较两个结果，应该是True'''
for d in zip(sql_data, alchemy_data):
print(d)
print('第一例结果是：{}'.format(operator.eq(sql_data, alchemy_data)))
'''-------------------------------------------------------------------------------------------------'''
'''----------------------------------------------第二例-----------------------------------------------
功能说明：
查询主键为 10004 的员工的所有年薪，需 Employees，Salaries，Title 三个表联合查询。
结果是： 返回字段为 emp_no, birth_date, first_name, last_name, gender, hire_date,
title(新增字段，需联表 Title), times， salary
# -*- coding:utf-8 -*-
__author__ = '东方鹗'
__blog__ = 'http://www.os373.cn'
from models import session, Employee, Department, DeptEmp, DeptManager, Salary, Title
import operator
from sqlalchemy import case
'''----------------------------------------------第一例-----------------------------------------------
功能说明：
使用主键对 employees 表进行查询，结果是： 返回该主键对应的单条数据！
'''
'''使用 sql 语句方式进行查询'''
sql = "select * from employees where emp_no = 10006"
sql_data = [(d.emp_no, d.birth_date, d.first_name, d.last_name, d.gender,
d.hire_date) for d in session.execute(sql)]
'''使用 sqlalchemy 方式进行查询'''
d = session.query(Employee).get(10006)
alchemy_data = [(d.emp_no, d.birth_date, d.first_name, d.last_name, d.gender,
d.hire_date)]
'''比较两个结果，应该是True'''
for d in zip(sql_data, alchemy_data):
print(d)
print('第一例结果是：{}'.format(operator.eq(sql_data, alchemy_data)))
'''-------------------------------------------------------------------------------------------------'''
'''-------------------------------------------第二例--------------------------------------------------
功能说明：
对 employees 表进行查询，结果是：从第 4 行开始查询，返回之后的 10 行数据！值为一个列表。
'''
'''使用 sql 语句方式进行查询'''
sql = "select * from employees limit 10 offset 4"
sql_data = [(d.emp_no, d.birth_date, d.first_name, d.last_name, d.gender,
import os, shutil
from config import UPLOAD_FOLDER, FACES_FOLDER, EMBEDDINGS_FOLDER
from models import session
def clean_folder(folder):
for file in os.listdir(folder):
file_path = os.path.join(folder, file)
try:
if os.path.isfile(file_path):
os.remove(file_path)
except Exception as e:
print(e)
for folder in [UPLOAD_FOLDER, FACES_FOLDER, EMBEDDINGS_FOLDER]:
clean_folder(folder)
session.execute("DROP TABLE customers;")
session.commit()
def get_goods_parameters_selenium():
query_time_limit = 'select * from ' \
'(select distinct on (tasks.id) tasks.id, tasks.link, tasks.update_time, ' \
'results.task_id_id, results.datetime ' \
'from mainapp_parsingtasks as tasks ' \
'left join mainapp_parsingresults as results ' \
'on results.task_id_id = tasks.id order by tasks.id, results.task_id_id, results.datetime desc) ' \
'as final_table ' \
'where now() - (final_table.update_time * INTERVAL \'1 hour\') ' \
'> final_table.datetime or final_table.datetime is Null'
logger.info('Browser started')
default_settings = get_default_settings()
profile = webdriver.FirefoxProfile(default_settings.firefox_profile)
browser = webdriver.Firefox(profile)
while True:
print('Run iteration...')
logger.info('Run iteration...')
default_settings.start_iteration_time = datetime.now()
default_settings.last_ping_time = datetime.now()
session.commit()
distinct_tasks = session.execute(query_time_limit)
for dict_task in distinct_tasks:
try:
browser.get(dict_task[1])
logger.info(f'Try get {dict_task[1]}')
result_dict = {}
title = browser.find_element_by_class_name('product-title')
result_dict['title'] = title.text
price = browser.find_element_by_class_name('product-price-value')
result_dict['price'] = price.text
quantity = browser.find_element_by_class_name('product-quantity-tip')
result_dict['quantity'] = quantity.text
rating = browser.find_element_by_class_name('overview-rating-average')
result_dict['rating'] = rating.text
reviews = browser.find_element_by_class_name('product-reviewer-reviews')
result_dict['reviews'] = reviews.text
solds = browser.find_element_by_class_name('product-reviewer-sold')
result_dict['solds'] = solds.text
price_mark = browser.find_element_by_class_name('product-price-mark')
result_dict['price_mark'] = price_mark.text
shipping_price = browser.find_element_by_class_name('product-shipping-price')
result_dict['shipping_price'] = shipping_price.text
shipping_info = browser.find_element_by_class_name('product-shipping-info')
result_dict['shipping_info'] = shipping_info.text
img_ = browser.find_element_by_class_name('magnifier-image')
result_dict['img_'] = img_.get_attribute('src')
json_result = json.dumps(result_dict)
new_result_record = ParsingResults()
new_result_record.status = 1
new_result_record.datetime = datetime.now()
new_result_record.task_id_id = dict_task[0]
new_result_record.json = json_result
session.add(new_result_record)
default_settings.last_ping_time = datetime.now()
session.commit()
except Exception as e:
print(f'Failed parse this {dict_task[1]}\n{e}')
logger.error(f'Failed parse this {dict_task[1]}\n{e}')
print('Sleeping time...')
default_settings.finish_iteration_time = datetime.now()
session.commit()
time.sleep(60 * default_settings.sleeping_time)
def tagSearch(tags,
limit=None,
page=None,
service=None,
sort=None,
dd_enabled=False):
'''
Search for a list of tags in database using string LIKE method
relies on pipe delimited tag list and string search. Not as optimized
as I would like but linear worst case so maybe ok for our small
local server.
'''
if service == 'archive':
if not tags:
if sort == "created-desc":
results = session.query(Posts).order_by(
desc('created_at')).limit(limit).offset(limit *
page).all()
elif sort == "created-asc":
results = session.query(Posts).order_by('created_at').limit(
limit).offset(limit * page).all()
elif sort == "score-desc":
results = session.query(Posts).order_by(
desc('score')).limit(limit).offset(limit * page).all()
elif sort == "score-asc":
results = session.query(Posts).order_by('score').limit(
limit).offset(limit * page).all()
elif sort == "views-desc":
results = session.query(Posts).order_by(
desc('views')).limit(limit).offset(limit * page).all()
elif sort == "views-asc":
results = session.query(Posts).order_by('views').limit(
limit).offset(limit * page).all()
else:
results = session.query(Posts).order_by(
desc('id')).limit(limit).offset(limit * page).all()
return [post.as_dict() for post in results]
else:
tag_query = ''
tag_field = 'dd_tags' if dd_enabled else 'tags'
order_clause = ''
# giant if clause for ordering fix later
if sort == "created-desc":
order_clause = "ORDER BY date(created_at) desc"
elif sort == "created-asc":
order_clause = "ORDER BY date(created_at)"
elif sort == "score-desc":
order_clause = "ORDER BY score desc"
elif sort == "score-asc":
order_clause = "ORDER BY score asc"
elif sort == "views-desc":
order_clause = "ORDER BY views desc"
elif sort == "views-asc":
order_clause = "ORDER BY views"
for i, tag in enumerate(tags):
if i == 0:
tag_query += '''{} LIKE '%|{}|%' '''.format(tag_field, tag)
else:
tag_query += '''AND {} LIKE '%|{}|%' '''.format(
tag_field, tag)
query = '''SELECT * FROM posts
WHERE {}
{}
LIMIT {} OFFSET {}
'''.format(tag_query, order_clause, limit,
limit * page)
results = session.execute(query)
results = [Posts.from_tuple(r) for r in results]
return [post.as_dict() for post in results]
elif service == 'gelbooru':
post_json = gelbooru_api.get_posts(tags=tags, limit=limit, page=page)
post_json = gelbooru_api.download_thumbnails(posts=post_json)
return post_json
elif service == 'yandere':
post_json = yandere_api.get_posts(tags=tags, limit=limit, page=page)
post_json = yandere_api.download_thumbnails(posts=post_json)
return post_json
elif service == 'konachan':
post_json = konachan_api.get_posts(tags=tags, limit=limit, page=page)
post_json = konachan_api.download_thumbnails(posts=post_json)
return post_json
elif service == 'lolibooru':
post_json = lolibooru_api.get_posts(tags=tags, limit=limit, page=page)
post_json = lolibooru_api.download_thumbnails(posts=post_json)
return post_json
elif service == 'safebooru':
post_json = safebooru_api.get_posts(tags=tags, limit=limit, page=page)
post_json = safebooru_api.download_thumbnails(posts=post_json)
return post_json
else:
raise Exception("undefined service type")
def review():
review_form = ReviewForm(request.form)
profile_form = ProfileForm()
if request.method == 'POST' and review_form.validate():
reviewerid = review_form['reviewerid']
partnerid = review_form['partnerid']
reviewerid = int(reviewerid.raw_data[0])
partnerid = int(partnerid.raw_data[0])
classID = review_form['classID']
classID = int(classID.raw_data[0])
percentage = review_form['percentage']
percentage = int(percentage.raw_data[0])
repartner = review_form['repartner']
repartner = int(repartner.raw_data[0])
prof = review_form['professorid']
prof = str(prof.raw_data[0])
grade = review_form['grade']
grade = int(grade.raw_data[0]) / 25
#make sure that the reviewer has a profile - if they don't make them make one
if len(student_exists(reviewerid)) == 0:
return render_template(
'create_profile.html',
form=profile_form,
error=
'No fair reviewing a friend if you don\'t have your own profile! Make one before you proceed.'
)
if len(student_exists(partnerid)) == 0:
return render_template(
'create_profile.html',
form=profile_form,
error=
'That person\'s not registered yet. Make a profile for them before you review!'
)
#make sure that both are listed as having taken the class
if taken_class(reviewerid, classID) == 0:
session.execute(
"insert into taken(classID, studentID) values (:classID, :reviewerid)",
{
'reviewerid': reviewerid,
'classID': classID
})
if taken_class(partnerid, classID) == 0:
session.execute(
"insert into taken(classID, studentID) values (:classID, :partnerid)",
{
'partnerid': partnerid,
'classID': classID
})
try:
session.execute(
"insert into reviews (classID, reviewerid, partnerid, grade, percentage, repartner, prof) values (:classID, :reviewerid, :partnerid, :grade, :percentage, :repartner, :prof)",
{
'classID': classID,
'reviewerid': reviewerid,
'partnerid': partnerid,
'grade': grade,
'percentage': percentage,
'repartner': repartner,
'prof': prof
})
session.commit()
return redirect(url_for('get_student_profile', id=partnerid))
except exc.SQLAlchemyError:
return render_template(
'error.html',
error=
'Hey now - you can\'t take a class more than once. You or your partner is already registered.',
link="/review",
destination='back')
return render_template('create_review.html', form=review_form)
dm.from_date,
dm.to_date,
dp.dept_name,
t.title,
t.from_date,
t.to_date,
e.birth_date,
e.first_name,
e.last_name,
e.gender,
e.hire_date
"""
sql_data = [
(d.dept_no, d.dept_name, d.title, d.t_from_date, d.t_to_date, d.birth_date,
d.first_name, d.last_name, d.gender, d.hire_date, d.from_date, d.to_date)
for d in session.execute(sql)
]
'''使用 sqlalchemy 方式进行查询'''
alchemy_data = session.query(Department.dept_no, Department.dept_name, Title.title,
Title.from_date.label("t_from_date"), Title.to_date.label("t_to_date"),
Employee.birth_date, Employee.first_name, Employee.last_name, Employee.gender,
Employee.hire_date, DeptManager.from_date, DeptManager.to_date).\
join(DeptManager, Department.dept_no==DeptManager.dept_no).\
join(Employee, Employee.emp_no==DeptManager.emp_no).\
join(Title, and_(Title.emp_no==Employee.emp_no,
Title.from_date==DeptManager.from_date,
Title.to_date==DeptManager.to_date)).\
group_by(Department.dept_no, DeptManager.from_date, DeptManager.to_date, Department.dept_name,
Title.title, Title.from_date, Title.to_date, Employee.birth_date,
Employee.first_name, Employee.last_name, Employee.gender, Employee.hire_date).all()
'''比较两个结果，应该是True'''
print first.head_teacher.id
print first.head_teacher.subject
def update_test():
tom = session.query(Teacher).get(1) # not get(id=1)
tom.subject = 'Math'
session.commit()
def delete_test():
tom = session.query(Teacher).get(1)
print tom.students
session.delete(tom)
# lucy = session.query(Student).get(1)
# session.delete(lucy)
session.commit()
print session.query(Teacher).all()
print session.query(Student).all()
if __name__ == '__main__':
insert_test()
query_test()
update_test()
query_test()
delete_test()
session.execute('drop table student, teacher;') # need to commit
# Student.drop_table() # not working, make this file running forever!!!
session.commit()
