0

Using Python's mysql.connector, how can I select all records that match a tuple?

For example:

ids = (b'6TRsHMzWSrq0zeembo0Vbg',
       b'7Qoq53lKTk-RZOi830t3MA', 
       b'7ZGO9S3DTcGHjwrfpV0E0A')

And then the query would do something like:

query = SELECT first_name
        FROM users
        WHERE id = (ids)

Returning records for all users whose ID appears in the tuple

Anshul Goyal
  • 73,278
  • 37
  • 149
  • 186
ensnare
  • 40,069
  • 64
  • 158
  • 224

1 Answers1

2

Try doing this

query = "SELECT first_name FROM users WHERE id IN " + str(list(ids))
cursor.execute(query)

On second thoughts, the following should also work

query = "SELECT first_name FROM users WHERE id IN " + str(ids)

EDIT

OP mentions in comments that the ids are binary data returned from a previous query. In which case, taking hint from this answer and using BINARY operator, the following query should work

query = "SELECT first_name FROM users WHERE BINARY id IN " + str(ids) ;
Community
  • 1
  • 1
Anshul Goyal
  • 73,278
  • 37
  • 149
  • 186
  • Runs into problems converting the binary values to string...should I decode them into utf-8? – ensnare Jul 08 '14 at 13:56
  • @ensnare what kind of data is in `ids` tuple? It doesn't look like binary.. In any case, have a look at the [structs](https://docs.python.org/2/library/struct.html) module to convert the strings to integer from binary. – Anshul Goyal Jul 08 '14 at 14:04
  • the ids tuple contains a tuple of binary (case-sensitive) values returned from a previous query. Is there a way to do this w/o the conversion? – ensnare Jul 08 '14 at 14:33
  • @ensnare Check my edits. Let me know if they work. If they don't, you will probably need to generate `hexcode` for ids and check for hexcodes. – Anshul Goyal Jul 08 '14 at 16:50