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.
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
>>> dgard...@creatureshop.com
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>
> --
> David Gardner
> Pipeline Tools Programmer
> Jim Henson Creature Shop
> dgard...@creatureshop.com
>
>
> >
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
