psycopg2 - multiple insert, but insert one common timestamp

Question

I am trying to insert to multiple tables, but have them share only one timestamp. I am implementing something like this:

for table in table_names:
    current_utc_time = str(datetime.utcnow())
    cursor.execute("INSERT INTO public.{0} VALUES({1}, {2})".format(table, current_utc_time, foo))

But this set of code throws me the following error...

Traceback (most recent call last):
  File "C:/Users/EricKim/Documents/sage/fake_data_push.py", line 58, in <module>
    cursor.execute("INSERT INTO public.{0} VALUES({1}, {2})".format(table, current_utc_time, df.iloc[row_num, col_num]))
psycopg2.ProgrammingError: syntax error at or near "16"
LINE 1: INSERT INTO public.rt_torqx VALUES(2018-08-30 16:26:35.20088...

There must be some formatting error, but I do now know what's wrong, because to my understanding 2018-08-30 16:26:35.20088 is the right format of pgsql timestamp string format. Can anyone tell me what's wrong?

bimsapi · Accepted Answer · 2018-08-30 19:15:55Z

1

I'd recommend against Python string format() for your parameters. Instead, use psycopg2 parameter passing via %s, which will do type conversion for you. Something like:

right_now = datetime.utcnow()
cursor.execute(f'INSERT INTO public.{table} VALUES(%s, %s)', (right_now, df.iloc[row_num, col_num],))

(Not tested, but the gist is 1) use the f-string (or .format()) only for the table name, since that's not a parameter, and 2) use the %s placeholders for actual parameters; you may need to cast the df value via str()... depends on the data type in question)

See http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

answered Aug 30, 2018 at 19:15

bimsapi

5,0652 gold badges22 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

psycopg2 - multiple insert, but insert one common timestamp

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related