SQL and CSV

[Tim Golden]

Nick wrote:
> I have a requirement to read a CSV file. Normally, no problem, just
> import CSV and slurp the file up.
> 
> However, in this case I want to filter out lines that have fields set
> to particular values.
> 
> It would be neat to be able to do something like this.
> 
> select * from test.csv where status <> "Canceled"
> 
> Using adodb I can do this, so long as I don't have the where clause. :-
> (
> 
> Is there a reasonable lightweight way of doing this in Python?
> 
> I could write some python code that is used to filter rows, and inport
> that from config, but it's not quite as elegant as an SQL route.


Not entirely clear what you are and aren't prepared to try here, but...
the most obvious Python-based way to do this is treating the csv reader
as an iterator and filtering there. Your last line suggests that's not
what you want but just in case I've misunderstood:

<test.csv>
id,code,status
1,"ONE","Active"
2,"TWO","Cancelled"
3,"THREE","Active"
</test.csv>

<code>
import csv

for row in csv.DictReader (open ("c:/temp/test.csv", "rb")):
  if row['status'] != 'Cancelled':
    print row

</code>

Doesn't seem too onerous, and could obviously be wrapped in
some useful class/module.

But if you really want to go the SQL route, I believe there are
ODBC adapters for CSV which, combined with PyODBC or CeODBC,
would probably take you where you want to go.

TJG