Fake Programmers

Ivo Flipse

8:00 PM

should I store the whole line as a string?

just put all the 256 values in one string with a , between the values and insert that whole thing?

Josh

:8275 What is the line?

ok... um...

what are the 256 values?

Ivo Flipse

well it's a rectangle, so each 63 values would only apply to the first line

data looks like this for each frame, just well... larger

Josh

ok, thinking

Steven Rumbalski

Why not use pickle.dump or numpy.dump instead? Then you can just dump your multidimensional arrays in their entirety. No reason to design a database if you're only going to use it for persistence of python objects.

Ivo Flipse

but where does the numpy data go to @StevenRumbalski?

Steven Rumbalski

8:03 PM

a file.

Josh

I guess what I don't understand is, in that image, where are the sensors? Is each set of numbers (the 0s or the 0,4, 1,4, etc) sensors?

Ivo Flipse

every value in there is a sensor

Josh

ok so if so, what's the significance of the numbers separated by commas?

Ivo Flipse

so you would then advice against putting it in a database alltogether @StevenRumbalski?

in the image @Josh? they're just european formatted

Josh

:8291 so 0,4 is... a single integer?

Ivo Flipse

8:05 PM

yeah

Josh

forgive me for being american :-)

Ivo Flipse

forgive me for the poor formatting ;)

Josh

then each number there would be a single dog_measurement_data

Steven Rumbalski

Depends, do you have any other uses for this database than offering persistance to you python program? If yes, do database, otherwise dump it.

Josh

the first one would be dog_measurement_data with sensor_row=0, sensor_col=0, value=0

the first (from the top left) yellow one would be sensor_row=1,sensor_col=8, value=40

Ivo Flipse

8:07 PM

I'm going to put more data in there @StevenRumbalski, but those won't give me as much of a headache

Josh

or whatever 0,4 is...

Ivo Flipse

0.4 :P

Josh

OH!!!!!!!!!

DUUUUHHHH

I am sorry

Steven Rumbalski

also check out the shelve module. it uses the pickle module, but gives it a python dictionary like interface.

Ivo Flipse

but hence why I asked: if sqlite takes strings, why not concatenate all the numbers for a column into one string

then it's only 63 queries per frame

Josh

8:08 PM

You could do that, but it will take up more space

well, it might. it might not actually

Ivo Flipse

well it's already horrible with regards to space

Josh

if you were to do it that way, drop the sensor_col column and change value to a varchar values

Ivo Flipse

though isn't sqlite compressed at least a little bit?

Steven Rumbalski

yes, sqlite does compress on disk.

Josh

The advantage to doing it the way I said originally is you can get a single value using SQL

Ivo Flipse

8:09 PM

well that's one major advantage

each file is 16 Mb if it isn't

Josh

your way, you can only get an entire row, and you need to parse the row in python

Ivo Flipse

what would be the performance on having to query 250*16.000 numbers?

Josh

but if you only ever need rows, no big deal

Ivo Flipse

true, with your way I can select sub areas

Josh

:8319 might be better than having to query 250 rows and use CPU time to split each one bay commas

Ivo Flipse

8:11 PM

and in the end often I only need a subselection even for a limited number of frames

Josh

converting from a string to an array of numbers can be slower than fetching those numbers from the database

Ivo Flipse

true, though if I had to process the data I would just load the whole array and start from there

but the use case is much less frequent

Josh

can be. I am not a python nor sqlite expert, so I can't say for sure :-) If this were PHP & MySQL, I'd store each individual value

Ivo Flipse

no it makes sense

but it's not really wise to constantly having to query it, is it?

Josh

:8329 You can use a single query to fetch all values

Ivo Flipse

8:12 PM

so every time I have to use the value

Steven Rumbalski

well, gotta go, but before you spend time writing sql and translating your arrays into tables, take a quick perusal of the pickle and shelve modules. I use both databases and shelve/pickle for persistence. shelve/pickle means i can basically pickup wherever i left off.

Steven Rumbalski

bye.

Josh

later @Steven!

Ivo Flipse

thanks @StevenRumbalski

Josh

