Use The Data Entry Form To Have Python Insert Data Into MySQL

August 9, 2019 Python And MySQL, Python In HTML No Comments

In our previous post we showed how to enter data into a data entry form and display the values on the screen.

In this post, we will continue the concept, but we are actually going to enter it into our MySQL database.

Here are a few changes we are making:

Python Global Variables In A Module

We are going to use some modular level variables to make our code easier to maintain. Here is a shot:

Python Procedure

We are going to use a Python procedure to handle the inserting of data into our MySQL database.

Here is all the code:

#!/usr/bin/python
print "Content-type: text/html\n\n"
print "<html>"
print "<head>"
print "<title>Providers</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
        
        #slice notation like substr
        arg= params[3:]
        print 'show clicked record on form'
        print '<br>'
        print '<br>'
        sql = "SELECT * FROM tblProviders WHERE prv_id=" + arg
        print sql
        print '<br>'
        print '<br>'
        show_table()
        
    else:
        #CREATE THE NEW RECORD
        
        #get the field 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"

        #insert the contents of the form into the table

        print "<h2>Enter New Provider:</h2>"
        insert_values(fname,city)  
        
        
        print "<form action = 'providers_add_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 = 'Submit' /></td>"
        print "</tr>"
        
        print "</table>"

        print "</form>"

        
        show_table()

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))
    
    # Close the connection
    conn.close()
    
def show_table():
    
    conn = MySQLdb.connect('localhost', USERNAME,PASSWORD,DB)

    cursor = conn.cursor()

    sql="SELECT * FROM tblProviders"

    cursor.execute(sql)

    # Get the number of rows in the result set
    numrows = cursor.rowcount

    print "<table width='100%' border='1px'>"
    print "<th>ID</th><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_add_form_mysql.py?id=" + str(id) + "'>" + 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 it is in action:

http://pythoninhtmlexamples.com/files/med/providers_add_form_mysql.py