CODEWITHSUNDEEP
pythonmysqlpython-3.x
Gary
Gary

Reputation: 143

Use Parameterized Query with Python and MySQL

I'm using Python + MySQL and want to use parameterized query. I'm stuck. I've encountered an error and can't figure out how to solve it. I've spent a day, checked dozens of articles, used various options (sinle quotes, double quotes, prepared statements) and still no luck.

Requirements: use Parameterized Query

Here is basic demo of the issue:

#!/usr/bin/python3
import mysql.connector as mysql

conn = mysql.connect(host=server, user=username, passwd=password, autocommit=True)
try:
    create_database_query = "CREATE DATABASE %s;"
    db_name = "BOOKS"

    cursor = conn.cursor()

    print(f"Creating {db_name} database... ", end='')
    cursor.execute(create_database_query, (db_name,))
    print("Success")

except mysql.Error as error:
    print("Parameterized query failed {}".format(error))

Output: Creating BOOKS database... Parameterized query failed 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 ''BOOKS'' at line 1

So it looks like it uses too many quotes (2 single quotes on each side). The code above works fine if I change the following line: create_database_query = "CREATE DATABASE %s;" and put backtick around %s

The problem that now it creates a database but with invalid chars - 'BOOKS' (quotes are now part of db name). Duh...

If I use prepared statements then the same issue occurs but slightly different error message: 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 '?' at line 1

Environment:

What is going on? Any ideas? Thanks

Upvotes: 2

Views: 2080

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562330

You can't use query parameters for identifiers (like a database name or table name or column name).

Query parameters can be used only in place of a constant value — a quoted string, quoted date/time, or a numeric value. Not identifiers, expressions, SQL keywords, etc.

To combine a database name with your CREATE DATABASE statement, you have to format it into the string in a way that forms the full statement before it is sent to MySQL.

db_name = "BOOKS"
create_database_query = "CREATE DATABASE %s;" % db_name

cursor.execute(create_database_query)

Because this creates a risk of SQL injection when you format variables into your string, it's up to you to make sure the db_name is safe.

Update: Thanks to @Parfait for the reminder about current best practices of string-formatting.

Prefer:

db_name = "BOOKS"
create_database_query = "CREATE DATABASE {};".format(db_name)

Or F-strings:

db_name = "BOOKS"
create_database_query = f"CREATE DATABASE {db_name};"

(In other words, Python has become Ruby ;-)

Upvotes: 3

Related Questions