Grabbing comma separed values from SQLite and putting them in a list

[PythonNPC]

Hello,

I am really confused about this.

Let's say I have a database and one cell contains the following:

334.0,1698.5,26.17,2323.0

I want to convert it into a list like

[334.0, 1698.5, 26.17, 2323.0]

How can this be achieved?

[buran]

I understand one cell as single value in table/column/row
split the string at commas and cast elements to float/Decimal

And why don't fix the DB and have separate fields?

[PythonNPC]

I understand one cell as single value in table/column/row
split the string at commas and cast elements to float/Decimal

And why don't fix the DB and have separate fields?

I thought about fixing the DB, but there can be variable number of float values. I thought about NoSQL database but the application is going to be running on a Raspberry Pi, so I would rather have something lightweight like SQLite.

[buran]

I thought about fixing the DB, but there's can be variable number of floats.

In a relational database, you would have separate table with one-to-many relationship. Each value will be in separate row. And you will have a field that will link each of them to respective record in the main table.
Of course you can have everything in one table, again each value on separate row, but in this case you will repeat all other fields in the record, which is not advisable


In the current setup, as I already explained, you can split the string at commas and cast each element to int

[PythonNPC]

I thought about fixing the DB, but there's can be variable number of floats.

In a relational database, you would have separate table with one-to-many relationship. Each value will be in separate row. And you will have a field that will link each of them to respective record in the main table.
Of course you can have everything in one table, again each value on separate row, but in this case you will repeat all other fields in the record, which is not advisable


In the current setup, as I already explained, you can split the string at commas and cast each element to int

Yes, I have given a thought to that approach as well.

According to you, what would be the better approach? Comma separated values or a separate table?

The end goal is stability and less load on the Raspberry Pi because the Raspberry Pi will be running on a battery and want it to last long by reducing load on the processor.

[buran]

First of all - I don't have information about the whole picture, but even based on the information you have provided so far, I would go for separate table. It's the proper DB design.
As to the performance/cost of INSERT vs. UPDATE (I assume you will either insert new records or update existing by concatenating new values to existing string, right?) - it's really a tricky question and depends on many factors, so I definitely cannot give a definitive answer. Probably you can run comparison test. I would expect that inserts in separate table would be less CPU intensive, though.

[PythonNPC]

First of all - I don't have information about the whole picture, but even based on the information you have provided so far, I would go for separate table. It's the proper DB design.
As to the performance/cost of INSERT vs. UPDATE (I assume you will either insert new records or update existing by concatenating new values to existing string, right?) - it's really a tricky question and depends on many factors, so I definitely cannot give a definitive answer. Probably you can run comparison test. I would expect that inserts in separate table would be less CPU intensive, though.

Thank you. I will go for a separate table as it is the correct way to design a database.

The insert and update queries will likely be more CPU intensive than separating a string, but, it's not like I am storing thousands of values. 10 at the most. So I may lose like 5 minutes of battery life out of a total of three hours. So that's fine.

[buran]

I will go for a separate database

Not DB, but table in the same database

The insert and update queries will likely be more CPU intensive than separating a string,

I am confused. My understanding is that you will write to db continuously, adding new values from some source . That is why I compare INSERT and UPDATE statements. Also, the update, suggest you must keep the current values or retrieve them before update.
If your data are constant, i.e. preloaded and you just run SELECT queries, than if values are in separate table, you don't need to split the string, so it should be cheaper in terms of CPU resource.

Can you elaborate on the overall project and what you are doing?

[buran]

Here are 2 examples for mock use case
using separate tables:

import random
import sqlite3

def measurments(num):
    sensors = ('Sensor1', 'Sensor2', 'Sensor3')
    for _ in range(num):
        yield (random.choice(sensors), random.randint(30, 100))


class Sensor:
    def __init__(self, sensor_id, sensor_type, sensor_brand, values=None):
        self.id = sensor_id
        self.type = sensor_type
        self.brand = sensor_brand
        self.values = values or []

    def add_value(self, value):
        self.values.append(value)

    
    def __str__(self):
        return (f"{self.id}, {self.type}, {self.brand}, values:{self.values}")


