Creating a temperature control system with Raspberry Pi and ESP32 (3) Recipient Python file

specification

Register the ESP32 data sent to the Raspberry Pi in SQLite

-Execute UDP received python file at startup -Install Apache on Raspberry Pi-Enable SQLite -Create php file. Make it visible in the browser

I've just learned php, so I'm planning to make something horribly simple.

file organization

home/pi/   └SQLiteHTMLShow/      ├UDP_SQLite.py      └ db/        └data.db

UDP reception → SQLite registration

Create a python file that receives UDP and registers data in SQLite.

python UDP_SQLite.py



# -*- coding: utf-8 -*-
import socket #UDP transmission
import struct #For number → byte string conversion
from contextlib import closing #For with

import sqlite3
import time
import datetime

import sys

UDP_IP = "" #It's fine like that
UDP_PORT = 1234 #Port number to receive
sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM) #Socket generation

sock.bind((UDP_IP, UDP_PORT)) #Register the socket
#Specify database
dbname = '/home/pi/SQLiteHTMLShow/db/data.db'

with closing(sock): #Automatically close socket when program exits
    
  while True: #infinite loop
    try:
      data, addr = sock.recvfrom(1024) #Receive
      
      #print ( data ) #Display byte string as it is
      #print(addr[0]) #IP address display
      #print(addr[1]) #Display of source port
      
      #The name of the table in the database
      ESP32 =addr[0].split('.')
      print('dbtable = ' + 'ip' + str(ESP32[2] + ESP32[3]))#IP address 192.Use 168 or less as the table name
      dbtable = 'ip' + str(ESP32[2] + ESP32[3])
      
      #Connect to SQLite
      conn = sqlite3.connect(dbname)
      c = conn.cursor()
   
      #Check if there is a table in SQLite
      checkdb = conn.execute("SELECT * FROM sqlite_master WHERE type='table' and name='%s'" % dbtable)

      #If there is no table, create a new table
      if checkdb.fetchone() == None:
          create_table = '''create table ''' + dbtable + '''(id integer primary key autoincrement, time text,
                    ip text, port integer, temp text, humi text)'''
          print(create_table)

          c.execute(create_table)#Run
          conn.commit()

      #Save temperature, humidity and time stamps.
      sql = 'insert into ' + dbtable + '(time,ip,port,temp,humi) values (?,?,?,?,?)'
      
      todaydetail = datetime.datetime.today()
      time = todaydetail.strftime("%Y/%m/%d %H:%M:%S")
      ip = addr[0]
      port=addr[1]

      print(str(data))
      #Decompose the device number, temperature, and humidity strings that are sent together
      val=str(data).replace('b','').replace("'",'').split(',')
      
      print(float(val[0]),float(val[1]))
      
      data= (time,ip,port,float(val[0]),float(val[1]))
      c.execute(sql, data)
      conn.commit()
      #Disconnect
      conn.close()
    except:
      print(sys.exc_info())

With this, the IP address specified in ESP32 becomes the table name as it is and is registered in SQLite.

If the IP addresses of ESP32 are individually set, a separate table will be created.

I think that when I use it for work, it will occupy the company's IP address widely, so I will use only one IP address.

Recommended Posts

Creating a temperature control system with Raspberry Pi and ESP32 (3) Recipient Python file
Production of temperature control system with Raspberry Pi and ESP32 (1)
Production of temperature control system with Raspberry Pi and ESP32 (2) Production of transmission device
Creating a temperature / humidity monitor with Raspberry Pi (pigpio version)
Creating a simple PowerPoint file with Python
Control the motor with a motor driver using python on Raspberry Pi 3!
[Python + PHP] Make a temperature / humidity / barometric pressure monitor with Raspberry Pi
Get temperature and humidity with DHT11 and Raspberry Pi
Measure CPU temperature of Raspberry Pi with Python
Record temperature and humidity with systemd on Raspberry Pi
Measure temperature and humidity with Raspberry Pi3 and visualize with Ambient
Ubuntu 20.04 on raspberry pi 4 with OpenCV and use with python
Install pyenv on Raspberry Pi and version control Python
Control music playback on a smartphone connected to Raspberry Pi 3 and bluetooth with AVRCP
Create a web surveillance camera with Raspberry Pi and OpenCV
Python beginner opens and closes interlocking camera with Raspberry Pi
Create an LCD (16x2) game with Raspberry Pi and Python
Measure and compare temperature with Raspberry Pi and automatically generate graph
getrpimodel: Recognize Raspberry Pi model (A, B, B +, B2, B3, etc) with python
Easily make a TweetBot that notifies you of temperature and humidity with Raspberry Pi + DHT11.
[Raspberry Pi] Stepping motor control with Raspberry Pi
Servo motor control with Raspberry Pi
Make a recommender system with python
Using a webcam with Raspberry Pi
Christmas classic (?) Lighting a Christmas tree with Raspberry Pi and Philips Hue
Make a thermometer with Raspberry Pi and make it viewable with a browser Part 4
Make a Kanji display compass with Raspberry Pi and Sense Hat
[Note] Using 16x2-digit character LCD (1602A) from Python with Raspberry Pi
How to upload a file to Cloud Storage using Python [Make a fixed point camera with Raspberry PI # 1]
Building a distributed environment with the Raspberry PI series (Part 2: PiServer analysis and alternative system design)
Exclusive control with lock file in Python
Read and format a csv file mixed with comma tabs with Python pandas
[Raspberry Pi] Add a thermometer and a hygrometer
Detect analog signals with A / D converter using python on Raspberry Pi 3!
Build a Tensorflow environment with Raspberry Pi [2020]
Use python on Raspberry Pi 3 to light the LED with switch control!
A memo with Python2.7 and Python3 on CentOS
Make a wireless LAN Ethernet converter and simple router with Raspberry Pi
I tried to make a traffic light-like with Raspberry Pi 4 (Python edition)
RabbitMQ message notification app in Python with Growl ~ with Raspberry Pi and Julius ~
I connected the thermo sensor to the Raspberry Pi and measured the temperature (Python)
Make a wash-drying timer with a Raspberry Pi
Operate an oscilloscope with a Raspberry Pi
Detect temperature using python on Raspberry Pi 3!
Create a car meter with raspberry pi
I made a configuration file with Python
Working with GPS on Raspberry Pi 3 Python
Source compile Apache2.4 + PHP7.4 with Raspberry Pi and build a Web server --2 PHP introduction
Cross-compiling Raspberry Pi and building a remote debugging development environment with VS Code
Create a simple reception system with the Python serverless framework Chalice and Twilio
Source compile Apache2.4 + PHP7.4 with Raspberry Pi and build a Web server ―― 1. Apache introduction
[For beginners] I made a motion sensor with Raspberry Pi and notified LINE!
Using the 1-Wire Digital Temperature Sensor DS18B20 from Python on a Raspberry Pi
Build a distributed environment with Raspberry PI series (Part 3: Install and configure dnsmasq)
Use Raspberry Pi Python to TMP36 analog temperature sensor and MCP3008 AD converter
Raspberry Pi 3 x Julius (reading file and grammar file)
How to read a CSV file with Python 2/3
Discord bot with python raspberry pi zero with [Notes]
Building a python environment with virtualenv and direnv
Procedure for creating a LineBot made with Python
I tried L-Chika with Raspberry Pi 4 (Python edition)