over 1 year ago

重點1:先把預設的database改成mysql。
重點2:要把git設定好

1. 在新建一個專案的時候,就要先設定好。不然,以後要從SQlite 改成 MySQL ,會很麻煩:

terminal
rails new project --database=mysql
- 安裝到`run  bundle install`會出現錯誤訊息,好像是`nokogiri 1.6.8.1`
- 錯誤訊息:
terminal
Installing nokogiri 1.6.8.1 with native extensions
Gem::Ext::BuildError: ERROR: Failed to build gem native extension.
感覺上好像還是可以繼續下去。不過,之後要用nokogiri的時候,好像要用比較舊的版本。(註1)
$ gem install nokogiri -v 1.6.3.1 -- --with-iconv-dir=`xcode-select -p`/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.12.sdk/usr --with-xml2-include=`xcode-select -p`/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.12.sdk/usr/include/libxml2

2. 設定mysql的帳號密碼

- yml檔很注意縮排,縮排代表分段
- 這邊有兩個地方的username都要改成root
- password要改成MySQL的密碼(密碼不是就被看到了嗎?)
database.yml
default:&default
        adapter: mysql2
        encoding: utf8
        pool: 5
        username: root
        password: 密碼
        socket: /var/run/mysqld/mysqls.socket
database.yml
production:
        <<: *default
        database: project_production
        username: root
        password: 密碼

database.yml的密碼是明碼,所以如果不要被看到,要在.gitignore裡面做設定,這樣database.yml就不會被上傳到github去了。
[ 2.0 ] 9. 將寫好的專案部署 ( Deploy ) 到遠端 Server 上

.gitignore
/config/database.yml

3. 之後要輸入:

terminal
rake db:create

4. 然後開另一個terminal進入專案之後,輸入

terminal
rails server

5. 進入首頁看有沒有成功

html
http://localhost:3000/

6. 先設定好git

terminal
git init
git add .
git commit -m 'any message'

7. 先到Github(或者是BitBucket)去設定一個新的repo,會有這些code可以貼到terminal

terminal
git remote add origin git@github.com:tienshunlo/broadway.git
git push -u origin master

註1. 好像要用比較舊的版本

 
over 1 year ago

參考資料:文章:RailsFun.tw 新手教學_day2 HD
參考資料:影片:RailsFun.tw 新手教學_day2 HD

37:00:開始講rails

1.看有哪些help:rails --help
2.Rails 的預設資料庫是 sqlite
3.-d 重新設定資料庫為 mysql
4.在新建一個專案的時候,就要先設定好,不然以後要從SQlite改成MySQL很麻煩:

rails new project --database=mysql

5.看有哪help,已經新增過專案了,所以出現的東西會不一樣
project/project$ rails --help

41:50:設定mysql的帳號密碼

6.設定mysql的帳號密碼:database.yml
yml檔很注意縮排,縮排代表分段
這邊有兩個地方的username都要改成root
password要改成MySQL的密碼(密碼不是就被看到了嗎?)

default:&default
        adapter: mysql2
        encoding: utf8
        pool: 5
        username: imroot
        password: imroot
        socket: /var/run/mysqld/mysqls.socket
production:
        <<: *default
        database: project_production
        username: imroot
        password: imroot

7.database的密碼是明碼,所以如果不要被看到,要在.gitignore裡面做設定,這樣database.yml就不會被上傳到github去了。
[ 2.0 ] 9. 將寫好的專案部署 ( Deploy ) 到遠端 Server 上
8.之後要輸入rake db:create
9.然後開另一個terminal進入專案之後,輸入rails server
10.http://localhost:3000/才會啟動

44:00

Gemfile管理版本

46:00

Gemfile.lock這個project裡面所有相依性的gem,要寫到gemfile裡面去之後,才能夠使用。

49:00

開始講所有的檔案
temp README.rdoc public lib Gemfile config.ru bin vendor test Rakefile log Gemfile.lock db config app

53:45

config :所有的設定檔都在這邊,偷別人的就要偷這個
database.yml 資料庫的設定檔

56:00

config/environment
rails分三個模式 development.rb production.rb test.rb

1:00:00

config/secrets.yml
session會被竊取,不要給別人看到

1:03:00

開始實做migration
mysql很重要(我的顯示不出來東西)

mysql -u root -p
如果有一萬筆資料,ruby要用each跑一萬次,mysql只要一行程式
 
over 1 year ago

啟動MySql:
a. 先去系統偏好設置,先把Start MySQL Server打勾
b. 在Terminal的地方輸入:source ~/.bash_profile
c. 登錄mysql: mysql -u root -p

安裝MySql
1.JC教法:RailsFun.tw 新手教學_day2 HD
- 步驟1:sudo apt-get install mysql-server
- 步驟2:sudo apt-get install libmysqlclient-dev
- 步驟3:gem i mysql2
- (不過,我的電腦是Darwin,好像不能用apt-getsudo apt-get install mysql-server這好像是Ubuntu的指令)

2.所以改參考:
- 進階開發環境安裝
- mac安裝mysql的兩種方法(含配置)
- [SQL]在Mac與Ubuntu上安裝MySQL與登入設定
- Ubuntu 安裝和設定 MySQL

3.iHower教法:進階開發環境安裝
brew install mysql
不過出現錯誤訊息

