The following code will allow you to:
-Enter (insert) new quizzes
-Modify (Update) existing ones
-Delete quizzes you don’t want.
(Click image to see it in action – login with “demo” and “123”)
Click here for the main project link
#!/usr/bin/python
print "Content-type: text/html\n\n"
print "<html>"
print "<head>"
print "<title>Enter Quizes</title>"
print "</head>"
print "<body>"
#GLOBAL VARIABLES
USERNAME = 'db_user'
PASSWORD = 'pwd'
DB = 'database-name'
import MySQLdb
import os
#import requests #this script doesn't like this (doesn't fail, just blank screen)
# Import modules for CGI handling
import cgi, cgitb
def init():
#read the query params
params = os.environ.get('QUERY_STRING')
#DEBUG:
#print 'params= ' + str(params)
#print '<br>'
#print '<br>'
res = params.split('=') #TESTS THAT THERE IS A QUERY STRING
#display based on if params were sent
if str(res) == "['']":
#DEBUG:
print ('You should not be here')
print '<br>'
print "<a href='index.py'>You need to login first.</a>"
else:
#DEBUG:
#print ('some params were passed - show them')
#get the field values
form = cgi.FieldStorage()
# Get data from fields
if form.getvalue('quiz'):
quiz = form.getvalue('quiz')
else:
quiz = "Not set"
if form.getvalue('userid'):
userid = form.getvalue('userid')
else:
userid = "Not set"
#----------------------------
searchParams = [i.split('=') for i in params.split('&')] #parse query string
for key, value in searchParams:
#DEBUG:
#print('<b>' + key + '</b>: ' + value + '<br>\n')
#show the quizzes for user and allow for data entry
if key == 'id':
#when the form initializes, it should be blank
print("<h2>Enter And List Quizzes</h2>")
quiz = "Not set"
userid = value
show_form(value)
#********************
#insert the contents of the form into the table
#********************
show_table(userid)
elif key == 'insert':
print("<h2>Insert Record</h2>")
insert_values(userid,quiz)
show_table(userid)
elif key == 'edit':
print("<h2>Edit Record</h2>")
#uses cgi to parse the querystring, and ability to access individual arguments
userid = cgi.parse_qs( params )["user"][0]
qid = cgi.parse_qs( params )["edit"][0]
#-------------------------
#show the clicked record.
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
cursor = conn.cursor()
sql = "SELECT qui_quiz FROM tblQuizzes WHERE qui_id=" + qid
cursor.execute(sql)
conn.commit()
for row in cursor:
qui_quiz = row[0]
# Close the connection
conn.close()
#-------------------------
#show the edit version of the form
show_form_edit(userid,qid,qui_quiz)
show_table(userid)
elif key == 'edit2':
userid = form.getvalue('userid')
qid = form.getvalue('qid')
qui_quiz = form.getvalue('qui_quiz')
update_values(qid,qui_quiz,userid)
show_table(userid)
elif key == 'delete':
print("<h2>Delete Record</h2>")
#uses cgi to parse the querystring, and ability to access individual arguments
userid = cgi.parse_qs( params )["user"][0]
delete_values(value,userid)
show_table(userid)
#----------------------------
def insert_values(userid,quiz):
#DEBUG
#print ("<br><br>insert_values<br><br>")
#print ("userid="+ str(userid) + "<br>")
#print ("quiz="+ str(quiz) + "<br>")
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
cursor = conn.cursor()
#Python will handle the escape string for apostrophes and other invalid SQL characters for you
# %d or %i should be used for integer or decimal values.
# %s is used for string values
sql = "INSERT INTO tblQuizzes (qui_user_id,qui_quiz) VALUES (%s, %s)"
args=(userid, quiz)
try:
cursor.execute(sql, args)
#add this to make sure you data is being inserted
conn.commit()
print "<br><font color=green>****************RECORD INSERTED****************</font><br>"
print ("<h3><a href='enter-quizzes.py?id=" + str(userid) + "'>Back</a></h3>")
except error as e:
print("error")
print(e)
return None
# Close the connection
conn.close()
def update_values(quiz_id,quiz,userid):
#DEBUG
#print("edit = " + str(quiz) + " - for QUIZ=" + str(quiz_id) + "<br>")
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
cursor = conn.cursor()
#Python will handle the escape string for apostrophes and other invalid SQL characters for you
sql = "UPDATE tblQuizzes SET qui_quiz = %s WHERE qui_id =%s"
cursor.execute(sql, (quiz,quiz_id))
conn.commit()
print '<br><font color=green>**************Record Updated**************<br>'
print ("<h3><a href='enter-quizzes.py?id=" + str(userid) + "'>Back</a></h3>")
# Close the connection
conn.close()
def delete_values(arg,userid):
#DEBUG
#print("delete = " + arg + " - for user=" + userid + "<br>")
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
cursor = conn.cursor()
sql="DELETE FROM tblQuizzes WHERE qui_id=" + arg
try:
cursor.execute(sql)
conn.commit()
print "<br><font color=red>****************RECORD DELETED****************</font><br>"
print ("<h3><a href='enter-quizzes.py?id=" + str(userid) + "'>Back</a></h3>")
except error as e:
print(e)
return None
print "<br><font color=red>****************DELETION FAILED!****************</font><br>"
# Close the connection
conn.close()
def show_table(userid):
#DEBUG:
#print("show table<br>")
conn = MySQLdb.connect('localhost', USERNAME,PASSWORD,DB)
cursor = conn.cursor()
sql="SELECT * FROM tblQuizzes WHERE qui_user_id = " + userid
#DEBUG:
#print("<br>" + str(sql) + "<br>")
cursor.execute(sql)
# Get the number of rows in the result set
numrows = cursor.rowcount
print "<table width='100%' border='1px'>"
print "<th>Edit</th><th>Delete</th><th>Quiz</th>"
# Get and display all the rows
for row in cursor:
qid = row[0]
quiz = row[1]
print '<tr>'
print '<td width=2%>'
print "<a href='enter-quizzes.py?edit=" + str(qid) + "&user="+str(userid)+"'>Edit</a>" #need to convert the index to a string
print '</td>'
print '<td width=2%>'
print "<a href='enter-quizzes.py?delete=" + str(qid) + "&user="+str(userid)+"'>Delete</a>" #need to convert the index to a string
print '</td>'
print '<td>' + row[2] + '</td>'
print '</tr>'
print '</table>';
# Close the connection
conn.close()
def show_form(userid):
#DEBUG:
#print("show form<br>")
print "<form action = 'enter-quizzes.py?insert=1' method = 'post'>"
print "<table width='100%' border='0px' bgcolor=lightgreen>"
print "<tr>"
print "<td><strong>User:</strong></td>"
print "<td><input type = 'text' style='background-color:grey' readonly name = 'userid' value='" + str(userid) + "'></td>"
print "</tr>"
print "<tr>"
print "<td><strong>Quiz:</strong></td>"
print "<td><input type = 'text' name = 'quiz' value=''></td>"
print "</tr>"
print "<tr>"
print "<td><input type = 'submit' value = 'Submit' /></td>"
print "</tr>"
print "</table>"
print "</form>"
def show_form_edit(userid,qid,qui_quiz):
#DEBUG:
#print("show form<br>")
print "<form action = 'enter-quizzes.py?edit2=1' method = 'post'>"
print "<table width='100%' border='0px' bgcolor=lightgreen>"
print "<tr>"
print "<td><strong>User:</strong></td>"
print "<td><input type = 'text' readonly style='background-color:grey' name = 'userid' value='" + str(userid) + "'></td>"
print "</tr>"
print "<tr>"
print "<td><strong>Quiz ID:</strong></td>"
print "<td><input type = 'text' readonly style='background-color:grey' name = 'qid' value='" + str(qid) + "'></td>"
print "</tr>"
print "<tr>"
print "<td><strong>Quiz:</strong></td>"
print "<td><input type = 'text' name = 'qui_quiz' value='" + str(qui_quiz) + "'></td>"
print "</tr>"
print "<tr>"
print "<td><input type = 'submit' value = 'Submit' /></td>"
print "</tr>"
print "</table>"
print "</form>"
#start here:
init()
print "</body>"
print "</html>"
