CODEWITHSUNDEEP
pythonmysqlmysql-python
Rick
Rick

Reputation: 3

Multiple arguments for Python MySQLdb clause

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)}

Upvotes: 0

Views: 2754

Answers (1)

lucemia
lucemia

Reputation: 6627

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)

Upvotes: 1

Related Questions