mysql.connector doesn' t insert with python command, but manually the query works

Question

So after my research on stackoverflow didn't bring me any further here ist my code (I cannot post the exact code , because this is a problem I have at work) and problem:

 import mysql.connector
 .    
 .
 .
 cnx = mysql.connector.connect(user, password, host, database)
 cursor = cnx.cursor()

 for-loop:
     if condition:
          cursor.execute("INSERT INTO table (the_columns) VALUES (%s)", (my_values))
          cnx.commit()

I tried to insert manually already and it worked, but somehow my python code won't do the insert.

The manual insert:

INSERT INTO table (column1,...,column7) VALUES (string1,....,string6, now())

I have no error message, I can only look into the database and see that the new valiues aren't there. Did anyone else face this problem? Can anyone suggest what could be the problem?

The issue was solved, there has been a problem because the previous dev had two instances with the same name and it wasn't a msql error at all. — dima
– dima, Commented Sep 18, 2016 at 13:33
For future comers, add cursor.commit() before closing the connection cnx.close(), as said here with explanation. — mondayrris
– mondayrris, Commented Jul 8, 2022 at 4:01

N.K · Accepted Answer · 2016-09-16 15:33:16Z

1

Might be because you dont have to put your variable between "(" ")" ? Did you tried to put the value directly inside the sql, then instead of the variable containing it? What do you mean by "manually"?

Anyway, you should put all your variables in an array, before passing this array as the second argument:

query = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) VALUES (%s, %s, %s, %s)"
)
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

EDIT: I just checked, thats the main exemple if you google your problem... wait did you searched for this a little bit? That's the best wait to learn dude: search by yourself before asking for help.

edited Sep 16, 2016 at 15:33

answered Sep 16, 2016 at 15:25

N.K

1,6832 gold badges22 silver badges34 bronze badges

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

4 Comments

dima Over a year ago

yes, I tried and meant that by manually. No the parantheses are not the problem, it worked before with the exact same allignment. And I tried to remove them, which has thrown an Syntax Error.

N.K Over a year ago

ok, try to put your variable in a data array like the exemple (Edited sorry >.>)

dima Over a year ago

I tried that already and I googled for one hour and it didn't work also. why else would I ask on stackoverflow?

N.K Over a year ago

can you edit and show the "manual" way you tried please? (because many engineers lose their time on stackOverflow, and they have the answer to absolutely everything, that's why)

Phillip Stack · Accepted Answer · 2016-09-16 17:17:24Z

0

Try turning your format variables into a tuple. If this does not work. Try creating the query as a variable seperately and printing it out and running it in your sql console directly. You might get more meaningful errors.

 cnx = mysql.connector.connect(user, password, host, database)
 cursor = cnx.cursor()

 for-loop:
     if condition:
         cursor.execute("INSERT INTO table (the_columns) VALUES (%s)", (my_values,))
         cnx.commit()

OR

 cnx = mysql.connector.connect(user, password, host, database)
 cursor = cnx.cursor()

 for-loop:
     if condition:
         sql = "INSERT INTO table ({}) VALUES ({})".format(the_columns,my_values)
         print(sql)
         cursor.execute(sql)
         cnx.commit()

edited Sep 16, 2016 at 17:17

answered Sep 16, 2016 at 17:12

Phillip Stack

3,3882 gold badges17 silver badges25 bronze badges

Collectives™ on Stack Overflow

mysql.connector doesn' t insert with python command, but manually the query works

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related