SELECT `sensor_row`,sensor_col`,`value` FROM `dog_measurement_data`
WHERE `dog_measurement_id`=*some measurement id* AND `frame`=*some frame number*
ORDER BY `sensor_row`,`sensor_col`

single query which gives you ALL values for a given frame

Ivo Flipse

8:14 PM

right

Josh

again, not sure about python, but in PHP, that would give me an array of arrays of numbers

so with one fell swoop I can fetch and re-build in memory the data structure

Ivo Flipse

and dumping it in is just a loop of all three dimensions, which I could do directly when I parse the data

Josh

with your way, first the strings are fetched and then they are parsed. But it may be better with sqlite to fetch 63 strings rather than 255*63 numbers, I'm not sure

With MySQL, I'm 99% sure my way would be more efficient, due to caching and table format

@Sathya wanna weigh in since you know a LOT more about database internals than I??

Ivo Flipse

no but I like your suggestion, because if later on I only need one paw's data

I just call the cells I need

not the other lot I don't need

Josh

And the other argument is a theoretical one, namely that the database "understands" your data better

that is to say, if you store a string of comma-seperated valued, you've created a "proprietary" data structure rather than using what sqlite knows about

Ivo Flipse

8:18 PM

how ironic, given that I don't understand them :P

Sathya

:8319 performance: with the right index and right sql, it really shouldn't take more than 1 or 2 to fetch the values

Josh

now, does that really matter when you're the only one using the database? no :-)

Ivo Flipse

well, but there's the catch

Josh

but it means that if other programs/programmers/etc need access to your data, they need to be taught how to interpolate the strings

again, trivial if the strings are CSV :-)

Ivo Flipse

I need to make it so that others will be able to use it later as well

Josh

8:20 PM

most people would be able to figure out without to much difficulty what the CSV strings are

if you wanted to store the numbers as a gzipped JSON string, now you're really showing the point I'm trying to make :-)

it's a minor, kinda nitpicky thing imho. But the zealots on SO would say, use built in data structures

JNK

you want a DB!

Josh

(I think they would anyway :-)

JNK

as it gets more complex you can also create stored procedures for your calls

2

JNK

so that others looking at the data don't have to figure out anything

Josh

:8368 excellent point!

JNK

8:21 PM

they run a stored proc with the right parameters and it automagically appears

Sathya

:8345 I don't think it would be right to store 1,3,4,5,5,66,7 in a single cell

JNK

oh hell no

JNK

dont store numerical data as a string or varchar

JNK

you negate the usefulness of datatypes

Josh

whew I feel better -- I was just waiting for people to tell me I was wrong :-)

:8376 A more concise way to say what I was fumbling around with :-)

JNK

8:23 PM

if you do that you may as well just use a stack of post-it notes and a calculator or abacus

Sathya

thats like storing an array of numbers in invididual variables

(not quite the analogy, but it means it isn't the right way to do it)

now whether it is the easy way - that's another whole discussion :)

Ivo Flipse

what I like most is that inserting just 1 value indeed seems very trival

Josh

:8385 My point exactly, again, better said than I said it!

Ivo Flipse

all I need to do is translate your MySQL to SQLite

Josh

:8386 Ah, I didn't make that point, but it's a very good one. With a string you have to update the whole row to change one value

Ivo Flipse

8:25 PM

or python code

Josh

:8388 yeah, sorry :-(

Ivo Flipse

no it's fine

I'm just going to drop a lot of your fields for now

first see what I can get in there

Josh

SO question, "Please convert this MySQL SQL to sqlite SQL?" :-)

Ivo Flipse

I just need to figure out how to pull out the 3D sections afterwards

because the frame might be just one number giving me all the sensors in it

but when I need a 2D slice

btw what's with the INT UNSIGNED NOT NULL PRIMARY KEY

is that really required?

JNK

INT = integer

JNK

8:32 PM

UNSIGNED = obvious

JNK

NOT NULL = can't put null in the field

JNK

PRIMARY KEY defines it as the key for the table

JNK

not REQUIRED but a good idea for data integrity

Josh

sorry, was harassing Diago

It's different in sqlite

um, who said it...

Ivo Flipse

obviously that auto increment part was used to automatically fill it up, following those rules

Josh

8:34 PM

in Fake Programmers on Super User Chat, Sep 17 at 19:47, by DMA57361

for sqlite autocrement a field just needs to be INTEGER PRIMARY KEY (from: http://www.sqlite.org/faq.html#q1)

Ivo Flipse

oh sorry :$

Josh

np :-)

NOT NULL I always do in MySQL, because NULL can make for confusing code

Ivo Flipse

I was planning on trying it out right now, but my GF is starting to look more angry :P

Josh

UNSIGNED I use in mysql to change the range of int from -2.3 billion - 2.3 billion to 0 - 4.3 billion

:8448 uh oh

better deal with that, I hear those get worse if unchecked :-)

Ivo Flipse

hehe :P

Josh

8:37 PM

@Sathya has read my volumes on advice for dealing with women, roflmao

/me goes for the most edits in a 10-sec period badge!

Welcome @Arrieta

Sathya

:8457 ROFLMAO

Ivo Flipse

sql = """INSERT INTO 'dogs_measurement' (dogs_measurement_id,frame,sensor_row,sensor_col, value) VALUES (1,0,0,0,0.0),(1,0,0,1,1.0),(1,0,1,0,2.0),(1,0,1,1,0.5)"""

doesn't work apparently

some , is messing things up

but where!

Josh

What's the error?

Ivo Flipse

OperationalError: near ",": syntax error

when I try to cursor.execute(sql)

JNK

you need quotes on those strings i think

Josh

8:42 PM

:8481 LOL, I love SQL error messages...

JNK

in SQL Server you would need single quotes like: '1,0,0,0,0,0.0'

JNK

thats a string value, right

Sathya

:8488 sign me up for that.

Josh

LOL. Forgot the <sarcasm> tag ;-)

Sathya

Even better are oracle error messages.

Ivo Flipse

8:45 PM

no that was the measurement_id, frame, the sensor row etc

Sathya

ORA-06512 PL/SQL or numeric value error.

Josh

:8490 no, values for:dogs_measurement_id,frame,sensor_row,sensor_col, value)

Sathya

and a million reasons for that.

note to self: I'm ranting to self

Josh

haha. I'm with you @Sathya

:8475 Try: sql = """INSERT INTO 'dogs_measurement' (dogs_measurement_id,frame,sensor_row,sensor_col, value) VALUES (1,0,0,0,0.0)"""

JNK

oh each was a row

Josh

8:46 PM

er, edited

Sathya

er

JNK

do a single row

Sathya

you can't do insert into x,x,x,x,

Josh

:8516 yeah I guess sqlite can't do that

Ivo Flipse

that works @Josh

Sathya

8:47 PM

for multiple values

Josh

:8519 can only MySQL do that?

Sathya

:8522 even Oracle can't

:8524 I believe so.

Josh

:8525 Really??? I use that ALL THE TIME!

Ivo Flipse

so I guess I need to put it in a loop and create that last part iteratively?

JNK

beware sql injection!

JNK

8:48 PM

j/k @IvoFlipse

Ivo Flipse

that sounds like a bug @JNK

Sathya

you can into INSERT INTO <TABLE> VALUES (<X,Y,Z>)

JNK

its a way for people to mess with your db

Josh

:8530 yeah

JNK

if you allow user input and dont paramaterize/sanitize

Sathya

8:48 PM

You can't do INSERT INTO <TABLE> VALUES((X,Y,Z), (X1,Y1,Z1))

JNK

2

Ivo Flipse

hence why I asked it on SO! :P

Josh

:8540 Have a star :-)

Ivo Flipse

I knew you guys would bring this up

Josh

Where's @HoLiVeR'DO UNION EVIL SQL when we need him? :-)

Ivo Flipse

8:50 PM

but for now there won't be any user input @JNK, so hopefully I can stop worrying (well the only user is me)

Josh

:8551 What about if you decide to hack yourself?

JNK

if it's you i'd be VERY worried :)

Josh

can never be too careful...

JNK

scandanavians and DBs dont mix

Ivo Flipse

btw @Josh, I guess you should update your answer with the (x,y,z),(x,y,z) part, which apparently isn't possible

Josh

8:53 PM

:8556 Thank you sir, editing away

JNK

<3 Little Bobby Tables

Josh

Done

:8559 One of my fav XKCDs

JNK

me too

Ivo Flipse

and again a small typo in the SELECT part, another ' gone missing

are those necessary actually -->'value'<--

those ' '

Josh

The markdown highlither doesn't work right when I'm editing! Grr

So change the `s to 's for sqlite?

