sqlite1.py~

import sqlite3
import os.path
import sys
import random


def makeDatabase(databaseName):
	if databaseName[-3:] != ".db":
		databaseName = databaseName + ".db"
	conn = sqlite3.connect(databaseName)
	conn.commit()
	conn.close()
def listToString(list):
	string = ""
	for i in list:
		string += str(i)+","
	return string[:-1] 

def stringToList(string):
	list = [str(line) for line in string.split(',')]
	return list

#class for connecting, inserting, and retrieving information from a sqlite3 database
class SqliteDB:
	
	#connects to the database, alters its name if named incorrectly
	def __init__(self, databaseName):
		if databaseName[-3:] != ".db":
			databaseName = databaseName + ".db"
		if os.path.isfile(databaseName):
			self.databaseName = databaseName;
			self.conn = sqlite3.connect(self.databaseName)
			self.cursor = self.conn.cursor()
		else:
			#sees if database name is unique, so it doesn't overwrite anything
			sys.exit("This database does not exist, use the makeDatabase(databaseName) to create it")

	def createTables(self):
		#creates tables if they do not exist
		self.cursor.execute("CREATE TABLE IF NOT EXISTS student (wID text, email text, UNIQUE(wID, email) ON CONFLICT ABORT)")
		self.cursor.execute("CREATE TABLE IF NOT EXISTS submissions (labNumber int, wID text, URL text, URLsToGrade text, Grade real)")
		self.cursor.execute("CREATE TABLE IF NOT EXISTS uniqueStudentURL (labNumber int, wID text, URL text, UNIQUE(URL) ON CONFLICT ABORT)")
		self.cursor.execute("CREATE TABLE IF NOT EXISTS expert (labNumber int, URL text, grade text, hidden int, PRIMARY KEY(labNumber, URL, hidden))")
		self.cursor.execute("CREATE TABLE IF NOT EXISTS grades (labNumber int, URL text, wID text, grade text, PRIMARY KEY(labNumber, URL, grade))")	
		self.cursor.execute("CREATE TABLE IF NOT EXISTS weight (wID text, weight real, UNIQUE(wID))")
		##check to see if the tables have already been created
		#creates columns in tables for each lab specified
		self.conn.commit()

	#adds a person into the database, works for both new users and existing ones 
	def addEntry(self, wID, URL,labNumber):
		if self.databaseName != None and self.conn != None and self.cursor !=None:
			#If the student did not submit a URL (aka the inputted URL is '')
			if URL == '':
				self.cursor.execute("INSERT INTO submissions VALUES(?,?,?,?,?)", [labNumber, wID, URL,'',''])	
			#try putting the student and its URL into the uniqueStudentURL database to check if the URL is unique
			else:	
				try:
					self.cursor.execute("INSERT INTO uniqueStudentURL VALUES (?,?,?)", [labNumber, wID, URL])
					#if there is no error in inserting to a table where URL has to be unique, put it in the actual student database
					self.cursor.execute("INSERT INTO submissions VALUES(?,?,?,?,?)", [labNumber, wID, URL,'',''])	
				#if the try fails, that means that the URL is already in the db, duplicate URL found!
				except:
					self.cursor.execute("SELECT wID FROM uniqueStudentURL WHERE URL=?", [URL])
					print "URL: " + URL + " was initially submitted by: " + self.cursor.fetchall()[0][0]
					URL = "DUPLICATEURL"
					self.cursor.execute("INSERT INTO submissions VALUES(?,?,?,?,?)", [labNumber, wID, URL,'',''])	
			self.conn.commit()	
	#retrieves URL for a specific student and specific lab number
	def getURL(self, wID, labNumber):
		
		self.cursor.execute("SELECT URL FROM submissions WHERE labNumber=? AND wID=?", [labNumber, wID])
		URL = self.cursor.fetchone();
		return (URL[0])

	def addExpertURL(self, labNumber, URL,  grade, hidden):
		
		self.cursor.execute("SELECT * FROM expert WHERE URL = ?", [URL])
		#adds in a user if not in database already
		presentURL = self.cursor.fetchone()
		if presentURL ==  None:
			self.cursor.execute("INSERT INTO expert VALUES (?, ?, ?, ?)", [labNumber, URL, listToString(grade), hidden])
			self.conn.commit()	
		elif presentURL == URL: 
			print "The URL " + URL + " is already in the expert database"
		else:
			sys.exit("Trying to overrite")

		##find a way to make seperate expert tables for each lab, and then join them together to prevent the staggaring of grades in the excel sheet
		#self.cursor.execute("SELECT * FROM expert WHERE Lab1Grade")
		#print self.cursor.fetchall()
		
		#query = ("SELECT {0} FROM expert WHERE wID
	def getExpertURLs(self, labNumber):
		self.cursor.execute("SElECT URL, grade FROM expert where labNumber=?", [labNumber])
		URLsAndGrades = {}
		for d in self.cursor.fetchall():
			URLsAndGrades[str(d[0])] = stringToList(str(d[1]))
		return URLsAndGrades

	def finalize(self, labNumber, seed, N, MOOC=False):
		##randomize the youtube URLs
		#for each wID
			#put that into the databse under the student ID
		self.cursor.execute("SELECT URL FROM expert WHERE labNumber=? and hidden=0", [labNumber])
		expertURL = [str(d[0]) for d in self.cursor.fetchall()]
		

		# find all the hidden expert videos	
		self.cursor.execute("SELECT URL FROM expert WHERE labNumber=? and hidden=1", [labNumber])
		hiddenURL = [str(d[0]) for d in self.cursor.fetchall()]
		
		#get all the studnet URLs
		self.cursor.execute("SELECT URL from submissions WHERE labNumber=?", [labNumber])
		data = [str(d[0]) for d in self.cursor.fetchall()]
		
		#assign the students whos videos are designated expert graded URLs to grade, and remove them from the URL pool retrieved above
		if len(expertURL) + N + 1 <= len(data):
			pseudoURL = {}
			for d in expertURL:
				#if the expertURL is not in the data list, then it is a video that is not submitted by a student this sem
				#semester, in which case, we skip it
				if d in data:
					self.cursor.execute("SELECT wID FROM submissions WHERE URL=?", [d])
					indice = (data.index(d) + 1) % len(data)
					while data[indice] in expertURL or data[indice] in hiddenURL:
						indice = (indice + 1) % len(data)
					pseudoURL[d] = data[indice]
					data.remove(d)
			for d in hiddenURL:
				if d in data:
					indice = (data.index(d) + 1) % len(data)
					while data[indice] in expertURL or data[indice] in hiddenURL:
						indice = (indice + 1) % len(data)
					pseudoURL[d] = data[indice]
					data.remove(d)

		self.cursor.execute("SELECT wID FROM submissions WHERE labNumber=? and URL is ''", [labNumber])
		noURLSubmitted = [str(d[0]) for d in self.cursor.fetchall()]
		wIDPseudoURL = {}
		if(data.count('') > 0) and not MOOC:
			for d in noURLSubmitted:
				indice = (data.index('') + 1) % len(data)
				while data[indice] == '':
					indice = (indice + 1) % len(data)
				wIDPseudoURL[d] = data[indice]
				data.remove('')
		else:
			while '' in data:
				data.remove('')


		self.cursor.execute("SELECT wID FROM submissions WHERE labNumber=? AND URL=?", [labNumber, "DUPLICATEURL"])
		noURLSubmitted = [str(d[0]) for d in self.cursor.fetchall()]
		if(data.count("DUPLICATEURL") > 0) and not MOOC:
			for d in noURLSubmitted:
				indice = (data.index("DUPLICATEURL") + 1) % len(data)
				while data[indice] == "DUPLICATEURL":
					indice = (indice + 1) % len(data)
				wIDPseudoURL[d] = data[indice]
				data.remove("DUPLICATEURL")
		else:
			while '' in data:
				data.remove('')

		
		#self.cursor.execute(query)

		random.shuffle(data)
		selectFrom = data + data[:N + len(expertURL) + 1]
		if len(pseudoURL.keys()) > 0:
			params = ("Lab" + str(labNumber) + "URLSToGrade", "Lab" + str(labNumber) + "URL")
			for key in pseudoURL.keys():
				startIndex = selectFrom.index(pseudoURL[key])
				URLSToGrade = selectFrom[startIndex: startIndex+N+1]
				for i in hiddenURL: 
					URLSToGrade.append(i)
				random.shuffle(URLSToGrade)
				self.cursor.execute("UPDATE submissions SET URLsToGrade=? WHERE URL=?", [listToString(expertURL + URLSToGrade), key])
				self.conn.commit()
		if len(wIDPseudoURL.keys()) > 0:
			for key in wIDPseudoURL.keys():
				startIndex = selectFrom.index(wIDPseudoURL[key])
				URLSToGrade = selectFrom[startIndex: startIndex+N+1]
				for i in hiddenURL: 
					URLSToGrade.append(i)
				random.shuffle(URLSToGrade)
				self.cursor.execute("UPDATE submissions SET URLsToGrade=? WHERE wID=?", [listToString(expertURL + URLSToGrade), key])
				self.conn.commit()
			
		if len(data) > N:
			for d in data:
				startIndex = selectFrom.index(d)
				URLSToGrade = selectFrom[startIndex:startIndex+N+1]
				for i in hiddenURL: 
					URLSToGrade.append(i)
				random.shuffle(URLSToGrade)
				params = ("Lab" + str(labNumber) + "URLSToGrade", "Lab" + str(labNumber) + "URL")
				self.cursor.execute("UPDATE submissions SET URLsToGrade=? WHERE URL=? and labNumber=?", [listToString(expertURL + URLSToGrade), d, labNumber])
		self.conn.commit()
	def getURLsToGrade(self, wID, labNumber):
		self.cursor.execute("Select URLsToGrade FROM submissions WHERE wID=? and labNumber=?", [wID, labNumber])
		dbExtract = self.cursor.fetchone()
		if dbExtract == None:
			return False
		else:
			return [i for i in stringToList(dbExtract[0])]
	
	def addGrade(self, wID, labNumber, URL, grade):
		URLsToGrade = self.getURLsToGrade(wID, labNumber)
		if URLsToGrade != False:
			if URL in URLsToGrade:
				self.cursor.execute("INSERT INTO grades VALUES(?, ?, ?, ?)", [labNumber, URL, wID, listToString(grade)])
				self.conn.commit()
			else:
				print "wID: " + wID + " was not assigned to grade URL: " + URL
		else:
			print("wID: " + wID + " not in the submissions table")

	def wIDGradesSubmitted(self, wID, labNumber):
		URLsToGrade = self.getURLsToGrade(wID, labNumber)
		gradesSubmitted = {}
		for URL in URLsToGrade:
			self.cursor.execute("SElECT grade FROM grades WHERE wID = ? AND URL = ?",[wID, URL])
			dbExtract = self.cursor.fetchall()
			#if they did not grade the URL assigned to them
			if dbExtract!=[]:
				gradesSubmitted[URL] =  stringToList(str(dbExtract[0][0]))
			else:
				gradesSubmitted[URL] = None
		return gradesSubmitted

	def compareToExpert(self, wID, labNumber):
		expertURLsAndGrades = self.getExpertURLs(labNumber)
		userSubmittedGrades = self.wIDGradesSubmitted(wID, labNumber)
		URLsGraded = userSubmittedGrades.keys()
		for key in expertURLsAndGrades.keys():
			if key in URLsGraded:
				print expertURLsAndGrades[key]
				print userSubmittedGrades[key]	
				
	def getGrades(self, wID, labNumber):
		URL = self.getURL(wID, labNumber)
		self.cursor.execute("SELECT grade,wID FROM grades WHERE URL=?", [URL])
		grades = {}
		for d in self.cursor.fetchall():
			grades[str(d[1])] = str(d[0])
		return grades
	def check(self, labNumber):
		params = ("Lab" + str(labNumber) + "URL", "Lab" + str(labNumber) + "URLsToGrade", None)
		self.cursor.execute("Select URL, URLsToGrade FROM submissions WHERE URL!= ''")
		fetch = self.cursor.fetchall()
		individualURL = [str(d[0]) for d in fetch]
		URLList = listToString([str(d[1]) for d in fetch])

		for i in range(1, len(individualURL)-1):
			if individualURL[i] not in stringToList(URLList[i]):
				print individualURL[i]
				return False
		return True
		


