Python MySQL Parameter Query Programming Error: 1064 (42000)

Question

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.

Parfait · Accepted Answer · 2020-03-12 19:11:42Z

1

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))

answered Mar 12, 2020 at 19:11

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

2 Comments

Fatemeh Over a year ago

Thanks, it works. But when I use it with a left join query = "SELECT * FROM DB.Table1 LIMIT %s OFFSET %s LEFT JOIN DB.Table2 ON Table1.id =Table2.id" and cur.execute(query, (j, i)) I faced with the same error.

Parfait Over a year ago

LIMIT and OFFSET can only run at the very end of a query to limit the entire joined resultset. Consider using a subquery or CTE then join in outer level.

Rafael Ribeiro · Accepted Answer · 2020-03-12 18:12:29Z

1

You can use cur.execute(query % (q,p)) or

query = "SELECT * FROM DB.Table LIMIT {} OFFSET {}"
cur.execute(query.format(q, p))

answered Mar 12, 2020 at 18:12

Rafael Ribeiro

1013 bronze badges

1 Comment

Parfait Over a year ago

Note: This answer change OP's use of SQL parameterization for the ill-advised string SQL concatenation.

Collectives™ on Stack Overflow

Python MySQL Parameter Query Programming Error: 1064 (42000)

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related