upGradGPT/GPT_Interview_beta

1import pymysql2#We will use connect() to connect to RDS Instance3#host is the endpoint of your RDS instance4#user is the username you have given while creating the RDS instance5#Password is Master pass word you have given6#db = pymysql.connect(host="database-gpt.cjdcshirzwmk.us-east-1.rds.amazonaws.com", user = "admin123", password="admin123", port=3306)7# you have cursor instance here8#cursor = db.cursor()9def DB_SessionID():10    11    # cursor.execute("select version()")12    db = pymysql.connect(host="gpt-database-new.cjdcshirzwmk.us-east-1.rds.amazonaws.com", user = "admin123", password="admin123", port=8080)13    # you have cursor instance here14    cursor = db.cursor()15    db.select_db('gpt_interview_data')16    17    try:18        # Begin the transaction19        db.begin()20        print("Connection sucessfull")21        # Execute the SQL queries22        cursor.execute('''INSERT INTO table1 (session_id, rating)23                        SELECT t.count + 1, 524                        FROM (SELECT COUNT(*) AS count FROM table1) AS t;''')25        cursor.execute("SELECT COUNT(*) AS new_length FROM table1")26 27        # Commit the transaction28        db.commit()29        #print("Transaction completed successfully")30        session_id = cursor.fetchone()[0]31        #print(session_id)32        return session_id33 34    except Exception as e:35        # Rollback the transaction if any error occurs36        db.rollback()37        #print("Transaction failed")38        #print(e)39        return 040 41    finally:42        # Close the cursor and connection43        cursor.close()44        db.close()45        46 47 48def insertData(session_id, score, question_no):49    db = pymysql.connect(host="gpt-database-new.cjdcshirzwmk.us-east-1.rds.amazonaws.com", user = "admin123", password="admin123", port=8080)50    # you have cursor instance here51    cursor = db.cursor()52    db.select_db('gpt_interview_data')53 54    score_obj_no = question_no - 155    question = score[score_obj_no].get("question")56    response = score[score_obj_no].get("response")57    grade = score[score_obj_no].get("score")58    feedback = score[score_obj_no].get("feedback")59    #code for pack-unpack60    # Pack two 32-bit integers into a single 64-bit integer61    sessionid_qno = packed = (int(session_id) << 32) | question_no62    #print("sqo", sessionid_qno, "question",question_no, "session", session_id)63    #directly formatting the string with variables is vulnerable to SQL injection attacks, so using parameterized queries instead of simple one.64    sql2 = '''INSERT INTO table2 (session_id, session_question, question_num, question, response, feedback, score)65VALUES (%s, %s, %s, %s, %s, %s, %s)'''66#    sql2 = '''insert into table2 (session_id, session_question, question_num, question, response, feedback, score)67#VALUES (2, 3564373, 3, "dsfds", "sdfgsdg", "sfdgg", 4)'''68    #print(type(session_id), session_id, type(sessionid_qno),sessionid_qno, type(question_no),question_no, type(question),question, type(response),response, type(feedback),feedback, type(grade),grade)69    try:70        # Begin the transaction71        db.begin()72        #print("Connection sucessfull 2")73        # Execute the SQL queries74        cursor.execute(sql2,(session_id, sessionid_qno, question_no, question, response, feedback, grade))75 76        # Commit the transaction77        db.commit()78        #print("Transaction 2 completed successfully")79        return 180 81    except Exception as e:82        # Rollback the transaction if any error occurs83        db.rollback()84        #print(session_id)85        #print(score_obj_no)86        #print("Transaction 2 failed")87        #print(e)88        return 089 90    finally:91        # Close the cursor and connection92        cursor.close()93        db.close()94 95 96 97 98# Unpack the integers from the packed value99# a_unpacked = packed >> 32100# b_unpacked = packed & 0xffffffff101 102# print("a:", a)103# print("b:", b)104# print("packed:", packed)105# print("a_unpacked:", a_unpacked)106# print("b_unpacked:", b_unpacked)107