David Gardner wrote:
> Thanks for the tip about TypeDecorator I got it working to parse up
> the string, still not sure why SA is returning a string and not a
> tuple of integers,
> but at this point I have a good work-around and I'm happy. Especially
> since TypeDecorator allows me to return those numbers
> as a dictionary.
>
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]
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
