In out last post we covered how to add a new record to the MySQL database. In this post will add to the previous and show you how to delete a record from the database.
First of all we are going to add 2 new columns to our datasheet below:
This will allow us to delete the row we’ve selected:
“http://pythoninhtmlexamples.com/files/med/providers_delete_form_mysql.py?id=14&action=delete”
Notice the “action=delete” parameter.
So basically we will click the link and pass the “action=delete” parameter back to the page. Then we’ll use that value to determine what action we will take.
#Python has no switch case statement
if action == 'edit':
if action == 'delete':
delete_values(arg)
if action == 'create':
If the action is delete then we call the delete_values procedure, passing the record id to the procedure.
def delete_values(id):
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
cursor = conn.cursor()
sql="DELETE FROM tblProviders WHERE prv_id=" + id
cursor.execute(sql)
print "****************RECORD DELETED****************"
# Close the connection
conn.close()
Here is all the code so far:
#!/usr/bin/python
print "Content-type: text/html\n\n"
print "<html>"
print "<head>"
print "<title>Delete Provider</title>"
print "</head>"
print "<body>"
#GLOBAL VARIABLES
USERNAME = 'username'
PASSWORD = 'password'
DB = 'database'
import MySQLdb
import os
# Import modules for CGI handling
import cgi, cgitb
def init():
#read the query params
params = os.environ.get('QUERY_STRING')
findthis="="
#find if there is an equal sign in the query param
#use int to convert the variable to a number from a text value
intEquals = int(params.find(findthis))
#test for queryparam or first time you have arrived
if intEquals > -1:
#RECORD ALREADY EXISTS
#------------------------------------------------------------------------------------
#["id"] - just selects the "id" parameter, but the [0] selects the actual value
arg = cgi.parse_qs( params )["id"][0]
#["action"] - just selects the "action" parameter, but the [0] selects the actual value
action = cgi.parse_qs( params )["action"][0]
#------------------------------------------------------------------------------------
#Python has no switch case statement
if action == 'edit':
sql = "SELECT * FROM tblProviders WHERE prv_id=" + arg
print sql
print '<br>'
print '<br>'
if action == 'delete':
#sql="DELETE FROM tblProviders WHERE prv_id=" + arg
delete_values(arg)
if action == 'create':
#print 'create the new record.<br>'
form = cgi.FieldStorage()
# Get data from fields
if form.getvalue('fname'):
fname = form.getvalue('fname')
else:
fname = "Not set"
if form.getvalue('city'):
city = form.getvalue('city')
else:
city = "Not set"
insert_values(fname,city)
#read the values from the database
fname=''
city=''
print "<form action = 'providers_delete_form_mysql.py' method = 'post'>"
print "<table width='100%' border='0px' bgcolor=lightgreen>"
print "<tr>"
print "<td><strong>First Name:</strong></td>"
print "<td><input type = 'text' name = 'fname' value='" + fname + "'></td>"
print "</tr>"
print "<tr>"
print "<td><strong>City:</strong></td>"
print "<td><input type = 'text' name = 'city' value='" + city + "'></td>"
print "</tr>"
print "<tr>"
print "<td><input type = 'submit' value = 'Edit' /></td>"
print "</tr>"
print "</table>"
print "</form>"
show_table()
else:
#CREATE THE NEW RECORD ONLY IF THE ACTION IS CREATE
print "New Record...."
#default values
fname=''
city=''
print "<form action = 'providers_delete_form_mysql.py?id=0&action=create' method = 'post'>"
print "<table width='100%' border='0px' bgcolor=lightgreen>"
print "<tr>"
print "<td><strong>First Name:</strong></td>"
print "<td><input type = 'text' name = 'fname' value='" + fname + "'></td>"
print "</tr>"
print "<tr>"
print "<td><strong>City:</strong></td>"
print "<td><input type = 'text' name = 'city' value='" + city + "'></td>"
print "</tr>"
print "<tr>"
print "<td><input type = 'submit' value = 'Submit' /></td>"
print "</tr>"
print "</table>"
print "</form>"
show_table()
def delete_values(id):
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
cursor = conn.cursor()
sql="DELETE FROM tblProviders WHERE prv_id=" + id
cursor.execute(sql)
print "****************RECORD DELETED****************"
# Close the connection
conn.close()
def insert_values(firstname,cty):
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 = "INSERT INTO tblProviders (prv_FirstName,prv_City) VALUES (%s, %s)"
cursor.execute(sql, (firstname, cty))
print '**************Record Added**************'
# Close the connection
conn.close()
def show_table():
print '<br><br>'
print "<a href='http://pythoninhtmlexamples.com/files/med/providers_delete_form_mysql.py'>Add New Record</a>"
print '<br><br>'
conn = MySQLdb.connect('localhost', USERNAME,PASSWORD,DB)
cursor = conn.cursor()
sql="SELECT * FROM tblProviders ORDER BY prv_ID"
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>"
print "<th>First Name</th><th>City</th>"
# Get and display all the rows
for row in cursor:
id = row[0]
print '<tr>'
print '<td>'
print "<a href='providers_delete_form_mysql.py?id=" + str(id) + "&action=edit'>" + str(id) + "</a>" #need to convert the index to a string
print '</td>'
print '<td>'
print "<a href='providers_delete_form_mysql.py?id=" + str(id) + "&action=delete'>" + str(id) + "</a>" #need to convert the index to a string
print '</td>'
print '<td>' + row[1] + '</td>'
print '<td>' + row[2] + '</td>'
print '</tr>'
print '</table>';
# Close the connection
conn.close()
#start here:
init()
print "</body>"
print "</html>"
Here is in action:
http://pythoninhtmlexamples.com/files/med/providers_delete_form_mysql.py
