Categories: Uncategorized

[API][MySQL][Ruby][JSON]Insert Event Information from ATND via ATND API

Overview

Try to insert event data from ATND which is event support web site in Japan.

Environment

  • OS
    • Linux version 3.2.0-67-generic (buildd@brownie) (gcc version 4.6.3 (Ubuntu/Linaro 4.6.3-1ubuntu5) ) #101-Ubuntu SMP Tue Jul 15 17:46:11 UTC 2014
  • Ruby
    • 2.1.2
  • MySQL
    • 5.5

Development

ATND API

Document is here(Japanease). You can get event data by accessing “http://api.atnd.org/events/”.
You can add some GET parameters like this “http://api.atnd.org/events/?keyword_or=ruby,haskell&count=100&format=json”. Following code has standard library only., 
require 'net/http'
require 'uri'
require 'json'

uri = URI.parse('http://api.atnd.org/events/?keyword_or=ruby,haskell&count=100&format=json')
response = Net::HTTP.start(uri.host, uri.port, use_ssl: uri.scheme == 'https') do |http|
  http.open_timeout = 5
  http.read_timeout = 10
  http.get(uri.request_uri)
end

case response
  when Net::HTTPSuccess
    json = response.body
    puts JSON.parse(json)
  else
    puts 'ERROR'
end
Result is like following. 
{
    "results_returned": 1,
    "results_start": 1,
    "events": [{
        "event": {
            "event_id": 58057,
            "title": "ひっそり Ruby な飲み会",
            "catch": "kzrb meetup mini",
            "description": "\u003cp\u003eRuby に興味のある方、ちょっと集まって飲みませんか :)\u003c/p\u003e\n\u003cp\u003e※後述の開始時間、終了時間は \u003cstrong\u003e目安です\u003c/strong\u003e :p\u003c/p\u003e\n\u003cp\u003e※費用は実費ですが、おそらく4000円ぐらいになる見通しです。\u003c/p\u003e",
            "event_url": "http://atnd.org/events/58057",
            "started_at": "2014-11-21T19:30:00.000+09:00",
            "ended_at": null,
            "url": null,
            "limit": null,
            "address": "石川県金沢市片町",
            "place": "片町のどこか（調整中）",
            "lat": "36.5606421",
            "lon": "136.6512888",
            "owner_id": 62172,
            "owner_nickname": "kiyohara",
            "owner_twitter_id": "kiyohara",
            "accepted": 6,
            "waiting": 0,
            "updated_at": "2014-10-21T18:03:59.000+09:00"
        }
    }]
}

Insert to MySQL

Table specification is following. 
*************************** 1. row ***************************
  Field: event_id
   Type: int(11)
   Null: NO
    Key: PRI
Default: NULL
  Extra: auto_increment
*************************** 2. row ***************************
  Field: title
   Type: varchar(512)
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: summary
   Type: varchar(1024)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 4. row ***************************
  Field: description
   Type: blob
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 5. row ***************************
  Field: event_url
   Type: varchar(1024)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 6. row ***************************
  Field: event_member_url
   Type: varchar(2048)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 7. row ***************************
  Field: event_start_time
   Type: datetime
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 8. row ***************************
  Field: event_end_time
   Type: datetime
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 9. row ***************************
  Field: event_updated_time
   Type: datetime
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 10. row ***************************
  Field: attendance_limit
   Type: smallint(6)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 11. row ***************************
  Field: attendance_people
   Type: smallint(6)
   Null: YES
    Key:
Default: 0
  Extra:
*************************** 12. row ***************************
  Field: waiting_people
   Type: smallint(6)
   Null: YES
    Key:
Default: 0
  Extra:
*************************** 13. row ***************************
  Field: address
   Type: tinyblob
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 14. row ***************************
  Field: place
   Type: tinyblob
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 15. row ***************************
  Field: lattitude
   Type: double
   Null: YES
    Key:
Default: 0
  Extra:
*************************** 16. row ***************************
  Field: longitude
   Type: double
   Null: YES
    Key:
Default: 0
  Extra:
*************************** 17. row ***************************
  Field: event_owner_id
   Type: int(11)
   Null: YES
    Key:
Default: -1
  Extra:
*************************** 18. row ***************************
  Field: event_owner_name
   Type: varchar(256)
   Null: YES
    Key:
Default:
  Extra:
*************************** 19. row ***************************
  Field: event_owner_twitter_id
   Type: varchar(256)
   Null: YES
    Key:
Default:
  Extra:
*************************** 21. row ***************************
  Field: updated_time
   Type: timestamp
   Null: NO
    Key:
Default: CURRENT_TIMESTAMP
  Extra: on update CURRENT_TIMESTAMP
*************************** 22. row ***************************
  Field: created_time
   Type: timestamp
   Null: NO
    Key:
Default: 0000-00-00 00:00:00
  Extra:
