How to create and update a SQL database with real-time data received from a python socket?-CodePudding

Data Source

I am receiving data from a driving simulator in real-time. The following is a socket program that receives the data from the simulator and then prints it:

import socket
import struct

UDP_IP = "127.0.0.1"
UDP_PORT = 4001

sock = socket.socket(socket.AF_INET, # Internet
                     socket.SOCK_DGRAM) # UDP
sock.bind((UDP_IP, UDP_PORT))

while True:
    data, addr = sock.recvfrom(1024) # buffer size is 1024 bytes
    fields = struct.unpack_from('=ddd', data)
    print(fields[0],fields[1],fields[2])

Output

The printed data appear as follows in a shell:

What I want to do

I want to store these data in a database. Later, I want to use the database in an application. My question is: how do I create a database that can store and save these real-time data? I have no prior knowledge of creating databases. Please guide me about the relevant resources.

What I have tried:

I first created a database in the same location where I have the socket script (described above):

Database creation:

 import sqlite3

conn = sqlite3.connect('test_database')
c = conn.cursor()

c.execute('''
          CREATE TABLE IF NOT EXISTS position
          ([y] REAL PRIMARY KEY, [x] REAL, [z] REAL)
          ''')
         
         
conn.commit()

Attempt to update the database with the real-time data:

Then I modified my script file to store the data in a database as follows:

import socket
import struct
import sqlite3

conn = sqlite3.connect('test_database')
c = conn.cursor()

UDP_IP = "127.0.0.1"
UDP_PORT = 4001

sock = socket.socket(socket.AF_INET, # Internet
                     socket.SOCK_DGRAM) # UDP
sock.bind((UDP_IP, UDP_PORT))

while True:
    data, addr = sock.recvfrom(1024) # buffer size is 1024 bytes
    fields = struct.unpack_from('=ddd', data)
    #print("received message:", data)
    print(fields[0],fields[1],fields[2])
    #sock.sendto(fields.encode(), ("127.0.0.1",7070))
     
    c.execute('''
              INSERT INTO position (y, x, z)

                    VALUES
                    (fields[0],fields[1], fields[2])
              ''')
    conn.commit()

Error:

But I get the following error:

2315.259850934807 35025.972653539255 -1.7375892216255708
Traceback (most recent call last):
  File "C:\NadsMiniSim_2.3\bin.x64\testIP-UDP3.py", line 22, in <module>
    c.execute('''
sqlite3.OperationalError: near "[0]": syntax error

I am not sure what went wrong. Please guide me.

CodePudding user response：

The correct syntax to insert data to an sqlite table using placeholders is

c.execute(
    'INSERT INTO position (y, x, z) VALUES (?, ?, ?)',
    (fields[0], fields[1], fields[2]),
)

i.e. use ? instead of a value, then pass that many values as a tuple afterwards.

The other option is to use named placeholders:

c.execute(
    'INSERT INTO position (y, x, z) VALUES (:y, :x, :z)',
    {'y': fields[0], 'x': fields[1], 'z': fields[2]},
)