Python: parsing SQL query

Question

I'm trying to reduce the number of queries that I run on a database. I currently run a query to determine the deliveries that occurred in the last day, then queries each delivery twice, once to find account information, then again to find which items were delivered in order to invoice them. I can combine the last two sets of queries into one query, but am having trouble separating each delivery into a separate object.

Here's the query I get:

AccountName    Date    ItemName    Quantity    Cost

     A        3/8/16      i1           2      $1.00
     A        3/8/16      i2           3      $2.00
     A        3/8/16      i3           1      $3.00
     B        3/8/16      i3           3      $3.00
     B        3/8/16      i4           1      $4.00
     C        3/8/16      i2           2      $2.00

I want to organize this into:

AccountName = [A,B,C]
Date = [3/8/16,3/8/16,3/8/16]
ItemDetails = [ 
           [[i1,i2,i3],[2,3,1],[$1,$2,$3]],
           [[i3,i4],[3,1],[$3,$4]],
           [[i2],[2],[$2]] 
          ]

And what's your question? What have you already tried? Where are you getting stuck? — Chris
– Chris, Commented Mar 8, 2016 at 21:18
What is your approach? Do you want to tune your query or parse the output from this query? — mprat
– mprat, Commented Mar 8, 2016 at 21:23
I'd like to parse my current query and am having trouble selecting the item details associated with one account. For example, how do I return only the ItemNames, Quantities, Costs of Account A from my query. — Vong Ly
– Vong Ly, Commented Mar 8, 2016 at 22:00

inspectorG4dget · Accepted Answer · 2016-03-08 21:30:03Z

1

Here's something that should get you started:

import csv

from datetime import datetime as dt

data = {}
with open('path/to/file') as infile:
    for _ in range(2): infile.readline()
    for name, date, item, quant, cost in csv.reader(infile, delimiter='\t'):
        if name not in data: data[name] = {}
        if date not in data[name]: data[name][date] = {}
        if item not in data[name][date]: data[name][date][item] = []
        data[name][date][item].append((quantity, cost))

Now, you can query data by account name, per each date, to see how much (and at what cost) of each item was purchased

answered Mar 8, 2016 at 21:30

inspectorG4dget

115k30 gold badges159 silver badges253 bronze badges

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

Collectives™ on Stack Overflow

Python: parsing SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related