Did a quick test using psycopg2 and it returns a tuple of six longs:
(9892718L, 1046L, 189L, 235L, 9890143L, 1105L)
---------------------------
import psycopg2
import psycopg2.extensions
DB_HOST = 'localhost'
DB_NAME = 'hdpsdb'
DB_USER = 'testuser'
DB_PASS = 'testuser'
db_uri = """dbname='%s' user='%s' host='%s' password='%s'""" %
(DB_NAME,DB_USER,DB_HOST,DB_PASS)
pg2con = psycopg2.connect(db_uri)
cursor=pg2con.cursor()
cursor.execute("""SELECT * FROM farm.call_job_status('testshow');""")
row = cursor.fetchone()
print row
cursor.close()
pg2con.close()
Michael Bayer wrote:
> David Gardner wrote:
>
>> I have a composite type that I defined as:
>>
>> CREATE TYPE farm.job_status_ret AS
>> (total bigint,
>> "valid" bigint,
>> invalid bigint,
>> processing bigint,
>> pending bigint,
>> canceled bigint);
>>
>>
>> I dropped the text field. When I run the query in postgres I get the six
>> distinct fields:
>> hdpsdb=# SELECT * FROM farm.call_job_status('testshow');
>> total | valid | invalid | processing | pending | canceled
>> ---------+-------+---------+------------+---------+----------
>> 9892718 | 116 | 20 | 0 | 9886233 | 6349
>>
>>
>> but from SQLAlchemy I just get a string:
>> >>> session.query(func.farm.call_job_status('testshow')).first()
>> ('(9892718,116,20,0,9886233,6349)',)
>>
>>
>> Looks like the TypeDecorator will do what I need.
>>
>
>
> it would be interesting to nail down exactly what psycopg2's contract is
> here. strange that it does that.
>
>
>
>
>
>> Michael Bayer wrote:
>>
>>> David Gardner wrote:
>>>
>>>
>>>> I have a PostgreSQL function that returns a composite type (a text
>>>> field
>>>> and 6 bigint columns).
>>>> Currently I am calling it with:
>>>> session.query(Job,func.farm.call_job_status(Job.path)).filter(Job.path=='testshow').first()
>>>>
>>>> Which returns a tuple, but the second element is a string. I could
>>>> probably parse the string, but that wouldn't be very elegant.
>>>> I was wondering is there an object that I can subclass to support this?
>>>>
>>>> I tried passing in type_=(String,Integer,...) as well as
>>>> type_=composite(SomeObj) neither worked.
>>>>
>>>>
>>> unsure what this means. the text field + 6 int columns are returned as
>>> one big string ? if so, that would be a postgresql/psycopg2 behavior,
>>> so
>>> you'd have to parse the string (most cleanly using TypeDecorator).
>>>
>>>
>>>
>>>
>>>
>> --
>> David Gardner
>> Pipeline Tools Programmer
>> Jim Henson Creature Shop
>> [email protected]
>>
>>
>>
>
>
> >
>
>
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
[email protected]
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
