How to retrieve data from SQLite faster in python

Question

I have the following info in my database (example):

longitude (real): 70.74
userid (int): 12

This is how i fetch it:

import sqlite3 as lite
con = lite.connect(dbpath)
    with con:
    cur = con.cursor()
    cur.execute('SELECT latitude, userid FROM message')
    con.commit()
    print "executed"
    while True:
        tmp = cur.fetchone()
        if tmp != None:
            info.append([tmp[0],tmp[1]])
        else:
            break

To get the same info on the form [70.74, 12] What else can I do to speed up this process? At 10,000,000 rows this takes approx 50 seconds, as I'm aiming for 200,000,000 rows - I never get through this, possible to a memory leak or something like that?

Sorry, I was incorrect, I actually saved userid as text to conserve any leading zeros. — bjornasm
– bjornasm, Commented Mar 21, 2015 at 1:12
What is your measure? How fast does this run and what type of improvement are you hoping for?? — Frank V
– Frank V, Commented Mar 21, 2015 at 1:33

Patrick McLaren · Accepted Answer · 2015-03-21 03:23:25Z

1

From the sqlite3 documentation:

A Row instance serves as a highly optimized row_factory for Connection objects. It tries to mimic a tuple in most of its features.

Since a Row closely mimics a tuple, depending on your needs you may not even need to unpack the results.

However, since your numerical types are stored as strings, we do need to do some processing. As @Jon Clements pointed out, the cursor is an iterable, so we can just use a comprehension, obtaining the float and ints at the same time.

import sqlite3 as lite

with lite.connect(dbpath) as conn:
  cur = conn.execute('SELECT latitude, userid FROM message')

  items = [[float(x[0]), int(x[1])] for x in cur]

EDIT: We're not making any changes, so we don't need to call commit.

edited Mar 21, 2015 at 3:23

answered Mar 21, 2015 at 1:30

Patrick McLaren

1,00811 silver badges22 bronze badges

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

2 Comments

Jon Clements Over a year ago

You can drop the .fetchall as that will pull a list of tuples by default... it'll act as an iterable otherwise, so for x in cur is fine. One could even drop the creation of the cursor object and execute on the connection object...

Patrick McLaren Over a year ago

Thanks! I'll add those suggestions now.

Collectives™ on Stack Overflow

How to retrieve data from SQLite faster in python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related