Python SQL SELECT syntax with multiple parameters LIMIT

Question

I have an SQL statement that works in MySQL:

SELECT * FROM kaio_jadwal WHERE param1=88 AND param2=973 AND param3=22554 LIMIT 0,10

When I try with Python:

sql = """SELECT * FROM kaio_jadwal WHERE param1=%s AND param2=%s AND param3=%s LIMIT %s OFFSET %s"""
filter = (param1,param2,param3,limit,offset)

And I got an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10' OFFSET '25''

And this is my code:

sql = """SELECT * FROM kaio_jadwal WHERE param1=%s AND param2=%s AND param3=%s LIMIT %s OFFSET %s"""
filter = (param1,param2,param3,limit,offset)
cursor.execute(sql,filter)
rows = cursor.fetchall()

How should the syntax be in Python to work?

Peter Mortensen · Accepted Answer · 2019-09-04 16:41:38Z

2

cursor.execute is converting your limit and offset into strings, but MySQL expects ints. Try changing your SQL string with the %d notation for offset and limit:

sql = """SELECT * FROM kaio_jadwal WHERE param1=%s AND param2=%s AND param3=%s LIMIT %d OFFSET %d"""

edited Sep 4, 2019 at 16:41

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Aug 21, 2019 at 13:46

Hrabal

2,5332 gold badges24 silver badges31 bronze badges

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

3 Comments

Fatah Over a year ago

stil got eror "%d format: a number is required, not str"

Neeraj Agarwal Over a year ago

@Fatah check the type of limit and offset in your filter statement. It appears from the error that one or both of them are of type string not a number.

Hrabal Over a year ago

@Fatah as nagarwal said using %d implies converting the arguments to ints, so filter = (param1,param2,param3,int(limit),int(offset)) would solve

Collectives™ on Stack Overflow

Python SQL SELECT syntax with multiple parameters LIMIT

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