Python psycopg2 multiple columns in INSERT query

Question

I want to execute an INSERT query via psycopg2, for this question let's simplify it to just:

query = """ INSERT INTO %s("%s") VALUES(%s); """

This works just fine when I do:

params = [AsIs(table_name), AsIs(column_name), value]    
cursor.execute(query, params)

Now, my Pandas dataframe has about 90+ columns, I want to know what the best way to extend the query above to be able to execute it for multiple columns.

I have tried joining every column and value together as a single string and passing that in. I have also tried creating a string with 90+ "\"%s\"" and I have also tried creating a format string ie. """INSERT INTO {0} ({1}...{n}) VALUES ({n+1...n+n})""".format(...). There are unrelated issues that prevent these from working, but is there an easier way to handle this multiple column case?

Community · Accepted Answer · 2017-05-23 11:59:28Z

1

I'm not familiar with pandas but you probably want something like this:

columns = ', '.join(column_names)    # Where column names is a tuple or list of all the column headings you want in your query.
query = """ INSERT INTO %s("%s") VALUES(%%s); """ % (table_name, columns)
params = [value]
cursor.execute(query, params)

The point is that you need to insert the column headings and the values separately. See this post for a much better explanation than what I can provide:

Psycopg2 Insert Into Table with Placeholders

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Mar 4, 2016 at 7:14

kaisquared

4265 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

Python psycopg2 multiple columns in INSERT query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related