import sqlite3, sys
import numpy as N

def save_sqlite(fname, data, table = 'data'):
	# saving recarray to an sqlite file
	conn = sqlite3.connect(fname,detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
	c = conn.cursor()

	# transform the 
	descr = data.dtype.descr
	nr_var = len(descr)

	# create a table if it doesn't exist yet
	try:
		c.execute('create table %s (id text, a real, b integer)' % table)
	except sqlite3.OperationalError:
		print "Table already exists."

	# 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 and closing the connections
	conn.commit()
	conn.close()

def load_sqlite(fname, table = 'data'):
	conn = sqlite3.connect(fname)
	c = conn.cursor()
	c.execute('select * from %s' % table)

	# dtype should be auto-detected
	return N.fromiter(c, dtype=[('id','S20'),('a',float), ('b', int)])

if __name__ == '__main__':
	import sys

	# creating simulated data and variable labels
	varnm = ['id','a','b']						# variable labels
	nobs = 50
	id = [('id'+str(i)) for i in range(nobs)]	# adding a string variable
	data1 =	N.random.randn(nobs,1)	
	data2 =	N.random.randint(-100, high = 100, size = (nobs,1))		

	# putting the data together
	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)

	# turning the array into a recarray
	descr = [(varnm[i],d[i].dtype) for i in xrange(len(varnm))]
	data = N.rec.fromarrays(d, dtype=descr)

	# saving recarray to an sqlite file
	save_sqlite('testdata.sqlite',data)

	# loading recarray from an sqlite file
	data = load_sqlite('testdata.sqlite')
	print data