Python Forum
Grabbing comma separed values from SQLite and putting them in a list
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Grabbing comma separed values from SQLite and putting them in a list
#1
Hello,

I am really confused about this.

Let's say I have a database and one cell contains the following:
Quote: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?
Reply
#2
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?
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
(Apr-08-2020, 02:16 PM)buran Wrote: 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.
Reply
#4
(Apr-10-2020, 06:28 AM)PythonNPC Wrote: 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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#5
(Apr-10-2020, 06:37 AM)buran Wrote:
(Apr-10-2020, 06:28 AM)PythonNPC Wrote: 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.
Reply
#6
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.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#7
(Apr-10-2020, 07:52 AM)buran Wrote: 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.
Reply
#8
(Apr-10-2020, 09:29 AM)PythonNPC Wrote: I will go for a separate database
Not DB, but table in the same database

(Apr-10-2020, 09:29 AM)PythonNPC Wrote: 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?
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#9
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Copying the order of another list with identical values gohanhango 7 1,060 Nov-29-2023, 09:17 PM
Last Post: Pedroski55
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Comparing List values to get indexes Edward_ 7 1,081 Jun-09-2023, 04:57 PM
Last Post: deanhystad
  Adding values with reduce() function from the list of tuples kinimod 10 2,512 Jan-24-2023, 08:22 AM
Last Post: perfringo
  user input values into list of lists tauros73 3 1,023 Dec-29-2022, 05:54 PM
Last Post: deanhystad
  [SOLVED] [BeautifulSoup] Turn select() into comma-separated string? Winfried 0 1,086 Aug-19-2022, 08:07 PM
Last Post: Winfried
  How to format Excel column with comma? dee 0 1,335 Jun-13-2022, 10:11 PM
Last Post: dee
  Help with subtracting values using SQLite & Python Extra 10 3,286 May-10-2022, 08:36 AM
Last Post: ibreeden
  decimal comma DPaul 9 2,215 Feb-22-2022, 12:25 PM
Last Post: DeaD_EyE
  AttributeError: 'list' object has no attribute 'values' ilknurg 4 14,770 Jan-19-2022, 08:33 AM
Last Post: menator01

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020