4.只好下載檔案來安裝:
mac安裝mysql的兩種方法(含配置)
這邊先教用下載檔案來安裝,然後再用brew install mysql
這個方法成功了。

5.成功的步驟:
a. 先下載檔案: Download MySQL Community Server
b. 安裝
c. 有一個步驟會跳出暫時的密碼,要先記下來,之後再來改密碼。
d. 暫時密碼記好之後,要進入系統偏好設置
e. 開啟mysql服務:Start MySQL Server要一直打開,不然在Terminal打入任何跟mysql的指令時,就會出現ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
f. 啟動之後,還是沒辦法用mysql -u root -p 登入,還要改東西
g. 在Terminal的地方輸入:/usr/local/mysql/bin,然後用ls看有沒有mysql
h. 在Terminal的地方輸入:vim ~/.bash_profile來開啟這個檔案去增添新路徑
i. 增加mysql/bin的目錄,貼上:PATH=$PATH:/usr/local/mysql/bin
(vim的操作方法,參考:http://linux.vbird.org/linux_basic/0310vi.php)
(要編輯,先輸入"i",離開按"esc")
j.離開vi的方法:按esc之後,再按
:wq可以存檔離開。
quit!不存檔。
k. 在Terminal的地方輸入:source ~/.bash_profile
l. 現在你就可以通過mysql -u root -p登錄mysql了
m.會讓你輸入之前暫時的密碼
n. 在Terminal的地方輸入,可以修改密碼:SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

 
over 2 years ago

主題: jqueryui

https://blog.yorkxin.org/posts/2012/11/09/tips-on-loading-jquery-in-rails/
https://jqueryui.com/tabs/#vertical
http://job.achi.idv.tw/2010/02/05/jquery-ui-getting-started/
http://api.jqueryui.com/tabs/

主題:paperclip + devise

http://www.peoplecancode.com/en/tutorials/users-avatars-uploading-images-using-paperclip

  1. 登入以後,要先回答三個問題。

    • 可以來計算權重
    • 參考本來用來回答問題(issue-option-respond)的方法來設計回答的類別(category)。
    • 要分成哪幾類別?
      • 我想要找一個:
        • 經濟能力符合我的要求的對象
        • 價值觀與我接近的另一半
        • 跟我擁有類似興趣喜好的對象
        • 門當戶對的另一半
      • 哪一個是合適的戀愛對象?
        • 經濟能力符合我的要求
        • 雙方的價值觀接近
        • 兩個人的興趣喜好是相似的
        • 雙方是門當戶對的
      • 哪一個是合適的結婚對象?
        • 經濟能力符合我的要求
        • 雙方的價值觀接近
        • 兩個人的興趣喜好是相似的
        • 雙方是門當戶對的
      • 經濟能力:
        • 物質生活:出門一定要有汽車代步,或是有人接送?要結婚一定要有車又有房?懂得享受美食?
        • :?
        • :?
        • :?
      • 價值觀:
        • 生活態度:今日事、今日畢是很重要的?
        • 工作態度:可以為公司加班到深夜?穩定的工作比刺激的工作來的好?
        • 愛情觀:有備胎是很正常的?跟另一半一定要有共同的興趣?每個節慶都要過,是種浪漫的行為?
        • 金錢觀:有喜歡的東西,卡先刷了再說?
        • 兩性關係:婚前性行為是必要的?
        • 人際關係:我可以為朋友兩肋插刀?情和義,值千金?有人曾說我見色忘友?偶爾借點小錢給朋友沒啥關係?
      • 個人特質:
        • 生活作息:半夜兩點前絕對不上床睡覺?
        • 外型打扮:可以接受微禿?
        • 休閒活動:比起戶外活動,比較偏好待在室內?
        • 興趣喜好:從不聽流行歌曲?
        • 飲食習慣:
      • 家世背景:
        • 宗教信仰:雙方必須要有共同的信仰?
        • 原生家庭:父母親對我的影響很大?母親節跟父親節是一定要過的節日?我來自於單親家庭。父母交代的事情,我通常都會做到。我從不跟父母頂嘴。
        • 親子關係:
    • 要用複選(checkbox)?複選的權重可以比較多,不過應該要怎麼做成複選的選單,然後還要區分程度?
    • 還是單選(radiobox)?比較單純,計算應該做得來,不過對於使用者來說,可能很難從很多的選項中挑選一個最重要的出來。
    • 註冊跟登入後的頁面:devise wiki
    • JC對Devise的介紹:
    • 註冊後,要先回答權重問題: 註冊後的頁面
    • 登入後,每天要回答三個問題:這個的做法好像比較多
  2. 聊天系統

  3. 問題回答的時間 - :updated_at
    可以排序來配對
    剛好因為有option(後台建置時候會有created_at 和 updated_at)跟respond(用戶回答的時候會有created_at 和 updated_at)的關係。

  4. 新增功能:加入好友: Like
    Rais Tutorials應該有

  5. 新增功能:用戶頁面:
    Rais Tutorials應該有
    頁面設計

  6. 新增功能:部落格
    公布有哪些聯誼活動
    每個聯誼活動有自己的專屬問題

  7. relation

  8. 新增功能:篩選:好感度低於50%的,不會顯示。

  9. 問題publish:設定問題上限日期。

 
over 2 years ago

RDBMS

mysqld 是server程式
mysql 是client端

看Mysql灌在哪裡:
ps aux

進入mysql:
mysql -u root -p
-u:是user的意思
-p:是password的意思

看有多少資料庫:
show databases;
(要有;,才是一個完整的句子)

進入特定的資料庫:
use 某一個資料庫的名字;
會出現:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

就可以開始連線(?)

看特定table:
show tables;

看users的table內容:
select * from users

在RDBMS應該要打成SHOW TABLES;,都要大寫。
不過table name要按照原本的大小寫。SELECT * FROM users;
SQL就完全不分大小寫。

如何玩RAILS接到RDBMS去

00:05:00
做一個亂數產生器

100.times.do |i|
   user = User.create(:email => "#{i}@gamil.com", :password => i.to_s * 8)
   Order.create(:user_id, :status => rand(10), :total => rand(100), :update_at => Time.now + (rand(363*2*60*60*24)))
end
200.times.do |i|
   Order.create(:user_id, :status => rand(10), :total => rand(100)+1, :update_at => Time.now + (rand(363*2*60*60*24)))
end

user會有100個
order會有100 +200筆

抽出第一筆交易
SELECT * FROM orders LIMIT user 1;

10:40
join的動作
要先選一個起點,先從少的開始
101 user <=> 300 orders

SELECT * FROM users
先用*表示所有的東西
FROM後面接的是起點

建議起點後面接WHERE=>
SELECT * FROM users WHERE users.id = 1;

然後在選擇要的資料來取代 =>
SELECT id, email FROM users WHERE users.id =1 ;

JOIN有三種:
INNER JOIN:兩邊同時存在才列出來
LEFT JOIN :左邊有,右邊就會列出來,右邊如果沒有會出現NULL
RIGHT JOIN:右邊有,左邊就會列出來

INNER JOIN
SELECT users.id, users.email, orders.status FROM users INNER JOIN orders ON users.id = orders.user_id WHERE users.id =1;
ON後面是接連接詞:把兩個table接在一起的方法
如果有加連接詞的話,前面要指定table,本來的id要變成users.idemail要變成users.email,也可以把第二個表給拉出來 orders.status

LEFT JOIN
SELECT users.id, users.email, orders.status FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.id =1;

RIGHT JOIN
SELECT users.id, users.email, orders.status FROM users RIGHT JOIN orders ON users.id = orders.user_id WHERE users.id =1;

18:00
GROUP BY 加總:按什麼為key來做壓縮
SELECT users.id, users.email, orders.status FROM users INNER JOIN orders ON users.id = orders.user_id WHERE users.id IN (1,2) GROUP BY users.id;

22:00
rename是用AS,通常都放在table跟column的後面

25:00
接在最後面再過濾一次的HAVING語法
SELECT u.id AS u_id, u.email AS email, COUNT(o.id) AS count, o.id FROM users AS u RIGHT JOIN orders AS o ON u.id = o.user_id WHERE u.id < 10 GROUP BY u.id HAVING count > 3;

26:00
LIMIT : 只想顯示兩筆
OFFSET :前面略過一筆

SELECT u.id AS u_id, u.email AS email, COUNT(o.id) AS count, o.id FROM users AS u RIGHT JOIN orders AS o ON u.id = o.user_id WHERE u.id < 10 GROUP BY u.id HAVING count > 3 LIMIT 2;

SELECT u.id AS u_id, u.email AS email, COUNT(o.id) AS count, o.id FROM users AS u RIGHT JOIN orders AS o ON u.id = o.user_id WHERE u.id < 10 GROUP BY u.id HAVING count > 3 LIMIT 2 OFFSET 1;

LIMIT 30 OFFSET 30
每頁30筆,第二頁開始會漏掉前面的30筆(從31開始)

28:00
SUB SELECT:把一個select的結果,塞到另一個select去做過濾式

(1)SELECT u.id, COUNT(o.id) AS count FROM users AS u INNER JOIN orders AS o ON u.id = o.user_id GROUP BY u.id HAVING count >3;

可以用在INNER JOIN
SELECT * FROM tablenameA INNER JOIN tablenameB
可以把(1)放到tablenameB去

SELECT u.id, u1.id FROM users AS u INNER JOIN (SELECT u.id, COUNT(o.id) AS count FROM users AS u INNER JOIN orders AS o ON u.id = o.user_id GROUP BY u.id HAVING count >3) AS u1 ON u.id = u1.id

(1)也可以用在WHERE
SELECT * FROM users AS u WHERE id IN (SELECT o.user_id FROM orders AS o WHERE o.status >3 GROUP BY o.user_id);

改成ruby的打法
User.select('*').from('users AS u').where('id IN (SELECT o.user_id FROM orders AS o WHERE o.status > 3 GROUP BY o.user_id)')

00:41:00
如何製作報表:GROUP BY的應用 - 用日期去做壓縮

SELECT u.id, o.id. o.status, o.updated_at FROM users AS u INNER JOIN orders AS o ON o.user_id = u.id;

mysql DATE_FORMAT
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

把%d改成01,就可以改成月報表
SELECT u.id, o.id. o.status, DATE_FORMAT(o.updated_at, "%Y-%m-01"), COUNT(o.id) AS count FROM users AS u INNER JOIN orders AS o ON o.user_id = u.id GROUP BY DATE_FORMAT(o.update_at, %Y-%m-01);

計算最大值,最小值,平均,總和
SELECT u.id, o.id. o.status, DATE_FORMAT(o.updated_at, "%Y-%m-01"), MAX(o.status) AS max, MIN(o.status) AS min, COUNT(o.id) AS count, AVG(o.status) AS avg, SUM(o.status) AS sum FROM users AS u INNER JOIN orders AS o ON o.user_id = u.id GROUP BY DATE_FORMAT(o.update_at, %Y-%m-01);

00:52:46
不建議每次都用SELECT:佔用table,會做table lock的動作,會影響到別人的使用狀況,別人就不能修改。

不可能每次都重跑,所以要在rails 把暫存表做出來
rails g migration add_statics

def change
  create_table :statics, :id => false do |t|
     t.integer :kind, :limit => 1, :default => 0, :null => false
     t.timestamp :time_at, :null => false
     t.integer :max
     t.integer :min
     t.integer :count
     t.integer :avg
     t.integer :sum
  end
  add_index :statics, [:kind, :time_at], :unique => true
end

rake db:migrate
就做好table了

如何把暫存表一次存光光
公式
INSERT INTO Table { Column1, Column2} VALUES { Value1, Value2}, { Value1, Value2}

不用加VALUES

INSERT INTO statics ( kind, time_at, max, min, count, avg ,sum) (SELECT 3 AS kind, DATE_FORMAT(o.updated_at, "%Y-01-01") AS time_at, MAX(o.status) AS max, MIN(o.status) AS min, COUNT(o.id) AS count, AVG(o.status) AS avg, SUM(o.status) AS sum FROM users AS u INNER JOIN orders AS o ON o.user_id = u.id GROUP BY DATE_FORMAT(o.updated_at, "%Y-01-01"))

結果:
Query OK, 25 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

改成 3 As kind 跟 "%Y-01-01" 就是年報表
改成 2 As kind 跟 "%Y-%m-01" 就是月報表
改成 1 As kind 跟 "%Y-%m-%d" 就是日報表

語法其實是有問題的,因為有index,所以不能用第二次
會出現:
ERROR 1062(23000): Duplicate entry '' for key

所以要用mysql insert update
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a) +VALUES(b);

