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?

The first uses Python string substitution. The second uses Psycopg parameter passing and is the only recommended one. — Clodoaldo Neto
– Clodoaldo Neto, Commented Oct 4, 2016 at 9:03

icuken · Accepted Answer · 2016-10-04 08:25:05Z

7

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.

answered Oct 4, 2016 at 8:25

icuken

1,36610 silver badges11 bronze badges

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

1 Comment

piro Over a year ago

Don't even suggest to use the first approach

Collectives™ on Stack Overflow

postgresql insert timestamp error with python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related