I am trying to write Python code to insert a new row or if the primary key exists, then update 2 columns with new values which are calculated from variables.
cnx = mysql.connector.connect(user='admin', password='admin', host='127.0.0.1', database='cr_database')
cur = cnx.cursor()
country_string = 'Canada'
number_string = '1234567890'
now_datetime = datetime.now()
attr1_integer = 1
attr2_integer = 10
cur.execute("INSERT INTO cr_table(cr_string, cr_date, cr_country_string, cr_attr1, cr_attr2) VALUES (%s %s %s %s %s) ON DUPLICATE KEY
UPDATE cr_attr1 = cr_attr1 + 1, cr_attr2 = cr_attr2 + VALUES(%s)",(number_string, datetime.now('%Y-%m-%d'), country_string , attr1_integer, attr2_integer, attr2_integer) )
but I get the following 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 '1 10) ON DUPLICATE KEY UPDATE cr_attr1 = cr_attr1 + 1, cr_attr2 = ' at line 1
The same query works perfectly in MySQL editor (after stripping out the Python code) What am I doing wrong in my Python code?