CODEWITHSUNDEEP
pythonmysqlpymysql
sanjihan
sanjihan

Reputation: 6024

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

Upvotes: 2

Views: 4394

Answers (3)

Eugene Yarmash
Eugene Yarmash

Reputation: 150041

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

Upvotes: 3

rajeshcis
rajeshcis

Reputation: 382

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

Upvotes: 0

dhishan
dhishan

Reputation: 127

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

Upvotes: 0

Related Questions