MicroPython and PostgreSQL

All ESP32 boards running MicroPython.
Target audience: MicroPython users with an ESP32 board.
Post Reply
alexps
Posts: 2
Joined: Sun Nov 21, 2021 4:06 pm

MicroPython and PostgreSQL

Post by alexps » Sun Nov 21, 2021 4:35 pm

Hi guys.
I have a ESP32-DevKitC V4 board and my idea was to connect a temperature sensor to it and send measurements to Posgres batabase. But I faced an issue with the installation of Python packages that could help me with connection to Postgres. I've started with psycopg2, but it all ended with an error. Then I've tried to install several other packages, but ofr all of them it ends up with errors. :cry:
Maybe somebody could advice on the best way to write some data to Postgres with the board I have.
Thank you very much in advance.

alexps
Posts: 2
Joined: Sun Nov 21, 2021 4:06 pm

Re: MicroPython and PostgreSQL

Post by alexps » Fri Nov 26, 2021 8:11 pm

Or maybe anyone have an idea how to write some data to any other DB (for example MySQL) with ESP32?

User avatar
scruss
Posts: 360
Joined: Sat Aug 12, 2017 2:27 pm
Location: Toronto, Canada
Contact:

Re: MicroPython and PostgreSQL

Post by scruss » Fri Nov 26, 2021 9:52 pm

Typically you wouldn't connect directly to the database host, but would send messages containing the temperature readings via MQTT, and a broker on the host machine would convert these messages to database rows

User avatar
pythoncoder
Posts: 5956
Joined: Fri Jul 18, 2014 8:01 am
Location: UK
Contact:

Re: MicroPython and PostgreSQL

Post by pythoncoder » Sat Nov 27, 2021 10:50 am

Agreed. Another approach is micropython-iot which links a number of networked MicroPython clients to a server on a PC (or such as Raspberry Pi). This could connect to a database.
Peter Hinch
Index to my micropython libraries.

Lixas
Posts: 10
Joined: Fri Aug 21, 2020 9:09 am

Re: MicroPython and PostgreSQL

Post by Lixas » Thu Dec 02, 2021 11:44 am

You may want to look into https://github.com/mevdschee/php-crud-api yes, its a full blown API for databases. If you just need to insert data from MCU into Database- create quite simple PHP (or whatever language works for you) server side script. I have PHP scrip that accept parameters for temperature, humidity via GET, and on my MCU i just make request to http://example.com/insert.php?t=23.5&h=57

Another option, would be to install client on your ESP32 https://github.com/dvrhax/uPyMySQL but its very resource hungry. I've tried to play around with it. It works. But selecting data result size is very limited. Inserting only should works as expected. But you may need to mpy-cross some files or freeze in firmware to lower ram usage.

I would go with API solution, either full blown or custom (insert data only) but this involves additional infrastructure.
Also, for my personal need i've set up following http://bukys.eu/_media/project/smarthom ... tok=ce013e
where SmartHome central (Node-Red) read MQTT messages, filters what is required and write data into database. Proxy is the device, that read BLE broadcasts and send MQTT messages. Works stable, without major issues almost a year now

Post Reply