CODEWITHSUNDEEP
pythoncassandra
Alex
Alex

Reputation: 1345

Gather rows under the same key in Cassandra/Python

I was working through the Pattern 1 and was wondering if there is a way, in python or otherwise to "gather" all the rows with the same id into a row with dictionaries.

CREATE TABLE temperature (
                 weatherstation_id text,
                 event_time timestamp,
                 temperature text,
                 PRIMARY KEY (weatherstation_id,event_time)
                 );

Insert some data

INSERT INTO temperature(weatherstation_id,event_time,temperature)
                 VALUES ('1234ABCD','2013-04-03 07:01:00','72F');
INSERT INTO temperature(weatherstation_id,event_time,temperature)
                 VALUES ('1234ABCD','2013-04-03 07:02:00','73F');
INSERT INTO temperature(weatherstation_id,event_time,temperature)
                 VALUES ('1234ABCD','2013-04-03 07:03:00','73F');
INSERT INTO temperature(weatherstation_id,event_time,temperature)
                 VALUES ('1234ABCD','2013-04-03 07:04:00','74F');

Query the database.

SELECT weatherstation_id,event_time,temperature
                 FROM temperature
                 WHERE weatherstation_id='1234ABCD';

Result:

 weatherstation_id | event_time               | temperature
-------------------+--------------------------+-------------
          1234ABCD | 2013-04-03 06:01:00+0000 |         72F
          1234ABCD | 2013-04-03 06:02:00+0000 |         73F
          1234ABCD | 2013-04-03 06:03:00+0000 |         73F
          1234ABCD | 2013-04-03 06:04:00+0000 |         74F

Which works, but I was wondering if I can turn this into a row per weatherstationid.

E.g.

{
 "weatherstationid": "1234ABCD", 
 "2013-04-03 06:01:00+0000": "72F", 
 "2013-04-03 06:02:00+0000": "73F", 
 "2013-04-03 06:03:00+0000": "73F", 
 "2013-04-03 06:04:00+0000": "74F"
}

Is there some parameter in cassandra driver that can be specified to gather by certain id (weatherstationid) and turn everything else into dictionary? Or is there needs to be some Python magic to turn list of rows into single row per id (or set of IDs)?

Upvotes: 0

Views: 54

Answers (1)

Alan Boudreault
Alan Boudreault

Reputation: 329

Alex, you will have to do some post execution data processing to get this format. The driver returns row by row, no matter what row_factory you use.

One of the reasons the driver cannot accomplish the format you suggest is that there is pagination involved internally (default fetch_size is 5000). So the results generated your way could potentially be partial or incomplete. Additionally, this can be easily be done with Python when the query execution is done and you are sure that all required results are fetched.

Upvotes: 1

Related Questions