I am trying to insert many columns into a table row using psycopg2 with Python 2.7. Is there a way for me to shorten the INSERT syntax? I currently have to type out the placeholder (%s) over and over again like VALUES(%s,%s,%s,...%s)
Edit: Currently I have
tableCursor.execute('INSERT INTO "table" ("x1","x2","x3","x4")' +
'VALUES (%s,%s,%s,%s)', (val1,val2,val3,val4))
I want to use more than placeholders but it's kinda repetitive to keep typing %s.
Nope.
If you have enough columns that this is an issue, then (a) you probably need to re-think your schema design, and (b) you can always use ', '.join(['%s']*n) where n is the number of parameters to produce the string.
>>> ', '.join(['%s']*7)
'%s, %s, %s, %s, %s, %s, %s'
>>> 'INSERT INTO tablename(col1,col2,col3,col4,col5,col6,col7) VALUES (' + ', '.join(['%s']*7) + ');'
'INSERT INTO tablename(col1,col2,col3,col4,col5,col6,col7) VALUES (%s, %s, %s, %s, %s, %s, %s);'
I agree that it'd be nice to have psycopg automatically deduce the VALUES list based on the number of columns named if you didn't give it one explicitly. It doesn't, though, and it's easy enough to DIY.
This works too:
tableCursor.execute('INSERT INTO "table" ("x1","x2","x3","x4")' +
'VALUES %s', [(val1,val2,val3,val4)])
Tuples are adapted by recursively adapt their arguments and put in parentheses, so they are good to represent records, composite types or lists of values for IN.
