CODEWITHSUNDEEP
rubyormscripting
Verhogen
Verhogen

Reputation: 28621

ORM for SQL Scripting

What is the best way to run simple SQL scripts in a database (preferably DBM 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.

Upvotes: 1

Views: 277

Answers (2)

Mladen Jablanović
Mladen Jablanović

Reputation: 44080

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)}"

Upvotes: 4

Robert Harvey
Robert Harvey

Reputation: 180808

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

Upvotes: 0

Related Questions