[Zope3-Users] SQL integration

Wed Feb 22 11:51:00 EST 2006

Here is the kind of thing I've been doing -- keep in mind, I'm still new at
this.  I'm not using connection caching as I let the database handle that -
I find it smarter and more powerful than Zope in this area (and just
simpler).  I have found the ISQLScript implementation to be a good example;
see if you can find and examine the source to that.  You can build off of
this as well if you want to use DTML.  I find I don't need DTML, so I don't.

My general approach for SQL integration is to create a content object that
represents my database application and it has different views, and contains
objects from the database as need be. In this way, I can have one database
connection used by all the views and objects, without having to manually
select the connection for each object or view.  Unlike sqlos, I do not need
to put it in a file and I can keep everything TTW.

I'm trying to brief, so if you need more ideas, let me know.

--
from zope.interface import implements
from interfaces import ICustomerApplication

from zope.app import zapi
from zope.app.rdb import queryForResults
from zope.app.rdb.interfaces import IZopeDatabaseAdapter
from zope.app.container.btree import BTreeContainer

class CustomerApplication(BTreeContainer):
    """A transaction object."""
    implements(ICustomerApplication)

    def __init__(self, connectionName=''):
        super(BTreeContainer, self).__init__()
        self.connectionName = connectionName

    def _setConnectionName(self, name):
        self._connectionName = name

    def _getConnectionName(self):
        return self._connectionName

    def getConnection(self):
        name = self.connectionName
        connection = zapi.getUtility(IZopeDatabaseAdapter, name)
        return connection()

    def listContacts(self):
        """List all of the functions"""

        try:
            connection = self.getConnection()
        except KeyError:
            raise AttributeError("The database connection '%s' cannot be "
                                 "found." % (self.connectionName))
        query = "select * from contacts"
        result = queryForResults(connection, query)
        return result

    # See zope.app.sqlscript.interfaces.ISQLScript
    connectionName = property(_getConnectionName, _setConnectionName)

--
Interface...
class ICustomerApplication(IContainer):
    """A Customer Application."""

    connectionName = Choice(
        title=u"Connection Name",
        description=u"The Connection Name for the connection to be used.",
        vocabulary="Connection Names",
        required=False)
--

My browser view is configured like:
  <page
      for="contacts.interfaces.ICustomerApplication"
      permission="zope.View"
      name="contacts.html"
      template="contacts.pt"
      class=".contacts.ContactsView"
      menu="zmi_views" title="Contacts"
      />

-- 
Page template something like:
<html metal:use-macro="context/@@standard_macros/view"
    i18n:domain="zope">
<body>
<div metal:fill-slot="body">
  <table class='listing'>
    <thead>
    <tr>
      <th>First Name</th>
      <th>Last Name</th>
    </tr>
    </thead>
    <tr tal:repeat="contact view/listContacts">
      <td tal:content="contact/firstname">Eileen</td>
      <td tal:content="contact/lastname">Dover</td>
    </tr>
  </table>

</div>

</body>
</html>