Search code examples
pythonpostgresqlpsycopg2tld

Insert list or tuple into table without iteration into postgresql


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


Solution

  • 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!