Tech Tips

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.