why cannot insert python variable into sql query?

Question

I am not able to insert a timestamp into a mysql (v8.0.27) database using a python variable. (Note I am using pymysql as the cursor).

The following code works as expected:

testQuery = f"""
      INSERT INTO test_table_2(time, id) VALUES ('2020-05-23 05:30:10', 4);
      """
cursor.execute(testQuery)

But the following code does not work:

time = '2020-05-23 05:30:10'
testQuery = f"""
      INSERT INTO test_table_2(time, id) VALUES ({time}, 4);
      """
cursor.execute(testQuery)

and gives the following 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 '05:30:10, 4)' at line 1")

(I know that using f-strings is not good for injection attacks but I don't care too much about that for the moment as I am just trying to get something simple working. However any suggestions for improvements are also welcome. I have tried other more complex approaches using stored procedures and other ways but this is also not working. Have put a post here on this if anyone can help with the stored procedure version of this question: cannot insert datetime field with stored procedure into mysql database )

The table is created using the following schema:

CREATE TABLE IF NOT EXISTS test_table_2 (
          id INT, 
          time TIMESTAMP,
          PRIMARY KEY (time)
          );

Answer 1

You forgot to put quotes around {time}, like you did with the hard-coded timestamp.

But don't use string substitution, use a prepared statement with parameters.

time = '2020-05-23 05:30:10'
testQuery = f"""
      INSERT INTO test_table_2(time, id) VALUES (%s, 4);
      """
cursor.execute(testQuery, (time,))