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')

How np.nan looks in DB

#!/usr/bin/env python

import numpy as np
import sqlite3
 
def main():
    con = sqlite3.connect("pb1_net_psd.db")
    cursor = con.cursor()

    buf = '''
CREATE TABLE pb1_net_psd(
       run_id INTEGER NOT NULL,
       run_subid INTEGER NOT NULL,
       boloid INTEGER,
       NET_psd REAL,
       NET_diff REAL,
       buf TEXT,
       PRIMARY KEY(run_id, run_subid)
       );'''
    con.execute(buf)

    sqls = [(1,1, 1, 10, np.nan, 'test'),
            (1,2, 1, 10., np.nan, 'test'),
            (1,3, 1, True, np.nan, np.nan),
            (1,4, 1, np.nan, np.nan, np.nan),
            (1,5, 2., 10, np.nan, 'hoge')]

    con.executemany('INSERT INTO pb1_net_psd(run_id, run_subid, boloid, NET_psd, NET_diff, buf) VALUES(?, ?, ?, ?, ?, ?)', sqls)
    con.commit()

    for line in con.execute('SELECT * from pb1_net_psd'):
        print line
        print [type(l) for l in line]
    
    con.close()
 
if __name__ == '__main__':
    main()

np.nan looks empty,

sqlite3 pb1_net_psd.db 
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
sqlite> select * from pb1_net_psd;
1|1|1|10.0||test
1|2|1|10.0||test
1|3|1|1.0||
1|4|1|||
1|5|2|10.0||hoge

but it's "None" in sqlite3.

(1, 1, 1, 10.0, None, u'test')
[<type 'int'>, <type 'int'>, <type 'int'>, <type 'float'>, <type 'NoneType'>, <type 'unicode'>]
(1, 2, 1, 10.0, None, u'test')
[<type 'int'>, <type 'int'>, <type 'int'>, <type 'float'>, <type 'NoneType'>, <type 'unicode'>]
(1, 3, 1, 1.0, None, None)
[<type 'int'>, <type 'int'>, <type 'int'>, <type 'float'>, <type 'NoneType'>, <type 'NoneType'>]
(1, 4, 1, None, None, None)
[<type 'int'>, <type 'int'>, <type 'int'>, <type 'NoneType'>, <type 'NoneType'>, <type 'NoneType'>]
(1, 5, 2, 10.0, None, u'hoge')
[<type 'int'>, <type 'int'>, <type 'int'>, <type 'float'>, <type 'NoneType'>, <type 'unicode'>]