Pickle and sqlite - loading and saving recarray's

Vincent Nijs <v-nijs-GCdv8E9reVRqeCPtdOfA2ld870/[email protected]> Sat, 21 Jul 2007 00:10:54 -0500

Newsgroups	gmane.comp.python.db.pysqlite.user
Message-ID	<C2C6FF8F.842D%[email protected]>

I posted the message below on the numpy discussion list. Few if any people
there seem to use sqlite.

I am interesting in using sqlite to store data for scientific research. I
wrote the attached test program to save and load a simulated 11x500,000
recarray (a common array type in Numpy). Average save and load times are
given below (timeit with 20 repetitions). The save time for sqlite is not
really fair because I have to delete the data table each time before I
create the new one. It is still pretty slow in comparison. Loading the
recarray from sqlite is significantly slower than cPickle. I am hoping there
may be more efficient ways to save and load recarray¹s from/to sqlite than
what I am now doing. Note that I infer the variable names and types from the
data rather than specifying them manually.

saving recarray with cPickle:       1.448568 sec/pass
saving recarray with sqlite:         193.286204 sec/pass

loading recarray using cPickle:    0.471365 sec/pass
loading recarray with sqlite:        15.977018 sec/pass

To run the test you do need numpy (http://www.scipy.org/Download)

I did get an interesting response on this issues from Francesc Altet
(designer of pytables) on the numpy-list that might (or might not :) ) be of
interest to readers of the pysqlite list.

http://permalink.gmane.org/gmane.comp.python.numeric.general/16221

Best,

Vincent

_______________________________________________
pysqlite mailing list
pysqlite-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/pysqlite

load_sqlite_test.py (application/octet-stream, 3.3 KB)

def test_pickle_save(fname):
	# saving recarray as a pickle file
	f = open('%s.pickle' % fname,'wb')
	cPickle.dump(data,f,2)
	f.close()

def test_pickle_load(fname):
	# loading recarray from pickle file
	f = open('%s.pickle' % fname,'rb')
	data = cPickle.load(f)
	f.close()

def test_save_sqlite(fname, table = 'data'):
	# saving recarray to an sqlite file
	conn = sqlite3.connect('%s.sqlite' % fname)

	c = conn.cursor()

	# getting the variable names
	varnm = data.dtype.names
	nr_var = len(varnm)

	# transform to types sqlite knows
	types = []
	for i in data[0]:
		if type(i) == N.string_: types.append('text')
		if type(i) == N.float_: types.append('real')
		if type(i) == N.int_: types.append('integer')
	
	create_string = ",".join(["%s %s" % (v,t) for v,t in zip(varnm,types)])

	# create a table if it doesn't exist yet
	try:
		c.execute('drop table %s' % table)
	except sqlite3.OperationalError:
		pass

	c.execute('create table %s (%s)' % (table,create_string))

	# putting the data into the table
	exec_string = 'insert into %s values %s' % (table,'(%s)' % (','.join(('?')*nr_var)))
	c.executemany(exec_string, data)

	# commiting the data to the database
	conn.commit()

	# closing the connection
	conn.close()

def test_load_sqlite(fname, table = 'data', str_length = 20):

	conn = sqlite3.connect('%s.sqlite' % fname)
	c = conn.cursor()

	# get all data
	c.execute('select * from %s' % table)

	# getting data types
	types = []
	for i in c.fetchone():
		if type(i) == unicode: types.append('S%s' % str_length)
		if type(i) == float: types.append('float')
		if type(i) == int: types.append('int')

	# variable names
	varnm = [i[0] for i in c.description]

	# autodetected dtype
	dtype = zip(varnm,types)
	data = N.fromiter(c, dtype = dtype)

	# closing the connection
	conn.close()

if __name__ == '__main__':
	from timeit import Timer
	import numpy as N
	import os, cPickle, sqlite3

	# making a directory to store simulate data
	if not os.path.exists('./data'): os.mkdir('./data')

	# creating simulated data and variable labels
	varnm = ['id','a','b','c','d','e','f','g','h','i','j']			# variable labels
	nobs = 500000
	data1 =	N.random.randn(nobs,5)	
	data2 =	N.random.randint(-100, high = 100, size = (nobs,5))		

	# adding a string variable
	id = [('id'+str(i)) for i in range(nobs)]

	data1 = [i for i in data1.T]
	data2 = [i for i in data2.T]

	d = []
	d.append(N.array(id))
	d.extend(data1)
	d.extend(data2)

	descr = [(varnm[i],d[i].dtype) for i in xrange(len(varnm))]
	data = N.rec.fromarrays(d, dtype=descr)

	n = 20
	fname = './data/data'

	# testing pickle
	t1 = Timer('test_pickle_save(\"%s\")' % fname, 'from __main__ import test_pickle_save')
	print "\n\nTest saving recarray using cPickle\n"
	print "%.6f sec/pass" % (t1.timeit(number=n)/n)

	# testing sqlite
	t2 = Timer('test_save_sqlite(\"%s\")' % fname, 'from __main__ import test_save_sqlite')
	print "\n\nTest saving recarray with sqlite\n"
	print "%.6f sec/pass" % (t2.timeit(number=n)/n)

	# testing pickle
	t3 = Timer('test_pickle_load(\"%s\")' % fname, 'from __main__ import test_pickle_load')
	print "\n\nTest loading recarray using cPickle\n"
	print "%.6f sec/pass" % (t3.timeit(number=n)/n)

	# testing sqlite
	t4 = Timer('test_load_sqlite(\"%s\")' % fname, 'from __main__ import test_load_sqlite')
	print "\n\nTest loading recarray with sqlite\n"
	print "%.6f sec/pass" % (t4.timeit(number=n)/n)