[Zope-DB] Proper way to do DB connections from external methods?

[Charlie Clark]

On 2004-07-27 at 20:18:59 [+0200], Dave Cinege 
<dcinege-mlists-dated-1091384343.081965 at psychosis.com> wrote:
> On Tuesday 27 July 2004 05:23, you wrote:
> > On 2004-07-26 at 04:57:15 [+0200], Dave Cinege
> 
> > The best thing is to use connection objects and ZSQL methods unless you
> > have a need for extremely dynamic queries in which case you can use an
> 
> Yes I do. There just doesn't seem to be any point to doing any of this in
> dtml. Additionally I want this code to be consistent with the (much more
> extensive) back end.
> 
> I have already thought that I can make a single ZSQL method and just pass 
> the
> entire SQL statement to it.That sounds a bit dirty....
> 
> > external method to call the DA directly.
> 
> DA? Hmmm <thumbs through Zope Book> Ah, ok Database Adapter.
> I would *think* this would be a better way to do it. And when you say DA do
> you mean I would interact directly with the DA product, opening my own
> connctions, or with an existing Conneciton Object to a DA?
> 
> > Our mxODBCZopeDA has both query
> > and execute methods for example which allow you to hook into Zope's
> > transactional management but write your queries completely independently.
> 
> Any example snippets or docs on a standard way to interact with DA's?
> (Again I'm using mysql)

Now then, it does so happen there's one I prepared earlier:

3) Use an external Method. This has the advantage of being much finer 
grained 
conrtol than allowing all PythonScripts to do something but done properly is 
nearly as flexible.

Create a file in ~/Extensions say ODBC_SQL.py

This is essentially needs only one function:

def callSQL(connectionObject, statement):
	conn = connectionObject.database_connection()
	return conn.execute(statement)

Create an external method say SQL which points to callSQL in ODBC_SQL

You can then call this method from any PythonScript and simply pass it the 
connection and statement you want to execute.

In your case the PythonScript needs only to be modified slightly, depending 
on what you want to.

statement = """select [Building ID] from [Buildings]
 where [Building Name] = ?""", building)[1]
columns, results = context.SQL(container.crc_db, statement)

You might want to try and see if your DA supports this or the simple 
.query() method. If it works, fine, if not try our DA and see if that helps.

Charlie
-- 
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
 >>> 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 ! ::::