How do I create a threadsafe connector?

Introduction

Despite the ZODB being really nice - and often a fine solution for databases - it may be necessary to use a SQL backend (for existing projects) or just because you like SQL to manage extremely large databases.

In general for all interactive things ZODB is the better choice because it allows you to use ZOPE's nice through the web facilities. Working with SQL data is a lot more work creating forms, managing permissions etc. I chose it for a financial system so people could use other SQL clients to manage some of the data.

SQL Adapters

Best is to use/adapt/study one of the existing SQL connectors. Quite a few people use the one for PostgreSQL? (psycopgda) - which acts as a default implementation and the documentation is reasonable. The MySQL? (mysqldbda) version is a little out of date - but so far has worked for me.

The main reason for using one of these adapters is that they are thread-safe. One thing I found out the hard way is that, while ZOPE uses a threaded webserver all threads share the same address space (i.e. one Python interpreter). Defining a global connector to attempt connection pooling leads to inconsistent data.

It is a warning: NEVER USE GLOBAL DATA in ZOPE. It is not safe. And it is one of those areas where Python does not protect you from your own foolishness.

To use an adapter just follow the instructions that come with the package. You will be able to set up a connection through the [ZMI]? - it is a ZOPE utility. This utility also allows you to run simple SQL scripts from the web interface.

Once the utility has been initialised properly you can call it as a ZOPE utility and a database connector is returned.

Setting up the connection

Unpack the SQL adapter package. Read the instructions that come with it first. Here I just list the steps as an example:

Add the package location to the PYTHONPATH and add the provided configure.zcml, e.g. mysqldbda-configure.zcml, to ./etc/package-includes/.

Run the site from:

http://yoursite/++skin++Rotterdam/++etc++site/tools/@@contents.html

Which should allow you to add a database adapter of the form:

dbi://username:password@host:port/databasename

Make sure you are setting the correct permissions for the connector. In principle you can now run SQL queries through the ZMI (without any Python!) - as well as getting the database handle from the adapter:

adapter = zapi.queryUtility(IZopeDatabaseAdapter, 'myadapter')
if adapter is None:
  raise ConnectionError, ('Invalid adapter')
connection = adapter()
return queryForResults(connection, 'select * from table')

Which returns a list of rows/columns as a ResultSet?.

An example can be found in Richter's ZOPE Development Book online http://wiki.zope.org/zope3/talesexpr.html. For more check the implementation and tests in the ZOPE source tree at ./zope/app/rdb.

If you feel this page is incorrect/incomplete - please edit it!