Converting database query results from strings to tuples efficiently

Question

I have an sqlite query which returns the following python list of tuples:

[(u'1623,0.0,1651,2.0e-06,1679,5.0e-06,1707,7.0e-06,1735,9.0e-06,1762,1.1e-05,1790,1.4e-05'),
( u'1933,458273.7,1940,460182.6,1947,464019.3,1954,465922.8,1961,467856.0,1968,479385.0')]

Each string here contains a tuple of x,y pairs i.e.one item is 1623,0.0 The output which I want is:

[(1623,0.0,1651,2.0e-06,1679,5.0e-06,1707,7.0e-06,1735,9.0e-06,1762,1.1e-05,1790,1.4e-05),
(1933,458273.7,1940,460182.6,1947,464019.3,1954,465922.8,1961,467856.0,1968,479385.0)]

Is there an efficient way to perform this translation?

Maybe it can be performed at the sql level but I dont think so, my query is as follows:

select group_concat( COALESCE(timestamp, "")
                     || COALESCE(",", "")
                     || COALESCE(value, "") )
              from table where type='VALUE' group by eventid;

I cant find a way to concatenate more than one column without converting it to a string

Why are you concatenating columns in the first place? Why not just query for timestamp, value? — Martijn Pieters
– Martijn Pieters, Commented Feb 5, 2014 at 11:30
And COALESCE(",", "") is always going to be ",", so why are you using COALESCE there at all? — Martijn Pieters
– Martijn Pieters, Commented Feb 5, 2014 at 11:30
@Martijn Pieters I cant do a simple query of timestamp, value since they are grouped by a field called eventID. If I did "select eventID group_concat( COALESCE(timestamp, "") ...." I would get: [(1 | "data1, data2, data3"), (2|"data1, data2, data3")] so the tuples in the final result are due to the eventIDs depicting a specific group. Valid comment on COALESCE I can remove that :) Thanks — Har
– Har, Commented Feb 5, 2014 at 11:34
p.s that is the reason I am using the "group_concat" and "group by" keywords — Har
– Har, Commented Feb 5, 2014 at 11:39
Yes, I see what you are trying to do here, and you are going about it the wrong way. :-) — Martijn Pieters
– Martijn Pieters, Commented Feb 5, 2014 at 11:41

Martijn Pieters · Accepted Answer · 2014-02-05 11:47:17Z

3

Don't group in SQL, group in Python and avoid having to serialize then unserialize your values:

from itertools import groupby
from operator import itemgetter


cursor.execute('''select eventid, timestamp, value from table
                  where type="VALUE" order by eventid''')
for eventid, rows in groupby(cursor, itemgetter(0)):
    print eventid
    for _, timestamp, value in rows:
        print timestamp, value

Use grouping in SQL only when you need to aggregate your data (to sum, calculate averages, count, etc.) not when you need individual rows from the groups for further processing.

If you need your output exactly as described (a tuple with timestamp, value pairs repeated) you can produce that with:

from itertools import groupby
from operator import itemgetter

cursor.execute('''select eventid, timestamp, value from table
                  where type="VALUE" order by eventid''')
result = [tuple(chain.from_iterable(r[1:] for r in rows))
          for eventid, rows in groupby(cursor, itemgetter(0))]

e.g. for every unique eventid produce a tuple consisting of the chained timestamp and value columns.

edited Feb 5, 2014 at 11:47

answered Feb 5, 2014 at 11:41

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

2 Comments

Har Over a year ago

Yes, that seems like the way to go. I thought there might have been some sql magic to do this. Since iterating through alot of elements is quiet slow but there doesnt seem to be a way around this.

Martijn Pieters Over a year ago

@user1932405: the alternative is to interpret your strings as tuples using ast.literal_eval() but that's intensive as well. It is also more work for both SQLite and Python.

Collectives™ on Stack Overflow

Converting database query results from strings to tuples efficiently

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related