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?

Tim Biegeleisen · Accepted Answer · 2023-01-16 14:32:44Z

2

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.

answered Jan 16, 2023 at 14:32

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to insert string into query in python pymysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest