Reading values from database

[Hobbybob]

I have a couple of counters for my power- and gas-usage. These are devices of a dummy-hardware, and I fill the values using calls to the JSON-URL from an Arduino that reads my P1-port (RPi is not located in the meter cupboard).

When I view the log I get these nice graphs, and I can download the inidivdual values that make the graph up as CSV or something else.

Now is my question: is it possible to read these individual values from the database? So I'm not talking about the last value, but a range of them (e.g. all values of today).

I'll be using it in a cron, so bash or python would work the best, but PHP is also a possibility. My problem is accessing the database through a script.

Suggestions?

[SweetPants]

It's a Sqlite database, so there are plenty of examples on the net for Perl, Python to access it.

[Hobbybob]

It's a Sqlite database, so there are plenty of examples on the net for Perl, Python to access it.

Okay, I have worked with PHP, but never with Perl or Python before. I'll try to gather something together!

[SweetPants]

If you google for "Php Sqlite" there is plenty of information available to access the database with Php http://php.net/manual/en/book.sqlite.php

[Egregius]

Use php to read the sqlite database. Once you have the data in a array you can do whatever you want with it.
Maybe even better for the future: let php store the raw data in a mysql database, at least that's what I do for some thermometers.

[Hobbybob]

Okay, thanks SweetPants and Egregius!

2 more questions though: Where do I put the PHP-file? I tried putting it in /home/pi/domoticz/www and requesting it through the browser (192.168.0.35:8080/test.php), but all I got was a "Domoticz Offline"-page (without the test.php appended it responds normally).
Second question: I seem to need the nme of the database to connect to it. Is that simply "domoticz.db", like the file I can download through the backup-button?

[Egregius]

You'll need a PHP enabled webserver like apache, lighttpd, nginx,...
Will post some code tomorrow as example.

[Hobbybob]

You'll need a PHP enabled webserver like apache, lighttpd, nginx,...
Will post some code tomorrow as example.

Okay, I'll wait for your reply then, thanks for the effort!
I was under the impression that PHP could be used as standard within the www-folder of Domoticz, don't know where I got that idea though...

[Egregius]

I have these PHP packages installed on my RPi:

Code: Select all

php7.0 php7.0-cgi php7.0-common php7.0-fpm php7.0-json php7.0-mysql php7.0-phpdbg php7.0-sqlite3 php-apcu php-common 
php7.0-bcmath php7.0-cli php7.0-curl php7.0-gmp php7.0-mbstring php7.0-opcache php7.0-readline php7.0-xml php-cgi 

You need at least php7.0-sqlite3 to enable reading from a SQL lite 3 database and the user that runs the script (or the user that runs the webserver) needs full read/write/execute permissions to the folder and file where the domoticz.db database is stored.

Then, to select some stuff (adjust the path to database, mine is stored in tmpfs):

Code: Select all

<?php
$db=new SQLite3('/var/log/domoticz.db');
$results=$db->query('SELECT Temperature, Date FROM Temperature WHERE DeviceRowID = 442');
while($row=$results->fetchArray()){
	print_r($row);
	echo '<br>';
}

[Hobbybob]

I have these PHP packages installed on my RPi:

Code: Select all

php7.0 php7.0-cgi php7.0-common php7.0-fpm php7.0-json php7.0-mysql php7.0-phpdbg php7.0-sqlite3 php-apcu php-common 
php7.0-bcmath php7.0-cli php7.0-curl php7.0-gmp php7.0-mbstring php7.0-opcache php7.0-readline php7.0-xml php-cgi 

You need at least php7.0-sqlite3 to enable reading from a SQL lite 3 database and the user that runs the script (or the user that runs the webserver) needs full read/write/execute permissions to the folder and file where the domoticz.db database is stored.

Then, to select some stuff (adjust the path to database, mine is stored in tmpfs):

Code: Select all

<?php
$db=new SQLite3('/var/log/domoticz.db');
$results=$db->query('SELECT Temperature, Date FROM Temperature WHERE DeviceRowID = 442');
while($row=$results->fetchArray()){
	print_r($row);
	echo '<br>';
}

Thanks a million! I have my domoticz.db in /home/pi/domoticz/domoticz.db, not in /var/log, but I got enough info here to get it working I think!

[Egregius]

If not: ask