Python MySQL SELECT WHERE with list

Question

I have the following Python MySQL code.

cursor = mydb.cursor()

cursor.execute('SELECT id FROM table1 WHERE col1=%s AND col2=%s', (val1, val2))
ids = cursor.fetchall()

for id in ids:
    cursor.execute('SELECT record_key FROM table2 WHERE id=%s limit 1', (id[0], ))
    record_keys = cursor.fetchall()
    print(record_keys[0][0])

How can I make this more efficient? I am using 5.5.60-MariaDB and Python 2.7.5. I have approximately 350 million entries in table1 and 15 million entries in table2.

`cursor.execute('SELECT record_key FROM table2 WHERE id IN %s limit 1', (id[0], ))`? — pault, Sep 13 '19 at 18:02
Possible duplicate of [imploding a list for use in a python MySQLDB IN clause](https://stackoverflow.com/questions/589284/imploding-a-list-for-use-in-a-python-mysqldb-in-clause) — pault, Sep 13 '19 at 18:03

score 1 · Accepted Answer · answered Sep 13 '19 at 18:02

Happily, you can do this in a single query using a LEFT JOIN.

cursor = mydb.cursor()

cursor.execute(
    "SELECT t1.id, t2.record_key FROM table1 t1 "
    "LEFT JOIN table2 t2 ON (t1.id = t2.id) "
    "WHERE t1.col1=%s AND t2.col2=%s",
    (val1, val2),
)
for id, record_key in cursor.fetchall():
    pass  # do something...

Python MySQL SELECT WHERE with list

1 Answers1