On Wed, 21 Jan 2009 10:55:14 -0000, King Simon-NFHD78 <[email protected]> wrote:
>> -----Original Message----- >> From: [email protected] >> [mailto:[email protected]] On Behalf Of Faheem Mitha >> Sent: 20 January 2009 22:05 >> To: [email protected] >> Subject: [sqlalchemy] passing tuple argument into >> sqlalchemy.sql.text string >> >> Hi, >> >> I've got a query as follows: >> >> from sqlalchemy.sql import text >> >> gq = text(""" >> SELECT decode_genotype(cell.snpval_id, snp.allelea_id, >> snp.alleleb_id) FROM cell JOIN snp ON snp.fid = >> cell.snp_id WHERE cell.patient_chipid IN ('DUKE00001_plateA_A10.CEL', >> 'DUKE00001_plateA_A11.CEL') >> """) >> I want to pass in the tuple as an argument, and was wondering >> how to do >> it. >> >> So, I'm looking for something conceptually like >> >> gq = text(""" >> SELECT decode_genotype(cell.snpval_id, snp.allelea_id, >> snp.alleleb_id) FROM cell JOIN snp ON snp.fid = >> cell.snp_id WHERE cell.patient_chipid IN :plist >> """) >> >> gq = conn.execute(gq, plist="('DUKE00001_plateA_A10.CEL', >> 'DUKE00001_plateA_A11.CEL')") >> >> Note, I want to pass in a tuple of arbitary length, so >> changing this to >> pass two string arguments would not do. Perhaps I'm supposed >> to pass in >> some bindparams too, but I don't know what type I should be using. >> Regards, Faheem. > I'm not sure you can do that in the general case. I think bind > parameters (in the DBAPI sense) are only really intended for > substituting individual query parameters, not lists. > > If you are happy to regenerate your query each time you want to execute > it, you could create a function which generates a string of the form > "(:p0, :p1, :p2, :p3)" for the given tuple length, and appends that to > the query. > > If you use the SQLAlchemy expression language to build that query, it'll > do that for you automatically. > > Hope that helps, > > Simon Hi Simon, Thanks for your reply. I've already been using sql expressions to create this query, but it was not obvious how to do this using copy to, so I switched back to not using it. gq = select([func.decode_genotype(cell_table.c.snpval_id, snp_table.c.allelea_id, snp_table.c.alleleb_id)], from_obj=[cell_table.join(snp_table)], order_by = 'sort_key(snp.chromosome), snp.location') patient_sublist = ['DUKE00001_plateA_A10.CEL', 'DUKE00001_plateA_A11.CEL'] gq = gq.where(cell_table.c.patient_chipid.in_(patient_sublist)) print gq #gq = conn.execute(gq).fetchall() The result of this is SELECT decode_genotype(cell.snpval_id, snp.allelea_id, snp.alleleb_id) AS decode_genotype_1 FROM cell JOIN snp ON snp.fid = cell.snp_id WHERE cell.patient_chipid IN (:patient_chipid_1, :patient_chipid_2) ORDER BY sort_key(snp.chromosome), snp.location The question is, can I make this into a copy using sql expressions, ie. can I do something like (the current version of my query) copy (select array_to_string(array_agg(e.decode_genotype_1), E'\t') from (SELECT decode_genotype(cell.snpval_id, snp.allelea_id, snp.alleleb_id) AS decode_genotype_1 FROM cell JOIN snp ON snp.fid = cell.snp_id WHERE cell.patient_chipid IN :plist ORDER BY sort_key(snp.chromosome), snp.location) as e) to '/tmp/btsnpSNP_6-chr.ped' with csv; The differences between the version above and the version below, are because I made additions to the query since I switched away from using sql expressions. Please CC me on any reply. Regards, Faheem Mitha. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
