1

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?

Latika Agarwal
  • 973
  • 1
  • 6
  • 11
Sun
  • 89
  • 1
  • 2
  • 11

2 Answers2

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())
Shivam Batra
  • 68
  • 7
  • 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 Jun 07 '18 at 13:59
1

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()
Sun
  • 89
  • 1
  • 2
  • 11