Multiple arguments for Python MySQLdb clause

Question

Below is a small subset of the data I'm working with. I can format the data any way I please. The data within the variable 'dc' is made up of the values 'id1' and 'id2'. What I want to do is be able to issue one SELECT statement for all of the values I have in 'dc'. For some reason, no matter what I try in the 'cursor.execute' statement or within the 'format_strings' variable I can't seem to get the proper code to be able to pass two variables to MySQL.

Comments/suggestions on how to format the data ('dc') or code to perform one SELECT statement would be very helpful.

results = ()

dc = ['103,4770634', '42,427752', '64,10122045', '42,13603629', '42,25516425', '103,2748102', '42,1966402', '42,30262834', '42,6667711', '18,13737683', '42,28921168', '42,26076925', '103,3733654', '42,23313527', '64,3307344', '103,3973533', '42,6360982', '48,11846077', '103,3775309', '64,10122050', '42,1965119', '103,4265810', '103,3971645', '103,4962583', '103,689615', '42,22834366', '103,761655', '95,1184', '64,9594482', '42,22855603', '48,8654764', '103,4226756', '42,23366982', '103,3897036', '42,11339650', '101,6369', '42,25830920', '103,5009291', '42,29238961', '59,6299475', '42,22931663', '42,25839056', '43,11864458', '43,41346192', '103,4261645', '42,3747082', '103,4795050', '42,9417503', '103,4245623', '42,61431911']

try:
    format_strings = ','.join(['%s%s'] * len(dc))
    cursor.execute("SELECT * FROM tbl1 WHERE id1=(%s) AND id2=(%s)" % format_strings, (dc))
    res = cursor.fetchall()
    results = results + res
except Exception, e:
    print e

UPDATE

Taking what @lecumia and @beroe posted below I came up with the following, not as elegant and probably not super efficient but it works.

results = ()
id1 = []
id2 = []

dc = ['103,4770634', '42,427752', '64,10122045', '42,13603629', '42,25516425']
for d in dc:
    id1.append(d.split(',')[0])
    id2.append(d.split(',')[1])

try:
    sql = "SELECT * FROM DomainEmails WHERE email_id IN (%s) AND domain_id IN (%s)"
    in_id1 = "'" + "', '".join(id1) + "'"
    in_id2 = "'" + "', '".join(id2) + "'"
    sql = sql % (in_id1, in_id2)
    cursor.execute(sql)
    res = cursor.fetchall()
    results = results + res
except Exception, e:
    print e

Actual Query

SELECT * FROM tbl1 WHERE id1 IN ('103', '42', '64', '42', '42') AND id2 IN ('4770634', '427752', '10122045', '13603629', '25516425')

Query Results

These match what I was expecting:

{'id1': 42L, 'id2': 427752L, 'firstseen': datetime.date(2010, 5, 6)}
{'id1': 42L, 'id2': 427752L, 'firstseen': datetime.date(2011, 5, 2)}
{'id1': 42L, 'id2': 13603629L, 'firstseen': datetime.date(2011, 3, 21)}
{'id1': 42L, 'id2': 13603629L, 'firstseen': datetime.date(2011, 4, 17)}

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

based on

Executing "SELECT ... WHERE ... IN ..." using MySQLdb

results = ()

dc = ['103,4770634', '42,427752', '64,10122045', '42,13603629', '42,25516425', '103,2748102', '42,1966402', '42,30262834', '42,6667711', '18,13737683', '42,28921168', '42,26076925', '103,3733654', '42,23313527', '64,3307344', '103,3973533', '42,6360982', '48,11846077', '103,3775309', '64,10122050', '42,1965119', '103,4265810', '103,3971645', '103,4962583', '103,689615', '42,22834366', '103,761655', '95,1184', '64,9594482', '42,22855603', '48,8654764', '103,4226756', '42,23366982', '103,3897036', '42,11339650', '101,6369', '42,25830920', '103,5009291', '42,29238961', '59,6299475', '42,22931663', '42,25839056', '43,11864458', '43,41346192', '103,4261645', '42,3747082', '103,4795050', '42,9417503', '103,4245623', '42,61431911']

try:
    sql = "SELECT * FROM tbl1 WHERE id1 in (%s) AND id2 in (%s)"
    in_ids = ', '.join(map(lambda x: '%s', dc))
    in_ids = in_ids % tuple(dc)
    sql = sql % (in_ids, in_ids)
    cursor.execute(sql)
    res = cursor.fetchall()
    results = results + res
