On May 8, 1:45 am, Nick <nic...@gmail.com> wrote:
> On May 5, 8:27 pm, Tim Golden <m...@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
--
http://mail.python.org/mailman/listinfo/python-list
