Aha! thanks for the tip.
You are right, because that is the way psql returns it.
I re-wrote my PostgreSQL function to return an array of bigint,
which then psycopg2 and SQLAlchemy see as an array of integers, which
works out really great for me.
> I don't think SA is at fault: I believe that your SA query is generating
> different SQL than your manual SQL.
>
> Your SA query likely generates this SQL:
> SELECT farm.call_job_status(job.path) WHERE job.path = 'testshow' LIMIT 1;
>
> whereas your manual SQL is:
> SELECT * FROM farm.call_job_status('testshow');
>
> The key point is that your SA query has the function call in the columns
> clause (causing PostgreSQL to convert the tuple to a scalar), but your
> manual SQL has the function call in the from clause. Please try the top
> SELECT statement in psycopg2 and let us know if it returns a tuple
> instead of a string. For kicks, try it in psql too.
>
> I don't think this really helps you avoid parsing the result yourself,
> but at least you know why! :)
>
> -Conor
>
>
>> David Gardner wrote:
>>
>>> 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]
>>>
>>>
>>>
>>>
>> --
>> 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
-~----------~----~----~----~------~----~------~--~---
