Python create dynamic select statement for mysql

Question

In my MySQL database I have 10 tables. Based on the user input, I want to select the rows from one of the table. The user input is an image. I am getting the number of columns and rows of pixels the image has and storing it in a variable.

rows,cols,channels = img.shape
w = int(cols)
h = int(rows)
l = w-10
m = w+10
p = h-10
q = h+10
area = w*h

the mysql tables are created basis the area of the image, and hence, depending on the area, I want to select the values from a specific table.

if (area < 110980):
        sql = "select * from adf1 where WIDTH between %s and %s and HEIGHT between %s and %s;",(l,m,p,q)
    elif (area < 182520):
        sql = "select * from adf2 where WIDTH between %s and %s and HEIGHT between %s and %s;",(l,m,p,q)
else:
        sql = "select * from adf10 where WIDTH between %s and %s and HEIGHT between %s and %s;",(l,m,p,q)
cur = mysql.connect().cursor()
cur.execute(sql)

I am getting the following error:

TypeError: cannot concatenate 'str' and 'tuple' objects

However, when I pass on a single query within cur.execute I am able to retrieve the results.

cur.execute("select * from adf10 where WIDTH between %s and %s and HEIGHT between %s and %s;",(l,m,p,q))

user8060120 · Accepted Answer · 2018-08-20 06:28:46Z

1

current your variable sql is tuple, so if you want to pass it as parameters to the execute you should use *

cur.execute(*sql)

you can read more in the doc unpacking-argument-lists

answered Aug 20, 2018 at 6:28

user8060120

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

2 Comments

Apricot Over a year ago

Thank you for the answer and the document link, this works...will accept your answer in 5 mins

user8060120 Over a year ago

glad to help you

Equinox · Accepted Answer · 2018-08-20 06:36:48Z

0

this should fix your problem. You need to use % not comma in the queries inside if else.

"select * from adf2 where WIDTH between %s and %s and HEIGHT between %s and %s;"%(l,m,p,q)

answered Aug 20, 2018 at 6:36

Equinox

6,7583 gold badges27 silver badges34 bronze badges

Collectives™ on Stack Overflow

Python create dynamic select statement for mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related