if True:
	os.remove("test.db")
	makeDatabase("test.db")
	sqldb = SqliteDB("test.db")
	sqldb.createTables()
	sqldb.addEntry("1", "1lkjsdf", 1)
	sqldb.addEntry("2", "1lkjsdf", 1)
	sqldb.addEntry("3", "1lkjsdf", 1)
	sqldb.addEntry("4", "4lkjsdf", 1)
	# sqldb.addEntry("4a",None , 2)
	sqldb.addEntry("5", "5lkjsdf", 1)
	sqldb.addEntry("6", "6lkjsdf", 1)
	sqldb.addEntry("7", "7lkjsdf", 1)
	sqldb.getURL("1", 1)
	sqldb.getURL("2", 1)
	sqldb.addExpertURL(1, "5lkjsdf",[1, 2, 3, 4, 5, 6, 7], 0)
	sqldb.addExpertURL(1, "2lkjsdf", [1, 7, 3, 1, 6, 3], 0)
	# sqldb.addEntry("8", None, 2)
	sqldb.addEntry("8", '', 1)
	sqldb.addEntry(9, "hidden", 1)
	sqldb.addExpertURL(1, "hidden", [1, 2, 3], 1)
	print "testing below"
	sqldb.finalize(1, 1, 3)
	print sqldb.getURLsToGrade("1", 1)
	sqldb.addGrade("1",1,  "5lkjsdf", [1, 2, 3, 4])
	sqldb.addGrade("12",1,  "asdf", 1)
	sqldb.addGrade("1", 1, "2kjla", 1)
	sqldb.addGrade("2", "1", "5lkjsdf", [4, 3, 2, 1])
	sqldb.wIDGradesSubmitted("1", 1)
	sqldb.getGrades("5", 1)
	sqldb.getExpertURLs(1)
	sqldb.compareToExpert("1",1)
	sqldb.check(1)
# sqldb.addExpert("expertVideo", 1, 1)		
# sqldb.addExpert("test2", 2, 2)