我试图使用studentID从一个表中获取他们的名字和姓氏,然后使用HomeworkID获取他们其中一个作业的结果,并使用tkinter将结果显示在listbox小部件中。当然,在它被显示之前,我首先需要能够获得数据。 这就是我迄今为止所做的:
def viewresults (self, sethw):
print (self.Homework_To_Set.get())
#resultslist = []
conn = sqlite3.connect('MyComputerScience.db')
c = conn.cursor()
homeworkID = c.execute("SELECT HWID FROM HomeworkInfo WHERE HomeworkName = ?", (self.Homework_To_Set.get(), )).fetchone()
studentsnamecheck = c.execute("SELECT StudentID FROM HomeworkSet WHERE HWID = ?", ((homeworkID[0], ))).fetchall()
if len(studentsnamecheck) == 0:
self.View_Results.insert(END, "No one has completed this homework!")
else:
for x in studentsnamecheck:
c.execute("SELECT FName || ' ' || SName FROM users WHERE UserID = ?", (str(x), ))
res1 = (c.fetchall())
for i in (studentsnamecheck):
result_collect = c.execute("SELECT Results FROM HomeworkSet WHERE StudentID = ? AND HWID = ?", ((str(i)), self.Homework_To_Set.get() ))
var_insert_results = ((res1), (result_collect))
self.View_Results.insert(END, str(var_insert_results))
这是电流输出:
([], <sqlite3.Cursor object at 0x000001F40E1809D0>)
([], <sqlite3.Cursor object at 0x000001F40E1809D0>)
([], <sqlite3.Cursor object at 0x000001F40E1809D0>)
([], <sqlite3.Cursor object at 0x000001F40E1809D0>)
([], <sqlite3.Cursor object at 0x000001F40E1809D0>)
([], <sqlite3.Cursor object at 0x000001F40E1809D0>)
([], <sqlite3.Cursor object at 0x000001F40E1809D0>)
这是数据库的架构:
c.execute("""CREATE TABLE IF NOT EXISTS users (
UserID INTEGER PRIMARY KEY AUTOINCREMENT,
FName text,
SName text,
username text,
password text,
userType text,
ClassName text);""")
c.execute("""CREATE TABLE IF NOT EXISTS ClassInfo (
ClassID INTEGER PRIMARY KEY AUTOINCREMENT,
ClassName text,
Teacher text);""")
c.execute("""CREATE TABLE IF NOT EXISTS HomeworkInfo (
HWID INTEGER PRIMARY KEY,
HomeworkName text);""")
c.execute("""CREATE TABLE IF NOT EXISTS HomeworkSet (
HWID integer,
StudentID text,
Results text,
FOREIGN KEY (StudentID) REFERENCES users(UserID)
FOREIGN KEY (HWID) REFERENCES HomeworkInfo(HWID));""")
您的代码中有几个问题:
users
表中没有UserID
字段;它可能是username
str(x)
应该是x[0]
res1
而不是studentsnamecheck
循环str(i)
应该是x[0]
self.Homework_To_Set.get()
应该是homeworkID[0]
因此,代码应该是:
但是,您可以将
SELECT
语句组合到一个查询中:相关问题 更多 >
编程相关推荐