Inserting a list of value into SQLite column from python

Question

Say I have a list of following values:

listA = [1,2,3,4,5,6,7,8,9,10]

I want to put each value of this list in a column named formatteddate in my SQLite database using executemany command rather than loop through the entire list and inserting each value separately.

I know how to do it if I had multiple columns of data to insert. For instance, if I had to insert listA,listB,listC then I could create a tuple like (listA[i],listB[i],listC[i]). Is it possible to insert one list of values without a loop. Also assume the insert values are integers.

UPDATE: Based on the answer provided I tried the following code:

def excutemanySQLCodewithTask(sqlcommand,task,databasefilename):
    # create a database connection
    conn = create_connection(databasefilename)
    with conn:
        cur = conn.cursor()
        cur.executemany(sqlcommand,[(i,) for i in task])
        return cur.lastrowid

tempStorage = [19750328, 19750330, 19750401, 19750402, 19750404, 19750406, 19751024, 19751025, 19751028, 19751030]

excutemanySQLCodewithTask("""UPDATE myTable SET formatteddate = (?) ;""",tempStorage,databasefilename)

It still takes too long (roughly 10 hours). I have 150,000 items in tempStorage. I tried INSERT INTO and that was slow as well. It seems like it isn't possible to make a list of tuple of integers.

Please clarify what you want to do. Do you want to insert a new row for each value? And if so, is there only one column named 'values' in the table? If there are other columns, do they accept null? OR do you want to update existing rows with these new values? If so, you need a primary key value paired with each value to update correct. Also, you specify a column named values in the explanation, but your code shows a column called formatteddate. Altogether is it very unclear what you intend to do. — C Perkins
– C Perkins, Commented Oct 24, 2019 at 15:50
Although this is not an answer to fix the overall problem, be aware that running an UPDATE command without a WHERE clause to limit the rows will update ALL rows in the table. The code you have is essentially updating ALL table rows with one value, then replacing ALL formatteddate columns with another value, then repeating over and over about 150,000 times... so that in the end, all rows have same values, the last value in the list. (All other values in the list are overwritten.) — C Perkins
– C Perkins, Commented Oct 24, 2019 at 16:01
@CPerkins I have a list in Python with integers(they are formatted dates). I already have a table with multiple columns and all rows have some value in them. I added a new column to the table named formatteddate. This new column has null values. I want to put the values in the list into the new column named formatteddate. Thanks for pointing out some mistakes in the above code but that has been fixed. I just forgot to fix it here. First, let me ask you this: which would be better INSERT or UPDATE? I assume INSERT because there are no existing values in that column. (Cont'd) — mol_lon
– mol_lon, Commented Oct 25, 2019 at 4:01

Daniel Roseman · Accepted Answer · 2019-10-20 19:49:23Z

3

As you say, you need a list of tuples. So you can do:

cursor.executemany("INSERT INTO my_table VALUES (?)", [(a,) for a in listA])

answered Oct 20, 2019 at 19:49

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

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

3 Comments

mol_lon Over a year ago

Thanks for helping. I tried it your way but it is still too slow. It seems like it is inserting one value at a time.

C Perkins Over a year ago

It is inserting one value at a time. executemany() reruns the same SQL statement for each value passed individually for the single SQL parameter ?.

James VB Over a year ago

In the sql, "INSERT..." statement, what column is ListA being inserted to?

Collectives™ on Stack Overflow

Inserting a list of value into SQLite column from python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related