Maybe most of Rubyist use mysql or mysql2 to treat MySQL.
I will use mysql2 here.
Gemfile is following. 
ruby '2.1.2'

source 'https://rubygems.org'

gem 'mysql2', '0.3.16'
Complete code is following. 
require 'net/http'
require 'uri'
require 'json'
require 'mysql2'

# ATND API part
data = nil
uri = URI.parse('http://api.atnd.org/events/?keyword_or=ruby,haskell&count=100&format=json')
response = Net::HTTP.start(uri.host, uri.port, use_ssl: uri.scheme == 'https') do |http|
  http.open_timeout = 5
  http.read_timeout = 10
  http.get(uri.request_uri)
end

case response
  when Net::HTTPSuccess
    json = response.body
    data = JSON.parse(json)
  else
    puts 'ERROR'
    exit 1
end

# MySQL part
def convert(data)
  fields = {}
  fields['title']                  = data['title'].gsub(/"/,'\"')
  fields['summary']                = data['catch'].gsub(/"/,'\"')            if !data['catch'].nil?
  fields['description']            = data['description'].gsub(/"/,'\"')      if !data['description'].nil?
  fields['event_url']              = data['event_url']                       if !data['event_url'].nil?
  fields['event_member_url']       = data['url']                             if !data['url'].nil?
  fields['event_start_time']       = data['started_at']                      if !data['started_at'].nil?
  fields['event_end_time']         = data['ended_at']                        if !data['ended_at'].nil?
  fields['event_updated_time']     = data['updated_at']                      if !data['updated_at'].nil?
  fields['attendance_limit']       = data['limit']                           if !data['limit'].nil?
  fields['attendance_people']      = data['accepted']                        if !data['accepted'].nil?
  fields['waiting_people']         = data['waiting']                         if !data['waiting'].nil?
  fields['address']                = data['address'].gsub(/"/,'\"')          if !data['address'].nil?
  fields['place']                  = data['place'].gsub(/"/,'\"')            if !data['place'].nil?
  fields['lattitude']              = data['lat']                             if !data['lat'].nil?
  fields['longitude']              = data['lon']                             if !data['lon'].nil?
  fields['event_owner_id']         = data['owner_id']                        if !data['owner_id'].nil?
  fields['event_owner_name']       = data['owner_nickname'].gsub(/"/,'\"')   if !data['owner_nickname'].nil?
  fields['event_owner_twitter_id'] = data['owner_twitter_id'].gsub(/"/,'\"') if !data['owner_twitter_id'].nil?
  fields['created_time']           = Time.now.strftime('%Y-%m-%d %H:%M:%S')
  return fields
end

records = []
event_data_list = data['events']
event_data_list.map{|data| records.push(convert(data['event']))}

client = Mysql2::Client.new(:host => 'localhost', :username => 'test', :password => 'test', :database => 'test')
records.map { |rec| client.query('INSERT INTO event_info (' + rec.keys.join(',') + ') VALUES ("' + rec.values.join('","') + '")') };
MySQL record is like following after running above. 
              event_id: 1
                 title: ひっそり Ruby な飲み会
               summary: kzrb meetup mini
           description: <p>Ruby に興味のある方、ちょっと集まって飲みませんか :)</p>
<p>※後述の開始時間、終了時間は <strong>目安です</strong> :p</p>
<p>※費用は実費ですが、おそらく4000円ぐらいになる見通しです。</p>
             event_url: http://atnd.org/events/58057
      event_member_url: NULL
      event_start_time: 2014-11-21 19:30:00
        event_end_time: NULL
    event_updated_time: 2014-10-21 18:03:59
      attendance_limit: NULL
     attendance_people: 5
        waiting_people: 0
               address: 石川県金沢市片町
                 place: 片町のどこか（調整中）
             lattitude: 36.5606421
             longitude: 136.6512888
        event_owner_id: 62172
      event_owner_name: kiyohara
event_owner_twitter_id: kiyohara
          updated_time: 2014-11-01 12:48:40
          created_time: 2014-11-01 12:48:39
More useful form of the code is here.
zuqqhi2

Share
Published by
zuqqhi2
10 years ago

Recent Posts

The Benefits of PubSubClient and Points to Be Aware Of

When I send MQT…

2 months ago

Memo of How to Build Java Develop Environment

Since I don't u…

4 months ago

How to Upload Program to Arduino Using PlatformIO IDE for VSCode

When you want t…

10 months ago

How to avoid GPG error when using ROS Docker image

Some weeks ago,…

11 months ago

Trying to Develop Visited Countries Colored Map Generation App with Streamlit

Streamlit is a …

1 year ago

Run Amazon FreeRTOS on M5Stack Core2 for AWS – ESP32 IoT Development Kit で Amazon FreeRTOS (macOS)

I bought M5Stac…

2 years ago