Why does a mySQL Python Query not insert a new entry?

Question

So I am trying to add a new entry into my mySQL Database. The problem here is, that it increases the id, but does add the entry. After a little bit of googling I found that a failed INSERT query also increases the AUTO_INCREMENTd value (id in my case).

The mySQL Table is created using CREATE TABLE IF NOT EXISTS TS3_STAMM_1 (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, ts3_uid VARCHAR(64) NOT NULL, points INT(8) UNSIGNED NOT NULL); which is called by the function qServer.execute(querystring) inside python's MySQLdb module.

Then I use qString = "INSERT INTO TS3_STAMM_1 (name, ts3_uid, points) VALUES ('{}', '{}', {})".format(name, uid, pnts) (the datatypes are correct, I at least quadrouplechecked) with the function qServer.exectue(qString) to insert a new entry into the database.

But it is incrementing the ID, but its not adding an entry. So my guess would be its a failed query, but why? How does it happen? How to fix it?

Simple SELECT querys work fine the same way, also adding data manually works fine. Only the python query fails.

Note: qServer is the connection to the server, and its defined with:

try:
    qConn = MySQLdb.connect(host="...", user="...", passwd="...", db="...")
    qServer = qConn.cursor()
except OperationalError:
    print("Cannot connect to mySQL Database! Aborting...")
    exit(1)

Please do not use string formatting for passing arguments. That's a footgun for many reason, SQL injection being the worst. Use your DB-API driver's placeholders and pass args to execute() separately. In your incomplete example you never show a call to commit. If you do not commit, no data is persisted. — Ilja Everilä
– Ilja Everilä, Commented Jan 27, 2018 at 12:31
The lack of the commit() seems to have been the cause of the data not saving... Thanks! — JH_WK
– JH_WK, Commented Jan 27, 2018 at 13:41

Ilja Everilä · Accepted Answer · 2018-01-27 14:21:28Z

2

Use commit Luke.

>>> cursor.execute("INSERT INTO employees (first_name) VALUES (%s)", ('Jane', ))
>>> qConn.commit()

Using str.format for creating SQL query is bad idea.

edited Jan 27, 2018 at 14:21

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

answered Jan 27, 2018 at 12:33

baklarz2048

11k2 gold badges33 silver badges38 bronze badges

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

5 Comments

JH_WK Over a year ago

What is cnx in this case?

baklarz2048 Over a year ago

It is connection object qConn in your case.

JH_WK Over a year ago

How would that formatting approach look for more variables? I tried it with %s, %s, %d and on the %d it says %d format: a number is required, not str. This happens even when I make sure and put the pnts variable in the int() function. My approach right now is to use [...].execute("[...]", (name, uid, int(pnts))) but this cannot format on the %d since it thinks that pnts is a string, even when I mark it as int

JH_WK Over a year ago

Nevermind, I found it out. You can only format with one formatting placeholder, either %d or %s. But %s is fine, I can just convert it to a string. Thanks!

Ilja Everilä Over a year ago

You don't need to convert to string, and int() does not mark anything, but creates a new integer. The driver creates suitable SQL for each value based on its type and in case of MySQLdb driver the placeholder is always %s, and is not Python's old style %-formatting.

Collectives™ on Stack Overflow

Why does a mySQL Python Query not insert a new entry?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related