Tag Archives: Python

sqlite.remote version 1.0

March 22, 2020

A while back I released some simple code to allow access to a SQLite database on my embedded devices. A python/html5 app that provided an HTML5 interface to a python api. My goal is a simple to use method that allows quick and easy access to  databases on raspberry pi (or similar) used for configuration and logging on my IoT devices. The requirements are minimal :

  1. User login via http auth
  2. The ability to change a user password.
  3. SSL via self signed certificates
  4. Basic database access via a browser based app

Today version 1.0 of the sqlite.remote tool is being released. Available via git hub, the app meets the goals specified above.

Some assembly is required — mainly in the form of generating self signed certificates for the devices and browsers that will be used to access them. Check out my previous post about securing local IoT devices.  Generate the certs that you need before installing the sqlite.remote tool.

Installation:

Since I use this on the Raspberry PI it is installed in the pi user’s home directory. Any system that supports python should work, as should any user.

First clone the git hub repository:

 git clone https://github.com/m2ag-labs/m2ag-sqlite-remote.git

Then cd to the install directory and chmod the installer script:

chmod +x install.sh

Then ./install.sh to install dependencies. This app requires flask and flask_httpauth. Depending on your current update status the dependencies may already be installed.

After the install is completed the install can be tested by changing to the project root and running:

python3  api.py sqlite-remote.sqlite

This should start the api server and show a  message like this in the console:

As you can see we are using the barebones development server to run our api. Despite the message to not use this in production, I am using this in production. My usage only calls for one or two users at a time infrequently hitting the api. I decided to trade a simple implementation for a more robust web server and more complicated install. Plus, the web server would generally sit idle for day to day use.

It should also be noted that the api is being served over https. This is requires a self signed certificate to be installed on the device. This is configured at the bottom of api.py:

Adjust the paths to the correct certificate files. The api can be run without ssl if desired. For me, I want to provide at least a minimal level of security to my IoT devices. I intend this to be a config and control interface, but the devices will generally be inside a controlled firewall. Self signed certs  and a basic login meets my needs.

As part of the install a service a file is and copied to the systemd system directory. If the default location (/home/pi/m2ag-sqlite-remote) is changed, change it in this file.  To enable the service:

sudo systemctl m2ag-sqlite-remote enable
and 
sudo systemctl m2ag-sqlite-remote start

After the api is running navigate to your device at:

https://your-device.local:5001  to get the app page:

The default user is “pi” and the password is “raspberry”. These can be set in the credentials popup:

Select close to get back to query screen. If the query button is pressed with an empty query an “Ok” message will be returned. This tells us the configuration is correct:

To change the password use e the credentials popup:

‘Ok’ will appear in the status on a successful change.

To add new users first select * from users and get the hash for pi’s password and then insert a user using the same password hash. You can then use the new user’s credentials and change the password with the set password dialog.

General usage:

This app should handle most updates and edits to tables. Since the services setup specifies the database at startup additional tables to be added to the sqlite-remote.sqlite database. The only requirement for the app is the user table with a text username and password fields. This table could be added to any database. Just insert the pi user (or any other user) into the user tables via the command line app or some other tool.

It is hoped that you find this app useful, please feel free to open issues on github or comment here if there are problems with the app. Keep in mind it is barebones on purpose, all I need it to do is update tables remotely for me. If more complex usage is envisioned it may be modifications will need to be made.

If you find this work helpful perhaps you would consider supporting m2ag.labs open source efforts by buying us coffee. Any amount would be appreciated. Please use this link to do so:

Small Donate Button

Thanks for stopping by.

Control an Arduino from Python with PyCmdMessenger

March 6, 2020

There are many methods that can be used to provide communication with a host computer and an Arduino device. ArduinoCmdMessenger is a stable and relatively easy to use choice. It enables simple messages to be sent to the Arduino to control actions and return data.

Command Messenger itself can be installed via the Arduino Library Manager. The latest is version 4.0. It comes with examples in both C# and Visual Basic. My requirement is Python, so the PyCmdMessenger will be used. PyCmdMessenger can be installed via pip3. 

Both the projects have not had many updates in the recent years, but I find in combination they address the needs of my projects.

The problem:

I want to use Raspberry PI for IoT work. I want to use an Arduino to run some sensors that the PI will poll and publish.

True, the PI can handle most sensors and do the work for me. But in this particular case I have one of Adafruit’s CCS811  air quality sensors. The only way to get the sensor to work directly on the Raspberry PI is to slow the i2c bus speed down as described here -> https://learn.adafruit.com/adafruit-ccs811-air-quality-sensor/raspberry-pi-wiring-test .

I don’t really want to slow the i2c bus down on the PI as I have it busy with some other sensors. Plus — the implementation I have in mind is the CCS811 and a BME280 comprise an add on module for other projects. Using an Arduino Leonardo clone, the DF Robot Beetle, I can treat the implementation as a plug and play add on for other projects such as our up coming Raspberry Alarm Clock.

There are other options like Firmata. But I was not looking to couple the Arduino code so closely. I could roll my own implementation, but I’d rather use something that is already available. I  just want to be able to reliably pass commands and data back and forth asyhronously.  PyCmdMessenger provides a solution for this.

An example project can be found in the m2ag.labs examples repo. The code is documented and can be modified to use alternate sensors.

Prototype Sensor module.
Prototype Beetle sensor module. There is plenty of unused capacity on the Beetle that could be put to use.

Links:

https://github.com/thijse/Arduino-CmdMessenger

https://github.com/harmsm/PyCmdMessenger