Search code examples
pythonmysqlsqllimitoffset

Python MySQL Parameter Query Programming Error: 1064 (42000)

I want to query MySQL tables using python program but I got this error:

ProgrammingError: 1064 (42000): 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 '0'' at line 1

The confusing thing is that when I don't use variables, the query runs perfectly as it is shown below:

cur = connection.cursor()
query = "SELECT * FROM DB.Table LIMIT 10 OFFSET 0"

cur.execute(query)

records = cur.fetchall()

for record in records:
    print(record)

But I need to select data batch by batch and I have to do the above command in a for loop. And I need to define variables. But I got error 1064. Here is the code with error:

i = 0
p = str(i)
j = 10
q = str(j)

cur = connection.cursor()
query = "SELECT * FROM DB.Table LIMIT %s OFFSET %s"

cur.execute(query,(q,p,))

records = cur.fetchall()

for record in records:
    print(record)

I appreciate your help.

Solution

  • Simply, do not convert parameterized values to string as error shows single quotes:

    i = 0
j = 10

cur = connection.cursor()
query = "SELECT * FROM DB.Table LIMIT %s OFFSET %s"

cur.execute(query, (j, i))