Ivo Flipse

8:59 PM

that too probably

Josh

sorry, CEO asking what work I got done today, yikes ;-)

Ivo Flipse

solved some database problems? ;)

TomWij

Meh @ SQL... Use LINQ! :P

Josh

LOL

Ivo Flipse

strange I tried executing:

sql = """SELECT sensor_row,sensor_col,value
FROM dogs_measurement
WHERE dogs_measurement_id = 1
AND frame = 0
ORDER BY sensor_row,sensor_col"""

but when I execute it, it doesn't return anything

Josh

9:04 PM

Try dropping AND frame=0

and add frame to the SELECt

sql = """SELECT frame,sensor_row,sensor_col,value
FROM dogs_measurement
WHERE dogs_measurement_id = 1
ORDER BY sensor_row,sensor_col"""

See if that works. If no, repeat with dogs_measurement_id

sql = """SELECT dogs_measurement_id,frame,sensor_row,sensor_col,value
FROM dogs_measurement
ORDER BY sensor_row,sensor_col"""

Ivo Flipse

<sqlite3.Cursor object at 0x10D08FB0>

Josh

to debug

Ivo Flipse

guess it doesn't print the result

Josh

:8614 Now you need a python expert

Ivo Flipse

well Alex Martelli doesn't dwell here :P

