How to call a python script within a Z-SQL method?
Hello list. I'm trying to pass a result (or column of results) to a python script, within an SQL query. SELECT dealer_no, COUNT(mobile_no) as connects, entry_date FROM connects WHERE entry_date >= #2003/11/20# GROUP BY dealer_no, entry_date where I'm wanting entry_date to be passed to a python script (and have the result returned as part of the sql query) How Can I do something like this? ***************************** SELECT dealer_no, COUNT(mobile_no) as connects, <dtml-call to_month(entry_date)> FROM connects WHERE entry_date >= #2003/11/20# GROUP BY dealer_no, entry_date ***************************** ###to_month return input.mm() Thanks Muchly Regards, Julian Clark
Julian Clark wrote:
I'm trying to pass a result (or column of results) to a python script, within an SQL query.
...
where I'm wanting entry_date to be passed to a python script (and have the result returned as part of the sql query)
How Can I do something like this? ***************************** SELECT dealer_no, COUNT(mobile_no) as connects, <dtml-call to_month(entry_date)> FROM connects WHERE entry_date >= #2003/11/20# GROUP BY dealer_no, entry_date *****************************
###to_month return input.mm()
Sort of. If you read the DTML specs, you will see that dtml-call returns nothing, but dtml-var does. --jcc -- "My point and period will be throughly wrought, Or well or ill, as this day's battle's fought."
It was very unclear to me what you actually are trying to do here. The subject line is easy -- you can call python scripts exactly as you would from any other DTML: <dtml-var expr="compute_entry_date(ham, spam)"> The result will be *rendered into the query* and then sent to the SQL engine to return an SQL results object. But your description clouds this. It sounds like you actually want to do the query and then call a python script to act on the *results of the query* not the *query*. If that's the case, you need to wrap the SQL in a python script call -- i.e. you don't want to call python from SQL, you want to call an SQL query from python. That's not too hard, though -- the results object behaves very much like a python sequence. If you want to see what you're working with, just return the object or a str() conversion of it so you can see the output. I think you might have to rephrase your question in those terms if it still isn't clear what you need to do. Cheers, Terry -- Terry Hancock ( hancock at anansispaceworks.com ) Anansi Spaceworks http://www.anansispaceworks.com
Hi Terry, You understood me correctly. Using a sql query called from within a python script, would you write the query within the script? or would you call a seperate sql method from the script? Maybe I'm thinking in the wrong direction, but if the first case is correct, how would you specify the database connection that you would use for the sql from within the script? (apologies if this is a stupid question, I'm still relatively new to python, and I'm only now getting the hang of dtml) Cheers, Julian -----Original Message----- From: Terry Hancock [mailto:hancock@anansispaceworks.com] Sent: Thursday, 27 November 2003 2:56 AM To: zope Cc: Julian Clark Subject: Re: [Zope] How to call a python script within a Z-SQL method? It was very unclear to me what you actually are trying to do here. The subject line is easy -- you can call python scripts exactly as you would from any other DTML: <dtml-var expr="compute_entry_date(ham, spam)"> The result will be *rendered into the query* and then sent to the SQL engine to return an SQL results object. But your description clouds this. It sounds like you actually want to do the query and then call a python script to act on the *results of the query* not the *query*. If that's the case, you need to wrap the SQL in a python script call -- i.e. you don't want to call python from SQL, you want to call an SQL query from python. That's not too hard, though -- the results object behaves very much like a python sequence. If you want to see what you're working with, just return the object or a str() conversion of it so you can see the output. I think you might have to rephrase your question in those terms if it still isn't clear what you need to do. Cheers, Terry -- Terry Hancock ( hancock at anansispaceworks.com ) Anansi Spaceworks http://www.anansispaceworks.com
On Thursday 27 November 2003 09:12 pm, Julian Clark wrote:
Using a sql query called from within a python script, would you write the query within the script? or would you call a seperate sql method from the script?
The latter, definitely. You just want to call the ZSQL method (which is just like any other function, really. The only thing that is weird is that ZSQL methods only take keyword arguments). The object you get back is a "results" object. I can tell you that it isn't really*, but it very much acts like a list of class instances where each instance contains attributes corresponding to the keys in the returned DB row. (And no methods -- you can add methods to them with "Pluggable Brains", but that's an advanced topic). Your 2nd question therefore doesn't arise. I would recommend separately testing the ZSQL method object (use the test tab that is part of ZSQL method objects' management interface), before trying to implement a python script around it. You'll also want to dump the results as text from the Python script to show you what it looks like as a Python literal: ## In your script results = myZSQLQuery(foo='spam', spam='foo', ...) return str(results) That'll help you figure out the python code you need to do the job. You will likely find list comprehensions useful: def isGoodRow(row): # decide if we want this row if good: return 1 else: return 0 def cookRow(row): # do some stuff to the row data return cooked_row return [cookRow(r) for r in results if isGoodRow(r)] or something like that. Cheers, Terry *It's actually a specially implement instance with a sequence interface, that simulates this behavior. Apparently for improved performance with long query results. -- Terry Hancock ( hancock at anansispaceworks.com ) Anansi Spaceworks http://www.anansispaceworks.com
Julian Clark wrote at 2003-11-26 17:07 +0800:
I'm trying to pass a result (or column of results) to a python script, within an SQL query.
Sounds impossible...
SELECT dealer_no, COUNT(mobile_no) as connects, entry_date FROM connects WHERE entry_date >= #2003/11/20# GROUP BY dealer_no, entry_date
where I'm wanting entry_date to be passed to a python script (and have the result returned as part of the sql query)
"entry_date" is a field in your database table. A Python script can only access this field through SQL (which is possible, e.g. using a second Z SQL method). When you call the script in the SQL code above, then the scripts result will affect the *SOURCE* for an SQL command but not its result.
... How Can I do something like this? ***************************** SELECT dealer_no, COUNT(mobile_no) as connects, <dtml-call to_month(entry_date)> FROM connects WHERE entry_date >= #2003/11/20# GROUP BY dealer_no, entry_date *****************************
###to_month return input.mm()
You do not call the python script inside the SQL but use a Python Script to postprocess the result. The easiest way is to call "to_month" on access to "entry_date". When you know the result set is small, you can also use something like transformedQuery(): r = context.mySQLMethod() for hit in r: r.entry_date = entry_date.mm() return r -- Dieter
participants (4)
-
Dieter Maurer -
J. Cameron Cooper -
Julian Clark -
Terry Hancock