Arduino serial data export to database?

Development Code
1

Hi guys!

I'm just starting out with coding and DIY electronics. I have an upcoming project in which i will build a power meter using the Arduino and the ACS712. I have so far been successful in reading voltage and current from the serial monitor in the Arduino IDE. My goal is to build a webpage where the results will be presented, as well as the integral of the power-time graph.

I have a RPI which i will use as a web server and database. The idea is to use the USB interface to transfer the serial data to the Pi. I have so far established a connection between the Arduino and the RPI and been able to read the serial data using Minicom.

My question is, how would i set up a database which imports the serial data and presents it in four different categories?
I need a database with the following categories: time, voltage, current and power (watts). Which database is most suitable for my purposes? Mysql or Sqlite?

How would i differentiate the float the values sent by the Arduino from each other so that the database understands which value is voltage and what value is current?

Hope you guys can bring some clarity in to my issues!

/J

2

In short, the database can't directly import the data by itself. You need a program (or script, or whatever) to do it.

If you only need to store that data, it wont matter for you. Just pick the one you are most comfortable with or is easier to deploy in your case.

As said before, the database wont get it for you. You need to tell it which is which.

There are many ways to implement this, but i would just do a simple script in whatever language you are most comfortable with. This script will recieve the data from the Arduino and get it uploaded onto the database.

You can diferenciate between values by tagging them when you pass them onto the script, or by always putting them on the same order. That way your script can tell which is which and can upload them into the correct category.

3

Thnx for your reply!

Let's say that i create a python script that stores the values from the serial data in two different variables. How would i then "adress" the database to receive that data? I am complete newby when it comes to databases.

/J

4

It would be sort of a two parter. You would need a script/program to do polling of the arduino, and store the results in the database.
And a second program which you can do requests to for retrieving the data from the database.
imo the simplest solution would just be a small webpage which could handle the showing of data right on the pi, it would be fairly easy to retrieve the data out of a mysql database, and show it through a web view by simply entering the ip address of the pi into your favorite browser.

5

PHP can talk directly to serial ports by use of modules such as pear and pecl. Theres some really neat features available that are not well known. I've used pear to communicate with a label printer for a little inventory project.

6

Thanks for your replies!

This is sort of what i had in mind. I want a database for further analysis of the data. I will have the power meter connected to a solar panel and therefore i wish to plot some graphs and do some calculations during a longer period of time. Can i do that if i post the serial data directly on a webpage without storing it somewhere?

Maybe i'm attacking this project with the wrong approach? My primary objective is to data analysis and my secondary objective is to post the data on a webpage. What other options might there be to solve the issue?

@SudoSaibot

7

You'll need a large population of data to do data analysis like graphs and what not. This really is where mySQL and such kind of databases shine, they're very optimised for searching in large data sets, and storing of these, not to mention make sure the data's integrity is ok.
Storing those data in any other kind of database(e.g a xml file or likes) would mean excessive wear on the SD card(cells have a limited write cycles life time), and very in-efficient data handling, where you basically have to reinvent the fork and knife.
Simplest way of managing these populations of data is really just to shove them into an old school database, and do some queries through whichever interface you feel fit to make.
Regardless of what you do the data has to be stored somewhere on the raspberry pi. Be it in a database, in memory(not very likely, and extreeeeeemely unsafe), or in a file of some kind.
And yes you would be able to make a unit which could post a small html of sorts with the latest measurement of the arduino, but it simply doesn't have the memory to contain a history of your measured data(these types of hardware usually just have kB's of memory, some may have megabytes, and no storage, other than flash storage to upload your software). Maybe a few measurements, but nothing useable for data analysis, and if the unit for some reason restart, or anything that data history would be gone.

1 Like
8

Alright, a database seems like a good option then!

Would Microsoft azure be a good option? I might get access to it since i'm a student. What kind of bandwidth would be necessary to keep a SQL database updated?
Just a few kbits/s? I currently have a 32 GB micro SD card in the pi, would this be sufficient for at least a few weeks of tracking?

I am not really on point with what you mean with that the RPI doesn't have enough memory, the RPI 3 has 1 GB of ram.
If you are thinking of the Arduino what kind of data would i be storing on it? The purpose of having an external unit such as the RPI is to store data and possibly a web server. I only intend to use the Arduino as a slave for harvesting sensor values and nothing else. The html site would be built on the pi if decide to go that route.

