SQL and CSV

[John Machin]

On May 8, 1:45 am, Nick <nic... at gmail.com> wrote:
> On May 5, 8:27 pm, Tim Golden <m... at timgolden.me.uk> wrote:
>
>
>
> > Nick wrote:
> > > Part of the problem is that the 'selection' needs to be in a config
> > > file. I can put the if row['status'] != 'Cancelled': return True into
> > > a config, read it and eval it, but its not quite as clean as ansql
> > > route.
>
> > Still not clear what the restriction is. If you were writingSQLyou'd have to read *something* from your config file,
> > unless you're suggesting that the "config file" is in fact
> > aSQLfile. Which is one way of doing it, but then you might
> > just as well have your config file as a Python file and
> > import it.
>
> > Have I missed the point somewhere here? Can you give an
> > example -- even a fictional one -- of what you couldn't
> > do using, say, the example I gave earlier?
>
> > TJG
>
> Solution found. In the end I used SQLite to read from a csv file, and
> now I can query the CSV file. The file is read using the csv module
>
> First create a function
>
> def fraction(p, denom):
>     num, frac = p.split ('-')
>     return float (num) + float (frac) / denom
>
> for use within queries.
>
> Now build the class.
>
>         self.filename  = filename
>         self.dialect   = dialect
>         self.query     = query
>         reader = csv.reader (open (filename, 'r'))
>         self.connection = sqlite.connect(":memory:")
>         self.connection.create_function("fraction", 2, fraction) #
> Adds in function
>         self.cursor = self.connection.cursor()
>         first = True
>         for row in reader:
>             if first:
>                 headers = []
>                 for r in row:
>                     n = r.strip().replace (' ', '_').replace ('-','_')
>                     headers.append (n)
>                 command = 'create table csv (%s)' % ','.join (headers)
>                 self.cursor.execute (command)
>                 first = False
>             else:
>                 command = 'insert into csv values ("%s")' % '","'.join
> (row)
>                 self.cursor.execute (command)
>
> and then I can use this
>
>         self.cursor.execute (self.query)
>         rows = self.cursor.fetchall()
>         headers = []
>         for r in self.cursor.description:
>             headers.append (r[0])
>         results = Results.Results (headers, self.name, {})
>         i = 0
>         for row in rows:
>             results.add (row, i)
>             i = i + 1
>         return results
>
> to query the results.
>
> Results.Results is one of my classes that's reused in lots of places.
>
> The query then looks somethign like this
>
>                 select
>                     Client_Reference_Number as TrdNbr,
>                     Asset_Number as ISIN,
>                     Quantity as Qty,
>                     status
>                 from
>                     csv

The remaining lines of your SELECT statement are incredibly redundant
AFAICT. It seems you have pushed the contents of your csv file into a
data base and pulled them ALL out again ... not what I'd call a
"query". What's the point?


>                 where status in ("CANCELLED")
>
>                 union
>
>                 select
>                     Client_Reference_Number as TrdNbr,
>                     Asset_Number as ISIN,
>                     Quantity as Qty,
>                     status
>                 from
>                     csv
>                 where status not in ("CANCELLED")
>
> All incredibly neat and the first time I've used SQLite.
>
> nick