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)
          );

Use print(testQuery) and you'll see the problem.

Barmar
– Barmar

2022-01-25 18:21:18 +00:00
Commented Jan 25, 2022 at 18:21 — Barmar
– Barmar, Commented Jan 25, 2022 at 18:21

Barmar · Accepted Answer · 2022-01-25 18:22:56Z

3

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,))

answered Jan 25, 2022 at 18:22

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

why cannot insert python variable into sql query?

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