How to directly query a relational/SQL database from a ZOPE product?

How do I connect to and query a relational database that was configured with a DA e.g. ZPsycopgDA in a Zope product?

I want to send my own SQL queries using bound parameters and receive the results preferrable as a set of Result objects.

I don't want to use ZSQLMethods as I can't create one for every query beforehand, and ZSQLMethods don开发者_如何学Python't support bound parameters.

---

The Zope Database Adapters (DA) code base is some of the oldest code still in use in Zope, and is thus somewhat archaic. When you call a Zope DA, you get a database connection object (a DB instance) that in turn has a query method:

connection = context.idOfZPsycoPGDA()
connection.query('SELECT * FROM your_table')

The query method is not exactly a Python database API standard method. It accepts multiple SQL statements separated by \0 null characters, but won't support SELECTs if there is more than one statement.

The ZPsychoDA query method also accepts query parameters:

connection.query('SELECT * FROM your_table WHERE id=?', ('yourid',))

but more importantly, the ZPsychoDA adapter also gives you access to a regular database cursor:

c = connection.cursor()
c.query('SELECT * FROM your_table WHERE id=?', ('yourid',))

My advise is to just use that and do regular Python DB API calls via the database cursor you get there.

---

The database connector instances should provide a query() method accepting a SQL command. And you get hold of the database adapter instance through acquisition (or traversal via restrictedTraverse('/path/to/da'):

result = context.my_zpyscopgda.query("select * from foo")

result = context.restrictedTraverse('/path/to/my_zpyscopgda').query("select * from foo")