Bulk inserting a dataframe using psycopg2 (error: 'dict' object does not support indexing)

Question

Big thanks in advance, relatively new to psycopg2.

I'm trying to bulk insert data in the form of a pandas dataframe to my existing postgres database.

try:
    psycopg2.extras.execute_values(
                cur=cur,
                sql=sql.SQL("""
                INSERT into {table_name} ( {columns} )
                VALUES %s
                """).format(table_name=Identifier(entity),
                            columns=SQL(', ').join(map(Identifier, column_names))
                            ),
                argslist=dataframe,
                template=None,
                page_size=500)

except Exception as error:
    print(ERROR: ' + error)

I get the error below when I run this:

string index out of range

I tried changing the dataframe to a dict, using:

dataframe = dataframe.to_dict(orient='records')

The output that I am getting from the except clause is now as follows:

'dict' object does not support indexing

Any help hugely appreciated, I'm not sure what the issue is here.

Thanks in advance

Does this stackoverflow.com/a/8666415/5666087 answer your question? — jkr
– jkr, Commented Apr 29, 2020 at 13:32
In case you are passing dicts, your placeholder is (%(dict_key_1)s, %(dict_key_2)s, ...) instead of %s — Maurice Meyer
– Maurice Meyer, Commented Apr 29, 2020 at 21:01

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

This seems like a case of an unhelpful error message. Quoting from another SO answer:

You have to give %% to use it as % because % in python is use as string formatting so when you write single % its assume that you are going to replace some value with this.

So when you want to place single % in string with query always place double %.

Try the following code, which replaces %s with %%s.

try:
    psycopg2.extras.execute_values(
                cur=cur,
                sql=sql.SQL("""
                INSERT into {table_name} ( {columns} )
                VALUES %%s
                """).format(table_name=Identifier(entity),
                            columns=SQL(', ').join(map(Identifier, column_names))
                            ),
                argslist=dataframe,
                template=None,
                page_size=500)

except Exception as error:
    print(ERROR: ' + error)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 29, 2020 at 13:35

jkr

19.6k5 gold badges48 silver badges78 bronze badges

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

1 Comment

Cam Westwood Over a year ago

I get the error message: the query doesn't contain any '%s' placeholder

PythonSherpa · Accepted Answer · 2020-04-29 13:38:26Z

0

Alternatively, you could use pandas.to_sql:

from sqlalchemy import create_engine
engine = create_engine('"postgresql://postgres:postgres@localhost/postgres"', echo=False)

df.to_sql('table_name', con=engine, if_exists='append')

Play around with this before you try this in production...

answered Apr 29, 2020 at 13:38

PythonSherpa

2,6004 gold badges22 silver badges41 bronze badges

3 Comments

Cam Westwood Over a year ago

I've had a go at doing this and found it to be on the slow side, while reading about how to speed it up, I read elsewhere that to_sql is a less efficient way of doing this when dealing with larger volumes of data.and I should output to temp file and batch copy into the db. Not sure if you have any thoughts on this

sammywemmy Over a year ago

i know there is a multi option in pandas to_sql. also if u r using sqlalchemy as ur connection, there is an insert many option that pscopg2 provides. somewhere in the docs. it will speed up ur insertions into the database table

PythonSherpa Over a year ago

You could try method 4 from this article: medium.com/analytics-vidhya/…

Collectives™ on Stack Overflow

Bulk inserting a dataframe using psycopg2 (error: 'dict' object does not support indexing)

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related