Reference

Tips

executemany

con = sqlite3.connect("test.db")
cursor = con.cursor()

scan_sql = []
tod_sql = []
for i in xrange(len(time)):
    .........
    scan_sql.append(scan_tmp)
    tod_sql.append(tod_tmp)

con.executemany('INSERT INTO pb1_ds_scan(id, az, el, hwp, ra, dec, dist_from_sun, object) VALUES(?, ?, ?, ?, ?, ?, ?, ?)', scan_sql)
con.executemany('INSERT INTO pb1_ds_tod(id, boloid, mean, rms) VALUES(?, ?, ?, ?)', tod_sql)

con.commit()
con.close()

It took about 1 sec. to insert 80,000 entries.

Querying by index

import sqlite3
con = sqlite3.connect('hoge.db')
....
query = con.cursor()
sql = 'SELECT sum(last_mjd-first_mjd) as sum FROM table WHERE (first_mjd between %.8lf and %.8lf) ;'%(mjd, mjd+1)
query.execute(sql)
sum = []
for row in query:
    sum.append(row[0])
con.close()

Querying by name

import sqlite3
con = sqlite3.connect('hoge.db')
....
con.row_factory = sqlite3.Row
query = con.cursor()
sql = 'SELECT sum(last_mjd-first_mjd) as sum FROM table WHERE (first_mjd between %.8lf and %.8lf) ;'%(mjd, mjd+1)
query.execute(sql)
sum = []
for row in query:
    sum.append(row['sum'])
con.close()

How to get the last modified row

id = Cursor.lastrowid

How to convert "Row" of sqlite to "array" of numpy

import time
import sqlite3
import numpy as np

con = sqlite3.connect('test.db')
cursor = con.cursor()

t0 = time.time()
cursor.execute('SELECT mean, rms FROM tod WHERE id=0 ORDER BY id;')
t1 = time.time()
print t1 - t0, 'sec'

less than 0.5 sec for 1e+5 rows

t0 = time.time()
vals = np.array(cursor.fetchall())
t1 = time.time()
print t1 - t0, 'sec'

less than 1 sec for 1e+5 rows

t0 = time.time()
mean = vals[:,0]
rms = vals[:,1]
t1 = time.time()
print t1 - t0, 'sec'

almost 0 sec for 1e+5 rows

How to define my original function

import sqlite3

class median:
    def __init__(self):
        self.values = []

    def step(self, value):
        self.values.append(value)

    def finalize(self):
        v = sorted(self.values)
        len = len(v)
        return 0.5 * (v[(len-1)/2] + v[len/2])

con = sqlite3.connect('sqlite.db')
con.create_aggregate('median', 1, median)
cur = con.cursor()
cur.execute('select median(data1) from table1')