how to save data from an api with ruby and sqlite3

Question

I’m creating an app only in ruby, with sqlite 3,I have to create the bd in bin setup to save the parameters of the api,I’m trying to make this foma:

#!/usr/bin/env ruby
require 'sqlite3'
require 'faraday'
require 'json'
puts '== Instalando dependências =='
system 'gem install bundler --conservative'
system('bundle check') || system('bundle install')

puts "\n== Preparando banco de dados =="

DB_FILE = 'ibge_nomes.db'
begin
  db = SQLite3::Database.open DB_FILE
  db.execute <<~SQL
    CREATE TABLE Estados(
      id integer PRIMARY KEY ,
      sigla varchar(10) NOT NULL,
      nome varchar(100) NOT NULL
    );
  SQL

  response = Faraday.get('https://servicodados.ibge.gov.br/api/v1/localidades/estados?orderBy=nome')
  json = JSON.parse(response.body, symbolize_names: true)
  json.map do |dados|
    seed_data = []
    @id = dados[:id]
    @sigla = dados[:sigla]
    @nome = dados[:nome]
    seed_data = %i[id sigla nome]
    seed_data.each do |data|
    db.execute "INSERT INTO Estados VALUES ( ?, ?, ?)", data

  end
  end
rescue SQLite3::Exception => e
  puts e
ensure
  db.close if db
end
begin
  db = SQLite3::Database.open DB_FILE
  db.execute <<~SQL
    CREATE TABLE Cidades(
    id integer PRIMARY KEY ,
    sigla varchar(10) NOT NULL,
    nome varchar(100) NOT NULL
    );
  SQL

  response = Faraday.get('https://servicodados.ibge.gov.br/api/v1/localidades/municipios?orderBy=nome')
  json = JSON.parse(response.body, symbolize_names: true)
  json.map do |dados|
    cidades = []
    @id = dados[:id]
    @sigla = dados[:sigla]
    @nome = dados[:nome]
    cidades << %i[id sigla nome]
    cidades.each do |cidade|
      db.execute 'INSERT INTO Cidades VALUES (?,?,?)', cidades
    end
  end
rescue SQLite3::Exception => e
  puts e
ensure
  db.close if db
end

and displays the following error message when I give the binsetup command

Traceback (most recent call last):
    11: from bin/setup:24:in `<main>'
    10: from bin/setup:24:in `map'
     9: from bin/setup:30:in `block in <main>'
     8: from bin/setup:30:in `each'
     7: from bin/setup:31:in `block (2 levels) in <main>'
     6: from /home/luciane/.rvm/gems/ruby-2.7.2/gems/sqlite3-1.4.2/lib/sqlite3/database.rb:193:in `execute'
     5: from /home/luciane/.rvm/gems/ruby-2.7.2/gems/sqlite3-1.4.2/lib/sqlite3/database.rb:151:in `prepare'
     4: from /home/luciane/.rvm/gems/ruby-2.7.2/gems/sqlite3-1.4.2/lib/sqlite3/database.rb:194:in `block in execute'
     3: from /home/luciane/.rvm/gems/ruby-2.7.2/gems/sqlite3-1.4.2/lib/sqlite3/statement.rb:37:in `bind_params'
     2: from /home/luciane/.rvm/gems/ruby-2.7.2/gems/sqlite3-1.4.2/lib/sqlite3/statement.rb:37:in `each'
     1: from /home/luciane/.rvm/gems/ruby-2.7.2/gems/sqlite3-1.4.2/lib/sqlite3/statement.rb:41:in `block in bind_params'
/home/luciane/.rvm/gems/ruby-2.7.2/gems/sqlite3-1.4.2/lib/sqlite3/statement.rb:41:in `bind_param': can't prepare Symbol (RuntimeError)

I’ve done a lot of research but I can’t get any resolution Does anyone have any idea how to solve this mistake?

Answer 1

was seeing, apparently on this line

db.execute "INSERT INTO Estados VALUES ( ?, ?, ?)", data

here, I believe you would like to insert the id, sigla and nome but the value of data has the Symbol :id

I believe what you wanted to do is

json.map do |dados|
  seed_data = []
  @id = dados[:id]
  @sigla = dados[:sigla]
  @nome = dados[:nome]
  seed_data = %i[id sigla nome]
  data = seed_data.map { |key| dados[key] }
  db.execute "INSERT INTO Estados VALUES ( ?, ?, ?)", data
end

to debug, I added Gem pry-nav and used binding.pry

json.map do |dados|
  seed_data = []
  @id = dados[:id]
  @sigla = dados[:sigla]
  @nome = dados[:nome]
  seed_data = %i[id sigla nome]
  binding.pry # adcionar um ponto de debug aqui
  seed_data.each do |data|
    binding.pry # adcionar um ponto de debug aqui
    db.execute "INSERT INTO Estados VALUES ( ?, ?, ?)", data
  end
end