Problems building a python function that checks if a primary key already exists

Question

I'm trying to build a python function that, given a table name and the primary key of the table, it returns true if an entry with the same primary key already exists, otherwise it returns false

Here is my attempt:

def already_esists(tablename, pm_key) :

    myDB = mysql.connector.connect(
            host = "localhost",
            user = "user",
            passwd = "password",
            database = "myDatabase"
        )

    myCursor = myDB.cursor()

    # PM_KEY is the primary key attribute of every table for which I need to use this function
    query = "SELECT PM_KEY FROM %s WHERE PM_KEY = %s;"
    values = [tablename, pm_key]
    myCursor.execute(query, values)

    ID_already_exists = myCursor.fetchall()
    if ID_already_exists :
        logging.warning(primary_key + " Entry already exists")
        return True
    else :
        return False

However, once called, I get the following error:

mysql.connector.errors.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 ''table_name_example' WHERE PM_KEY = 'GELheLpr'' at line 1

GELheLpr is for example a primary key of an entry of table_name_example.

You would need `query = f"SELECT PM_KEY FROM {tablename} WHERE PM_KEY = %s;"` and then remove `tablename` from `values`. If `tablename` is user input then you'll need to cross-ref it against the table names that actually exist before building the query string to avoid SQL Injection — roganjosh, Dec 20 '22 at 17:54
Does this answer your question? [Python and MySQLdb: substitution of table resulting in syntax error](https://stackoverflow.com/questions/9394291/python-and-mysqldb-substitution-of-table-resulting-in-syntax-error) — roganjosh, Dec 20 '22 at 18:00
Oof. That dupe is right but man, the python string formatting has changed a lot in 10 years. I'm pretty sure everyone would prefer the f-string example I gave over the `%%` on the actual part that can use parameter substitution. Don't do that. I wonder if there's a better dupe... — roganjosh, Dec 20 '22 at 18:02
If you want to add an answer to make everything smoother for everyone I'll be glad to upvote and accept it. At least now people would have something more up to date to refer to — Gabrielek, Dec 20 '22 at 21:46

Problems building a python function that checks if a primary key already exists

0 Answers0