0

I'm trying to add a row in mysql table using python.

cursor.execute(
        "insert into tbl_ft1x2s (match_id, fair_odds, mkt_odds, pred_prob, return, edge, scatter_edge, scatter_trend, hist_prob, smpl_prop) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
        (match_id, fair_odds_1, mkt_odds_1, pred_prob_1, exp_return_1, edge_1, scatter_edge, scatter_trend, hist_prob_1,
         smpl_prop_1))

I'm getting this error,

mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'return, edge, scatter_edge, scatter_trend, hist_prob, smpl_prop) values (87507, ' at line 1

match_id is int while rest of all the columns are varchar.

I think issue is that the name of column "return" is a keyword in MYSQL. Am I right ? If yes then can anyone help me to solve this issue. Thanks

  • Can you tell me where exactly you want me to add double % ? –  Aug 29 '20 at 10:05
  • that's not working –  Aug 29 '20 at 10:09
  • I had your exact problem a year ago (I think...), and I know that I needed %%. Have a look at https://stackoverflow.com/questions/10678229/how-can-i-selectively-escape-percent-in-python-strings for other ideas then. Btw., if you can avoid insert and are fine with just creating a new table, I recommend to use df.to_sql(), see https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html. – questionto42 Aug 29 '20 at 10:10
  • Perhaps you try https://stackoverflow.com/questions/7540803/escaping-strings-with-python-mysql-connector. – questionto42 Aug 29 '20 at 10:31

1 Answers1

3

You are using the keyword return as a field name. That is probably why you have that error near that part of your SQL statement.

Here is a list of the reserved keywords in MySQL.

To avoid the problem, just add the backtick (`) around keywords.