-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathsave_performance_to_database.py
193 lines (178 loc) · 10.1 KB
/
save_performance_to_database.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
import sqlite3
import json
#Method to save students hackerrank and github status
def save_u_p_data(hackerrank_github_data):
database_connection = sqlite3.connect('userdatabase.db')
user_id = hackerrank_github_data['userid']
hackerrank_info = hackerrank_github_data['hackerrank_data']
github_info = hackerrank_github_data['github_data']
hackerrank_problems = hackerrank_github_data['hackerrank_problems']
hackerrank_submissions = hackerrank_github_data['hackerrank_submissions']
database_connection.execute("UPDATE STUDENTPERFORMANCE SET HACKERRANK_STATUS=?,GITHUB_STATUS=?,HACKERRANK_PROBLEMS=?,HACKERRANK_SUBMISSIONS=? WHERE USERID = ?",(hackerrank_info,github_info,hackerrank_problems,hackerrank_submissions,user_id))
database_connection.commit()
database_connection.close()
#print("Successfully Updated")
#Method to get previous hackerrank problems and latest question
def get_hackerrank_problems_and_latest_qtn(hackerrank_id):
database_connection = sqlite3.connect('userdatabase.db')
data_cursor = database_connection.cursor()
result_cursor = data_cursor.execute("SELECT HACKERRANK_PROBLEMS FROM STUDENTPERFORMANCE WHERE HACKERRANKID = '%s' "% hackerrank_id)
result_data = result_cursor.fetchall()
for row in result_data:
result = row[0]
if(result == None):
result = json.dumps({'problems_list':[],'problems_count':0,'recent_problem':''})
return result
#print(get_hackerrank_problems_and_latest_qtn('sivagembali'))
#Method to insert data into tables
def insert_data(name,password,email,mobile,college,gender,batch,location,hackerrankid,githubid,linkedinid):
print(name,password,email,mobile,college,gender,batch,location,hackerrankid,githubid,linkedinid)
database_connection = sqlite3.connect('userdatabase.db')
if(table_exists(database_connection,'STUDENTREGISTRATION')):
user_details = (name,password,email,mobile,college,gender,batch,location)
insertion_query = '''INSERT INTO STUDENTREGISTRATION(NAME,PASSWORD,EMAIL,MOBILE,COLLEGENAME,GENDER,BATCH,LOCATION) VALUES(?,?,?,?,?,?,?,?)'''
database_connection.execute(insertion_query,user_details)
database_connection.commit()
else:
database_connection.execute('''CREATE TABLE STUDENTREGISTRATION (USERID INTEGER PRIMARY KEY,NAME TEXT NOT NULL,PASSWORD TEXT NOT NULL,EMAIL TEXT UNIQUE,MOBILE TEXT NOT NULL,COLLEGENAME TEXT NOT NULL,GENDER TEXT NOT NULL,BATCH TEXT NOT NULL,LOCATION TEXT NOT NULL);''')
user_details = (1,name,password,email,mobile,college,gender,batch,location)
insertion_query = '''INSERT INTO STUDENTREGISTRATION(USERID,NAME,PASSWORD,EMAIL,MOBILE,COLLEGENAME,GENDER,BATCH,LOCATION) VALUES(?,?,?,?,?,?,?,?,?)'''
database_connection.execute(insertion_query,user_details)
database_connection.commit()
if(table_exists(database_connection,'STUDENTPERFORMANCE')):
student_info = check_email_exist_or_not(email)
#print(student_info['studentinfo'])
value = student_info['userid']
hackerrank_problems = json.dumps({'problems_list':[],'problems_count':0,'recent_problem':''})
user_details = (hackerrankid,githubid,hackerrank_problems,linkedinid,value)
insertion_query='''INSERT INTO STUDENTPERFORMANCE (HACKERRANKID,GITHUBID,HACKERRANK_PROBLEMS,LINKEDINID,USERID) VALUES(?,?,?,?,?)'''
database_connection.execute(insertion_query,user_details)
database_connection.commit()
else:
database_connection.execute('''CREATE TABLE STUDENTPERFORMANCE(S_NUMBER INTEGER PRIMARY KEY ,HACKERRANKID TEXT,GITHUBID TEXT,HACKERRANK_STATUS TEXT,HACKERRANK_SUBMISSIONS TEXT,HACKERRANK_PROBLEMS INTEGER,GITHUB_STATUS TEXT,LINKEDINID TEXT,STACKOVERFLOWID TEXT,USERID INTEGER,FOREIGN KEY (USERID) REFERENCES STUDENTREGISTRATION(USERID) )''')
student_info = check_email_exist_or_not(email)
#print(student_info['studentinfo'])
value = student_info['userid']
hackerrank_problems = json.dumps({'problems_list':[],'problems_count':0,'recent_problem':''})
user_details = (1,hackerrankid,githubid,hackerrank_problems,linkedinid,value)
insertion_query='''INSERT INTO STUDENTPERFORMANCE (S_NUMBER,HACKERRANKID,GITHUBID,HACKERRANK_PROBLEMS,LINKEDINID,USERID) VALUES(?,?,?,?,?,?)'''
database_connection.execute(insertion_query,user_details)
database_connection.commit()
database_connection.close()
#insert_data('Lakkimsetty Pavani','pavani','[email protected]','9133610848','Gayatri Vidya Parishad college of engineering(A)','female','Feb-18','vizag','pavanilakkimset1','lakkimsettypavani','')
#Method to retrive data from database returns a json
def get_ids_studentperformance(userid):
database_connection = sqlite3.connect('userdatabase.db')
data_cursor = database_connection.cursor()
result_cursor = data_cursor.execute("SELECT HACKERRANKID,GITHUBID FROM STUDENTPERFORMANCE WHERE USERID = %s "% userid)
result_data_set={}
result_data_set['userid']=userid
result_data = result_cursor.fetchall()
for row in result_data:
result_data_set['hackerrankid']=row[0]
result_data_set['githubid']=row[1]
data_cursor.close()
database_connection.close()
return result_data_set
#Method to retrive s_numbers from student performance table returns all students s_nubers
def get_snumber_from_studentperformance_table():
database_connection = sqlite3.connect('userdatabase.db')
data_cursor = database_connection.cursor()
data_cursor = database_connection.execute("SELECT USERID FROM STUDENTPERFORMANCE")
resultant_data = data_cursor.fetchall()
data_cursor.close()
database_connection.close()
return resultant_data
#Method to check email exist in the database or not if exist returns student info(foreign key value) of type dictionary
def check_email_exist_or_not(email):
database_connection = sqlite3.connect('userdatabase.db')
data_cursor = database_connection.cursor()
data_cursor = database_connection.execute("SELECT USERID FROM STUDENTREGISTRATION WHERE EMAIL=? OR MOBILE=? ",(email,email))
resultant_data = data_cursor.fetchall()
result = {}
for row in resultant_data:
result['userid']=row[0]
data_cursor.close()
database_connection.close()
return result
#print(check_email_exist_or_not('[email protected]'))
#Method to update hackerrankid and githubid and returns status message
def update_ids_to_database(user_details):
database_connection = sqlite3.connect('userdatabase.db')
student_info = user_details['userid']
hackerrank_id = user_details['hackerrankid']
github_id=user_details['github_id']
#print(hackerrank_id,github_id,student_info)
database_connection.execute("UPDATE STUDENTPERFORMANCE SET HACKERRANKID = ?,GITHUBID = ? WHERE USERID = ?",(hackerrank_id,github_id,student_info))
#database_connection.execute("INSERT INTO STUDENTPERFORMANCE(HACKERRANKID,GITHUBID,STUDENTINFO) VALUES(?,?,?)",(hackerrank_id,github_id,student_info))
#print("changes",database_connection.total_changes)
database_connection.commit()
database_connection.close()
return "Successfully Updated"
#Method to retrive data from database returns a json with github and hackerrank status
def get_student_github_hackerrank_status(student_info):
database_connection = sqlite3.connect('userdatabase.db')
data_cursor = database_connection.cursor()
data_cursor = database_connection.execute("SELECT HACKERRANK_STATUS,GITHUB_STATUS FROM STUDENTPERFORMANCE WHERE USERID='%s'" % student_info)
data = data_cursor.fetchall()
result_status = {}
for row in data:
result_status['hackerrank_status']=row[0]
result_status['github_status']=row[1]
return result_status
def get_hack_data_from_db(hackerrank_id):
database_connection = sqlite3.connect('userdatabase.db')
data_cursor = database_connection.cursor()
try:
data_cursor = database_connection.execute("SELECT HACKERRANK_STATUS FROM STUDENTPERFORMANCE WHERE HACKERRANKID='%s'" % hackerrank_id)
data = data_cursor.fetchall()
result_status = {}
for row in data:
result_status['hackerrank_status']=row[0]
return result_status
except Exception as exp:
return "Data Not Available"
def get_all_students_data():
database_connection = sqlite3.connect('userdatabase.db')
data_cursor = database_connection.cursor()
data_cursor = database_connection.execute("SELECT * FROM STUDENTREGISTRATION")
data = data_cursor.fetchall()
result_status = {}
for row in data:
s_id = row[0]
result_status[s_id] = {}
result_status[s_id]['name:']=row[1]
result_status[s_id]['password']=row[2]
result_status[s_id]['email'] = row[3]
result_status[s_id]['mobile'] = row[4]
result_status[s_id]['college']= row[5]
result_status[s_id]['gender']=row[6]
result_status[s_id]['batch']=row[7]
result_status[s_id]['location']=row[8]
return result_status
#print(result_status)
#get_all_students_data()
#method to check table exist or not
def table_exists(conn,table_name):
result = conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='"+table_name+"'")
result_data = result.fetchall()
if len(result_data)>0 and result_data[0][0] == table_name:
return True
else:
return False
"""#Method to store the data coming from the user registration
def save_user_registration_data(user_details):
database_connection = sqlite3.connect('userdatabase.db')
username = user_details['username']
password = user_details['password']
collegename = user_details['collegename']
email = user_details['email']
mobile = user_details['mobile']
gender = user_details['gender']
user_data = (username,password,email,mobile,collegename,gender)
insertion_query = '''INSERT INTO STUDENTREGISTRATION(NAME,PASSWORD,EMAIL,MOBILE,COLLEGENAME,GENDER) VALUES(?,?,?,?,?,?)'''
database_connection.execute(insertion_query,user_data)
database_connection.commit()
database_connection.close()
#save_user_registration_data({'username':'siva','password':'sanjuamma','email':'[email protected]','mobile':'8978098722','collegename':'gvp','gender':'male'})
"""