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
