[egenix-users] Accessing tables from a Python script in Zope
mal at egenix.com
Mon Jan 31 12:12:45 CET 2005
Breuer, Yvon wrote:
> Hello to all members of the list,
> I recently installed the mxODBCZopeDA and when I test it via the test tab everything works fine. A test via DTML worked fine too. Then I wanted to be more flexible so I added dtml-var's and again it worked just fine. But... ;-)
> I want to be completely free in how I built my SQL string. And I'd rather do this directly from a Python script in Zope instead of using a ZSQL Method. I need to be this flexible because I do not know which table I have to read and which fields I have to select before I start. Final goal is to copy data from one system to the other. So therefore I've got two eGenix mxODBC Database Connections in my root folder which I called SYS1 and SYS2. The ZSQL Methods I defined are ZSQLSYS1 and ZSQLSYS2. (How obvious ;)
> Enough background info, the question is: How can I execute a SQL statement from a Python script in Zope? I tried the sample coding from the Examples chapter in the mxODBC.pdf (in several ways), but this always resulted in an error.
You can do this by setting up one or more External Methods:
These method then have access to the connection objects. To get
access to a (physical) connection, simply call the connection
object. The Zope DA will then retun a DatabaseConnection object
which has among others, a method called .query().
You can call this method with an SQL statement and it will return
the results in form of a tuple (columns, rowset) with columns being
a Zope specific list of dictionaries describing the column types and
rowset a list of row tuples (in the order given in the columns
If you want to use bind parameters (? as place holders in the SQL),
you can also call the .execute() or .executemany() methods
on the DatabaseConnection (see the documentation for details).
Finally, if you want to bypass Zope completely, you can
import mxODBC directly in the external method and use its
interface. However, this is not recommended, since this also
bypasses the Zope transaction interface and could thus lead
to data corruption.
Professional Python Services directly from the Source (#1, Jan 31 2005)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
More information about the egenix-users