Database with Sqlite3

Question

I am creating a stock price database with Sqlite3 python library. But my code is taking ages to run, not sure why it is slow. Any idea how I can speed it up? Am I doing something wrong?

I am using Python 3.x, Anaconda

import pandas as pd
from googlefinance.client import get_price_data, get_prices_data, get_prices_time_data
import sqlite3
db = sqlite3.connect('database.db')
c = db.cursor()

param = {'q':'MMM', 'i':"86400",'x':"NYSE",'p':"25Y"}
end_of_day = pd.DataFrame(get_price_data(param))
end_of_day['Time']=end_of_day.index
count= len(end_of_day['Time'])

c.execute('CREATE TABLE IF NOT EXISTS MMM(date,open,high,low,close,volume)')

for i in range(0,count):
    c.execute('INSERT INTO MMM(date,open,high,low,close,volume) VALUES(?,?,?,?,?,?)',
              (str(end_of_day.iloc[i][5]),str(end_of_day.iloc[i][0]),str(end_of_day.iloc[i][1]),
              str(end_of_day.iloc[i][2]),str(end_of_day.iloc[i][3]),str(end_of_day.iloc[i][4])))
    db.commit()

c.close()
db.close()

Your insert query has two parts. First part defines fields array and second part defines values array. use must loop second part only to make it a values array buffering it in a string variable. currently your loop is executing insert query on every iteration. this is making it slow. then execute insert query after the loop by concatenating fields part and values part. — Manpreet Singh Dhillon
– Manpreet Singh Dhillon, Commented Apr 23, 2018 at 13:10

Shubhitgarg · Accepted Answer · 2018-04-23 13:23:20Z

2

Your code is taking time is because you are using commit for every insert and using execute whereas for bulk inserts you can use executemany().

Try to bind all the data in tuple and then append to list then use executemany for fast bulk inserts:

_list=[]
for i in range(0,count):
    _tuple=(str(end_of_day.iloc[i][5]),str(end_of_day.iloc[i][0]),str(end_of_day.iloc[i][1]),
     str(end_of_day.iloc[i][2]),str(end_of_day.iloc[i][3]),str(end_of_day.iloc[i][4])))
    _list.append(_tuple)
    _tuple=()

c.executemany('INSERT INTO MMM(date,open,high,low,close,volume) VALUES(?,?,?,?,?,?)',(_list))
db.commit()

answered Apr 23, 2018 at 13:23

Shubhitgarg

5846 silver badges20 bronze badges

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

1 Comment

Serge Ballesta Over a year ago

The real problem is the commit. The Sqlite3 documentation states that multiple INSERT are not a problem, but multiple COMMIT are...

sjw · Accepted Answer · 2018-04-23 13:35:26Z

1

Assuming count is large, that loop will really be slowing things down. You can use executemany() to speed things up. Try replacing your loop with this:

params = end_of_day.apply(tuple).tolist()  # Convert dataframe to list of tuples
c.executemany('INSERT INTO MMM(open,high,low,close,volume,date) VALUES(?,?,?,?,?,?)', params)
db.commit()

See documentation for more info on executemany().

An even easier option might be to simply use the built-in Pandas function to_sql():

end_of_day.to_sql('MMM', db)

You might have to rearrange your column order a little in Pandas before doing this, but it is a very convenient function to be aware of.

edited Apr 23, 2018 at 13:35

answered Apr 23, 2018 at 13:26

sjw

6,5512 gold badges30 silver badges41 bronze badges

Collectives™ on Stack Overflow

Database with Sqlite3

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related