Get dirty - wiring the form

by Paul Eipper — last modified Jul 15, 2009 03:57 AM
Let's code the backend of our form to insert the data on the DB.

It's here that we face actual code, even if not much of it, since Python makes it easy and fun for us :)



First we look what is that we need to do:

  • Process the input fields to conform to MySQL format (mainly the Date field).
  • Separate the keywords string to insert them into separate fields on the database.
  • If you used different names on the fields than the database you'll have to script through it as well.
  • Write the ZSQL methods to insert the data.


Now to convert this to code:

  1. Start by writing a Python script that will receive the form data and process it according to what we need. We'll call it py_form_process:
''' Process the form data to send to the database '''

request = container.Request
form = request.form
data = {}    # the dictionary that will hold our formated fields

''' Copy all form input data '''
for key in form.keys():
	data[key] = form[key]

''' Format the Date field to MySQL [YYYYMMDD] '''
data['Birthday'] = form['Birthday_year']  + \
		   form['Birthday_month'] + \
		   form['Birthday_day']

''' Format the Keywords string '''
data['keywords'] = form['Keyword'].replace(';',',').split(',')    # Replace ';' by ',' and then split on the commas
data['keywords'] = [ key.strip() for key in data['keywords'] ]    # Cleanup residual white-spaces

''' Format the Phone fields '''
# this is an example if you have different field names on the form
# than on what you'll need on the database. We are getting rid of
# phones that have only type but no number defined as well.

data['Telephone'] = []
for phone in form['Telephone']:
	if phone['column2']:
		data['Telephone'].append({ 'Phonetype' : phone['column1'],
					   'PhoneNumber' : phone['column2'] })

''' Forward our formatted data to a database dealing script '''
# receive the new ID for the person we create on the database
# so the ploneformgen success page shows the new ID.

form['IDperson'] = context.py_insert_data(data)
As you can see, we are forwarding the data to another script. It is better to break the code into small parts that do specific things, rather than one big script that does it all.


    2.  Next is our py_insert_data. Type data on the Parameter List to receive the data from our previous script.

''' Insert the formatted data in the database '''

''' Inserts the person data and returns the created ID '''
# we need the new ID to define the relations of the other tables.
# see the zsql method on how we return this.

newID = context.zsql_add_person(data)
data['IDperson'] = newID[0][0]

# now insert the other tables:

''' Info table '''
if data['InfoText']:
	context.zsql_add_info(data)

''' Keyword table '''
for key in data['keywords']
	if not key == '':
		data['Keyword'] = key
		context.zsql_add_keyword(data)

''' Donor table '''
for donor in data['Donor']:
	if donor['DonorName']:
		for key in donor.keys():
			data[key] = donor[key]
		context.zsql_add_donor(data)

''' Telephone table '''
for phone in data['Telephone']:
	if phone['PhoneNumber']:
		for key in phone.keys():
			data[key] = phone[key]
		context.zsql_add_telephone(data)

return data['IDperson']

Notice that we are using several ZSQL methods, that is, one for each table of our database. Defining them is our next step.


    3.  The ZSQL methods require knowledge of SQL and DTML ( unfortunately ;), I hope it is simple enough to follow along. These are the methods we'll need:

  • zsql_add_person
  • zsql_add_info
  • zsql_add_keyword
  • zsql_add_donor
  • zsql_add_telephone

Here is how we implement these ZSQLs. Please watch that on some tables I follow the field insert order as defined in the example table. I hope you know enough SQL to alter these details as needed or explicitly name the fields if required.

zsql_add_person

Arguments: Name, Birthday
INSERT INTO Person
VALUES (0,
    <dtml-sqlvar Name type=string>,
    <dtml-sqlvar Birthday type=int>
)

<dtml-var sql_delimiter>

SELECT MAX(IDperson) from Person

The SELECT MAX will return the last ID inserted on the Person table. We rely on this ID for all other inserts.


zsql_add_info

Arguments: IDperson, InfoText
INSERT INTO Info
VALUES (0,
    <dtml-sqlvar InfoText type=string>,
    <dtml-sqlvar IDperson type=int>
)

zsql_add_keyword

Arguments: IDperson, Keyword
INSERT INTO Keyword (Keyword)
VALUES (
    <dtml-sqlvar Keyword type=string>
)

ON DUPLICATE KEY UPDATE
   IDkey = LAST_INSERT_ID(IDkey)

<dtml-var sql_delimiter>

INSERT INTO Person_has_Keyword
VALUES (
    <dtml-sqlvar IDperson type=string>,
    LAST_INSERT_ID()
)

This one is a little more involved. The ON DUPLICATE is if we receive a duplicate Keyword, it will just return us the existing ID. Then we insert this ID and the Person ID on the pivot table.


zsql_add_donor

Arguments: IDperson, DonorName, Amount
INSERT INTO Donor (DonorName)
VALUES (
    <dtml-sqlvar DonorName type=string>
)
ON DUPLICATE KEY UPDATE
   IDdonor = LAST_INSERT_ID(IDdonor)

<dtml-var sql_delimiter>

INSERT INTO Person_has_Donor
VALUES (
    <dtml-sqlvar IDperson type=string>,
    LAST_INSERT_ID(),
    <dtml-sqlvar Amount type=string>
)


zsql_add_telephone

Arguments: IDperson, PhoneType, PhoneNumber
INSERT INTO Telephone
VALUES (0,
    <dtml-sqlvar PhoneType type=string>,
    <dtml-sqlvar PhoneNumber type=string>,
    <dtml-sqlvar IDperson type=int>
)


Back to the form, click the Edit link, access the overrides page and add in the After Validation Script field the call to the process form script:

here/py_form_process

Save it, and try it out. Check if it inserting the entered data correctly. Hopefully it works! :)


Check out the treasure map below, of how our system is currently structured:

Wiring Map