CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
gignosko
gignosko

Reputation: 987

psycopg insert timestamp without formatting it

I have a timestamp that I'm trying to insert into postgres using psycopg, but I'm having trouble getting things to work out and I think it's because I'm trying to use python's string formatting to drop the date into the right position. I have something like this

values = {"timestamp": u'2016-03-01T12:40:45.236697', ...}
"INSERT...VALUES(...{timestamp}...)".format(**values)

Postgres is tripping up on the "T", throwing a syntax error.

I've tried converting the string to a datetime object, but then the formatting just puts it back into a string that looks like this

2016-03-01 12:40:45.236697

and postgres hangs up on the space. I've tried to use PGs to_timestamp(), but PG hangs up in the same places. I'll end up with a few of these and I'm trying to avoid breaking this out into several strings that I have to concat back together. Does anyone have any ideas? I can manipulate the original timestamp string if I need to, but I can't figure out what that string should look like to make PG happy.

Thanks in advance

Upvotes: 2

Views: 6761

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169544

You should not format your string like that. As Ilja mentions in a comment you are vulnerable to SQL Injection attacks that way. You should instead pass the dictionary as the second argument to .execute(), e.g.:

import datetime as dt
t = u'2016-03-01T12:40:45.236697'
cur.execute(
    """INSERT INTO your_table (timestamp, ...)
        VALUES (%(timestamp)s, ...);""",
     {'timestamp': dt.datetime.strptime(t,'%Y-%m-%dT%H:%M:%S.%f'), ...})

This ensures that the correct datatype is inserted and psycopg2 handles the escaping for you.

Upvotes: 4

Related Questions