def get_all_probe(): try: gender = request.json['gender'] department = request.json['department'] # common in all case base_query = ''' SELECT COUNT(*) AS `total_rec` FROM employee e LEFT JOIN department d on e.dept_id = d.id ''' if gender == 'all' and department == 'all': query = base_query result = select_all(query, []) elif gender != 'all' and department == 'all': query = base_query + " where e.gender = %s" result = select_all(query, [gender]) elif gender == 'all' and department != 'all': query = base_query + " where e.dept_id = %s" result = select_all(query, [department]) else: query = base_query + " where e.dept_id = %s and e.gender = %s" result = select_all(query, [department, gender]) return jsonify(result) except Exception: return jsonify({'result': 'failure'})
def get_all_blogs(): if request.method == 'GET': # query = 'SELECT * FROM `blog`' query = '''SELECT blog.`blog_id`, blog.`heading`, blog.`body`, user.`name` FROM `blog` LEFT JOIN `user` ON blog.`user_id` = user.`user_id` ''' result = select_all(query, []) return jsonify(result) elif request.method == 'POST': n = int(request.json['n']) query = "SELECT * FROM `blog` ORDER BY `created_on` LIMIT %s" result = select_all(query, [n]) return jsonify(result)
def get_comments_by_blog_id(blog_id): # query = 'SELECT `comment` FROM `comment` WHERE `blog_id` = %s' query = """SELECT comment.comment, user.name FROM comment LEFT JOIN user ON comment.user_id = user.user_id WHERE comment.`blog_id` = %s """ result = select_all(query, [blog_id]) return jsonify(result)
def fetch_subject(): try: query = "SELECT `subject_name`, `subject_uuid` FROM `subject`" return select_all(query, []) except Exception as ex: print(ex) return jsonify({'result': 'failure'})
def author_select(): try: query = '''SELECT * FROM `authors`''' result = select_all(query, []) return jsonify(result) except Exception: return jsonify({'result': 'failure'})
def fetch_classes(): try: query = "SELECT `class_name`, `class_uuid` FROM `classes`" return select_all(query, []) except Exception as ex: print(ex) return jsonify({'result': 'failure'})
def book_fetch_searched(): try: searchParam = request.json['searchParam'] query = ''' SELECT * FROM (SELECT b.book_id, b.book_title,p.name as publisher, GROUP_CONCAT(DISTINCT a.name) AS authors, GROUP_CONCAT(DISTINCT c.name) AS categories FROM ((SELECT ba.book_id, ba.author_id, bc.category_id FROM book_author ba LEFT JOIN book_category bc ON ba.book_id = bc.book_id) as bac RIGHT JOIN books b ON b.book_id = bac.book_id LEFT JOIN authors a on a.id = bac.author_id LEFT JOIN category c on c.id = bac.category_id LEFT JOIN publishers p ON p.id = b.publisher_id) GROUP BY b.book_id) AS temp WHERE book_title LIKE '%{0}%' OR authors LIKE '%{0}%' OR categories LIKE '%{0}%' OR publisher LIKE '%{0}%' '''.format(searchParam) result = select_all(query) return jsonify(result) except Exception as ex: print(ex) return jsonify({'result': 'failure'})
def category_select(): try: query = '''SELECT * FROM `category`''' result = select_all(query, []) return jsonify(result) except Exception: return jsonify({'result': 'failure'})
def view(): db = sqlite3.connect("contacts.db") t = PrettyTable(['Name', 'Contact Number']) allContacts = select_all(db, "contact") for i in allContacts: t.add_row([i[0],i[1]]) print(t) t.clear() db.close()
def fetch_teacher(): try: query = """ SELECT t.teacher_name, c.class_name, s.section_name, sub.subject_name FROM teachers t LEFT JOIN classes c on t.class_uuid = c.class_uuid LEFT JOIN section s ON t.section_uuid = s.section_uuid LEFT JOIN subject sub on t.subject_uuid = sub.subject_uuid; """ return jsonify(select_all(query, [])) except Exception as ex: return jsonify({'result': 'failure'})
def summary(): try: user_id = request.json['user_id'] query = '''select tl.tasklist_id, tl.name, count(t.task_id) as count from tasklists tl left join task t on tl.tasklist_id = t.tasklist_id where tl.user_id = %s group by tl.tasklist_id, tl.name ''' arguments = [user_id] result = select_all(query, arguments) return jsonify(result) except Exception: return jsonify({'result': 'failure'})
def get_all_records(): try: gender = request.json['gender'] department = request.json['department'] sal_order = request.json['sal_order'] limit_beg = request.json['limit_beg'] limit_end = request.json['limit_end'] # common in all case base_query = ''' SELECT LPAD(e.id, 4, "0") as id, e.emp_uuid, e.emp_name, e.email, e.gender, e.salary, d.dept_name FROM employee e LEFT JOIN department d on e.dept_id = d.id ''' if sal_order == 'asc': temp_qry = 'ORDER BY salary ASC LIMIT {}, {}'.format( limit_beg, limit_end) else: temp_qry = 'ORDER BY salary DESC LIMIT {}, {}'.format( limit_beg, limit_end) if gender == 'all' and department == 'all': query = base_query + temp_qry result = select_all(query, []) elif gender != 'all' and department == 'all': query = base_query + " where e.gender = %s " + temp_qry result = select_all(query, [gender]) elif gender == 'all' and department != 'all': query = base_query + " where e.dept_id = %s " + temp_qry result = select_all(query, [department]) else: query = base_query + " where e.dept_id = %s and e.gender = %s " + temp_qry result = select_all(query, [department, gender]) return jsonify(result) except Exception: return jsonify({'result': 'failure'})
def select_tasklist(): try: user_id = request.json['user_id'] query = '''select tl.tasklist_id, tl.uuid as tl_uuid, tl.name as tl_name, tl.desc, group_concat(t.name separator ';;;') as t_name, group_concat(t.uuid separator ';;;') as uuid from tasklists tl left join task t on tl.tasklist_id = t.tasklist_id where tl.user_id = %s group by tl.tasklist_id;''' arguments = [user_id] result = select_all(query, arguments) return jsonify(result) except Exception: return jsonify({'result':'failure' })
def dir_list(uuid): try: if uuid == "all": query = "SELECT * FROM `dir` WHERE `parent_id` is NULL" else: query = "SELECT * FROM `dir` WHERE `parent_id` = '{0}'".format( uuid) result = select_all(query, []) return jsonify(result) except Exception as ex: print(ex) return jsonify({'result': 'failure'})
def search_tasklist(): try: user_id = request.json['user_id'] search = request.json['search'] query = '''select tl.tasklist_id, tl.uuid as tl_uuid, tl.name as tl_name, tl.desc, group_concat(t.name separator ';;;') as t_name, group_concat(t.uuid separator ';;;') as uuid from tasklists tl left join task t on tl.tasklist_id = t.tasklist_id where tl.user_id = {0} and tl.name like '%{1}%' group by tl.tasklist_id;'''.format(user_id, search) # return jsonify({'query': query}) result = select_all(query) return jsonify(result) except Exception: return jsonify({'result':'failure' })
def book_fetchall(): try: query = ''' SELECT b.book_id, b.book_title,p.name as publisher, GROUP_CONCAT(DISTINCT a.name) AS authors, GROUP_CONCAT(DISTINCT c.name) AS categories FROM ((SELECT ba.book_id, ba.author_id, bc.category_id FROM book_author ba LEFT JOIN book_category bc ON ba.book_id = bc.book_id) as bac RIGHT JOIN books b ON b.book_id = bac.book_id LEFT JOIN authors a on a.id = bac.author_id LEFT JOIN category c on c.id = bac.category_id LEFT JOIN publishers p ON p.id = b.publisher_id) GROUP BY b.book_id; ''' result = select_all(query, []) return jsonify(result) except Exception: return jsonify({'result': 'failure'})
def get_all_department(): query = 'SELECT id, dept_name FROM department' result = select_all(query, []) return jsonify(result)
def get_blog_by_user_id(user_id): query = 'SELECT * FROM `blog` WHERE `user_id` = %s' result = select_all(query, [user_id]) return jsonify(result)
def get_all_category(): query = 'SELECT * FROM `category` ORDER BY `category` ASC' result = select_all(query, []) return jsonify(result)