[sqlalchemy] Re: passing tuple argument into sqlalchemy.sql.text string
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Faheem Mitha
Sent: 20 January 2009 22:05
To: sqlalchemy@googlegroups.com
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 ('DUKE1_plateA_A10.CEL',
'DUKE1_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=('DUKE1_plateA_A10.CEL',
'DUKE1_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
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: passing tuple argument into sqlalchemy.sql.text string
On Wed, 21 Jan 2009 10:55:14 -, King Simon-NFHD78
simon.k...@motorola.com wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Faheem Mitha
Sent: 20 January 2009 22:05
To: sqlalchemy@googlegroups.com
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 ('DUKE1_plateA_A10.CEL',
'DUKE1_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=('DUKE1_plateA_A10.CEL',
'DUKE1_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 = ['DUKE1_plateA_A10.CEL', 'DUKE1_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 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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: passing tuple argument into sqlalchemy.sql.text string
On Wed, 21 Jan 2009 08:26:13 -0800, jason kirtland
j...@discorporate.us wrote:
Faheem Mitha wrote:
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 ('DUKE1_plateA_A10.CEL',
'DUKE1_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=('DUKE1_plateA_A10.CEL',
'DUKE1_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.
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)
Thanks for the suggestion. Can such an array argument be passed in
from Python?
Regards, Faheem.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: passing tuple argument into sqlalchemy.sql.text string
Faheem Mitha wrote:
On Wed, 21 Jan 2009 08:26:13 -0800, jason kirtland
j...@discorporate.us wrote:
Faheem Mitha wrote:
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 ('DUKE1_plateA_A10.CEL',
'DUKE1_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=('DUKE1_plateA_A10.CEL',
'DUKE1_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.
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)
Thanks for the suggestion. Can such an array argument be passed in
from Python?
Give it a try and let us know how it goes.
Cheers,
Jason
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
