Using sqlite3 with Ruby, is there a way to read each row as a hash instead of array?

Question

I am using sqlite3 as follows:

db = SQLite3::Database.open "data.db"

stm = db.prepare "SELECT * FROM COMPANIES"
rs = stm.execute

while (row = rs.next) do
    name = row[1]

that is, the row is an array instead of a hash. Is there a way to read it as a hash, so that

row[:name]
row[:address]

is the data. That's because in the future, if the table has a different order of columns, row[3] may become row[5], so row[:address] is much more definite.

Rather than use the SQLite gem, I'd recommend one of the ORMs that help insulate you from a DBM-specific language. SQLite is nice, but if your DB needs grow having to rewrite all your calls for a different DBM is a major pain. I like [Sequel](https://sequel.jeremyevans.net/) because it's very agnostic and very capable. https://sequel.jeremyevans.net/rdoc/files/doc/cheat_sheet_rdoc.html is a good overview. — the Tin Man, Dec 09 '19 at 07:33

score 5 · Answer 1 · answered Dec 09 '19 at 06:59

5

There's results_as_hash setter to get this behavior:

db = SQLite3::Databse.open 'data.db'
db.results_as_hash = true
# ...

answered Dec 09 '19 at 06:59

Marek Lipka

50,622
7
87
91

works great. I suppose it has to be `row["NAME"]` and can't be made to `row[:NAME]`? – nonopolarity Dec 09 '19 at 07:04

Using sqlite3 with Ruby, is there a way to read each row as a hash instead of array?

1 Answers1