pymysql.err.ProgrammingError Error while Inserting Row into MySQL table (using Python, Flask, ClearDB)

I have seen numerous posts about this but I have not been able to fix this issue. I am converting my database from SQLite to MySQL and am having an issue when inserting values into a table. In this statement, the keyword and sortType are variables that are passed into the method and not modified. keyword, type_search, and sort are of type TEXT while date_add is of type DATE.

conn = mysql.connect() 
cursor = conn.cursor() 
cursor.execute('INSERT INTO search (keyword, date_add, type_search, sort) VALUES (%s, date(), %s, %s)', (keyword, 'value', sortType))
conn.commit()

​x
 
conn = mysql.connect() cursor = conn.cursor() cursor.execute('INSERT INTO search (keyword, date_add, type_search, sort) VALUES (%s, date(), %s, %s)', (keyword, 'value', sortType))conn.commit()​

Error:

pymysql.err.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 '), 'value', 'views_week')' at line 1")

 
pymysql.err.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 '), 'value', 'views_week')' at line 1")​

I changed date to date_add as it was a keyword but this did not fix the issue. How can I fix this?

Thanks!

Answer

You could also create the table with the column default of the current timestamp:

create table search (
keyword...,
date_add TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
...)

 
create table search (keyword...,date_add TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,...)​

Then you could do the execute without mentioning the date_add column and it will be the current timestamp.

Advertisement

Answer