hoeray, I seem to have written working code to insert my data in a database

downside it, it's horribly slow

@Mokubai how much database experience do you have? :P

@IvoFlipse Only a little bit of basic SQL, I only really know the basics, store data, relationships, keys and pull it out via queries...

Any reason?

Mostly used Access, but tried a little of Python + SQLite

query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES """
for frames in range(nz):
    for rows in range(ny-1):
        for cols in range(nx-1):
            statement = query + "(%s, %s, %s, %s )" % (frames, rows, cols, data[rows,cols,frames])
            cur.execute(statement)

this works, but it inserts 3.9 million rows :P

I believe I need to write a loop that appends (frames, rows, cols, data[rows,cols,frames]) to an empty list, tuple or dictionary and then use executemany() to let sql batch process every insert

one moment, need to get my data head on...

I invited you to that Dropbox folder right?

You did

because I have a book MySQL for Python, that I try to use and figure this out :P

The statement query looks like it should evaluate correctly

yeah it works, it just took 6 minutes to insert a 16 Mb file

It's not that it's broken, just that the dataset is huge?

yeah, and because I insert it value for value, it takes forever

whereas with executemany (rather than execute) I would make a connection, dump a frame, make a connection, dump a frame etc

now it's connect, dump value, connect, dump value

Yeah, and python takes ages iterating through large lists...

poooooor IO :P

nice, I put in something that ignores zeros, that cut it down to 37.000 values

still too long I reckon but that's already one optimization :P

Using sqlite?

or full mysql?

I would have started the same way as you, brute force it all in there. but as you say executemany looks worthwhile

MySQL :P

I just need to check, there's a limit to the amount of data you put into MySQL

so I need a sensible 'cut off' point, without gimping my IO

optimally I guess I would insert the maximal amount of data

and the annoying thing is that the example from the book checks for file size on disk, but I don't see how that helps me decide where to cut it off

cursor.executemany(
        """INSERT INTO menu(name, price) VALUES (%s, %s)""",
        [
        ("bass", 6.75),
        ("catfish", 5),
        ("haddock", 6.50),
        ("salmon", 9.50)
        ] )
print "Finished!"

so I guess I need to create a list with tuples of the values I insert

it works :)

still takes a bloody 19 secs for the stripped down version, but I'm sure that most of that time is caused by the loop

Was trying to work out what is going on with stackoverflow.com/questions/1030941/… but not got python installed on this machine atm...

I think I need to come up with a quicker way to create a list/dictionary with: values.append((frames, rows, cols, data[rows,cols,frames]))

list comprehensions perhaps

I could ask it on SO, but I feel like a fool if I haven't even tried anything myself

Well, I can understand what you're doing and the examples, but you're doing it the way I would have as I don't know any better ;)

19 seconds isn't massively slow to me...

copying a file on an SSD? :P

it's that whole iteration that's ridiculously slow

I'm going see how Joe did that elsewhere :P

if I forget about filtering the zeros, I could prepopulate the first values, as they are always the same, they're more like indexes

or at least I wouldn't have to do it every time

I'm asking an SO question, I'll just explain the steps I just took and hope they see that as 'sufficient' effort

just need to calculate how much faster executemany makes it when I leave out the zero filter

Fair enough, sounds like a plan :)

though I'm afraid the list I'm building will be too long for executemany

omg! the executemany with the zero filter is just as long

and inserted something like 157 MB!!!

and inserted something like 157 MB!!! @Mokubai

Yowza!

So it took the same amount of time but inserted a huge amount more?

indeed

I guess that's because I not only store those data points, but also the frame, row, col + ID which all take up space, though that happens in the original version as well :\

very strange :S

hey @IvoFlipse

hi @Sathya, any idea what might be inefficient or wrong?

nope,

lemme install Python

installed the SSD today @IvoFlipse

I saw your tweet :)

:)

are you using 64-bit version of python? @IvoFlipse

I honestly don't know

@IvoFlipse also, where's the code where you're checking

I think it's 32, because some libraries weren't available in 64

checking for 0?

@IvoFlipse I meant 157 MB for your paws application or something else?

I checked that in a database viewer

but either I didn't commit yet or something else went wrong because after that it was empty :S

MySQL Database viewer?

Heidi

how many rows with the >0 condition?

37.521

37k rows in 20 seconds?

yeah

but most of that time is spent on building that list for executemany

hmm

inefficiency comes from the list then

indeed

but I don't know how to change it without messing up my SQL code

well you have a 3 level nested for

for frames in range(nz):
    for rows in range(ny):
        for cols in range(nx):

yup bad isn't it?

very

it does have to check 4M values :P

I think yo'll have to do something to change that, I'm not sure how/if that's possible

I guess I could have done something with range(64) range(256) and map those to the right values

and then use some magic to filter out the non-zero values :P

even if I wanted to insert the entire array, surely this could be done insanely fast right?

it's just the creation of those values to insert that's inefficient

yeah

just measure the time required only for the insert. Betcha it isn't very large

I simply don't know how to create that list any differently

@IvoFlipse have you tried it with the psyco module trying to optimise it, supposedly it might help with large loops where python is a bit "inefficient"... psyco.sourceforge.net

nope, how does it work?

and I get 2 non-answers right away :P

I figured as much @Mokubai :P

It does a bit of JIT compiling, just import it then do a psyco.full()

in some cases it can help, but otherwise it may be worth looking at Cython if it is the actual loop that is slow

psyco would just take over the regular loops?

list comprehensions didn't help either

how would list comprehensions help, I mean you need all the data to be ready right

speed up the looping process

I should use timeit, because it's not entirely fair to just blame the loop

there's a small processing going on before the loop too

ok

the list comphrehension took 13.9 secs my loop takes 13.8 secs

the database part only 3-4 seconds

indeed, as expected

yup, so I'm hoping there's a better way of transforming the data

interestingly enough, building the array in the first place doesn't seem to create so much overhead

hi @moala

well that SO question surely helped speed things up

kind of annoyed I don't know how load data works

and wonder if turning off the indexing when 'seeding' the table would help (just don't know how to get it working)

@IvoFlipse it would help, but not to a drastic extent

well the load data would speed it up a little bit further

it takes 6.8 seconds in total to open/load --> parse (1.8) --> strip of zeros/flatten (0.4) --> store in the db (4.6)

nice

so going straight from open to a flattened file would be faster

and probably letting sql parse the file even faster

so what improved the performance? List comprehension?

np.nonzero returns the indexes of data where it's nonzero (blazingly fast)

then using a zip-loop, which is also faster than a nested one

I just need to know how sql could parse my file, because I'd need to make the right columns for it to 'fit'

aha

how do you mean -

> how sql could parse my file,

dev.mysql.com/doc/refman/5.5/en/load-data.html

they recommended me to use this

LOAD DATA INFILE 'C://IvoPython//App//sel_1' INTO TABLE data;
/* SQL Error (1366): Incorrect integer value: 'Frame 0 (0.00 ms) ' for column 'id' at row 1 */

I 'get' why it doesn't work, it sees those headers and is oblivious as to what to do with it

Woo, I dun made me a data.SE query: data.stackexchange.com/superuser/s/1145/…

planning to do anything with it?

Used it to find a short post for my joy of answers blog post

ah very well

forbes.com/forbes/2011/0411/…

we <3 O'Reilly

Nice