Josh

9:07 PM

in PHP, I execute the query and get a result handle which I pass to a function to retrieve each row

Ivo Flipse

hehe

TomWij

print cursor.fetchall()

for row in cursor:
    print '-'*10
    print 'ID:', row[0]
    print 'Name:', row[1]
    print 'E-Mail:', row[2]
    print '-'*10

Ivo Flipse

that first line prints it :)

for some reason the loop results nothing :S

god I feel horrible at this

TomWij

From: devshed.com/c/a/Python/Using-SQLite-in-Python/3

What does cursor.fetchall() return you?

Ivo Flipse

yeah I saw the fetchall() somewhere, now I know what it does

Josh

9:11 PM

:8634 Nah, don't worry. This is a rough introduction you've chosen!

Ivo Flipse

the select query I reckon

Josh

When I taught C++ my first lesson was a basic calculator and that was too difficult, so you're doing great :-)

Ivo Flipse

well I do have some experience with Matlab

but it took me three months to come up with a peak detection algorithm

the end result was pure genious, if I might add :P

TomWij

:8539 You can do INSERT INTO tablename VALUES (X,Y,Z), (X,Y,Z), (X,Y,Z)

Sathya

:8646 not in Oracle

TomWij

9:16 PM

I see...

Josh

:8652 or sqlite apparently!

TomWij

INSERT INTO tablename
    SELECT X,Y,Z FROM DUAL
        UNION ALL
    SELECT X,Y,Z FROM DUAL
        UNION ALL
    SELECT X,Y,Z FROM DUAL

PostgreSQL, DB2, MSSQL and MySQL support the original. The second one works for Oracle, I don't know about SQLite.

Ivo Flipse

well I'm going to try some more tomorrow

Josh

:8656 Cool. I'm about to head out myself

Ivo Flipse

sadly, I'm on a business trip next week and then on my awesome roadtrip the month after :(

but I'll keep you guys posted on any progress

TomWij

9:19 PM

@IvoFlipse: Anyhow, if you're doing a large set of inserts, check out http://www.sqlite.org/faq.html#q19 anyway.
I used a similar thing at a school project for inserting a lot of RSS items into our database...

@IvoFlipse: Have fun, good luck... :-)

Ivo Flipse

thanks @TomWij, feel free to add that as a comment on my question :)

Josh

Good luck with it @Ivo! I'm headed out, catch you later!

I'll back back tomorrow probably

Transcript for