How to use a variable in a IN clause Mysql. Python script

Question

I am trying to optimize this block of code to use a single query rather than looping over and over.

while not (dataX):
            i += 1
            this_id = '/'.join(this_id.split('/')[0:-i])
            if not this_id:
                break
            else:
                dataX = db.conn[db_read].query("SELECT x AS xX FROM link WHERE _deleted = 0 AND _ref = %s AND _ntype = 'code' LIMIT 1;", data = (this_id,))

I want to use the IN clause with a variable that contains all possible substring but I can't get it to work.

this_id_list = "'/a/b/c/d/e' , '/a/b/c/d', '/a/b/c', '/a/b', '/a'"
result = db.conn[db_read].query("SELECT x AS xX FROM link WHERE _deleted = 0 AND _ref IN($this_id_list)")

Any idea what am I doing wrong and how to fix it? I would really appreciate any input! This is a Python script btw.

what does $this_id_list look like? The IN clause is expecting comma delimited values (and quoted if strings). — Kevin Bott
– Kevin Bott, Commented Sep 21, 2017 at 17:01
@KevinBott this_id_list = ['/a/b/c/d/e' , '/a/b/c/d', '/a/b/c', '/a/b', '/a'] — R.roy
– R.roy, Commented Sep 21, 2017 at 17:13

Jacques Amar · Accepted Answer · 2017-09-21 17:20:40Z

1

this_id_list = "'/a/b/c/d/e' , '/a/b/c/d', '/a/b/c', '/a/b', '/a'"

This should be a string

answered Sep 21, 2017 at 17:20

Jacques Amar

1,8231 gold badge12 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

R.roy Over a year ago

I have tried that and it still didn't work. Any other ideas?

Jacques Amar Over a year ago

What result do you actually get?

R.roy Over a year ago

When I run it, result ends up being False

Collectives™ on Stack Overflow

How to use a variable in a IN clause Mysql. Python script

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related