Carsten Haese wrote:
> On Tue, 22 May 2007 17:33:27 -0500, Carl Karsten wrote
>> Or some such abomination of results of one query as a parameter of a
>> 2nd.
>>
>> given my use case, I can understand why this isn't in the spec, and
>> why it may never be. but it seems to come up more often that I
>> would expect, so here we go.
>>
>> My current problem: reconcile transaction details that are off due
>> to rounding errors. the 2 sets of details are stored on different
>> servers, and no chance of getting one server to hit the 2nd, so the
>> python client code is going to have to help by getting a list of
>> keys from one and constructing "WHERE OtherKey IN ( 'key1', 'key2',
>> 'key3', ...)" which isn't 'hard' but I find annoying that I have
>> to convert formats in the application layer.
>
> Option 1: Create a temporary table on one server and load the data from the
> other server into it. Then, use a server side subquery or join the tables
> together.
>
> If you don't have write permission on either server, there is Option 2: Fetch
> all relevant data from server 1 into client memory, fetch all relevant data
> from server 2 into client memory, and do the reconciliation in client memory.
The perms issue can be taken care of by having the table created ahead of time.
but, the application level code is still 'custom' and falls into a similar pit
as embedding parameters into the SQL command string.
>
> There is also Option 3: Use actual parameter passing to build a WHERE ... IN
> (...) clause:
>
> cSql = ("select ktbl2_fk from tbl3 where OtherKey IN ("
> +",".join("%s" for _ in cList)
> +")" )
> cur.execute(cSql, cList)
your solution #3 demonstrates my point perfectly:
TypeError: not enough arguments for format string
so a bit of debugging and I come up with this version:
list = ['%s' % x for x in rows]
cSql = ("select ktbl2_fk from tbl3 where ktbl1_fk IN ("
+",".join("%s" for _ in list)
+")" )
print cSql
cur.execute(cSql, list)
But that has 2 list comprehensions - In an attempt to get it in line with your
'simple' example:
list = rows
cSql = ("select ktbl2_fk from tbl3 where ktbl1_fk IN ("
+",".join("%s" for _ in list)
+")" )
print cSql
cur.execute(cSql, list)
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for the
right syntax to use near '),('2',),('3',))' at line 1")
This is exactly the kind of stumbling I am trying to avoid.
I would think that a list of items, or even a whole cursor should be able to be
passed in just as elegantly as they are returned.
It might even help the optimizers. this is a stretch: I am assuming these are
not 'the same': "where x in (?,?)" and "...(?,?,?)" as where a single ? that
represented a list of any size would use the same execution plan. (but I am in
way over my head here, so feel free to just say no.)
Carl K
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
