how to insert string into query in python pymysql

Question

I have a following query:

cursor = connection.cursor()

query = """
        SELECT *
        FROM `my_database`.table_a

        """
result = cursor.execute(query)

which works as expected. But I need to change my_database in cursor.execute. I try:

cursor = connection.cursor()

query = """
             SELECT *
             FROM %s.table_a

             """
result = cursor.execute(query, ("my_database",))

which gives an error pymysql.err.ProgrammingError: (1064, "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 ''my_database'.table_a at line 2")

how can I insert database name in cursor.execute please?

Answer 1

It is not possible to bind a database name (or any other database object name) using a placeholder in a prepared statement. This would be, among other problems, a security risk. However, you might be able to use an f-string here instead:

cursor = connection.cursor()

db_name = "my_database"
query = f"""
         SELECT *
         FROM {db_name}.table_a
         """
result = cursor.execute(query)

It should also be mentioned that the above is only SQL injection safe if you are certain that the database name is not coming from outside your own application.