Data lookup from CSV file on SD card

Hardware Arduino Yún
1

Need some help understanding how to do the following:

Code examples would be greatly appreciated

I have a CSV file on the SD card that has several thousand rows (time stamped sensor values). I want to be able to take a sensor reading, then look up a data point in the CSV file and compare the actual read to the stored value, then make some action.

There are say 4 columns of data, the first column is the time stamp. Columns 2-4 are possible sensor values. For any given time stamp value I want to look look up possible values and compare those to the actual value read by the sensor.

I would be calling this function once every 10 seconds or so, so speed isn't a real concern.

Thank you so much for any help.

2

Use Python:

put header at your file header (timestamp,sensor1,sensor2,sensor3)

import csv
with open("filepath", 'rb') as f:
    reader = csv.DictReader(f)
    rows = [row for row in reader if row['timestamp'] = 'some time']
for row in rows:
    print row
3

Sonny, Thank you for your suggestion.

Now, can you suggest a method for adding a new "column" to the stored data set?

Example:

storeddata[time,val1,val2]
appenddata[time,val1,val2,val3]

4

SQLite: Small. Fast. Reliable. Choose any three.

TIMESTAMP with Sqlite3

Shell script and Sqlite3

Python and Sqlite3

Php and Sqlite3

Lua and Sqlite3

PRIMARY KEY with Sqlite3

Use SQLite which is file base database replace CSV will make your our life much easy.

5

Sonny,

I tried your examples with the Python and Sqllite3. I can get the examples to work but when I modify to meet my needs it doesn't work. Obviously I have some syntax wrong and I think it's in the Python file.

Using the sqltimestamp example I added some columns so the table is as follows:

[id,zone,sample,count,timestamp] where zone id = integer, zone = integer, sample= archer, count = varchar, timestamp = default time similar to your example.

When I run the script on the command line in shell with the insert command everything works fine. But when I use the arduino sketch and the python code I get an error referring to line 4 of the python code.

When I run your python arduino sql example the python code works fine.

What am I missing/doing wrong?

Here is the modified python file:

#!/usr/bin/python
import sqlite3 as sqlite
import sys
script,zone,sample,count = sys.argv
con = sqlite.connect('/mnt/sda1/sensor_flow.db')
cur = con.cursor()
cur.execute('''INSERT INTO tablica (zone, sample, count) VALUES(?,?,?)''',(zone, sample, count))
con.commit()
con.close()

Here is the modified Arduino code:

#include <Process.h>
int z=2; 
long int s = 0;
long int c;

void setup() {
  Bridge.begin(); // Initialize Bridge
}
void loop() {

  s++;
  c = random(0,300);
  Process p;        // Create a process and call it "p"
  p.begin("/mnt/sda1/pythonsqlite3.py"); // Process that launch the "pythonsqlite3.py" command
  p.addParameter(String(z)); // Add the parameter to "zone"
  p.addParameter(String(s)); // Add the parameter to "sample"
  p.addParameter(String(c)); // Add the parameter to "sample"

  p.run();      // Run the process and wait for its termination
  delay(10000);
}
6

Disregard... I found the problem. Forgot to change the table name in the Arduino sketch.

7

Now that I can add sensor data to the database, how to do I look up values in one database and compare those to the current sensor reading? The current value read will be stored in a separate database but it needs to be compared to values stored in a second database either before or after storing. I am assuming this is best done on the Linux side with Python???

Second question is how do I calculate the MODE for a given select query?

8

You need to spend time to learn SQL

  • SQL SELECT
  • SQL WHERE Clause
  • SQL AND & OR Operators
  • SQL ORDER BY
  • SQL BETWEEN Operator
  • SQL IN Operator