Save JSON from API endpoint to database in Ruby on Rails app

Question

I am new to Ruby on Rails and need some advice on this! I trying to build a webscraper and have a JSON file here at Kimono Labs, where you can turn website data into API's:

https://www.kimonolabs.com/api/3obhv4p0?apikey=WWHHbKEkOmAPXsObOccPBXTb5NgRyCNO that I want to save to the database of my Ruby on Rails application.

Specifically it's the "results" key that I want to save to the database. It contains data from Google Finance with the company, URL to quote page, P/E and latest price. Which has the following format:

"results": {
    "collection1": [
      {
        "property1": {
          "href": "https://www.google.com/finance?catid=TRBC:57&sort=a&ei=Tx2WVonTG9uhe7Hpv_AN",
          "text": "Company"
        },
        "property2": "P/E (ttm)",
        "property3": "Quote",
        "index": 1,
        "url": "https://www.google.com/finance?catid=TRBC%3A57&sort=PE_RATIO&ei=6tyMVrqxIdaP0ASF0pbACQ"
      },
      {
        "property1": {
          "href": "https://www.google.com/finance?q=NASDAQ:NANO&ei=Tx2WVonTG9uhe7Hpv_AN",
          "text": "Nanometrics Incorporated"
        },
        "property2": "10,100.72",
        "property3": "14.04",
        "index": 2,
        "url": "https://www.google.com/finance?catid=TRBC%3A57&sort=PE_RATIO&ei=6tyMVrqxIdaP0ASF0pbACQ"
      },

This is the migration I have:

ActiveRecord::Schema.define(version: 20160108073353) do

  create_table "stocks", force: :cascade do |t|
    t.string   "company"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string   "url"
    t.float    "pe"
    t.float    "quote"
  end

The question is how do I load this JSON file into my application? I think it has the following steps and therefore my question can be broken up into:

I need to open/load it with RestClient or OpenURI > how do I do this?
Parse the data and make it a Ruby hash > how do I do this?
Then loop through the hash and save data to database? > how do I do this?

Thanks for the help!

Inpego · Accepted Answer · 2016-01-13 15:14:24Z

7

Yes, these are right steps.

First, you should create Stock model:

$ rails g model stock --skip

And then:

# get JSON
result = Net::HTTP.get(URI.parse('https://www.kimonolabs.com/api/3obhv4p0?apikey=WWHHbKEkOmAPXsObOccPBXTb5NgRyCNO'))

# parse JSON
json = JSON.parse(result)

# save data to DB
json['results']['collection1'][1..-1].each do |data| # [1..-1] ignores first dummy element
  Stock.create(
    company: data['property1']['text'],
    url: data['url'],
    pe: data['property2'].gsub(',', ''), # .gsub removes thousands separator
    quote: data['property3'].gsub(',', '')
  )
end

answered Jan 13, 2016 at 15:14

Inpego

2,66715 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

chemook78 Over a year ago

Thanks Inpego, that works! I actually want to update this table based on the JSON data on a regular basis. The table is a result of a stock screener so the list of stocks will change (company name, quote, P/E; but not the URL belonging to company name). I am thinking to run a Stock.delete_all every time and then the code above to load a new table. Is that the way to go? Or do a check for the "company" column, see if there are any duplicates with the new data and update P/E + quote and add new rows if the company doesn't exist yet. Should I do this with an if statement? Thanks for the advice!

Inpego Over a year ago

The first way is more simple and most likely will perform better, I recommend to choose it therefore. I also recommend to use Stock.connection.execute("TRUNCATE TABLE #{Stock.table_name}") instead of Stock.delete_all for the sake of performance, in case you are not using external keys for this table.

Inpego Over a year ago

see stackoverflow.com/questions/139630/…

Collectives™ on Stack Overflow

Save JSON from API endpoint to database in Ruby on Rails app

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related