def create(db):
    with sqlite3.connect(db) as conn:
        conn.executescript(
"""DROP TABLE IF EXISTS "measurments";
DROP TABLE IF EXISTS "sensors";
CREATE TABLE "measurments" (
	`SensorID`	TEXT NOT NULL,
	`Value`	INTEGER NOT NULL
);
CREATE TABLE "sensors" (
	`SensorID`	TEXT NOT NULL,
	`SensorType`	TEXT NOT NULL,
	`SensorBrand`	TEXT NOT NULL
);
INSERT INTO `sensors` VALUES ('Sensor1','Temperature Sensor','Brand1'),
 ('Sensor2','Humidity Sensor','Brand2'),
 ('Sensor3','Temperature Sensor','Brand2');""")
        conn.commit()


def read_data(db):
    with sqlite3.connect(db) as conn:
        # retrieve all data. This could be defined as view in the db, so that it is simpel in the code
        data = conn.execute("""SELECT sen.SensorID, sen.SensorType, sen.SensorBrand, mes.Value 
                            FROM sensors as sen
                            INNER JOIN measurments as mes
                            ON sen.SensorID = mes.SensorID
                            ORDER BY mes.Rowid""")

        sensors = {}
        for sensor_id, sensor_type, sensor_brand, value in data:
            sensor = sensors.setdefault(sensor_id, Sensor(sensor_id, sensor_type, sensor_brand))
            sensor.add_value(value)
    return sensors


def write_data(db):
    with sqlite3.connect(db) as conn:
        # take measurments and write data to db:
        for measurment in measurments(10): # simulate 10 random observations
            conn.execute("""INSERT INTO measurments VALUES (?, ?);""", measurment)
            conn.commit()


if __name__ == '__main__':
    db = 'sample2.db'
    create(db)
    write_data(db)
    for sensor_id, sensor in read_data(db).items():
        print(sensor)

    # you can do simply
    with sqlite3.connect(db) as conn:
        data = conn.execute("""SELECT value FROM measurments where SensorID='Sensor1';""")
        values = [item for (item, ) in data]
        print(values)

or using one table, with values comma-separated:

import random
import sqlite3

def measurments(num):
    sensors = ('Sensor1', 'Sensor2', 'Sensor3')
    for _ in range(num):
        yield (random.choice(sensors), random.randint(30, 100))

class Sensor:
    def __init__(self, sensor_id, sensor_type, sensor_brand, values=None):
        self.id = sensor_id
        self.type = sensor_type
        self.brand = sensor_brand
        if values is None:
            self.values = []
        else:
            self.values = list(map(int, values.split(',')))

    def add_value(self, value):
        self.values.append(value)

    
    def __str__(self):
        return (f"{self.id}, {self.type}, {self.brand}, values:{self.values}")


# your way, sample1.db

def create(db):
    with sqlite3.connect(db) as conn:
        conn.executescript(
"""DROP TABLE IF EXISTS "sensors";
CREATE TABLE "sensors" (
	`SensorID`	TEXT NOT NULL,
	`SensorType`	TEXT NOT NULL,
	`SensorBrand`	TEXT NOT NULL,
	`Measurments`	TEXT
);
INSERT INTO `sensors` (SensorID,SensorType,SensorBrand, Measurments) VALUES ('Sensor1','Temperature Sensor','Brand1',NULL),
 ('Sensor2','Humidity Sensor','Brand2',NULL),
 ('Sensor3','Temperature Sensor','Brand2',NULL);""")
        conn.commit()

def read_data(db):
    with sqlite3.connect(db) as conn:
    
        # retrieve initial data
        data = conn.execute('SELECT * from sensors;')
        sensors = {}
        for record in data:
            sensor = Sensor(*record)
            sensors[sensor.id] = sensor
    return sensors

def write_data(db):
    with sqlite3.connect(db) as conn:
        # take measurments and write data to db:
        sensors = read_data(db)
        for sensor_id, value in measurments(10): # simulate 10 random observations
            sensor = sensors[sensor_id]
            sensor.add_value(value)
            conn.execute("""UPDATE sensors SET Measurments = ? WHERE `SensorID` = ?;""", (','.join(map(str, sensor.values)), sensor_id))
            conn.commit()


if __name__ == '__main__':
    db = 'sample1.db'
    create(db)
    write_data(db)
    for sensor_id, sensor in read_data(db).items():
        print(sensor)

    with sqlite3.connect(db) as conn:
        data = conn.execute("""SELECT Measurments FROM sensors WHERE SensorID='Sensor1';""")
        values = [int(value) for value in data.fetchone()[0].split(',')]
        print(values)

Note that this is sample implementation, so there might be different ways to do the same.
The setup is - having 3 Sensors, and we collect data from them to write to sqlite database