MySQL/Python -> Wrong Syntax for Placeholder in Statements?

Question

I am trying to use placeholder in an insert-statement.

I am using PyCharm/Python 3.6, a MySQL-Database, and the mysql.connector (don't know which of them exactly.)

Why doesn't the following code work?

insert_stmt = "INSERT INTO mydb.datensatz (Titel) VALUES ('%s');"
data = (titel)
cursor.execute(insert_stmt, data)
cnx.commit()

titel is a string.

This is what gets inserted, but I need to have the titel-string into that row.

When deleting the ' ' in the values-braces, PyCharm gives me an error with incorrect MySQL-syntax.

How to use placeholders in this case? How could I use more placeholders for example at inserting into more columns than one? Research didn't help.

I haven't done much more than play around with python a bit when it was new; but would parenthesis around the query string itself as shown in the official example here make a difference? — Uueerdo
– Uueerdo, Commented Apr 19, 2018 at 16:33
@Uueerdo Sadly it makes no difference, as it still inserts %s into that specific row. — Civoknats
– Civoknats, Commented Apr 19, 2018 at 16:37
According to the example you should not be be putting ''s around the %s... I just looked up PyCharm, is it just an IDE "intellisense-style error", or does it actually refuse to run the query? — Uueerdo
– Uueerdo, Commented Apr 19, 2018 at 16:49
@Uueerdo Without those quotes, it gives me an SQL-Syntax-Error, so they are needed I suppose :/ — Civoknats
– Civoknats, Commented Apr 19, 2018 at 17:01

Nick · Accepted Answer · 2018-04-19 18:10:49Z

1

You need to remove the quotes from the %s AND make sure your parameters are a in a tuple:

insert_stmt = "INSERT INTO mydb.datensatz (Titel) VALUES (%s);" # Removed quotes around %s
data = (titel,) # Added trailing comma to make tuple
cursor.execute(insert_stmt, data)
cnx.commit()

When you have a single value in a tuple, you must include a trailing comma: (item,)

answered Apr 19, 2018 at 18:10

Nick

7,5012 gold badges25 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

MySQL/Python -> Wrong Syntax for Placeholder in Statements?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related