0

I have a MySQL query issued from Python that looks like this:

foos: Sequence[str] = get_user_specified_strs()

# Return all the rows where `foo` is in `foos`.
# If `foos` contains the string '*', return everything.
results = await conn.query_rows(
  '''
  SELECT ...
  WHERE
    '*' IN %(foos)s
    OR foo IN %(foos)s
  ...
  ''',
  {'foos': foos}
)

The problem is that IN () is not valid in MySQL, so an empty foos will cause the query to fail. Is there a way to write the query without requiring assembling the SQL string dynamically, so that it can handle this case? I.e., treating IN () as FALSE.

Zizheng Tai
  • 6,170
  • 28
  • 79
  • It seems that if the `foo` column is not nullable, adding a `None` (which is a value guaranteed not to exist) to `foos` solves the problem. But I'm wondering if there is any other solutions. – Zizheng Tai May 28 '20 at 08:06
  • `LOCATE('*', %(foos)s) OR...` – danblack May 28 '20 at 08:08
  • @danblack `LOCATE` works with strings; I'm working with a sequence of strings. – Zizheng Tai May 28 '20 at 08:14
  • https://stackoverflow.com/a/13210590/5962802 – IVO GELOV May 28 '20 at 09:05
  • If you're still struggling, see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Strawberry May 28 '20 at 09:08

0 Answers0