I have a dictionary that has the following shape:

{0: {'a': 'cat',
  'b': 'dog',
  'c': 'bus',
  'd': 'snake'},
{1: {'a': 'ddsdf',
  'b': 'sdfs',
  'c': 'asdfsd',
  'd': 'asdfds'},
}

placeholders = ', '.join(['%s'] * len(df_merge.columns))
columns = ', '.join(df_merge.columns)

df_merge_reset_index = df_merge.reset_index(drop=True)
merge_to_dict = df_merge_reset_index.to_dict('dict')
merge_to_dict_df = pd.DataFrame.from_dict(merge_to_dict, orient='index')
dict_ = merge_to_dict_df.to_dict('dict')

sql = """INSERT INTO test ({}) VALUES ({}) ON CONFLICT DO NOTHING;""" \
        .format(columns_, placeholders_)

Currently, I am using a for loop

for i in dict_.values():
    cursor.execute(sql,
                   (i['a'], i['b'], i['c'], i['d'])
                   )

But this is very slow, and I would like to use either from psycopg2.extras import execute_values or from psycopg2.extras import execute_batch but the way my dictionary is setup does not allow this. Would anyone have a recommendation on how to transform my data so I can use it with execute_values or execute_batch?

---- UPDATE:

I changed the structure to something thats a little closer by keeping it as a record.

df_merge_reset_index = df_merge_.reset_index(drop=True)
merge_to_dict = df_merge_reset_index.to_dict('records')

[{'a': '',
  'b': '',
  'c': ',
  'd': ''}]

cursor.executemany(sql, merged_dict)

I get the following error cursor.executemany(sql, merged_dict) TypeError: dict is not a sequence

CodePudding user response：

If you have lots of rows you want to insert, do it in 2 steps instead of execute many and others. Even with execute many you'd have to be sending multiple queries when you can just be done in 2.

You can just load the entire df in a single query without worrying about the conflicts, and then you can just transfer it in another query.

Prerequisites:

1. Create an intermediate table for your actual table

Steps:

1. Turn df into a csv
2. COPY the csv to intermediate table with cursor.copy_from(csv)
3. insert into the actual table from the intermediate table

INSERT INTO actual (...)
SELECT ...
FROM intermediate
ON CONFLICT DO NOTHING 

https://www.psycopg.org/docs/cursor.html

CodePudding user response：

list_ = []
for i in merged_dict.values():
    list_.append((i['a'], i['b'], i['c'], i['d'], i['bid'],))
LOG.info("Execute many")
execute_values(cursor, insert_query, tuple_, template=None, page_size=100)