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.