CODEWITHSUNDEEP
pythonsqlzope
robcast
robcast

Reputation: 495

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't support bound parameters.

Upvotes: 1

Views: 931

Answers (2)

Martijn Pieters
Martijn Pieters

Reputation: 1121226

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.

Upvotes: 4

user2665694
user2665694

Reputation:

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

Upvotes: 1

Related Questions