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 >>>>> 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 >> >> >> > > > > > > -- 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 -~----------~----~----~----~------~----~------~--~---