CODEWITHSUNDEEP
rubysqlite
craigeley
craigeley

Reputation: 352

Parsing sqlite3 query responses in ruby

I'm trying to read and parse an sqlite3 query in Ruby using the sqlite3 gem. This db already exists on my machine.

I'm opening the db with db = SQLite3::Database.new "/path to/database.sqlite"

The I'm executing my particular query with db.execute( "SELECT * FROM `ZSFNOTE` WHERE `ZTRASHED` LIKE '0'" ) do |row|

Now, based on my (limited) experience, I was hoping that this could be parsed like a JSON response, where I could call something like row["ZTITLE"]. However, those headers aren't available in my response, I can only get at what I'm looking for by guessing an integer, like row[19].

I know I'm not even scratching the surface of the sqlite3 gem, but couldn't find the answer to this in the docs. Any help would be much appreciated.

Upvotes: 0

Views: 318

Answers (1)

Cereal
Cereal

Reputation: 3829

You can use #execute2 to get the headers. 

require 'sqlite3'
db = SQLite3::Database.new(':memory:')
db.execute 'CREATE TABLE "examples" ("header" varchar(20), "value" integer(8))'
db.execute 'INSERT INTO examples(header, value) VALUES("example", 1)'
db.execute2('select * from examples')
# => [["header", "value"], ["example", 1]]

You can map the headers to the columns like so:

headers, *rows = db.execute2('select * from examples')
rows.map! do |row|
    row.each_with_index.with_object({}) do |(col, i), o|
        o[headers[i]] = col
    end
end

rows.each do |row|
    p row['header']
end

# => "example"

Upvotes: 1

Related Questions