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()