ideally you should be able to say:
func.diary(iddip, fromd, tod, True).select(<params>)
however this will produce "SELECT diary(?, ?, ?, ?)" which isnt going
to work since its naming the function as a column. Function objects
in SA currently are designed to follow the syntax of a column, not a
from clause. this is related to ticket 172 http://www.sqlalchemy.org/
trac/ticket/172
For what you want to do below, i just committed a slight change in
changeset 1612 to enable a textual "FROM" clause to check for bind
parameter names, so with that revision you can say:
results = select(['iddip', 'day', 'amount'],
from_obj=[
"diary(:iddip,:fromdate,:todate,:live)"],
engine=metadata.engine
).execute(iddip=iddip, fromdate=fromd, todate=tod, live=True)
theres some more options using the text() function which can also
allow you to get typing information in there for the bind parameters
as well as the result set but it starts to get a little messy.
On Jun 12, 2006, at 3:13 PM, Lele Gaifax wrote:
> Hi,
>
> I'm having some trouble figuring out the best way to fetch data from a
> selectable stored procedure on a PG database.
>
> The only solution I found is the following::
>
> results = metadata.engine.text(
> 'select iddip, day, amount '
> 'from diary(:iddip,:fromdate,:todate,:live)'
> ).execute(iddip=iddip, fromdate=fromd, todate=tod, live=True)
>
> that does indeed return what I need. I can easily decorate the raw sql
> with some joins and I'm done.
>
> OTOH, this seems a little ugly, and I wonder if a better, more
> alchemic
> approach exists.
>
> I tried to get there using select(), for example::
>
> results = select(['iddip', 'day', 'amount'],
> from_obj=[
> "diary(:iddip,:fromdate,:todate,:live)"],
> engine=metadata.engine
> ).execute(iddip=iddip, fromdate=fromd, todate=tod, live=True)
>
> but that gave me a KeyError on 'iddip' no being given.
>
> Given that for all practical purposes such an SP is equivalent to a
> Table + an intrinsic set of bindparams, how can I best express and use
> such a source of data in SA?
>
> Thanks in advance for any hint, and of course for a great product too!
>
> bye, lele.
>
>
> _______________________________________________
> Sqlalchemy-users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
