Update MySQL database with Python 3

Question

I'm trying to insert data from python into a MySQL database I made with phpmyadmin. I'm using Python 3. This doesn't seem to update my database - what's happening here?

import MySQLdb
db = MySQLdb.connect("localhost", "uname", "password", "HORSERACES")
cursor = db.cursor()
sql = """INSERT INTO HORSERACES(Name, Event participant id, Market id, Event name, Event time)
VALUES('Brimful of Asha', 1099999876, 2098574628, 'Worthing Cup Chase', 29385756568, 2017年05月30日 00:00:00)"""
try:
 cursor.execute(sql)
 db.commit()
except:
 db.rollback()
db.close()
print ('Success')

The error I get is:

_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 'participant id, Market id, Event name, Event time)\n\tVALUES('Brimful of Asha', 10' at line 1")

• Alex Hall: If the try block raises an exception it's just going to rollback silently and say success without you knowing. Don't swallow exceptions silently.

Answer 1

What's happening here is poor exception handling

try:
 cursor.execute(sql)
 db.commit()
except:
 db.rollback()
 # Something goes wrong and you are rolling back silently

At the very least you should do

 import traceback
 traceback.print_exc()
 db.rollback()

then you know why the insert isn't happening.

Update: After you have added the traceback it's easy to see that mysql is chocking on column names. spaces are not allowed in column names. if you use them you need to escape them with backquotes, but still they don't play well on some libraries and clients.

• Davtho1983: You're right - I didn't know how to do that - have included the error message
• e4c5: and clearly ` Event participant id` is not a valid column name
• Davtho1983: Ok - I've taken spaces out of everything and the error I get is slightly different, but it's still about the syntax for the VALUES
• e4c5: This is becoming a chameleon question or rabbit hole question. Please be so kind as to mark the answer as correct since it did address the original issue. Since then you have changed the query at least twice. Please post a new question with your exact query and also with the relevent tables.
• e4c5: please also be aware that things like dates needed to be quoted "2017年05月30日 00:00:00"