Python SQL generate list of values

Question

Hi guys got a SQL stmt in python with an output of values from measurements. Each Measure has a unique id and timestamp. My output at the moment looks like this. Pythoncode for the Output:

arrayvals = []
    for row in result:
        arrayvals.append({
            'request_ts': row[0],
            'identifier': row[1],
            'ts': row[2],
            'value': row[5],
            })
   
    return (arrayvals)

OutPut:

{
    "result_array": [
        {
            "identifier": "1",
            "request_ts": "Mon, 22 Feb 2021 08:43:03 GMT",
            "ts": "Wed, 17 Feb 2021 12:12:00 GMT",
            "value": 17.1
        },
        {
            "identifier": "1",
            "request_ts": "Mon, 22 Feb 2021 08:43:03 GMT",
            "ts": "Wed, 17 Feb 2021 12:12:00 GMT",
            "value": 18.0
        },
....

What i want is for every id only 1 output with the values in a list. I have tried to relize this with fetchall() but now it get all outputs with all values like this: Pyhton code:

result2 = result.fetchall()
for row in result:
        arrayvals.append({
            'request_ts': row[0],
            'identifier': row[1],
            'ts': row[2],
            'value': [i[5] for i in result2],
            })

Output:

"result_array": [
        {
            "identifier": "01",
            "request_ts": "Mon, 22 Feb 2021 08:46:48 GMT",
            "ts": "Wed, 17 Feb 2021 12:12:00 GMT",
            "value": [
                17.1,
                18.0,
                2005.0,
                17000.0,
                1234.0
            ]
        }
]

But i want it like this:

"result_array": [
        {
            "identifier": "01",
            "request_ts": "Mon, 22 Feb 2021 08:46:48 GMT",
            "ts": "Wed, 17 Feb 2021 12:12:00 GMT",
            "value": [
                17.1,
                18.0
            ]
        }
{
            "identifier": "02",
            "request_ts": "Mon, 22 Feb 2021 08:46:48 GMT",
            "ts": "Wed, 17 Feb 2021 12:12:00 GMT",
            "value": [
                2005.0,
                17000.0,
                1234.0
            ]
        }
]

Is it possible to "combine" the id´s, to generate the last output? Nearly forgot my SQL looks like this:

SELECT
    NOW() AS request_ts,
    I.name AS identifier,
    AI.ts,
    AD.array_info_id,
    AD.index,
    AD.value AS measures
FROM
    machine M
INNER JOIN identifier I
    ON
    I.machine_id = M.id
INNER JOIN PDA_ArrayInfo AI
    ON
    I.id = AI.identifier_id
INNER JOIN PDA_ArrayData AD
    ON
    AD.array_info_id = AI.id
WHERE
    M.serial = :machine_serial_arg

From the answer of Masklinn:

for request_ts, identifier, ts, array_id, index, measures in result2.fetchall():
        vals = vals_dict.get(array_id)
        if vals is None:
            # add record to index
            values = vals_dict[array_id] = {
                'request_ts': request_ts,
                'identifier': identifier,
                'ts': ts,
                'value': [measures],
                }
            # adds the same record to the relust list
            arrayvals.append(values)
        else:
            # look up the record in the index
            vals['value'].append(measures)
return(values)

changed 2 things: 'value': [measures], vals['value'].append(measures)

now its working Thank you all

What is your underlying DBMS? (PostgreSQL, SQLIite, ???)

Nick
– Nick

2021-02-22 08:03:33 +00:00
Commented Feb 22, 2021 at 8:03 — Nick
– Nick, Commented Feb 22, 2021 at 8:03

Masklinn · Accepted Answer · 2021-02-22 08:00:45Z

2

Is it possible to "combine" the id´s, to generate the last output?

The best option would be to fix your sql statement to produce that to start with. Since you don't show the SQL, it's difficult to help there.

The second best option would be to store the data as a mapping of identifier to data, this way you can use the get method to check if there's already data for this identifier, if there isn't you create a new one, otherwise you just append to the existing value array:

for req_ts, ident, ts, _, _, values in result.fetchall():
    vals = vals_dict.get(ident)
    if vals is None:
        vals_dict[ident] = {
            'request_ts': req_ts,
            'identifier': ident,
            'ts': ts,
            'value': values,
        }
    else:
        vals['value'].extend(values)

The third best option, if the dict is impossible, is to... do the same with the list, look up if there's already a record added to the list with the id.

I'd still recommend using a dict as index if the number of entries in the list can be significant: looking up a record in a dict is an O(1) operation, in a list it's O(n), leading to quadratic behaviour. Not an issue if you only have a dozen entries, a huge problem if you have thousands.

for req_ts, ident, ts, _, _, values in result.fetchall():
    vals = vals_dict.get(ident)
    if vals is None:
        # adds the record to the index
        values = vals_dict[ident] = {
            'request_ts': req_ts,
            'identifier': ident,
            'ts': ts,
            'value': values,
        }
        # adds the same record to the results list
        arrayvals.append(values)
    else:
        # just look up the record in the index
        vals['value'].extend(values)

answered Feb 22, 2021 at 8:00

Masklinn

43.7k4 gold badges58 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

s0Nic Over a year ago

Thank you very much for your help. tried I still do not quite understand the code. does this come in addition to the other code? I am relatively new to python sorry.

Masklinn Over a year ago

It largely comes instead of the code you posted.

s0Nic Over a year ago

Ok Thank you this is working without the extend i got an error AttributeError: 'float' object has no attribute 'extend' trying to solve this and the code is perfectly working

Collectives™ on Stack Overflow

Python SQL generate list of values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related