samedi 4 avril 2015

Python 3.4.2: How to check columns for data matching in a sqlite database

I am creating a program that is designed for a pupil to do. It asks the pupil for their name, their class and then a series of 10 random questions. I have the first part working, now I am looking to add the pupil's name, class and score to a separate database, depending upon if they are in class 1, 2 or 3, and a separate table for each class in a sqlite database.


I am looking to record three scores for each student in the database, but I cannot figure out how to check if there is a score in the score 1 column, and if there is, then when the student runs the quiz the second time, their second score should be recorded in the score 2 column.



import random, sqlite3, os

def inputName():
userName = str(input("Please enter your full name: "))
if userName == "":
print("An error occured! Please enter your full name!", "\n")
inputName()
elif userName.isdigit():
print("An error occured! Please only include letters in your name!")
userName = None
inputName()
else:
return userName

def randomQuiz():

operator = ['+', '-', '*']
score = int(0)
question = int(1)
while question < int(11):
print("Question",question,";")
num1 = random.randint(0,15)
num2 = random.randint(0,15)
operation = random.choice(operator)
guess = input("What is " + str(num1) + " " + operation + " " + str(num2) +"? ")
try:
guess = int(guess)
except ValueError:
print("Nothing acceptable entered!")

answer = eval(str(num1) + operation + str(num2))
if guess == answer:
print("Correct!" + "\n")
score = score + int(1)
question = question + int(1)
else:
print("Incorrect! The right answer was", answer, "\n")
question = question + int(1)
print("Your score is", score, "out of 10")
return score

def inputClass():

userClass = input("Please specify if you are in class 1, class 2, or class 3: ")
try:
userClass = int(userClass)
except ValueError:
print("An error occured, please enter the corresponding number if you are in class 1, class 2, or class 3!","\n")
inputClass()

if userClass == 1:
userClass = str("Class 1")
elif userClass == 2:
userClass = str("Class 2")
elif userClass == 3:
userClass = str("Class 3")
else:
print("An error occured, please enter the corresponding number if you are in class 1, class 2, or class 3!","\n")
userClass = None
inputClass()

return userClass

def createDatabase():

if os.path.exists("ArithmeticQuizScores.db") == False: #checks if file doesn't exists
makeFile = open("ArithmeticQuizScores.db", "w") #if not, then it will create one
makeFile.close()
else:
return True

def createTable():

makeTableInDatabase = sqlite3.connect("ArithmeticQuizScores.db")
editDatabase = makeTableInDatabase.cursor()
editDatabase.execute("""CREATE TABLE IF NOT EXISTS Class1StudentsScores (studentName text, studentClass text, studentScore1 text, studentScore2 text, studentScore3 text)""")
editDatabase.execute("""CREATE TABLE IF NOT EXISTS Class2StudentsScores (studentName text, studentClass text, studentScore1 text, studentScore2 text, studentScore3 text)""")
editDatabase.execute("""CREATE TABLE IF NOT EXISTS Class3StudentsScores (studentName text, studentClass text, studentScore1 text, studentScore2 text, studentScore3 text)""")
makeTableInDatabase.commit()
editDatabase.close()

def addFirstData(studName, studClass, studScore):

value = [studName, studClass, studScore]
OpenDatabase = sqlite3.connect("ArithmeticQuizScores.db")
addToDatabase = OpenDatabase.cursor()

if studClass == str("Class 1"):
addToDatabase.execute('''INSERT INTO Class1StudentsScores ("studentName", "studentClass", "studentScore1") VALUES (?, ?, ?)''', value)

elif studClass == str("Class 2"):
addToDatabase.execute('''INSERT INTO Class2StudentsScores ("studentName", "studentClass", "studentScore2") VALUES (?, ?, ?)''', value)

else:
if studClass == str("Class 3"):
addToDatabase.execute('''INSERT INTO Class3StudentsScores ("studentName", "studentClass", "studentScore1") VALUES (?, ?, ?)''', value)

OpenDatabase.commit()
addToDatabase.close()

def addSecondData(StudN, StudS):
value2 = [StudN, StudS]
OpenDB = sqlite3.connect("ArithmeticQuizScores.db")
addToDB = OpenDB.cursor()

if StudC == ("Class 1"):
addToDB.execute('''INSERT INTO Class1StudentsScores ("studentName","studentScore2") VALUES (?, ?)''', value2)

elif studClass == ("Class 2"):
addToDB.execute('''INSERT INTO Class2StudentsScores ("studentName","studentScore2") VALUES (?, ?)''', value2)
else:
if studClass == ("Class 3"):
addToDB.execute('''INSERT INTO Class3StudentsScores ("studentName","studentScore2") VALUES (?, ?)''', value2)
OpenDB.commit()
addToDB.close()

def checkIfScore(SClass, SName, sScore):
GetConnection = sqlite3.connect("ArithmeticQuizScores.db")
GetCursor = GetConnection.cursor()

if SClass == str("Class 1"):
GetCursor.execute("SELECT * FROM Class1StudentsScores ORDER BY studentName ASC")
CheckName = GetCursor.fetchall()
for row in CheckName:
if row[0] == SName:
if row[2] is not None:
return addSecondData(SName, SClass, sScore)
else:
return addFirstData(SName, SClass, sScore)
else:
return addFirstData(SName, SClass, sScore)

elif SClass == str("Class 2"):
for row in CheckName:
if row[0] == SName:
if row[2] is not None:
return addSecondData(SName, SClass, sScore)
else:
return addFirstData(SName, SClass, sScore)
else:
return addFirstData(SName, SClass, sScore)
else:
if SClass == str("Class 3"):

for row in CheckName:
if row[0] == SName:
if row[2] is not None:
return addSecondData(SName, SClass, sScore)
else:
return addFirstData(SName, SClass, sScore)
else:
return addFirstData(SName, SClass, sScore)

def Quiz():

name = inputName()
Class = inputClass()
score = randomQuiz()
makeDB = createDatabase()
makeT = createTable()
addData = checkIfScore(name, Class, score)

Quiz()

Aucun commentaire:

Enregistrer un commentaire