SQL query on sqlite database
I like to query the sqlite database of pimatic to use the info in an other application.
But I don’t see how to connect to the database (the name of the database, port, username and password),
I searched for the info but i can’t find it. Where can I find this info?
Thanks in advance!
@lschip i believe it is a local file.
cd /pimatic-app
sudo sqlite3 pimatic-database.sqlite .tables
For example.
You could look into “phpLiteAdmin”?
I don’t understand this.
What I like to do is doing select and groupby sql statements. But for that I need to access the database fist.
Or isn’t this possible at all?
Thanks.
@lschip pimatic-database.sqlite is your database. It is in the pimatic-app folder.
I know where to find the pimatic database.
But how to access the info in it, with sql commands. Or is that not possible?
@lschip read my first post. That is how you access the sqlite database!
What kind of info do you want to extract from it?
there are many database tools.
maybe you should go for a gui guided one like phpmydamin
pimatic v0.9 has been released!
Support Pimatic and get some free stickers
Like us on Facebook
make it so !
@lschip said in SQL query on sqlite database:
I know where to find the pimatic database.
But how to access the info in it, with sql commands. Or is that not possible?
“Most SQL database engines are implemented as a separate server process. Programs that want to access the database communicate with the server using some kind of interprocess communication (typically TCP/IP) to send requests to the server and to receive back results. SQLite does not work this way. With SQLite, the process that wants to access the database reads and writes directly from the database files on disk. There is no intermediary server process.”
There is no server. Just connect with some software that understands sqlite directly to the mentoined file.
Like my projects and help? Consider donating electroneum etnjwAKGPqF6omQWRmpp9u2BPyVDG9VuyRQjNJ1S8yfBdfR9qeUQ46kRy8KS2CNqbpNLRrsgmNW6F2TMzxmZgPrh6KctrkrYbm
Is this a server app? If so, wouldn’t it just be easier to hit the REST API, grab everything, then filter object for the data you need?
And if it’s going to stay alive, just open a socket.io connection, then patch the filtered object with real-time updates. This way you’ll basically have a live query, that’s always in-sync.
i mention this, because imho, since an sqlite db is just a file that needs to be opened and read, having an entity other than the owner access it introduces needless risk (i.e. corruption, locking, etc).
Maybe its better to explain what I want to archief.
I want to make a wireless (esp8266, mysensors or propriety) pulse sender (for messuring gas, water etc) and put the data in a MySQL database. I don’t what use Domotics or something like that…
To make it easy I thought to receive the info with Pimatic (that is easy) where the data will be stored in the pimatic database for a limited period of time.
Then I can query the database, get the agregated info out of the pimatic database (select … group by…) and store this in the mysql database (which is serverd by another raspberry pi).
Then I can play around with the data from there (making all kind of graphs …).
The reason I want to do it this way is because the Pimatic raspberry has wifi, 433Mhz and 868Mhz receivers, the other Pi (with the mysql database) doesn’t.
And I also wouldn’t have any idea how to receive the info with the other Pi (with MySQL on it).
So querying the Pimatic database would solve this for me (I think)…
Any suggestion would be nice…
Thanks.
All what you want can you grab via REST API…
@Swen indeed @lschip, you could simply use the API, it is what its for. Simple http calls to get or write values to pimatic, and in the end, to the sql database…
The second Pi is connected to your LAN through wifi though, correct? As the others have mentioned, you can simply grab the info via an http call. Also, is there a reason to store the same information (albeit transformed) in a MySQL db on the second pi? MySQL is highly resource intensive and not a great idea to run on an rPi.
I put together a quick example (hopefully this is helpful to you, as well as others), that demonstrates how to obtain device information via the REST api using a nodejs. The devices param for the processJSON function is an array that can be manipulated (filtered, mapped etc…) as desired. From here, you can easily insert the data into a MySQL db or save it to a file, if you wish. However, you have endless other options using other node libraries.
