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

Question

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

Answer 1

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

Answer 2

You are not passing the value 1 for %s in the string format. stmt = "SELECT * FROM jsonTes ORDER BY website LIMIT %s" %n for limit n

Answer 3

you can use like that

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