except Exception, e:
    print e

Results

SELECT * FROM tbl1 WHERE id1 in (103,4770634, 42,427752, 64,10122045, 42,13603629, 42,25516425, 103,2748102, 42,1966402, 42,30262834, 42,6667711, 18,13737683, 42,28921168, 42,26076925, 103,3733654, 42,23313527, 64,3307344, 103,3973533, 42,6360982, 48,11846077, 103,3775309, 64,10122050, 42,1965119, 103,4265810, 103,3971645, 103,4962583, 103,689615, 42,22834366, 103,761655, 95,1184, 64,9594482, 42,22855603, 48,8654764, 103,4226756, 42,23366982, 103,3897036, 42,11339650, 101,6369, 42,25830920, 103,5009291, 42,29238961, 59,6299475, 42,22931663, 42,25839056, 43,11864458, 43,41346192, 103,4261645, 42,3747082, 103,4795050, 42,9417503, 103,4245623, 42,61431911) AND id2 in (103,4770634, 42,427752, 64,10122045, 42,13603629, 42,25516425, 103,2748102, 42,1966402, 42,30262834, 42,6667711, 18,13737683, 42,28921168, 42,26076925, 103,3733654, 42,23313527, 64,3307344, 103,3973533, 42,6360982, 48,11846077, 103,3775309, 64,10122050, 42,1965119, 103,4265810, 103,3971645, 103,4962583, 103,689615, 42,22834366, 103,761655, 95,1184, 64,9594482, 42,22855603, 48,8654764, 103,4226756, 42,23366982, 103,3897036, 42,11339650, 101,6369, 42,25830920, 103,5009291, 42,29238961, 59,6299475, 42,22931663, 42,25839056, 43,11864458, 43,41346192, 103,4261645, 42,3747082, 103,4795050, 42,9417503, 103,4245623, 42,61431911)

edited May 23 '17 at 12:28

Community

1
1

answered Oct 29 '13 at 01:16

lucemia

6,349
5
42
75

thanks lucemia, I'm getting the following error based on the above: (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 '%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) AND id2 IN' at line 1"). The number of format strings corresponds with the length of dc (which I shortened to 16 for testing purposes). Reviewing the link you shared as well. – Rick Oct 29 '13 at 01:39
Commenting on my own comment :) Syntactically everything appears correct: SELECT * FROM tbl1 WHERE id1 IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) AND id2 IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) - just doesn't have the actual data from 'dc'. I thought by doing the following it would fix the issue: 'cursor.execute(sql, dc)' - now I'm getting "not enough arguments for format string". Which is the same error I was getting with previous attempts. – Rick Oct 29 '13 at 01:44
I just switched 'dc' to a tuple and tried 'cursor.execute(sql, tuple(dc))' - still getting 'not enough arguments for format string' – Rick Oct 29 '13 at 01:52
I think the `join` `map` thing is messed up. Try just: `in_ids = "'" + "', '".join(dc) + "'"` – beroe Oct 29 '13 at 02:02
@beroe : thanks, unfortunately that doesn't do the trick, that gives me `SELECT * FROM tbl1 WHERE id1 IN ('103,4770634', '42,427752', '64,10122045', '42,13603629', '42,25516425') AND id2 IN ('103,4770634', '42,427752', '64,10122045', '42,13603629', '42,25516425')` I'm really looking for a 1:1 mapping, so when id1=103, id2=4770634 and on down the line for all the values in 'dc' – Rick Oct 29 '13 at 03:56
@lucemia :just realized I screwed up the initial commenting, want to ensure you saw our replies. – Rick Oct 29 '13 at 03:57
@user1834293. I see what you mean. I was trying to get the string to work. I was just doing something similar yesterday using a shared key to cross-ref the fields. – beroe Oct 29 '13 at 04:11
@lucemia Thanks to you both, I believe I have what I need. May need be as elegant/efficient but it appears to work properly. Open to further suggestions/comments. Going to run it on a larger sample set now. – Rick Oct 29 '13 at 04:43
@beroe Thanks to you both, I believe I have what I need. May need be as elegant/efficient but it appears to work properly. Open to further suggestions/comments. Going to run it on a larger sample set now. – Rick Oct 29 '13 at 04:43

Multiple arguments for Python MySQLdb clause

1 Answers1

Results