SQL and CSV

[Nick]

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
                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