mysql insert on duplicate update value
ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c)

所以應該要改成
INSERT INTO statics ( kind, time_at, max, min, count, avg ,sum) (SELECT 3 AS kind, DATE_FORMAT(o.updated_at, "%Y-01-01") AS time_at, MAX(o.status) AS max, MIN(o.status) AS min, COUNT(o.id) AS count, AVG(o.status) AS avg, SUM(o.status) AS sum FROM users AS u INNER JOIN orders AS o ON o.user_id = u.id GROUP BY DATE_FORMAT(o.updated_at, "%Y-01-01")) ON DUPLICATE KEY UPDATE max=VALUES(max), min=VALUE(min);
把要改的column寫上去,這邊只放兩個為例子
可以做成一個按鈕,讓你一直去更新資料

1:09:00
rails下如何去拿剛剛的資料
做statics.rb

Class Static < ActiveRecords::BASE

  KIND = [["0", 0],['1','Day'],['2','Month'],['3','Year']]

end


Static.where('kind = 3').each do |s|
 puts "#{s.time_at.strftime('Y%-%m-%d')} : sum : #{s.sum}"
end ; true

會出現
2015-01-01 : sum : 99
2016-01-01 : sum : 684
2017-01-01 : sum : 502

1:12:00
D3.js
BASIC CHARTS
HIGHCHARTS

