Gather rows under the same key in Cassandra/Python
<p>I was working through the <a href="https://academy.datastax.com/resources/getting-started-time-series-data-modeling" rel="nofollow">Pattern 1</a> 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.</p>

<pre><code>CREATE TABLE temperature (
                 weatherstation_id text,
                 event_time timestamp,
                 temperature text,
                 PRIMARY KEY (weatherstation_id,event_time)
                 );
</code></pre>

<p>Insert some data</p>

<pre><code>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');
</code></pre>

<p>Query the database.</p>

<pre><code>SELECT weatherstation_id,event_time,temperature
                 FROM temperature
                 WHERE weatherstation_id='1234ABCD';
</code></pre>

<p>Result:</p>

<pre><code> 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
</code></pre>

<p>Which works, but I was wondering if I can turn this into a row per <code>weatherstationid</code>.</p>

<p>E.g.</p>

<pre><code>{
 "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"
}
</code></pre>

<p>Is there some parameter in cassandra driver that can be specified to gather by certain id (<code>weatherstationid</code>) 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)?</p>

<p>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. </p>

<p>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.</p>

python cassandra