Search code examples
pythonmysqlpymysql

How to pass a variable to MySQL's LIMIT clause?

I am trying to make a SELECT statement to Mysql datbase using pymysql. This is the code. I am passing a variable to the select statement, and to my surprise this is a huge pain in the lemon. Any idea what am I missing here?

def getUrlFromDatabase(n):
    stmt = "SELECT * FROM jsonTes ORDER BY website LIMIT %s-1,1"
    cur.execute(stmt,str(n))
    return cur.fetchone()

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='passwd', db='email_database', charset='utf8')
cur = conn.cursor()
cur.execute("USE database")

getUrlFromDatabase(0)

Error:

This is what I try to achieve: Return the nth record from MySQL query

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''0'-1,1' at line 1")
Solution

  • LIMIT in MySQL takes numeric arguments, which must both be nonnegative integer constants. You have to calculate the expression in Python and then pass the integer as a single parameter. Also, you need to put the parameter in a tuple:

    def getUrlFromDatabase(n):
    stmt = "SELECT * FROM jsonTes ORDER BY website LIMIT %s, 1"
    cur.execute(stmt, (n-1 if n > 0 else 0,))
    return cur.fetchone()