I definitely see why i shouldn't use the pi in case of a power outage etc.

Anyways thnx once again for your help!

9

I have a database here that's 32GB which has 20,145,327 rows in just one of the tables, data storage on a 32GB card in a Pi for data logger storing a DateTime and a float/integer will be more than enough and SQLite will run quite happily on a Pi.

Only thing with the Pi is they have a tendency of corrupting the SD cards when power is yanked from them abruptly, however, USB memory sticks plugged into the Pi don't do it, so having your data stored on the USB stick removes that issue (in fact, the Raspberry Pi 3 can now have the OS booted directly from the USB stick).

If you're going to run a server in the Cloud on Azure though, you could potentially eliminate the Pi altogether and just use something like the Particle Photon which has a wifi module built in, that would then periodically send a HTTP request (KBs) to your web server with your data values in the request and have that store your data in a database on the server.

10

for what you need just use the mysql database on your raspberry, the Rpi can easily handle, and handle a apache server for your web interface at the same time, without even breaking a sweat.
I meant the embedded unit you have has a lot less memory than the Rpi, i'm guessing you probably only have a few KBs on the arduino.
The Rpi is actually quite a powerful computer for these kinds of things.
And yea i get your idea, but your previous post kind of sounded like you wanted the arduino to serve the data history, and not the measurement, which it is definitely not capable of, but it can easily do measurements and the Rpi polls the measured data of the ardo, and stores in a database, else it may have been me who had a brain aneurysm and misunderstood what you meant.

11

You'd probably be better off exporting your data as a CSV document.

12

@GoingPostal13

The particle photon seems interesting but since the cost of shipment is 60 USD i think thats a bit to pricy.
A normal MySql database running on the pi might be the best option after all, i have 64gb usb stick which i could easily dedicate to the project. Would running microsoft azure be a better solution than running it locally? Would it be a more complex set up? If can get Azure for free that might be the best option, by simply removing a lot of risk and giving it a guaranteed 24/7 uptime.

@Lauritzen

Thanks for clarifying that for me. I might have been a bit unclear of my intentions. English is not my natively spoken language so don't worry about brain aneurysm :)

@Dynamic_Gravity

How come?

13

CSV (comma separated value) documents are easy to parse and use for many different languages.

14

It depends on what you're trying to achieve. Running it locally would be far simpler (and logging all your data to a flat CSV file would be a doddle to do), but it really depends on what you're trying to do with the end data.

You can get other Arduino compatible boards with a wifi module in them (the Protons are an expensive version), such as the Wemos D1 mini, have a look on eBay - you can get datalogger modules you can plug a microSD card into for Arduino boards too, which you could log a simple CSV file to.

15

Serial is a fine. But there are many ways to achieve a goal. It may be easier to use an Ethernet shield and post chunks of data to a php script that dumps to a database. Then your database can be on your RPI or a remote server, just point the arduino to the right IP.

There are bunches of examples for sending http data on the arduino website. I have a little project I can share on github that sends data over the web.

16

I uploaded the ino project. This does not include the php script that recieves the data sent by the arduino.
Github

This is the bit that sends the data. Theres not much to it.

  else if(time - time_h >= interval_h || changeState != fanState)              // send heartbeat
  {
    //Serial.println(F("Sending heartbeat..."));
    time_h = time;
    int conn = client.connect(hostIP, hostPORT);
    
    if(conn)
    {
      char postContent[50];
      sprintf (postContent, "heartbeat=true&status=%d&temp=%d&did=%d", fanState, temperature, deviceId);
      
      // send heartbeat http
      client.println(F("POST /autohome/device.php HTTP/1.0"));
      client.print(F("Cookie: cookname="));
      client.print(hostUser);
      client.print(F("; cookpass="));
      client.println(hostPass);
      client.println(F("User-Agent: Arduino"));
      client.println(F("Host: localhost"));
      client.println(F("Content-Type: application/x-www-form-urlencoded"));
      client.print(F("Content-Length:"));
      client.println(strlen(postContent));
      client.println(F("Connection: Keep-Alive"));
      client.println();
      client.print(postContent);
      
      //Serial.println(F("Heartbeat Sent"));
    }
    else
    {
      //Serial.print(F("Error connecting to client... status="));
      //Serial.println(conn);
    }
    changeState = fanState;
    delay(1);
  }

