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?

Answer 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