Populate large files into sqlite from Python

Question

I am new to Python and Sqlite. I run large neural network simulations and I store the spikes as ASCII file(here named spikeTimes.csv ) with 2 columns, First column is the spike time and the second the Neuron Id. Each simulation run has a different parameter (call it theta). I want to populate the database such that I can query with Neuron Id and Theta to get all the spikes times for that neuron Id. This is what I am doing, it works, but is extremely slow since I am looping through every spike time. Could anybody provide suggestions to make it faster ? Thanks in advance.

from  peewee import *
from numpy import *
spkDB = SqliteDatabase('simData.db')

class SimData(Model):
    neuronId = IntegerField();

    class Meta:
        database = spkDB

class SpikeTimes(Model):
    spkNeuronId = ForeignKeyField(SimData, related_name = 'neuron')
    theta = DoubleField();
    spkTimes = DoubleField();      

    class Meta:
        database = spkDB

st = loadtxt('spkTimes.csv')
curTheta = 0;
SimData.create_table()
SpikeTimes.create_table()
for k in unique(st[:, 1]):
    tmp = SimData.create(neuronId = k)
    tmp.save()
    for m in st[st[:, 1] == k, 0]:
        tmpSt = SpikeTimes.create(spkNeuronId = tmp, theta = curTheta, spkTimes = m)
        tmpSt.save()

print 'done'

coleifer · Accepted Answer · 2014-05-21 22:00:50Z

1

Use a transaction.

with spkDB.transaction():
    for k in unique(st[:, 1]):
        tmp = SimData.create(neuronId = k)
        tmp.save()
        for m in st[st[:, 1] == k, 0]:
            tmpSt = SpikeTimes.create(spkNeuronId = tmp, theta = curTheta, spkTimes = m)
            tmpSt.save()

See also: http://peewee.readthedocs.org/en/latest/peewee/cookbook.html#bulk-inserts

answered May 21, 2014 at 22:00

coleifer

26.4k7 gold badges67 silver badges81 bronze badges

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

Collectives™ on Stack Overflow

Populate large files into sqlite from Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related