passing tuple argument into sqlalchemy.sql.text string

[Faheem Mitha]

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.

[King Simon-NFHD78]

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

[Faheem Mitha]

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.

[jason kirtland]

IN takes a list of scalars, each of which requires its own :bind
parameter. On Postgresql you might find it more convenient to use ANY,
which takes a single array argument. "WHERE cell.patient_chipid ANY
(:plist)"

[Faheem Mitha]

Thanks for the suggestion. Can such an array argument be passed in
from Python?

Regards, Faheem.

[jason kirtland]

Give it a try and let us know how it goes.

Cheers,
Jason