Document Actions
Plone and MySQL
Note: Return to tutorial view.
Introduction
This tutorial assumes that you already have MySQL setup and that you have knowledge of TAL. For more background on TAL, go here:
http://www.zope.org/Documentation/Books/ZopeBook/2_6Edition/AdvZPT.stx
In order to use MySQL as an external database with Plone, you need to install the following packages so that MySQL can communicate with Zope. Installation of these packages is beyond the scope of this tutorial.
- Python MySQL Package
- Zope
ZMySQLDA
Once you have successfully installed both the Python MySQL and ZMySQLDA packages, you will need to create two things within Plone in order to begin interacting with your MySQL database:
- A Z MySQL database connection
- Z SQL Method(s)
Z MySQL Database Connection
If you have installed the ZMySQLDA package correctly, you will see “Z MYSQL Database Connection” in the drop down menu located at the top right corner of the ZMI.
When adding a new database connection you should place it in the folder where you will be using it.
Under the property “Enter a Database Connection String” your settings should look similar to this:
Enter a Database Connection String: database@host username password
If you get an error using the DNS name of your MySQL server, try using its IP address instead.
Z SQL Method
Each MySQL query that you use within a page needs to have a Z SQL Method object. This method is the actual query done by Zope that accesses the database. The Z SQL Method is called within a page template via TAL and/or Python.
Creating Your First Page
This already
assumes that there is a simple table in your MySQL database that exists called users, and that it has
the following data:
Table Name: users
id first_name last_name
----------------------------
1 Rivers Cuomo
2 Eddie Vedder
3 Thom Yorke
First, we’ll need to add a new Z SQL Method within the same folder as our Z MySQL database connection called select_all_users, which will select all of the names from the table users. To make sure your query works correctly, you should choose “Add and Test” to test it.
Page Template (ZPT)
Next, we’ll need to add a new page template from the drop down menu located at the top right corner of the ZMI.
Under the id field of the page template enter listnames.
Insert the following code:
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"
lang="en"
metal:use-macro="here/main_template/macros/master"
i18n:domain="plone">
<body>
<div metal:fill-slot="main">
<h1>My List of Names</h1>
<ul>
<li tal:repeat="records context/select_all_users">
<span tal:replace="records/first_name">First Name</span> <span tal:replace="records/last_name">Last Name</span>
</li>
</ul>
</div>
</body>
</html>
*Note – I’ve included a metal tag here so that this page will be displayed within the main content area of a Plone site.
By using the tal:repeat command, we loop through each record result of the select_all_users query and assign it to the variable records. Since our table contains 3 records, it will loop through 3 times. Each time it loops through, a call is being made for to retrieve the values of first_name and last_name and is displayed via the tal:replace command.
The rendered HTML will look like this:
...
...
<h1>My List of Names</h1>
<ul>
<li>Rivers Cuomo</li>
<li>Eddie Vedder</li>
<li>Thom Yorke</li>
</ul>
...
...
A few notes to consider:
Since this is a page template, it will not show up within Plone’s “Content” tab even though it exists in a folder in the ZMI. You will need to access it via a hard link. For example, if you created a folder called myfolder and placed listnames in that folder, you would be able to access it via /myfolder/listnames.
Passing Arguments
We’ve been able to successfully display data from a MySQL database in Plone, but what about inserting, updating, or deleting data via Plone? In this example, we’ll go over how to insert data into a MySQL database. You’ll be able to take these same principles and apply them to update and delete data.
There are many ways to do this, but for this tutorial we
will be creating a form within a page template and submitting the values to a
Python script, which will then insert the data into a MySQL database.
Creating the Form
Create another new page template with the id addnames.
Insert the following code:
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"
lang="en"
metal:use-macro="here/main_template/macros/master"
i18n:domain="plone">
<body>
<div metal:fill-slot="main">
<h1>Add New User</h1>
<tal:comment tal:replace="nothing">
Submits the form's data to the python script insertusers. insertusers is where
the transaction with the MySQL database actually takes place.
</tal:comment>
<form name="insertusers" action="insertusers" method="post">
<fieldset>
<legend>User Details</legend>
<div class="field">
<label>First Name</label>
<p><input size="60" type="text" name="first_name"></p>
</div>
<div class="field">
<label>Last Name</label>
<p><input size="60" type="text" name="last_name"></p>
</div>
</fieldset>
<div class="formControls">
<input type="submit" value="Submit">
</div>
</form>
</div>
</body>
</html>
With this form, the values first_name and last_name are submitted to the Python script insertusers which we will create next.
The Python Script
To add a Python script, select “Script (Python)” from the drop down menu located at the top right corner of the ZMI.
Under the
id field of the Python script enter insertusers.
Insert the following code:
#Import a standard function, and get the HTML request and response objects.
from Products.PythonScripts.standard import html_quote
request = container.REQUEST
RESPONSE = request.RESPONSE# Insert data that was passed from the form
context.insert_users(first_name=request.first_name, last_name=request.last_name)# Re-direct back to your location
RESPONSE.redirect('/wherever/your/page/is/located')
The values first_name
and last_name that were submitted by the
page template form addnames to this Python script are retrieved via REQUEST objects and
then inserted into the MySQL database using a Z SQL Method called insert_users().
You may also want to validate the data before it is inserted into your database. For this, you can use Controller Page Templates or create your own validation methods within this Python Script before the insert statement. However, if you use Controller Page Templates you’ll get all the nice bells and whistles that Plone comes with regarding error feedback, such as the orange boxes that appear when a field is missing on a form submission.
Z SQL Method
The Z SQL method that we use for inserting values into the
MySQL database contains the two values first_name and last_name which are passed as arguments. dtml-sqlvar is used
when passing arguments from the Z SQL Methods to your MySQL database. The Arguments must be the same name as the dtml-sqlvar values.
Conclusion
Since everything we have done resides within the ZMI and
uses page templates, the data will not be searchable within Plone’s search
tool. In order for your MySQL data to appear within Plone’s search tool, you
will need to insert the data you wish to be searchable into the ZCatalog. This can be done in the Python script before or after you insert, update or delete your data.
