Twitter 自分のつぶやきをMySQLにぶちこむ

例によってやってみた感じ。
不要な項目もあるかもしれませんし、冗長な部分、至らぬ部分があるかもしれません。
気になった点はご指摘いただければ幸いです。

====

mysql>\s
略
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
略

mysql> use twitter;
Database changed
mysql> show tables;
+——————-+
| Tables_in_twitter |
+——————-+
| twit |
+——————-+
1 row in set (0.00 sec)

mysql> desc twit;
+———————–+————+——+—–+——————-+—————————–+
| Field | Type | Null | Key | Default | Extra |
+———————–+————+——+—–+——————-+—————————–+
| post_id | bigint(11) | NO | PRI | NULL | |
| screen_name | text | NO | | NULL | |
| in_reply_to_status_id | bigint(11) | NO | | NULL | |
| post_text | text | NO | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| profile_image_url | text | NO | | NULL | |
+———————–+————+——+—–+——————-+—————————–+
6 rows in set (0.00 sec)

# gem list ruby-mysql

*** LOCAL GEMS ***

ruby-mysql (2.9.3)

# gem list twitter4r

*** LOCAL GEMS ***

twitter4r (0.3.2)

# cat conf.yaml
test:
  login: Login_Twitter_id
  password: Twitter_password

#! /usr/local/bin/ruby -Ku
require 'rubygems'
gem 'twitter4r','>=0.3.0'
require 'twitter'
require 'twitter/console'
require "mysql"
require 'kconv'

my = Mysql::new("localhost", "db_user", "password")
my.query("set character set utf8")

twitter = Twitter::Client.from_config( 'conf.yaml' , 'test' )
post_id = Numeric.new
[*1..10].each{|i|
        twitter.timeline_for(:user , :id => 'Twitter_id' , :count => 200 , :page => i.to_s ) do |status|
                post_id = status.id.to_i
                screen_name = status.user.screen_name
                in_reply_to_status_id = status.in_reply_to_status_id ||=0
                text = status.text.to_s ; text = Kconv.toutf8("#{text}")
                created_at = status.created_at ; created_at = created_at.strftime("%Y-%m-%d %H:%M:%S")
                profile_image_url = status.user.profile_image_url
                begin
                        my.query("INSERT INTO `twitter`.`twit` ( `post_id` , `screen_name` , `in_reply_to_status_id` , `post_text` , `created_at` , `profile_image_url` ) VALUES (  #{post_id} , \'#{screen_name}\' , #{in_reply_to_status_id} , \"#{text}\" , \"#{created_at}\" , \"#{profile_image_url}\" );")
                rescue Mysql::Error
                        next
                end
        end
}