postgresql insert timestamp error with python

Question

I use psycopg2 for postgresql. Here is my snippet:

a = "INSERT INTO tweets (Time) VALUES (%s);" % (datetime.now(),)

cursor.execute(a)

this won't work and gives me an error:

ProgrammingError: syntax error at or near "20"
LINE 1: INSERT INTO tweets (Time) VALUES (2016-10-03 20:14:49.065092...

However, if I run this way:

cursor.execute("INSERT INTO tweets (Time) VALUES (%s);", (datetime.now(),))

it works. I want to know what is the difference between these two expressions, and what is wrong with the first one. Can I do this function use the first structure?

Answer 1

If you check the first query, it states INSERT INTO tweets (Time) VALUES (2016-10-03 20:14:49.065092..., that means, it tries to use unquoted value as a time and this won't work.

If you really want to use your first approach, you have to quote the value:

a = "INSERT INTO tweets (Time) VALUES ('%s');" % (datetime.now(),)
cursor.execute(a)

I'd suggest you to use the second approach, where client library handles all quotes and usually prevents a lot of possible problems like SQL injection.