1:14:00; end
用rails 塞資料,跟mysql 塞資料,速度的差別

t = Time.now.to_f ; 1000.times do |i| Static.create(:kind =>100, :time_at => Time.now + i ); end ; puts Time.now.to_f - t

跑一千筆花了七秒

mysql
ans = []
1000.times do ans << "(101, '#{Time.now + i). strftime('%Y-%m-%d %H:%M:%S')}'')" ; end ; true

ans就做好了
ans.join(',')然後就會變成我們要的sql
rails下可以直接執行sql
ActiveRecord::Base.connection.execute("INSERT INTO 'statics' ('kind', 'time_at') VALUES "#{ans.join(',')}")
跑一千筆只花了16.0ms

驗證數字是不是正確
Static.where('kind = 100').count
Static.where('kind = 101').count
都是1000筆

Static.where('kind' = 2).each do |s|
puts "#{s.time_at} : #{s.sum}"
end; true

就可以叫出來月的資料,然後拿去畫圖 HIGHCHARTS

1:24:00
把GROUP好的資料塞到另一個table去做cache
UPDATE FROM xxx WHERE id IN (SELECT)
UPDATE FROM xxx WHERE id IN (1,2,3,4,5)
DELETE FROM xxx WHERE id IN (SELECT)

 
about 1 year ago

index 還沒有裝impressionist

show 還沒有裝impressionist

DoctorsController 加上 impressionist only: [:show, :index]

index page:

show page: (Doctor_controller show method 還沒裝上 impressionist:impressionist(@doctor))
螢幕快照 2017-05-29 上午11.41.39

Doctor_controller show method 裝上 impressionist:impressionist(@doctor)
螢幕快照 2017-05-29 上午11.43.53

index page加上<%= doctor.impressionist_count %> 人瀏覽
螢幕快照 2017-05-29 上午11.45.14

show page加上<%= @doctor.impressionist_count %> 人瀏覽
螢幕快照 2017-05-29 上午11.45.59

 
about 1 year ago

counter_cache
https://rocodev.gitbooks.io/rails-102/content/chapter1-mvc/m/counter-cache.html

[Rails] Counter Cache用法
http://anxgang.logdown.com/posts/732495-rails-counter-cache-usage

計數快取 Counter Cache
https://ihower.tw/rails/performance.html

範例一:一個醫生有幾篇POST
Post還沒有裝Counter Cache

Post裝上Counter Cache
(Post belongs_to :doctor, counter_cache: :post_count)

這兩行不見了:
(0.2ms) SELECT COUNT() FROM posts WHERE posts.doctor_id = 2
(0.2ms) SELECT COUNT(
) FROM posts WHERE posts.doctor_id = 4

範例二:一篇POST有幾則回覆
Comment還沒有裝Counter Cache

Comment裝上Counter Cache
(Comment belongs_to :post, counter_cache: :comment_count)

這五行不見了:
(0.3ms) SELECT COUNT() FROM posts WHERE posts.doctor_id = 2
(0.4ms) SELECT COUNT(
) FROM comments WHERE comments.post_id = 21
(0.4ms) SELECT COUNT() FROM comments WHERE comments.post_id = 20
(0.3ms) SELECT COUNT(
) FROM comments WHERE comments.post_id = 18
(0.3ms) SELECT COUNT() FROM comments WHERE comments.post_id = 15
(0.3ms) SELECT COUNT(
) FROM comments WHERE comments.post_id = 14

 
over 1 year ago

參考資料:
Understanding Scope in Ruby

參考資料:
純.rb檔放置地方
參考:RailsFun.tw 新手教學 day3 HD:大概在53分鐘的地方
a. XX.rb檔案做在project_name/lib
b. 剛做好的時候,可以用ruby XX.rb
c. 然後到config/application.rbrequirerequire "XX" (不要有.rb)
d. require之後,可以用rails c試看看,先require "XX",然後再XX.run (我是設定self.run)
e. 還可以到view去使用 <%= XX.run %>,不過server記得要重開

terminal : rails c,大小寫有差 ; 有時候出現false是因為已經loaded ; 有時候不知道為什麼不用require
require 'mechanize' #如果是在cloud9

require 'Mechanize' #如果是在Terminal
terminal: google的頁面太複雜了,很難找到我要的連結,而且又有data-href這種東西,所以改用yahoo來搜尋。
agent = Mechanize.new
page = agent.get('https://tw.yahoo.com/')

google 跟 yahoo的表單有點不太一樣:
Mechanize examples
Getting Started With Mechanize

google_form = page.form('f')
google_form.q = 'ruby mechanize'
terminal: 如果是google首頁,會看到首頁的表單-forms: name是 f ; fields裡:name:q,value是空的
=>
{forms
  #<Mechanize::Form

   {name "f"}
   {method "GET"}
   {action "/search"}
   {fields
    [hidden:0x3fc7f160d9f4 type: hidden name: ie value: Big5]
    [hidden:0x3fc7f160d1fc type: hidden name: hl value: zh-TW]
    [hidden:0x3fc7f160cd4c type: hidden name: source value: hp]
    [hidden:0x3fc7f160c810 type: hidden name: biw value: ]
    [hidden:0x3fc7f160c2fc type: hidden name: bih value: ]
    [text:0x3fc7f19edad8 type:  name: q value: ]
    [hidden:0x3fc7f1995fcc type: hidden name: gbv value: 1]}
   {radiobuttons}
   {checkboxes}
   {file_uploads}
   {buttons
    [submit:0x3fc7f19ed6dc type: submit name: btnG value: Google 搜尋]
    [submit:0x3fc7f19ecca0 type: submit name: btnI value: 好手氣]}>}
terminal: 如果是yahoo首頁,會看到首頁的表單-forms: name是 nil ; fields裡:name:p,value是空的
=>
{forms
  #<Mechanize::Form

   {name nil}
   {method "GET"}
   {action "https://tw.search.yahoo.com/search"}
   {fields
    [hidden:0x3fc7f1884408 type: hidden name: fr value: yfp-search-sb]
    [text:0x3fc7f18810f0 type: text name: p value: ]}
   {radiobuttons}
   {checkboxes}
   {file_uploads}
   {buttons [submit:0x3fc7f187dedc type: submit name:  value: 搜尋]}>}>
terminal: 用mechanize的.form method 去找到要填的表單。
google_form = page.form('f') #找到這個叫做'f'的form

yahoo_form = page.form #找到這個沒有名字的form
terminal: 把q跟p設定成要搜尋的字串,console裡面會看到,value會變成我們送出的字串
#把q設定成要搜尋的字串  - 林正宗婦產科診所。

google_form.q = '林正宗婦產科診所'
yahoo_form.p = '林正宗婦產科診所'

=> #<Mechanize::Form

 {name nil}
 {method "GET"}
 {action "https://tw.search.yahoo.com/search"}
 {fields
  [hidden:0x3fc7f1884408 type: hidden name: fr value: yfp-search-sb]
  [text:0x3fc7f18810f0 type: text name: p value: 林正宗婦產科診所]}
 {radiobuttons}
 {checkboxes}
 {file_uploads}
 {buttons [submit:0x3fc7f187dedc type: submit name:  value: 搜尋]}>
terminal: 按下送出,送出表單
  page = agent.submit(yahoo_form, yahoo_form.buttons.first)
terminal: url_list = []放錯地方,就一輩子跑不出來了
url_list = []
page.links.each do |link|
  url = link if  link.text.start_with?("林正宗") && link.href.exclude?("yahoo")
  url_list << url
  url_list.delete_if {|url| url.nil?}
end; true
錯誤的步驟:url_list = []放錯地方,就一輩子跑不出來了
rails c

require 'Mechanize'
agent = Mechanize.new

page = agent.get('https://tw.yahoo.com/')
yahoo_form = page.form
url_list = []

@hospitals = Hospital.all
@hospitals[0,500].each do |h|
  
  name = h.name
  yahoo_form.p = name
  page = agent.submit(yahoo_form, yahoo_form.buttons.first)
  
  page.links.each do |link|
    url = link if link.text.start_with?(name[0,4]) && link.href !~ /(nhi|tylife|makesop|tel038|medicallab|deefeed|doctorknow|youtube|pchome|bamahome|319papago|doctor.tw|finddoc|yahoo|doctor01|ezlife|5151|facebook|ipeen|104hc|verywed|bizpo|1111|twypage|tw16|google|businessweekly|goo|wakema|pixnet|iyp|xuite|sina|wikia|nownews|commonhealth|healthnews|socailiao|web393|wikipedia|mywoo|104|mohw|lifego|ypgo|yes123|blogspot|16won|4a0b|yelp|twspecial|fuly|healthmedia|shsh|qmap|zhupiter|ilist|dmbom|yam|lecoin|iask|shopcool|datagovtw|518|pixstu|iguang|urmap|yeahday|cmoremap|lookup|hicare|itwyp|tut|medicaltravel|dadupo|tophealthclinics|medicaltravel|roodo|itel|salary|web66|hotfrog|ibeta|abiz|olc|freeweb|ck101|1673|121|freelist|taiwanschoolnet|tw66|wordpress|babyhome|fashionguide|gothejob|chcg|lienchiangcounty4|likeboy|fescomail|dradvice|hcl)/
    url_list << url.href if !url.nil?
    url_list.uniq!
  end
  @hospital = Hospital.find_by(:name => name)
  if url_list.empty?
    @hospital.website = "N/A"
    @hospital.save
  else  
    @hospital.website = url_list[0]
    @hospital.save
  end
end; true

Understanding Scope in Ruby

完整的code 正確的步驟:url_list = []要放到迴圈裡面; 把nhi留下來,taichung-clinic
rails c

require 'Mechanize'
agent = Mechanize.new

page = agent.get('https://tw.yahoo.com/')
yahoo_form = page.form


@hospitals = Hospital.all
@hospitals[1000..1015].each do |h|
  url_list = []
  name = h.name
  yahoo_form.p = name
  page = agent.submit(yahoo_form, yahoo_form.buttons.first)
  
  page.links.each do |link|
    url = link if link.text.start_with?(name[0,4]) && link.href !~ /(thehealthdb|ihao|tradelist|lifeshow|1637.tw|2012tt|aiwiki|fcu|tylife|makesop|tel038|medicallab|deefeed|doctorknow|youtube|pchome|bamahome|319papago|doctor.tw|finddoc|yahoo|doctor01|ezlife|5151|facebook|ipeen|104hc|verywed|bizpo|1111|twypage|tw16|google|businessweekly|goo|wakema|pixnet|iyp|xuite|sina|wikia|nownews|commonhealth|healthnews|socailiao|web393|wikipedia|mywoo|104|mohw|lifego|ypgo|yes123|blogspot|16won|4a0b|yelp|twspecial|fuly|healthmedia|shsh|qmap|zhupiter|ilist|dmbom|yam|lecoin|iask|shopcool|datagovtw|518|pixstu|iguang|urmap|yeahday|cmoremap|lookup|hicare|itwyp|tut|medicaltravel|dadupo|tophealthclinics|medicaltravel|roodo|itel|salary|web66|hotfrog|ibeta|abiz|olc|freeweb|ck101|1673|121|freelist|taiwanschoolnet|tw66|wordpress|babyhome|fashionguide|gothejob|chcg|lienchiangcounty4|likeboy|fescomail|dradvice|hcl)/
    url_list << url.href if !url.nil?
    url_list.uniq!
  end
  @hospital = Hospital.find_by(:name => name)
  if url_list.empty?
    @hospital.website = "N/A"
    @hospital.save
  else  
    @hospital.website = url_list[0]
    @hospital.save
  end
end; true
比較醜的寫法
url = link if link.text.start_with?(name[0,4]) && link.href.exclude?("yahoo") && link.href.exclude?("doctor01") && link.href.exclude?("ezlife") && link.href.exclude?("5151") && link.href.exclude?("facebook") && link.href.exclude?("ipeen") && link.href.exclude?("104hc") && link.href.exclude?("verywed") && link.href.exclude?("bizpo") && link.href.exclude?("1111") && link.href.exclude?("twypage") && link.href.exclude?("tw16") && link.href.exclude?("google") && link.href.exclude?("businessweekly") && link.href.exclude?("goo") && link.href.exclude?("wakema") && link.href.exclude?("pixnet") && link.href.exclude?("iyp") && link.href.exclude?("xuite")&& link.href.exclude?("sina") && link.href.exclude?("wikia") && link.href.exclude?("nownews") && link.href.exclude?("commonhealth") && link.href.exclude?("nhi") && link.href.exclude?("healthnews") && link.href.exclude?("socailiao") && link.href.exclude?("web393") && link.href.exclude?("wikipedia") && link.href.exclude?("mywoo") && link.href.exclude?("104") && link.href.exclude?("mohw") && link.href.exclude?("lifego") && link.href.exclude?("ypgo") && link.href.exclude?("yes123") && link.href.exclude?("blogspot") && link.href.exclude?("16won") && link.href.exclude?("4a0b") && link.href.exclude?("yelp") && link.href.exclude?("twspecial") && link.href.exclude?("fuly") && link.href.exclude?("healthmedia") && link.href.exclude?("shsh") && link.href.exclude?("qmap")
比較漂亮的寫法:篩掉不想要的網址
url = link if link.text.start_with?(name[0,4]) && link.href !~ /(nhi|tylife|makesop|tel038|medicallab|deefeed|doctorknow|youtube|pchome|bamahome|319papago|doctor.tw|finddoc|yahoo|doctor01|ezlife|5151|facebook|ipeen|104hc|verywed|bizpo|1111|twypage|tw16|google|businessweekly|goo|wakema|pixnet|iyp|xuite|sina|wikia|nownews|commonhealth|healthnews|socailiao|web393|wikipedia|mywoo|104|mohw|lifego|ypgo|yes123|blogspot|16won|4a0b|yelp|twspecial|fuly|healthmedia|shsh|qmap|zhupiter|ilist|dmbom|yam|lecoin|iask|shopcool|datagovtw|518|pixstu|iguang|urmap|yeahday|cmoremap|lookup|hicare|itwyp|tut|medicaltravel|dadupo|tophealthclinics|medicaltravel|roodo|itel|salary|web66|hotfrog|ibeta|abiz|olc|freeweb|ck101|1673|121|freelist|taiwanschoolnet|tw66|wordpress|babyhome|fashionguide|gothejob|chcg|lienchiangcounty4|likeboy|fescomail|dradvice|hcl)/    

一次跑太多比就會出現這種情況:Mechanize::ResponseCodeError: 999
Yahoo好像隔一個小時,就可以再爬一次,應該一次可以爬五佰筆資料。

 
over 1 year ago

從 郵局的網站 找到全省縣市鄉鎮的資料
衛福部找到登記在案的婦產科,跟用google找到的有很大的誤差,應該是很多婦產科其實是沒有自己的網站吧。

放在Excel的檔案裡面(沒辦法,跟Numbers不熟,用起來不順手。)
所以要找一個可以讀取excel的gem,
可是好像都不是很好用,最後找到spreadsheet
剛好可以滿足我的需求。

How do I read the content of an Excel spreadsheet using Ruby?

How do I read the content of an Excel spreadsheet using Ruby?
require 'spreadsheet'    
book = Spreadsheet.open('myexcel.xls')
sheet1 = book.worksheet('Sheet1') # can use an index or worksheet name

sheet1.each do |row|
  break if row[0].nil? # if first cell empty

  puts row.join(',') # looks like it calls "to_s" on each cell's Value

end

上面這招就很夠用了。

幾個注意的點:
1. Numbers要用輸出,能夠變成 .xls 跟 .xlsx,兩種檔案格式。可是spreadsheet gem只能讀.xls
2. irb跟cloud9(rails c)裡面,可以用require "spreadsheet"
3. 可是在rails c 裡面,要用require "Spreadsheet",記得要大寫
4. 好像還有其他解法:
Gem loads in irb but not console
Gem available in irb but not rails console
How to use a local gem in console with bundled environment
rails console require nokogiri return false (but works in irb)
5. 讀取excel還挺麻煩的,目前是覺得把他弄成一列一列的好像比較簡單,row。因為好像沒看到column的用法。所以都要把檔案調整一下。
6. numbers or excel裡面的公式,要記得調成。不然spreadsheet在處理的時候,會跑出來Formula
7. cloud9,如果資料庫設成mysql,每次進入rails c 之前要記得先開啟mysql-ctl start

rails c 試用暖身看看
require 'Spreadsheet'    
book = Spreadsheet.open('hospitals.xls')
sheet1 = book.worksheet('婦產科')
sheet1.each do |row|
  break if row[0].nil? # if first cell empty

  puts row.join(',') # looks like it calls "to_s" on each cell's Value

end

處理城市的資料:

處理城市
book = Spreadsheet.open('hospitals.xls')
sheet1 = book.worksheet('郵遞區號 (3)')

sheet1.each do |row|
  break if row[0].nil? # if first cell empty

  puts row.join(',') # looks like it calls "to_s" on each cell's Value

end

#把城市(City)的資料加到array裡面

city_name = []
sheet1.each do |row|
  break if row[0].nil? 
  city_name << row[1]
end;true

city_name.each do |name|
    @city = City.new
    @city.name = name
    @city.save
end

處理鄉鎮的資料:

處理鄉鎮的資料
book = Spreadsheet.open('hospitals.xls')
sheet1 = book.worksheet('郵遞區號 (3)')

#把鄉鎮的資料加到array裡面

raw_data =[]
sheet1.each do |row|
  break if row[0].nil?
  row.each do |data|
    raw_data << data
  end
end

#把nil的資料刪掉

list_data = raw_data.delete_if{|i| i.nil?}
#或是list_data = raw_data.compact!


#把數字的資料刪掉

final_data = list_data.delete_if{|i| i.is_a?(Numeric)}

#把鄉鎮按照城市別分類

temp = []
final_data.each do |str|
  case str
  when "臺北市" , "基隆市" , "新北市", "宜蘭縣", "新竹市", "新竹縣", "桃園市", "苗栗縣", "臺中市", "彰化縣", "南投縣", "嘉義市", "嘉義縣", "雲林縣", "臺南市", "高雄市", "南海諸島", "澎湖縣", "屏東縣", "臺東縣","花蓮縣", "金門縣", "連江縣"
    temp << []
  else
    temp[-1] << str
  end
end



#測試

i = 0
begin
temp[i].each do |p|
 puts p + "city_id=" + (i+1).to_s
 end
i += 1
end while i < 23

#存到區域(District)的資料庫去

i = 0
begin
temp[i].each do |p|
    @district = District.new
    @district.name = p
    @district.city_id = (i+1).to_s
    @district.save
 end
i += 1
end while i < 23


處理醫院:

rails c 把醫院名字、縣市、區域存進去資料庫
require 'Spreadsheet'    
book = Spreadsheet.open('hospitals.xls')
sheet1 = book.worksheet('婦產科')

#確認資料

sheet1.each do |row|
    break if row[0].nil? # if first cell empty

 puts row[1] + ":" +row[8].to_i.to_s + ":" + row[9].to_i.to_s
 end

#存入資料庫

sheet1.each do |row|
    break if row[0].nil? # if first cell empty

    @hospital = Hospital.new
    @hospital.name = row[1]
    @hospital.city_id = row[8].to_i
    @hospital.district_id = row[9].to_i
    @hospital.save
end


其他Spreadsheet gem的參考資料:
Parse excel file in python and ruby
Getting Started with Spreadsheet

 
over 1 year ago

nokogiri gem去爬google太麻煩了。
通常網頁看到的html tag,好像跟實際上的不太一樣。
所以用css selectorxpath,好像都會找不到,不知道是不是google 的搜尋頁面才會發生這種情況。

nokogiri雖然有辦法找到第一頁到第十頁的url,應該可以一頁一頁的爬下來。
不過,沒辦法找到下一頁的url,所以變成要連到第十一頁之後,再去找後面十頁的url?感覺有點蠢?
所以花了很多時間用css selector去找google的next page,可是都找不到。

mechanize gem好用很多,一下子就找到了next page裡面的url。
這樣在第一頁的時候,可以直接找到下一頁的url,用mechanize的.click的功能,就能跑到第二頁了。
第二頁爬完,還可以找到下一頁的URL,用mechanize的.click的功能,就又能跑到第三頁了。
大功告成。

不過,後來還是被google鎖IP了。
如JC大所說,一定會被鎖,可能要用curl 或是 RestClient
參考資料:用nokogiri 爬google 搜尋資料

另外,花了很多時間去試while怎麼用,比想像中難很多。
網路上可以找到的範例都是用數字去跑,都太簡單了,而我沒辦法舉一反三,只好一直試。
begin end while condition當符合condition(true)的時候,會一直跑。
我還以為這樣的寫法,是指:當condition符合的時候,要end勒...害我一直試。

search.rb
require 'mechanize'

class Search
  def self.run
  
    mechanize = Mechanize.new
    page = mechanize.get('https://www.google.com.tw/search?q=%E5%A9%A6%E7%94%A2%E7%A7%91')
    
    #google_url = "https://www.google.com.tw"

    #basic_url = "/search?q=%E5%A9%A6%E7%94%A2%E7%A7%91"

    #puts page.uri

    
    next_page = nil
    next_page = !page.link_with(text: '下一頁').nil? ? page.link_with(text: '下一頁') : nil
    
    counter = 1
    begin
    new_page = next_page.click if !next_page.nil?
      puts next_page.href
      new_page.css('//h3').each do |h|
        puts h.text
      end
      next_page = !new_page.link_with(text: '下一頁').nil? ? new_page.link_with(text: '下一頁') : nil
      puts next_page.href if !next_page.nil?
      new_page = !next_page.nil? ? next_page.click : nil
      counter += 1 if !next_page.nil?
    end while !next_page.nil?
  end
end
search.rb 記得要先require
require 'mechanize'
search.rb 必要的兩個步驟
mechanize = Mechanize.new
page = mechanize.get('https://www.google.com.tw/search?q=%E5%A9%A6%E7%94%A2%E7%A7%91')

('https://www.google.com.tw/search?q=%E5%A9%A6%E7%94%A2%E7%A7%91')
好像沒辦法改成
goole_url = "https://www.google.com.tw/search?q=%E5%A9%A6%E7%94%A2%E7%A7%91"
然後使用page = mechanize.get("google_url")

這邊有幾個mechenize的用法
link_with:找到符合條件的,new_page.link_with(text: '下一頁')
page.uri: 把頁面的url叫出來。
next_page.click:連結頁面。

Mechanize 參考資料:
Nokogiri 問題求救
The Mechanize Gem
Writing a Web Crawler

Getting Started With Mechanize

這一段還滿有趣的
agent = Mechanize.new
page = agent.get('http://google.com/')
google_form = page.form('f')
google_form.q = 'ruby mechanize'
page = agent.submit(google_form)
pp page

While 參考資料:
using while loop with nokogiri to navigate to next page
Ruby的網頁爬蟲open-uri和nokogiri庫