python - mysql syntax error on generated query

Question

Until recently the following code worked correctly.

for player in away_starters_names:
    on = 0
    this_player = player[0]
    if this_player in event:
        player_id = away_team_dict[this_player]
        player_id = int(player_id[0])
        team = 0
        team_id = away_id
        cur.execute("""INSERT INTO football.match_subs(player, time, added, game_id, home, on, team_id) VALUES (%s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE game_id = game_id""", (player_id, time, added, game_id, team, on, team_id))
        db.commit()

It now gives this error:

    Traceback (most recent call last):
  File "Z:\Coding\bbc\find_teams.py", line 508, in <module>
    cur.execute("""INSERT INTO football.match_subs(player, time, added, game_id, home, on, team_id) VALUES (%s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE game_id = game_id""", (player_id, time, added, game_id, team, on, team_id))
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 202, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
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 'on, team_id) VALUES (299191, 89.33, 0, 21570967, 0, 0, 2) ON DUPLICATE KEY UPDAT' at line 1")

What is wrong?

okay looking at the way this is highlighted on here (not on idle) it is blatently going to be using 'on' as a column name.. — user2073606
– user2073606, Commented Mar 4, 2013 at 22:55

Mike Brant · Accepted Answer · 2013-03-04 22:59:23Z

2

Use backticks around your field names. ON is a reserved word in MySQL

answered Mar 4, 2013 at 22:59

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

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

Collectives™ on Stack Overflow

python - mysql syntax error on generated query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related