Python-MySQL : removing single quotes around variable values in query while running db.execute(str, vars)

Question

I am running this code

    def details(self, dbsettings, payload):
        res = None
        with UseDatabase(dbsettings) as db:
            sql = "select * from %(tablename)s where userid = %(userid)s"
            result = db.run_query_vals(sql, payload)
            res = result.fetchall()
        return res

but get an error

SQLError: 1064 (42000): 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 ''statuser' where userid = '14'' at line 1

The arguments being passed are :

sql = "select * from %(tablename)s where userid = %(userid)s"
payload = {'tablename' : 'statuser', 'userid' : 14}

As far as I understand, the query being passed to MySQL is along the lines of

select * from 'statuser' where userid = '14'

which is where I get the error; the tablename isnt supposed to be enclosed in quotes. How do I have the name included without the quotes/make them backquotes?

(I don't want to hard-code the table name - this is a variable and is initialised according to different parameters during class creation). Any help here?

Answer 1

You can use the .format() from string in python:

def details(self, dbsettings, payload):
    res = None
    with UseDatabase(dbsettings) as db:
        sql = "select * from {tablename} where userid = {userid}"
        sql = sql.format(**payload)
        # result = db.run_query_vals(sql, payload) # Method to run query
        res = result.fetchall()
    return res