There is some very basic authentication sent as a cookie through the header. Both the client(arduino) and server run on a LAN and probably needs to be revised if you do use it over the in internet. This was not designed to be IOT (Internet Of Trash). The server is what makes the data and control remotely accessible.

The PHP script would function something like below. This is a bunch of cut/paste parts from a project and is nearly unreadable without the rest of the code. The source is not yet up on github but will be in the coming weeks.

//Device.php
if($logged_in == 1)
{
	$userInfo = $User->getInfo();
	if(isset($_POST))
	{
		//toFile($_POST);//-----------------------------------------------------------------------------------------------------------DEBUG
		$Form = new Form($_POST);
		$post = $Form->getFields();
		
		$Controller = new Device();

		if(isset($post["heartbeat"]))
		{
			//toFile($post);//-----------------------------------------------------------------------------------------------------------DEBUG
			if($Controller->heartbeat($post))
			{
				exit("Ok");
			}
			else
			{
				exit("Nope");
			}
		}
	}
}

//Func.php
	public function heartbeat($post)
	{
		$post["remoteUrl"] = $_SERVER['REMOTE_ADDR'];
		$post["heartbeat"] = date("mdyHis");
		$deviceInfo = $this->deviceInfo($post["did"], FALSE, FALSE);
		
		if($deviceInfo)
		{
			if($deviceInfo["type"] == "hcu")
			{
				if(!$this->dataLog($post["did"], $post["heartbeat"], $post["status"], 7, $post["temp"]))				//record to history
				{
					return FALSE;
				}
			}
			
			return $this->runUpdate($deviceInfo, $post);
		}
		else
		{
			return FALSE;
		}
	}

//Data.php
	protected function dataLog($did, $time, $status, $obj, $val)
	{
		$q = "INSERT INTO devicelog
		VALUES
		(
			'',
			'".$this->esc($did)."',
			'".$this->esc($time)."',
			'".$this->esc($status)."',
			'".$this->esc($obj)."',
			'".$this->esc($val)."'
		)";
		$result = mysqli_query($this->connect, $q);
		if($result)
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
1 Like
17

Thanks a lot for the code! :)

I am definitely looking forward to your Github post!

In the meantime i will try to set up a local database on the pi along with a website. I might change direction to the one you've chosen depending on the complexity of the project and the hurdles i will run in to.

Anyways thanks a lot for your help guys!

I'll be updating the forum post as i progress through the project, if there is an interest. I will do this project alongside with full time studies so updates might be a bit spread out.

/J

18

Hi again.

It seems like I've ran into some issues which i would be very grateful if anyone here had the solution to.

I have managed to create a script that does what i want. My issue is the values sent by the arduino (voltage and current) sometimes get's "switched". Therefore current get's written to the voltage row and vice versa. If i time the launch of the script perfectly with the arduino everything works just fine. I would like it to work more reliable than this. This is the code I've been using so far.

import sqlite3
import time
import datetime
import serial
values = []
serialArduino = serial.Serial('/dev/ttyACM0', baudrate=9600, timeout=1)
voltageRead = serialArduino.readline()
currentRead = serialArduino.readline()


conn = sqlite3.connect('solartracker.db')
c = conn.cursor()

def create_table():
        c.execute('CREATE TABLE IF NOT EXISTS stuffToPlot (voltage REAL, current REAL, datestamp TEXT')

def dynamic_data_entry():
        unix = time.time()
        datestamp = str(datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
        voltage = voltageRead
        current = currentRead
        c.execute("INSERT INTO stuffToPlot (datestamp, voltage, current) VALUES (?, ?, ?)",
        (datestamp, voltage, current))
        conn.commit()

#while True:
#       voltageRead = serialArduino.readline()
#       currentRead = serialArduino.readline()
#       print("V=", voltageRead, "A=", currentRead)

while True:
        dynamic_data_entry()
        time.sleep(1)
        conn.commit()
c.close()
conn.close()

Arduino Code:

void setup() {
  Serial.begin(9600);
  }

void loop() {
  float voltageRead = analogRead(A0);
  float ampsRead = analogRead(A1);
  float calculatedVoltage = voltageRead / 103;
  float calculatedCurrent = ampsRead / 1;
  Serial.println(calculatedVoltage);
  Serial.println(calculatedCurrent);
  delay(1000);
}

My question is then. How can i differentiate these values so that the database becomes correct?

/J