Create list/tuple for inserting multiple rows with one query

Question

Based on the question "psycopg2: insert multiple rows with one query" (psycopg2: insert multiple rows with one query)

From JS, I receive the following on serverside: "2,3,4..."

Then, on server side(python 3.6):

list_urlquery = urlquery_partition.strip().split(",")

 for i in range(len(list_urlquery)):

        list_urlquery[i] = "(" + str(list_urlquery[i]).strip() + ", '" + str(
                        file.filename).strip() + "," + str(PATH_ID).strip() + "')"

 insert_query = 'INSERT INTO tbl_ma (theid, thefilename, thepathid) VALUES %s'

 psycopg2.extras.execute_values(cursor, insert_query, list_urlquery, template=None, page_size=100)


 print (list_urlquery)

Output on Console: ["(2, 'Screenshot from 2018-05-29 07-13-47.png,1')", "(3, 'Screenshot from 2018-05-29 07-13-47.png,1')", "(4, 'Screenshot from 2018-05-29 07-13-47.png,1')"]

Error: INSERT has more expressions than target columns

I request you to guide me on this. How can I create a proper list/tuple and insert the data into database?

Shivam Batra · Accepted Answer · 2018-06-07 13:36:51Z

1

Can you try it like this:

list_urlquery[i] = """({}, '{}', '{}')""".format(str(list_urlquery[i]).strip(), str(file.filename).strip(), str(PATH_ID).strip())

I am assuming only theid column is integer.

If the the pathid is also int (numeric), then try below one

list_urlquery[i] = """({}, '{}', {})""".format(str(list_urlquery[i]).strip(), str(file.filename).strip(), str(PATH_ID).strip())

answered Jun 7, 2018 at 13:36

Shivam Batra

687 bronze badges

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

1 Comment

Sun Over a year ago

Yes, "thepathid" is also int(numeric). I tried the 2nd line as said but, still get the same error at the end "INSERT has more expressions than target columns".

Sun · Accepted Answer · 2018-06-08 15:40:45Z

As per the doc (http://initd.org/psycopg/docs/extras.html#fast-exec) it says, "argslist – sequence of sequences..."

Therefore, sequence of sequences == list of tuples

# creating list of tuples (sequence of sequences).
list_urlquery = urlquery_partition.strip().split(",") # list for "theid"
lst_filename = [] # list for "thefilename"
lst_pathid = [] #list for "thepathid"
lst_tpl_insertdata = [] # creating "List of Tuples == Sequence of sequences".

for i in range(len(list_urlquery)):
    lst_filename.append(str(file.filename).strip())
    lst_pathid.append(PATH_ID)

#combine all list using "zip()" for creating "List of Tuples == Sequence of sequences".
lst_tpl_insertdata = list(zip(list_urlquery,lst_filename,lst_pathid)) 

insert_query = 'INSERT INTO tbl_ma (theid, thefilename, thepathid) VALUES %s'        
psycopg2.extras.execute_values(cursor, insert_query, lst_tpl_insertdata, template=None, page_size=100)        
connection.commit()

Collectives™ on Stack Overflow

Create list/tuple for inserting multiple rows with one query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related