executesql insert multiple rows

[ssuresh]

I am trying to insert multiple rows into a postgresql table ..The SQL will look like this

insert into mytable(col1,col2) values(val1,val2),(val3,val4),(val5,val6) 

Since the web2py batching just iterates and executes each sql statement, I am trying to avoid multiple db calls by using executesql

I tried to do this using the following code

placeholderlist=[[val1,val2],[val3,val4],[val5,val6] ]

db.executesql(insert into mytable(col1,col2) values(%s,%s),placeholderlist])

But it does not work..Can somebody please help..

[Brian M]

Sorry, not totally sure how to fix it, but what you've got now is quite confused. You've got just 2 placeholders in your SQL statement "values(%s, %s)" but 3 in placeholderlist so right there it isn't going to work. Plus, you don't want "values([val1, val2], [val3, val4]) in the final SQL anyway you want "values(val1, val2)".
 
Just a guess (totally untested), but to get this to work you may have to make your SQL query dynamically.

placeholderlist=[[val1a,val2a], [val1b,val2b], [val1c,val2c]]
#you've got 3 sets of values, so you'll need 3 values(%s, %s) in the sql - add in the necessary values(%s, %s) dynamically based on len(placeholderslist)
value_placeholders = ', '.join(['values(%s, %s)']*len(placeholderslist))
sql = "insert into mytable(col1,col2) "+value_placeholders
#and you'd then need all of those value pairs in a single flattened list, again actually do dynamically
placeholders = []
for v in placeholderslist:
    placeholders.extend(v)
#gives placeholders = [val1a, val2a, val1b, val2b, val1c, val2c]
#and then hopefully this would work
db.executesql(sql, placeholders)
#hopefully ends up with
#INSERT INTO mytable(col1, col2) values(val1a, val2a), values(val2a, val2b), values(val3a, val3b)

Again I haven't tested this, don't even have postgresql installed at the moment but give it a shot.

[ssuresh]

Thanx Brian.. I too thought of something similar- concatenating to create the sql ..Since executing multiple statements is quite common, I thought something inbuilt will be there in web2py..

I dug through the code and found a executemany() function in  gluon/contrib/pg8000/dbapi.py  ..Will it serve the purpose? Is it accessible through DAL

[Brian M]

Sorry, don't have an install of PostgreSQL handy to try. I think that this likely isn't included in DAL because of differing syntax and ability between the different databases and nobody having volunteered to sort it out and submit a patch. (For example i use MSSQL and such inserts are different between 2005 and 2008 versions)

I suppose you could let the DAL build the individual insert statements for you with db.mytable._insert(field1=a, field2=b) which you then store and then concatenate the individual insert statements into one big series of queries and run it via executesql(). That might work too.