Python: SQL query results to table

Question

I have the results for a SQL query in Python and I would like to convert this into a table with the appropriate data formats.

The results now:

(datetime.datetime(2014, 1, 13, 0, 0), Decimal('6.16'), Decimal('5.95'), Decimal('4.05')),

I would like the results to be:

13/01/14, 6.16, 5.95, 4.05

I have tried:

Data = list(conn.execute(# some query));
Stuff = [];
for row in Data:
Stuff.append([i for i in zip(time.strftime("%d %m %Y", row['day'].timetuple()), float(row['col1']), float(row['col2']), float(row['col3']))]);

But this seems to only zip by 1 character and not each element separated by ','. Is there a way to zip by delimiter? Or any alternative implementation to get the desired result.

EDIT

The solution previously suggested creates a list with [day, a1, a2, a3] i.e. n x 1 table vs n x 4 table i.e. day, a1, a2, a3 which is required. Is there any way to do this using zip or something similar?

what does conn.execute retuen before you cast it to a list? — WeaselFox
– WeaselFox, Commented Feb 17, 2014 at 8:07
Could you post your full code? It looks like you might be using some optional features such as DictCursor — Peter Gibson
– Peter Gibson, Commented Feb 17, 2014 at 9:12

Peter Gibson · Accepted Answer · 2014-02-17 02:59:13Z

1

You don't need zip. zip takes multiple lists and interleaves them

>>> zip('abc', '123')
[('a', '1'), ('b', '2'), ('c', '3')]

Just append the data straight to your Stuff list

Data = list(conn.execute(# some query));
Stuff = [];
for row in Data:
    Stuff.append([time.strftime("%d %m %Y", row['day'].timetuple()), float(row['col1']), float(row['col2']), float(row['col3'])])

answered Feb 17, 2014 at 2:59

Peter Gibson

19.6k7 gold badges63 silver badges65 bronze badges

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

4 Comments

Black Over a year ago

Yields an n x 1 array whereas a n x 4 array is required.

WeaselFox Over a year ago

than your query returns just one row

Peter Gibson Over a year ago

@Blackholify which Python MySQL library are you using?

Black Over a year ago

@PeterGibson using psycopg2 (postgresql). I've got the solution so it's fine now. Thanks

Black · Accepted Answer · 2014-02-17 08:13:26Z

0

Stuff = []
for row in Data:
    Stuff.append([i for i in [row['day'], float(row['a1']), float(row['a2']), float(row['a3'])]]);

answered Feb 17, 2014 at 8:13

Black

4,6828 gold badges44 silver badges59 bronze badges

1 Comment

Peter Gibson Over a year ago

[i for i in [row['day'], float(row['a1']), float(row['a2']), float(row['a3'])]] this is equivalent (albeit slower) to just [row['day'], float(row['a1']), float(row['a2']), float(row['a3'])] which is what I proposed in my solution. If one works, then the other should also work

Collectives™ on Stack Overflow

Python: SQL query results to table

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related