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

1
2
3
4
5
6
7

mysql>\s
(snip)
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
(sinp)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

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)

1
2
3
4
5

# gem list ruby-mysql

*** LOCAL GEMS ***

ruby-mysql (2.9.3)

1
2
3
4
5

# gem list twitter4r

*** LOCAL GEMS ***

twitter4r (0.3.2)

1
2
3
4

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

#! /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
}