def getSingleCourse(id): try: conn = connector.connect2DB() cursor = conn.cursor() cursor.execute(''' SELECT coursesschema.courses.*, coursesschema.level.title as levelname, coursesschema.subject.title as tag FROM coursesschema.subject JOIN coursesschema.coursesandsubject on coursesschema.coursesandsubject.subject_id = coursesschema.subject.id JOIN coursesschema.courses on coursesschema.courses.id = coursesschema.coursesandsubject.course_id JOIN coursesschema.level on coursesschema.level.id = courses.level WHERE coursesschema.courses.id = %s''', [id] ) result = cursor.fetchone() #print(result, flush=True) if(result is not None): return { 'id': result[0], 'title': result[1], 'url': result[2], 'paid': result[3], 'price': result[4], 'number_subscribers': result[5], 'number_reviews': result[6], 'number_of_lectures': result[7], 'duration': json.dumps(result[8]), 'level': result[10], 'tags': [ result[11] ] } else: return {} ,404 except (psycopg2.Error) as error: return {'status': 400, 'error': error.pgerror } ,400
def deleteSingleCourse(id): try: conn = connector.connect2DB() cursor = conn.cursor() cursor.execute('CALL coursesschema.delete_course(%s)', [id] ) conn.commit() #print(result, flush=True) return {'status': 200} except (psycopg2.Error) as error: #print(f"f.. " , error, flush=True) if("23505" in error.pgcode): return {'status': 409, 'error': "duplicate key" } ,409 else: return {'status': 400, 'error': error.pgerror}, 400
def putSingleCourse(args,id): try: #print(args, id, flush=True) conn = connector.connect2DB() cursor = conn.cursor() cursor.execute('CALL coursesschema.update_course(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', [id, args.title, args.url, args.paid, args.price, args.number_subscribers, args.number_reviews, args.number_of_lectures, args.duration, args.level]) conn.commit() #print(result, flush=True) return {'status': 204}, 204 except (psycopg2.Error) as error: #print(f"f.. " , error, flush=True) if("23505" in error.pgcode): return {'status': 409, 'error': "duplicate key" } ,409 else: return {'status': 400, 'error': error.pgerror}, 400
def getCourses(args): try: conn = connector.connect2DB() cursor = conn.cursor() #GET ALL if (args.tag is None and args.level is None and args.price is None and args.comparator is None): cursor.execute(''' SELECT coursesschema.courses.*, coursesschema.level.title as levelname, coursesschema.subject.title as tag FROM coursesschema.subject JOIN coursesschema.coursesandsubject on coursesschema.coursesandsubject.subject_id = coursesschema.subject.id JOIN coursesschema.courses on coursesschema.courses.id = coursesschema.coursesandsubject.course_id JOIN coursesschema.level on coursesschema.level.id = courses.level ''' ) #GET PRICE elif (args.tag is None and args.level is None and args.price is not None): if(args.comparator is None): cursor.execute(''' SELECT coursesschema.courses.*, coursesschema.level.title as levelname, coursesschema.subject.title as tag FROM coursesschema.subject JOIN coursesschema.coursesandsubject on coursesschema.coursesandsubject.subject_id = coursesschema.subject.id JOIN coursesschema.courses on coursesschema.courses.id = coursesschema.coursesandsubject.course_id JOIN coursesschema.level on coursesschema.level.id = courses.level WHERE coursesschema.courses.price = %s''', [args.price] ) elif('lessThan' in args.comparator): cursor.execute(''' SELECT coursesschema.courses.*, coursesschema.level.title as levelname, coursesschema.subject.title as tag FROM coursesschema.subject JOIN coursesschema.coursesandsubject on coursesschema.coursesandsubject.subject_id = coursesschema.subject.id JOIN coursesschema.courses on coursesschema.courses.id = coursesschema.coursesandsubject.course_id JOIN coursesschema.level on coursesschema.level.id = courses.level WHERE coursesschema.courses.price < %s''', [args.price] ) elif('greaterThan' in args.comparator): cursor.execute(''' SELECT coursesschema.courses.*, coursesschema.level.title as levelname, coursesschema.subject.title as tag FROM coursesschema.subject JOIN coursesschema.coursesandsubject on coursesschema.coursesandsubject.subject_id = coursesschema.subject.id JOIN coursesschema.courses on coursesschema.courses.id = coursesschema.coursesandsubject.course_id JOIN coursesschema.level on coursesschema.level.id = courses.level WHERE coursesschema.courses.price > %s''', [args.price] ) #GET LEVEL elif (args.tag is None and args.level is not None and args.price is None and args.comparator is None): cursor.execute(''' SELECT coursesschema.courses.*, coursesschema.level.title as levelname, coursesschema.subject.title as tag FROM coursesschema.subject JOIN coursesschema.coursesandsubject on coursesschema.coursesandsubject.subject_id = coursesschema.subject.id JOIN coursesschema.courses on coursesschema.courses.id = coursesschema.coursesandsubject.course_id JOIN coursesschema.level on coursesschema.level.id = courses.level WHERE coursesschema.level.title = %s''', [args.level] ) #GET TAG elif (args.tag is not None and args.level is None and args.price is None and args.comparator is None): cursor.execute(''' SELECT coursesschema.courses.*, coursesschema.level.title as levelname, coursesschema.subject.title as tag FROM coursesschema.subject JOIN coursesschema.coursesandsubject on coursesschema.coursesandsubject.subject_id = coursesschema.subject.id JOIN coursesschema.courses on coursesschema.courses.id = coursesschema.coursesandsubject.course_id JOIN coursesschema.level on coursesschema.level.id = courses.level WHERE coursesschema.subject.title = %s''', [args.tag] ) except (psycopg2.Error) as error : return {'status': 400, 'error': error.pgerror } ,400 finally: rows = cursor.fetchall() results = [] for row in rows: results.append({ 'id': row[0], 'title': row[1], 'url': row[2], 'paid': row[3], 'price': row[4], 'number_subscribers': row[5], 'number_reviews': row[6], 'number_of_lectures': row[7], 'duration': json.dumps(row[8]), 'level': row[10], 'tags': [ row[11] ] }) res = sorted(results,key=lambda x: x['price'], reverse=True) return res