-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.
home/pi/ └SQLiteHTMLShow/ ├UDP_SQLite.py └ db/ └data.db
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.