Executemany not working (adding data to mysql)

Question

Hello I have been trying to add data to mysql using execute many. I've spent hours searching and trying but it doesn't work. Here is part of my code:

values = [("BEAFS","RBS00167","167","A1","BSC","RADIO X-CEIVER   ADMINISTRATION" ,"2015-04-07 12:10:00" ,"FUEL TANK ALARM G167-KARTERES")]   

db = MySQLdb.connect("localhost","root","","alarmsdb")
cursor = db.cursor()
sql = """INSERT INTO ALARMS_VF (elem_name, cell_name, site_no, alarm_severity,ne_type, alarm_name, alarm_raised_time, location_info) \
                               VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')""" #% (elem_name, cell_name, site_no, alarm_severity, ne_type, alarm_name, date, alarms)

   try:
      cursor.executemany(sql,values)
      db.commit()
   except:
      db.rollback()
 db.close()

I've tried everything execute works fine but executemany does not insert data. I tried using a list, tuple, list with tuples but none work. Even with one record to test it doesn't insert anything.

Do you know if using `executemany()` resulted in an exception and a rollback? If so maybe it's worth looking at that exception — bakkal, Apr 24 '15 at 09:58
Your query should not have `'%s'` but rather `%s`. And you should use [`pymysql`](https://github.com/PyMySQL/PyMySQL/) instead of `MySQLdb`. — Martin Thoma, Apr 24 '15 at 10:02
without exception throws TypeError: not all arguments converted during string formatting. But with "execute" only dont have this message it seems to work which means it is conerting.as for %s without quotes tried that before reading tutorials and trying everything since it was not working and still doesnt work. — Evangelos, Apr 24 '15 at 10:13
@moose: why should one use a somewhat obscure package instead of a known stable one ? — bruno desthuilliers, Apr 24 '15 at 10:19
@brunodesthuilliers Why do you think pymysql is obscure? It should be used, because it is compatible with Python 3 (and I had much less problems with pymysql than with mysqldb). It is also easy to switch. In most (all?) cases you can simply replace the string `MySQLdb` with `pymysql`. With 979 stars and 234 forks on GitHub I think it is quite well-known. — Martin Thoma, Apr 24 '15 at 10:20
@Evangelos: your bare `except` clause prevents you from knowing what went wrong. Add a `raise` statement after the `db.rollback()`, and you'll get a nice traceback with as much hints as you can get about the error, instead of your script just doing nothing. Exceptions and traceback are actually here to _help_ you, so don't silence them. — bruno desthuilliers, Apr 24 '15 at 10:22
@brunodesthuilliers See also http://stackoverflow.com/a/14076841/562769 — Martin Thoma, Apr 24 '15 at 10:23
@bruno i tried without except and throws "TypeError: not all arguments converted during string formatting" as i stated before tried that and searching for the error but nothing i added the except for the "execute" which works fine without any errors and i am wondering why "executemany" throws this error while execute is not? — Evangelos, Apr 24 '15 at 10:33
@Evangelos: with the code you posted, I get a `_mysql_exceptions.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 'BEAFS'', ''RBS00167'', ''167'', ''A1'', ''BSC'', ''RADIO X-CEIVER ADMINISTRATI' at line 2")" - obviously since you have single quotes around your placeholders. **Please** post the real code (or preferably a Minimal, Complete, and Verifiable example - http://stackoverflow.com/help/mcve) — bruno desthuilliers, Apr 24 '15 at 10:49

Evangelos · Answer 1 · 2015-04-24T11:23:41.657

I guess the first answer from moose was correct.But i used with combination of the commented #% (elem_name, cell_name, site_no, alarm_severity, ne_type, alarm_name, date, alarms) and i was confused somewhere as was trying to figure out the error and what went wrong.Anyway this works now:

values = [("BEAFS","RBS00167","167","A1","BSC","RADIO X-CEIVER ADMINISTRATION" ,"2015-04-07 12:10:00" ,"FUEL TANK ALARM G167-KARTERES")]  
                if alarm_name.startswith("RADIO X") and alarms != '' or alarm_name.startswith("CELL LOGICAL"):
                    db = MySQLdb.connect("localhost","root","","alarmsdb")
                    cursor = db.cursor()
                    sql = """INSERT INTO ALARMS_VF (elem_name, cell_name, site_no, alarm_severity,ne_type, alarm_name, alarm_raised_time, location_info) \
                               VALUES ( %s, %s, %s, %s, %s, %s, %s, %s)""" #% (elem_name, cell_name, site_no, alarm_severity, ne_type, alarm_name, date, alarms)

                    #try:
                    cursor.executemany(sql,values)
                    db.commit()
                    #except:
                        #db.rollback()
                    db.close()

Thank you all for your help

Executemany not working (adding data to mysql)

1 Answers1