Document Actions

MySQL connectivity in Zope/Plone

This How-to applies to: Any version.
This How-to is intended for: Integrators, Customizers

Basics how to connect MySQL from Zope and Plone.

You already have a MySQL database and want to show its data in Plone? Well thats a simple task, read here how.

You're a PHP guy first time on Plone and like start a new project?
You want to use MySQL tables to store your data? Well, so in 90% of all use-cases you should not: Learn how to do it the object-oriented Plonish way! (Hint: use Archetypes and ArchGenXML)

First: All software you need is on sourceforge mysql-python project

So here the basic steps:

Install mysql-python to your Python. Most linux-distributions are offering this packaged. If you're on windows there is a .exe download. Follow the links and documentation at sourceforge.
Download zmysqlda from sourceforge and extract it to your Products directory.
Restart Zope, login as manager, go to your Plone ZMI.
Add a Z MySQL Database Connection in ZMI 'Add'-drop-down. Follow the verbose instructions how to connect you database.
Add a Z SQL Method using the connection and write in your query.

Thats all, now you have the connection and you can execute querys from within Plone using the test tab.

For more and detailled info about using the connection read Zope Book: Relational Database Connectivity

You can use Page-Templates instead of DTML as well. Follow the Plone way of page templates to make result-display looking plonish:

 <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>

 <metal:main fill-slot="main">
     <tal:main-macro metal:define-macro="main"
                     tal:define="results here/your_sql_methods_id">
     FILL IN YOUR XHTML/TAL TO DISPLAY RESULTS
     </tal:main>
 </metal:main>
 </body>
 </html>

by jensens — last modified March 13, 2006 - 01:03 All content is copyright Plone Foundation and the individual contributors.

how do you pass arguments to the SQL Method?

Posted by dbusarow at August 21, 2006 - 02:55

How would I pass an argument to the Z SQL Method?

Say I have a select * from table where name = ? how is that handled?

Also, I have not found any examples of writing a Z SQL Method on the file system. Is that possible?

Thanks Dan

answered

Posted by dbusarow at August 26, 2006 - 13:20

passing arguments: you don't. not really. you use inside the ZSQLMethod

use dtml-sqlvar instead of dtml-var to get SQL safe cleaning of the variable to prevent SQL injection attacks.

storing a Z SQL Method on file system: Add them to one of your products skins folders. I added mine to my DIYPloneSkin skins/templates folder. Name the file with a .zsql extension. The first line of the file needs to list the parameters with a line like my_param_variable Then the SQL statement itself on additional lines.

Next, in the template page that uses the ZSQLMethod refer to the filename including the .zsql e.g.

to access a Z SQL Method stored in the file Products/theme_name/skins/templates/get_dealer_list.zsql

Including the extension seems wrong but it's the only way I could get the template to find it.

tags disappers

Posted by dbusarow at August 26, 2006 - 13:25

try again using &; escapes for the tags the above should be

you use <dtml-sqlvar your_param type="string">

and the first line of a .zsql file should be <params>my_param_variable</params%gt;

closing sleeping connections

Posted by mschreiber at May 17, 2007 - 17:49

Is there a way to close the connection/request from Plone to the MySQL database after the query has been completed. I tend to have a lot of sleeping processes on my processlist in MySQL from Plone...they just sit there, never dying.

Thanks!

Plone CMS: Open Source Content Management

Sections

Personal tools

Find Documentation by Topic