0

Can I insert 100k notes in <=1 seconds(database SQLite).I use python library now.My code is here.I have result 100k notes in 1,5 seconds.

conn = sqlite3.connect('data8.sqlite')
c = conn.cursor()
for i in range(1,100000):
    c.execute('''INSERT INTO data ('val1','val2','val3','val4','val5','val6','val7','val8','val9','val10')\
              VALUES ('%d','%d','%d','%d','%d','%d','%d','%d','%d','%d')''' % (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

conn.commit()
c.close()
conn.close()
linuxUser123
  • 529
  • 3
  • 17
Оксана Егорова
  • 1
  • 1
  • 3

3 Answers3

1

You can store all data into a list and pass this list to executemany():

data = []
for i in range(1, 100000):
    data.append((1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

c.executemany('INSERT INTO data (val1, val2, val3, val4, val5, val6, val7, val8, val9, val10) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', data)

I use the ? char to prevent the use of string formatting (and it should be used as a best practice). In my computer, I went from ~ 1 sec to < 0.3 sec.

Tiger-222
  • 6,677
  • 3
  • 47
  • 60
0

yes you can but It depends on your machine for example my machine did less than 1 msec and dont use ' ' like this INSERT INTO data ('val1','val2' ... use this INSERT INTO data (val1,val2 ...:

conn = sqlite3.connect('data8.sqlite')
c = conn.cursor()
c.execute("""
        CREATE TABLE IF NOT EXISTS data (
            val1 integer,
            val2 integer
        )
""")
for i in range(1,100000):
    c.execute('''INSERT INTO data (val1, val2)\
              VALUES (%d, %d)''' % (1, 2))
row= c.execute("""select count(*) from data""")
print(row.fetchone())
conn.commit()
c.close()
conn.close()
metmirr
  • 4,234
  • 2
  • 21
  • 34
0

It's not a matter of code, the insert speed is due to several variables:

  • PC performances.
  • The query itself, it depends on how much values and records you want to insert, the field type and how big is the value you want to insert.
  • In case you are inserting rows in an online database, the network connection also does its part.

To see if the speed gets higher, you could try to insert values on a more permormant machine (like a server).

Belfed
  • 165
  • 1
  • 13