Multi-value insert into postgresql with python-CodePudding

I want to build a multi-value insert for postgresql like:

INSERT INTO mytable 
VALUES 
    (<value a>, <value b>, …, <value x>),
    (<value 1>, <value 2>, …, <value n>),
    (<value A>, <value B>, …, <value Z>)

I've a dictionary with tuples. foo = [(1, 2, 3), (4, 5, 6), (7, 8, 9)]

How can I build the "correct" insert statement? How can I get rid of the brackets..-> [ ]

>>> 'INSERT INTO mytable VALUES%s' % [f for f in foo]
'INSERT INTO mytable VALUES[(1, 2, 3), (4, 5, 6), (7, 8, 9)]'

CodePudding user response：

",".join([f"({f[0]}, {f[1]}, {f[2]})" for f in foo])

is going to produce the string that you wanted

'(1, 2, 3),(4, 5, 6),(7, 8, 9)'

you can join it with the prefix string to get your desired outcome.

'INSERT INTO mytable VALUES %s' % ",".join([f"({f[0]}, {f[1]}, {f[2]})" for f in foo])

My suggestion is there might be way to bulk insert into the database. Try searching and reading the docs of the framework you are using

Note: handle sql injections