How do I use Ruby to extract data from a SQLite3 database and output it as static HTML?

Question

I want to write a script that retrieves data from a local SQLite3 database and then formats that data into an HTML file.

I'm not sure what "tools" to use, but I guess I'll need the sqlite3 gem and then I was thinking about using Sinatra for the HTML bit.

UPDATE: I'm not looking to create a web service. I just want to run a Ruby script with an sqlite3 database as input and extract a specific set of data, which I then want to format and output as a single file. I mentioned HTML, but anything like markdown would be great as this can then be exported in what ever format you want.

Answer 1

If you don't need http protocol I would suggest using template language, e.g. haml:

require "rubygems"
require "haml"

template = Haml::Engine.new(<<-EOD)
%table
  - vs.each do |v|
    %tr
      %td= v
EOD
template.render(Object.new, :vs => ["a", "b", "c"])
#=> "<table>\n  <tr>\n    <td>a</td>\n  </tr>\n  <tr>\n    <td>b</td>\n  </tr>\n  <tr>\n    <td>c</td>\n  </tr>\n</table>\n" 

Answer 2

I like your choice of Sinatra for the web app and suggest Sequel for the database adapter. Here's a short, untested app:

require 'sinatra'
require 'sequel'
require 'haml'

DB = Sequel.connect('sqlite://blog.db')
get '/' do
  @entries = DB[:posts].filter(live:true).order(:posted_on.desc).all
  haml 'home' # finds 'views/home.haml' and makes a string
end

get '/:postname' do
  @post = DB[:posts][short: params[:postname]]
  haml 'post'
end

home.haml

- # this will be wrapped in layout.haml if you have one
%h1 My Posts
%p Welcome to my site, I hope you like it.
#posts
  - @entries.each do |post|
    %h2{id:post[:short]}
      %a{href:post[:short]}= post[:title]
    %p= post[:overview]

post.haml

%h1= @post[:title]
%p#overview= @post[:overview]
 #contents= @post[:html]

A full introduction to Sinatra, Sequel, or Haml is outside the scope of Stack Overflow. Hope this gets you started.