Getting Error while Inserting a row to MYSQL using Python

Question

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

I had your exact problem a year ago (I think...), and I know that I needed %%. Have a look at stackoverflow.com/questions/10678229/… 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 pandas.pydata.org/pandas-docs/stable/reference/api/…. — questionto42
– questionto42, Commented Aug 29, 2020 at 10:10

Coder's Life · Accepted Answer · 2020-08-29 12:39:46Z

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.

answered Aug 29, 2020 at 12:39

Coder's Life

313 bronze badges

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

Collectives™ on Stack Overflow

Getting Error while Inserting a row to MYSQL using Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related