ORM for SQL Scripting

Question

What is the best way to run simple sql scripts in a database (preferably db implementation agnostically)?

So, for illustration purposes, using your best/suggested way, i'd like to see a script that creates a few tables with names from an array ['cars_table', 'ice_cream_t'], deletes all elements with id=5 in a table, and does a join between two tables and prints the result formatted in some nice way.

1. I've heard of Python and PL/SQL to do this
2. Ruby/Datamapper seems very attractive
3. Java + JDBC, maybe
4. Others?

Some of these are mostly used in a full application or within a framework. I'd like to see them used simply in scripts.

Answer 1

By using SQL DDL (Data Definition Language), which can be done db agnostically, if you're careful.

There are examples at the Wikipedia article: http://en.wikipedia.org/wiki/Data_Definition_Language

Answer 2

Python's sqlalchemy is also attractive.

Tutorials on defining and creating tables, querying.

Answer 3

Ruby/Sequel is currently my weapon of choice.

Short example from the site:

require "rubygems"
require "sequel"

# connect to an in-memory database
DB = Sequel.sqlite

# create an items table
DB.create_table :items do
  primary_key :id
  String :name
  Float :price
end

# create a dataset from the items table
items = DB[:items]

# populate the table
items.insert(:name => 'abc', :price => rand * 100)
items.insert(:name => 'def', :price => rand * 100)
items.insert(:name => 'ghi', :price => rand * 100)

# print out the number of records
puts "Item count: #{items.count}"

# print out the average price
puts "The average price is: #{items.avg(:price)}"