psycopg2 - insert into table from array

Question

Throughout my script I create a fairly long list that then I need to insert into my database. I want to know if there's a way to direct create a SQL statement with the contents of the array instead of overdoing something like:

cursor.execute(
            '''INSERT INTO 
                my_table 
            VALUES (%s, %s, %s, %s, %s, /* 'til almost infinity */);''', 
            (list[0], list[1], list[3]... /* again almost until infinity */))

match · Accepted Answer · 2018-03-08 21:04:06Z

2

You can use string formatting and join to achieve the VALUES expansion, using the length of your data list. You can then just use a list or tuple as is for the actual values. Something like:

cursor.execute("""INSERT INTO my_table VALUES({})""".format(','.join('%s' for x in my_list)), data)

answered Mar 8, 2018 at 21:04

match

11.2k3 gold badges25 silver badges46 bronze badges

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

Comments

pault · Accepted Answer · 2018-03-08 21:38:01Z

1

How about something like this:

cursor.execute(
    '''INSERT INTO 
        my_table 
    VALUES (''' + ','.join(['%s' for x in mylist]) + ''');''', 
    mylist
)

Use ','.join(['%s' for x in mylist]) to create a comma separated string of placeholders for your data.

edited Mar 8, 2018 at 21:38

answered Mar 8, 2018 at 20:53

pault

43.7k17 gold badges121 silver badges161 bronze badges

2 Comments

JD Gamboa Over a year ago

I did not know you could use * in such manner. It doesn't seem to work in this context, though.

pault Over a year ago

@JDGamboa how about without the *? Unfortunately, I don't have access to a PG database to test.

Collectives™ on Stack Overflow

psycopg2 - insert into table from array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related