In this post we will be updating our MySQL database using Python.
#!/usr/bin/python
print "Content-type: text/html\n\n"
print "<html>"
print "<head>"
print "<title>Update 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':
#---------------------------------------------------------------
#1. show the clicked record.
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
cursor = conn.cursor()
sql = "SELECT * FROM tblProviders WHERE prv_id=" + arg
cursor.execute(sql)
for row in cursor:
fname = row[1]
city = row[2]
#print fname
#print city
print "****************EDIT RECORD****************"
# Close the connection
conn.close()
if action == 'update':
#---------------------------------------------------------------
#2.Update the new values
#---------------------------------------------------------------
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"
update_values(fname,city,arg)
if action == 'delete':
fname = ''
city = ''
delete_values(arg)
if action == 'create':
#print 'create the new record.<br>'
fname = ''
city = ''
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)
print "<h2>Edit Record</h2>"
print "<form action = 'providers_update_form_mysql.py?id=" + arg + "&action=update' 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
#default values
fname=''
city=''
print "<h2>New Record</h2>"
print "<form action = 'providers_update_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 update_values(firstname,cty,arg):
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 tblProviders SET prv_FirstName = %s, prv_City= %s WHERE prv_id =%s"
cursor.execute(sql, (firstname, cty,arg))
print '**************Record Updated**************'
# Close the connection
conn.close()
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_update_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 width=3%></th><th width=3%></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_update_form_mysql.py?id=" + str(id) + "&action=edit'>Edit</a>" #need to convert the index to a string
print '</td>'
print '<td>'
print "<a href='providers_update_form_mysql.py?id=" + str(id) + "&action=delete'>Delete</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 it is in action:
http://pythoninhtmlexamples.com/files/med/providers_update_form_mysql.py
