insert multi values from python list into sqlite table

Question

I have over ten thousand of data in python list and it take over 300 seconds to add data from list into sqlite table. I would like to know how to add multi data at the same time or the fastest method to do it.

list_example = [('ST0001', '2020-04-11', 23.0), ('ST0002', '2020-04-11', 20.0), ('ST0003', '2020-04-11', 75.0)]

First code version:

import sqlite3

def add_temp_stock_data(PART_NO, INVDATE, COST):
    global conn
    global c
    conn = sqlite3.connect('PRODUCT_LIST.db')
    c = conn.cursor()
    c.execute("""CREATE TABLE IF NOT EXISTS PRODUCT_LIST (
        PART_NO TEXT,
        INVDATE TEXT,
        COST REAL)
        """,
            )
    with conn:
        c.execute("""INSERT INTO PRODUCT_LIST (PART_NO, INVDATE, COST)
                VALUES (?,?,?)""",
                  (PART_NO, INVDATE, COST,))
    conn.commit()

for i in range(len(list_example)):
    PART_NO = list_example[i][0]
    INVDATE = list_example[i][1]
    COST = list_example[i][2]
    add_temp_stock_data(PART_NO, INVDATE, COST)

Second code version:

def add_temp_stock_data(list_example):
    global conn
    global c
    conn = sqlite3.connect('PRODUCT_LIST.db')
    c = conn.cursor()
    c.execute("""CREATE TABLE IF NOT EXISTS PRODUCT_LIST (
        PART_NO TEXT,
        INVDATE TEXT,
        COST REAL)
        """,
            )
    for i in range(len(list_example)):
        PART_NO = list_example[i][0]
        print(PART_NO)
        INVDATE = list_example[i][1]
        print(INVDATE)
        COST = list_example[i][2]
        print(COST)
        with conn:
            c.execute("""INSERT INTO PRODUCT_LIST (PART_NO, INVDATE, COST)
                    VALUES (?,?,?)""",
                      (PART_NO, INVDATE, COST,))
    print(list_example)
    conn.commit()
add_temp_stock_data(list_example)

Try putting many insert statements at a time in a single transaction. medium.com/@JasonWyatt/… — Marichyasana
– Marichyasana, Commented Apr 24, 2021 at 6:23
Only execute the CREATE TABLE query once, you'll need to move it out of add_temp_stock_data(). Don't create a new connection for each query. Just create a connection and pass it as an argument to add_temp_stock_data(), you'll need to handle the case in which the connection closes prematurely. — Michael Ruth
– Michael Ruth, Commented Apr 24, 2021 at 6:39
@Marichyasana I'm trying to do that thing, but I'm a Python beginner So your useful link is too hard for me to understand. — Vittawat Laorungroj
– Vittawat Laorungroj, Commented Apr 24, 2021 at 6:53
@MichaelRuth Thank you, I did follow your recommendation, it's can reduce the time to be half!!. but I also do need to insert multi data in single transaction to make it faster if you don't mind please help. :) — Vittawat Laorungroj
– Vittawat Laorungroj, Commented Apr 24, 2021 at 6:56
@wallefan's answer already uses a single transaction since execute() is actually implemented as executemany() under the hood. You can verify this by reading the source code or you can trust this Python-specific sqlite optimization article. I'm running executemany() on data like yours with 100,000 inserts in under 100ms. You may be able to shave off a little more time by using sqlite3.Connection.executemany instead of sqlite.Cursor.executemany, but there's not much more room for improvement here in the code. — Michael Ruth
– Michael Ruth, Commented Apr 25, 2021 at 8:40

wallefan · Accepted Answer · 2021-04-24 07:09:06Z

2

Have a look at the documentation for sqlite3.Cursor.executemany().

purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

answered Apr 24, 2021 at 7:09

wallefan

4473 silver badges15 bronze badges

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

1 Comment

Vittawat Laorungroj Over a year ago

it's fantastic! Thank you, for your help.

DevScheffer · Accepted Answer · 2021-04-24 13:31:23Z

0


import sqlite3

def add_temp_stock_data(PART_NO, INVDATE, COST):
    global conn
    global c
    conn = sqlite3.connect('PRODUCT_LIST.db')
    c = conn.cursor()
    c.execute("""CREATE TABLE IF NOT EXISTS PRODUCT_LIST (
        PART_NO TEXT,
        INVDATE TEXT,
        COST REAL)
        """,
            )
    with conn:
        c.execute("INSERT INTO PRODUCT_LIST(PART_NO, INVDATE, COST) VALUES (?,?,?)",(PART_NO,INVDATE,COST)
                )
    conn.commit()

for i in range(len(list_example)):
    PART_NO = list_example[i][0]
    INVDATE = list_example[i][1]
    COST = list_example[i][2]
    add_temp_stock_data(PART_NO, INVDATE, COST)

edited Apr 24, 2021 at 13:31

answered Apr 24, 2021 at 4:52

DevScheffer

4994 silver badges17 bronze badges

1 Comment

Vittawat Laorungroj Over a year ago

Sorry, it's error occurred as sqlite3.OperationalError: no such column: ST0001

Collectives™ on Stack Overflow

insert multi values from python list into sqlite table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related