Database on the fly with scripting languages

There's sqlite3, included into python. With it you can create a database (on memory) and add rows to it, and perform SQL queries.

If you want neat ActiveRecord-like functionality you should add an external ORM, like sqlalchemy. That's a separate download though

Quick example using sqlalchemy:

from sqlalchemy import create_engine, Column, String, Integer, MetaData, Tablefrom sqlalchemy.orm import mapper, create_sessionimport csvCSV_FILE = 'foo.csv'engine = create_engine('sqlite://') # memory-only databasetable = Nonemetadata = MetaData(bind=engine)with open(CSV_FILE) as f:    # assume first line is header    cf = csv.DictReader(f, delimiter=',')    for row in cf:        if table is None:            # create the table            table = Table('foo', metadata,                 Column('id', Integer, primary_key=True),                *(Column(rowname, String()) for rowname in row.keys()))            table.create()        # insert data into the table        table.insert().values(**row).execute()class CsvTable(object): passmapper(CsvTable, table)session = create_session(bind=engine, autocommit=False, autoflush=True)

Now you can query the database, filtering by any field, etc.

Suppose you run the code above on this csv:

name,age,nicknamenosklo,32,noskloAfila Tun,32,afilatunFoo Bar,33,baz

That will create and populate a table in memory with fields name, age, nickname. You can then query the table:

for r in session.query(CsvTable).filter(CsvTable.age == '32'):    print r.name, r.age, r.nickname

That will automatically create and run a SELECT query and return the correct rows.

Another advantage of using sqlalchemy is that, if you decide to use another, more powerful database in the future, you can do so pratically without changing the code.

Use a DB in a library like SQLite. There are Python and Ruby versions .

Load your CSV into table, there might be modules/libraries to help you here too. Then SQL away.

Looked at Perl and and Text::CSV and DBI? There are many modules on CPAN to do exactly this. Here is an example (from HERE):

#!/usr/bin/perluse strict;use warnings;use DBI;# Connect to the database, (the directory containing our csv file(s))my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;");# Associate our csv file with the table name 'prospects'$dbh->{'csv_tables'}->{'prospects'} = { 'file' => 'prospects.csv'};# Output the name and contact field from each rowmy $sth = $dbh->prepare("SELECT * FROM prospects WHERE name LIKE 'G%'");$sth->execute();while (my $row = $sth->fetchrow_hashref) {     print("name = ", $row->{'Name'}, "  contact = ", $row->{'Contact'}. "\n");}$sth->finish();name = Glenhuntly Pharmacy  contact = Paulname = Gilmour's Shoes  contact = Ringo

Just type perldoc DBI and perldoc Text::CSV at the command prompt for more.