TRzfReFm

1import sqlite3
2
3# Define strings
4TABLE_PERSONS = "persons"
5COLUMN_ID = "id"
6COLUMN_NAME = "name"
7COLUMN_SURNAME = "surname"
8
9TABLE_SCORES = "scores"
10COLUMN_TASKNR = "tasknr"
11COLUMN_SCORE = "score"
12
13
14# Create database
15dbConnection = sqlite3.connect('score.db')
16db = dbConnection.cursor()
17print("Database found")
18
19# Create tables
20db.execute("CREATE TABLE IF NOT EXISTS " + TABLE_PERSONS + "(" + 
21			COLUMN_ID + " INTEGER PRIMARY KEY," + 
22			COLUMN_NAME + " TEXT," + 
23			COLUMN_SURNAME + " TEXT," +
24			"UNIQUE(" + COLUMN_NAME + "," + COLUMN_SURNAME + "))")
25db.execute("CREATE TABLE IF NOT EXISTS " + TABLE_SCORES + "(" +
26			COLUMN_ID + " INTEGER," + 
27			COLUMN_TASKNR + " TEXT, " + 
28			COLUMN_SCORE + " TEXT," +
29			"UNIQUE(" + COLUMN_ID + "," + COLUMN_TASKNR + "), " + 
30			"FOREIGN KEY(" + COLUMN_ID + ") REFERENCES " +
31			TABLE_PERSONS + "(" + COLUMN_ID + ") ON DELETE CASCADE)")
32
33# Read score file
34with open("score2.txt", "r") as file:
35
36	# Read every line, split the line and insert into corresponding variable
37	for line in file:
38		line = line.split(" ")
39		tasknr, name, surname, score = line[1], line[2], line[3], line[4]
40		fullname = name + " " + surname
41
42		# If student already exists in database ignore otherwise insert
43		db.execute("INSERT OR IGNORE INTO " +  TABLE_PERSONS + "(" + 
44			 COLUMN_NAME + "," + COLUMN_SURNAME + ")" +
45			 "VALUES(?,?)", (name, surname)) 
46		# Select person id and insert in score table correspondingly
47		db.execute("SELECT " + COLUMN_ID + " FROM " + TABLE_PERSONS +
48			 " WHERE " + COLUMN_NAME + " = '" + name +
49			 "' AND " + COLUMN_SURNAME + " = '" + surname + "'")
50		person_id = db.fetchone()[0]
51		db.execute("INSERT OR IGNORE INTO " +  TABLE_SCORES + "(" + 
52			 COLUMN_ID + "," +
53			 COLUMN_TASKNR + "," + 
54			 COLUMN_SCORE + ")" +
55			 "VALUES(?,?,?)", (person_id, tasknr, score))
56		dbConnection.commit()
57
58print("\nList of 10 highest scoring students")
59db.execute("SELECT name, surname, SUM(scores.score) FROM persons " +
60		   "INNER JOIN scores ON persons.id = scores.id " + 
61		   "GROUP BY scores.id ORDER BY SUM(scores.score) DESC LIMIT 10")
62for row in db.fetchall():
63	print("Name:", row[0], row[1], ", Score:", row[2])
64
65print("\nList of 10 most difficult tasks")
66db.execute("SELECT tasknr, SUM(scores.score) FROM persons " +
67		   "INNER JOIN scores ON persons.id = scores.id " + 
68		   "GROUP BY scores.tasknr ORDER BY SUM(scores.score) ASC LIMIT 10")
69for row in db.fetchall():
70	print("TaskNr:", row[0], ", Total score:", row[1])
71
72dbConnection.close()