def insertMarkPreview(): query_string = "SELECT\ CONCAT(d_group, \" (\", gr_spec, \")\"),\ d_name,\ CONCAT(marks.exm_id, \" (\", exm_type, \")\"),\ CONCAT(DAY(exm_date), \"-\", MONTH(exm_date),\"-\", YEAR(exm_date)),\ CONCAT(t_no, \" - \", t_surn, \" \", t_name, \" \", t_name2),\ CONCAT(students.stud_id, \" - \", stud_surn, \" \", stud_name, \" \",\ if (stud_name2 is null, '', stud_name2)),\ mark_value\ FROM university.exams\ JOIN university.disc_to_teachers\ USING (dtt_instance_id)\ JOIN university.disciplines\ USING (d_id)\ JOIN university.teachers\ USING (t_no)\ JOIN university.marks\ USING (exm_id)\ JOIN university.uni_groups\ ON university.uni_groups.gr_id = university.disc_to_teachers.d_group\ JOIN university.students\ USING (stud_id)\ ORDER BY exm_date, d_name, gr_spec, marks.stud_id;" query_rows_list = dbQuery(query_string) return query_rows_list
def updateQuery(t_no, n): query_string = '{}{}{}{}{}{}{}'.format( "UPDATE teachers", n, "k.teachers\ SET t_name = 'name", t_no + 1, "', t_name2 = 'name2100008'\ WHERE (t_no = '", t_no, "');") query_rows_list = dbQuery(query_string) return query_rows_list
def combo3(): query_string = "SELECT t_no,\ concat(t_no, ' - ', t_surn, ' ', t_name, ' ', t_name2)\ FROM university.teachers\ ORDER BY t_no;" query_rows_list = dbQuery(query_string) return query_rows_list
def keyFieldQuery(t_no, n): query_string = '{}{}{}{}{}'.format( "\ SELECT *\ FROM teachers", n, "k.teachers\ WHERE t_no =", t_no, ";") query_rows_list = dbQuery(query_string) return query_rows_list
def insertOne(t_no, n): query_string = '{}{}{}{}{}{}{}{}{}{}{}'.format( "INSERT INTO teachers", n, "k.teachers \ (t_no, t_surn, t_name, t_name2)\ VALUES (", t_no, ", 'surn", t_no, "', 'name", t_no, "', 'name2", t_no, "');") query_rows_list = dbQuery(query_string) return query_rows_list
def notKFQuery(t_no, n): query_string = '{}{}{}{}{}'.format( "\ SELECT *\ FROM teachers", n, "k.teachers\ WHERE t_name ='name", t_no, "';") query_rows_list = dbQuery(query_string) return query_rows_list
def maskQuery(lastDigits, n): query_string = '{}{}{}{}{}'.format( "\ SELECT * \ FROM teachers", n, "k.teachers \ WHERE t_surn LIKE '%", lastDigits, "';") query_rows_list = dbQuery(query_string) return query_rows_list
def removeMulti(t_no, n): qsTotal = "" for i in range(9): query_string = '{}{}{}{}{}'.format( "DELETE FROM teachers", n, "k.teachers \ WHERE (t_surn = 'surn", t_no, "');") qsTotal += query_string t_no += 1 query_rows_list = dbQuery(qsTotal) return query_rows_list
def queryGroups(year): query_string = '{}{}{}'.format( "\ SELECT gr_id, students.stud_id, stud_surn, stud_name, stud_name2, stud_status\ FROM university.students\ JOIN university.stud_to_grps\ ON university.students.stud_id = university.stud_to_grps.stud_id\ WHERE year_study =", year, "\ ORDER BY gr_id, stud_semester DESC, stud_surn, stud_name, stud_name2") query_rows_list = dbQuery(query_string) return query_rows_list
def defineExam(dtt_instance_id, day, month, exm_type): query_string = 'SELECT dtt_year FROM university.disc_to_teachers\ WHERE dtt_instance_id = {};'.format(dtt_instance_id) year = dbQuery(query_string) year = year[0][0] date = '{}-{}-{}'.format(year, month, day) print(date) query_string = 'INSERT INTO university.exams (exm_type, exm_date, dtt_instance_id)\ VALUES (\'{}\', \'{}\', {});'.format(exm_type, date, dtt_instance_id) dbInsertQuery(query_string) return year
def updateMultiNKey(t_no, n): qsTotal = "" for i in range(1): query_string = '{}{}{}{}{}{}{}'.format( "UPDATE teachers", n, "k.teachers\ SET t_name = 'name", t_no + 1, "', t_name2 = 'nameUpdateMultiNKey'\ WHERE (t_surn = 'surn", t_no, "');") qsTotal += query_string t_no += 1 query_rows_list = dbQuery(qsTotal) return query_rows_list
def dropStudentsPreview(): query_string = "SELECT students.*, marks.exm_id, mark_value, exm_type,\ CONCAT(DAY(exm_date), \"-\", MONTH(exm_date),\"-\", YEAR(exm_date))\ FROM university.marks\ JOIN university.students\ ON students.stud_id = marks.stud_id\ JOIN university.exams\ ON marks.exm_id=exams.exm_id\ WHERE mark_value < 3;" query_rows_list = dbQuery(query_string) return query_rows_list
def insertMulti(t_no, n): qsTotal = "" for i in range(3): query_string = '{}{}{}{}{}{}{}{}{}{}{}'.format( "INSERT INTO teachers", n, "k.teachers \ (t_no, t_surn, t_name, t_name2)\ VALUES (", t_no, ", 'surn", t_no, "', 'name", t_no, "', 'name2", t_no, "');") qsTotal += query_string t_no += 1 query_rows_list = dbQuery(qsTotal) return query_rows_list
def insertMarkCombo2(gr_id): query_string = "SELECT stud_id,\ concat(stud_id, ' - ',\ stud_surn, ' ', stud_name, ' ', if (stud_name2 is null, '', stud_name2),\ ' (status: ', stud_status,')')\ FROM university.students\ JOIN university.stud_to_grps\ USING (stud_id)\ WHERE gr_id = " query_string = '{}{};'.format(query_string, gr_id) query_rows_list = dbQuery(query_string) return query_rows_list
def queryExams(year): query_string = '{}{}{}{}{}'.format( "\ SELECT exm_type, YEAR(exm_date), MONTH(exm_date), DAY(exm_date), d_name \ FROM university.exams\ JOIN university.disc_to_teachers\ USING (dtt_instance_id)\ JOIN university.disciplines\ USING (d_id)\ WHERE exm_date BETWEEN '", year, "-01-01' AND '", year + 1, "-01-01'\ ORDER BY exm_date, exm_type") query_rows_list = dbQuery(query_string) return query_rows_list
def defineLecturers_overview(): query_string = "\ SELECT d_group, dtt_year, dtt_sem, t_no, exm_type,\ d_name,\ exams.exm_id, MONTH(exm_date), DAY(exm_date), dtt_instance_id\ FROM university.exams\ RIGHT JOIN university.disc_to_teachers\ USING (dtt_instance_id)\ JOIN university.disciplines\ USING (d_id)\ ORDER BY exm_date DESC, dtt_year DESC, dtt_sem DESC, d_group;" query_rows_list = dbQuery(query_string) return query_rows_list
def insertMarkCombo1(): query_string = "SELECT exm_id,\ concat(exm_id,' - ', d_name, '; type ', exm_type,\ '; group ', d_group, '; ', exm_date),\ d_group\ FROM university.exams\ JOIN university.disc_to_teachers\ USING (dtt_instance_id)\ JOIN university.disciplines\ USING (d_id)\ ORDER BY exm_id;" query_rows_list = dbQuery(query_string) return query_rows_list
def queryEvilTeachers(year, semester, min_count, mark_value): """ year is integer like 2016, 2017, 2018, or string 'ALL' semester is short integer, 1 or 2, or 3 (both semesters) """ #min_count = 1 #mark_value = 3 if semester == 1: lower_month = 4 upper_month = 8 elif semester == 2: lower_month = 10 upper_month = 12 elif semester == 3: #both semesters lower_month = 4 upper_month = 12 if year == 'ALL': where_string = '{}{}{}{}{}{}'.format( "\ WHERE MONTH(exm_date) BETWEEN ", lower_month, " AND ", upper_month, " AND mark_value = ", mark_value) else: where_string = '{}{}{}{}{}{}{}{}'.format( "\ WHERE YEAR(exm_date) = ", year, "\ AND MONTH(exm_date) BETWEEN ", lower_month, " AND ", upper_month, ' AND mark_value = ', mark_value) print(where_string) query_string = '{}{}{}{}{}'.format( "\ SELECT t_surn, t_name, t_name2, COUNT(mark_id), mark_value\ FROM university.teachers\ JOIN university.disc_to_teachers\ USING (t_no)\ JOIN university.exams\ USING (dtt_instance_id)\ JOIN university.marks\ USING (exm_id)", where_string, "\ GROUP BY teachers.t_no, mark_value\ HAVING COUNT(mark_id) >= ", min_count, "\ ORDER BY mark_value, COUNT(mark_value) DESC, t_surn") query_rows_list = dbQuery(query_string) return query_rows_list
def queryDropped(year, semester): """ year is integer like 2016, 2017, 2018 semester is short integer, 1 or 2 """ if semester == 3: where_string = '{}{}'.format("WHERE year_study = ", year) else: where_string = '{}{}{}{}'.format("WHERE year_study = ", year, " AND stud_semester = ", semester - 1) query_string = '{}{}{}'.format( "\ SELECT gr_id, students.stud_id, stud_surn, stud_name, stud_name2, stud_status\ FROM university.students\ JOIN university.stud_to_grps\ ON university.students.stud_id = university.stud_to_grps.stud_id\ ", where_string, "\ ORDER BY gr_id, stud_semester DESC, stud_surn, stud_name, stud_name2") query_rows_list = dbQuery(query_string) return query_rows_list
def optimize(n): query_string = '{}{}{}'.format("\ OPTIMIZE TABLE teachers", n, "k.test_table;") print(query_string) query_rows_list = dbQuery(query_string) return query_rows_list
def removeOneNKey(t_no, n): query_string = '{}{}{}{}{}'.format( "DELETE FROM teachers", n, "k.teachers \ WHERE (t_surn = 'surn", t_no, "');") query_rows_list = dbQuery(query_string) return query_rows_list
def defLectrComboGrps(): query_string = "SELECT CONCAT(d_id, '-', d_name) FROM university.disciplines\ ORDER BY d_id" query_rows_list = dbQuery(query_string) return query_rows_list