Insert list or tuple into table without iteration into postgresql

Question

I am new to python. What I am trying to achieve is to insert values from my list/tuple into my redshift table without iteration.I have around 1 million rows and 1 column.
Below is the code I am using to create my list/tuple.

cursor1.execute("select domain from url limit 5;")
for record, in cursor1:
    ext = tldextract.extract(record)
    mylist.append(ext.domain + '.' + ext.suffix)

mytuple = tuple(mylist)

I am not sure what is best to use, tuple or list. output of print(mylist) and print(mytuple) are as follows.

List output
['friv.com', 'steep.tv', 'wordpress.com', 'fineartblogger.net', 'v56.org']
Tuple Output
('friv.com', 'steep.tv', 'wordpress.com', 'fineartblogger.net', 'v56.org')

Now, below is the code I am using to insert the values into my redshift table but I am getting an error:

cursor2.execute("INSERT INTO sample(domain) VALUES (%s)", mylist) or
cursor2.execute("INSERT INTO sample(domain) VALUES (%s)", mytuple)

Error - not all arguments converted during string formatting

Any help is appreciated. If any other detail is required please let me know, I will edit my question.

UPDATE 1:

Tried using below code and getting different error.

args_str = ','.join(cur.mogrify("(%s)", x) for x in mylist)
cur.execute("INSERT INTO table VALUES " + args_str)

ERROR - INSERT has more expressions than target columns

A Hettinger · Answer 1 · 2017-04-07T21:40:29.490

1

I think you're looking for Fast Execution helpers:

mylist=[('t1',), ('t2',)] 
execute_values(cursor2, "INSERT INTO sample(domain) %s", mylist, page_size=100)

what this does is it replaces the %s with 100 VALUES. I'm not sure how high you can set page_size, but that should be far more performant.

edited Apr 07 '17 at 21:40

answered Apr 07 '17 at 21:34

A Hettinger

448
2
11

score 0 · Accepted Answer · answered Apr 10 '17 at 18:31

Finally found a solution. For some reason cur.mogrify was not giving me proper sql string for insert. Created my own SQl string and it works alot faster than cur.executeall()

list_size = len(mylist)

for len in range(0,list_size):
    if ( len != list_size-1 ):
        sql = sql + ' ('+ "'"+  mylist[len] + "'"+ ') ,'
    else:
        sql = sql + '('+ "'"+  mylist[len] + "'"+ ')'

cursor1.execute("INSERT into sample(domain) values " + sql)

Thanks for your help guys!

Insert list or tuple into table without iteration into postgresql

2 Answers2