用 Python 抓取 Ubike 開放資料

本次練習,要實作一個 ETL (Extract-Transform-Load) web service,然後分析 Ubike 一天的使用情況。

何謂 ETL?簡單的說,就是去抓別人的資料,經過整理,然後丟進自己的資料庫。透過這樣的做法,可以把資料整理成想要的形式,方便後續的分析以及處理。

實作分為兩個部分:

  1. 使用 Python 抓取 Ubike 資料,存放在 MySQL 資料庫中
  2. 利用 Django 網頁框架來呈現資料庫的資料

Ubike 開放資料

Ubike 的開放資料可以在台北市政府開放資料平台找到,連結在此。網站上的說明已經很清楚了,透過以下連結可以拿到經 gz 壓縮的 JSON 檔案 (有點不解為何要壓縮…)

http://data.taipei/youbike

下載完後,在 Windows 系統下解壓縮,會發現檔案損毀,無法開啟。 將檔案的附檔名改為 txt 後,居然就順利打開了@@,雖然網站上面寫:

部分瀏覽器如 Chrome 下載後會自動解壓縮,請留意!

但還是很不解,我平時用 Chrome 下載壓縮檔都不會自動解壓了,為啥這會自動勒?不管啦,反正在實作的時候是在 Linux 環境下,不會受到影響


Ubike 資料說明

每筆資料有以下 14 個欄位,其中有部分是不會變動的資料,如 sno、sna、sarea 等等,待會放資料庫時會獨立出來放

sno:站點代號
sna:場站名稱(中文)
tot:場站總停車格
sbi:場站目前車輛數量
sarea:場站區域(中文)
mday:資料更新時間
lat:緯度
lng:經度
ar:地(中文)
sareaen:場站區域(英文)
snaen:場站名稱(英文)
aren:地址(英文)
bemp:空位數量
act:全站禁用狀態

接著用 json viewer 來看看下載下來的 JSON 檔案,可以看到資料被包在 retVal 裡面。

ubike1 - 用 Python 抓取 Ubike 開放資料

用 Python 抓取資料

使用 urllib 來抓檔案,透過 gzip 解壓縮後,將 JSON 資料讀取出來

#!/usr/bin/env python

import urllib
import gzip
import json

url = "http://data.taipei/youbike"
urllib.urlretrieve(url, "data.gz")
f = gzip.open("data.gz", 'r')
jdata = f.read()
f.close()
data = json.loads(jdata)
for key,value in data["retVal"].iteritems():
    sno = value["sno"]
    sna = value["sna"]
    print "NO." + sno + " " + sna
[2018/06 更新] Python 3 的程式碼請參考這裡:https://colab.research.google.com/drive/1jQprW8RIsA_SEFpBm6POF8DgZ1XZ9YH6

這邊要注意的是,load 出來的 data 是 dict type,所以用 iteritems 來抓值 可以看到結果如下:

python getBike.py
NO.0134 捷運芝山站(2號出口)
NO.0135 捷運石牌站(2號出口)
NO.0136 國立臺北護理健康大學
NO.0137 國防大學
NO.0039 南港世貿公園
NO.0038 臺灣師範大學(圖書館)
...

安裝 MySQL 資料庫

如果已經安裝過 MySQL,可以跳過此部分。 這邊為了要加快速度,採用 phpMyAdmin 圖形介面來進行資料庫操作,因此直接安裝LAMP,步驟如下:

sudo apt-get install apache2
sudo apt-get install mysql-server
sudo apt-get install php5 libapache2-mod-php5
sudo apt-get install phpmyadmin
sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-enabled/phpmyadmin.conf
sudo /etc/init.d/apache2 restart

安裝完後,可以透過以下路徑看到管理介面

http://YOUR_IP/phpmyadmin

資料庫設計

將固定資料放在一起 (Table info),會變動的如目前數量、時間等等放一起 (Table data),然後利用 foreign key 做關聯 另外需要注意的是中文問題,這裡我使用 UTF-8 儲存,後面的 Python 程式也是設定 UTF-8 編碼

資料表結構 info

CREATE TABLE IF NOT EXISTS `info` (
 `sno` int(4) NOT NULL,
 `sna` varchar(100) CHARACTER SET utf8 NOT NULL,
 `sarea` varchar(20) CHARACTER SET utf8 NOT NULL,
 `lat` varchar(20) CHARACTER SET utf8 NOT NULL,
 `lng` varchar(20) CHARACTER SET utf8 NOT NULL,
 `ar` varchar(100) CHARACTER SET utf8 NOT NULL,
 `sareaen` varchar(20) CHARACTER SET utf8 NOT NULL,
 `snaen` varchar(100) CHARACTER SET utf8 NOT NULL,
 `aren` varchar(100) CHARACTER SET utf8 NOT NULL,
 PRIMARY KEY (`sno`),
 KEY `sno` (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

資料表結構 data

CREATE TABLE IF NOT EXISTS `data` (
 `index` bigint(20) NOT NULL AUTO_INCREMENT,
 `sno` int(4) NOT NULL,
 `tot` int(3) NOT NULL,
 `sbi` int(3) NOT NULL,
 `bemp` int(3) NOT NULL,
 `act` int(1) NOT NULL,
 `utime` datetime NOT NULL,
 PRIMARY KEY (`index`),
 KEY `sno` (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

由於我直接透過 phpMyAdmin 建資料庫,上面的 SQL 是由系統產生,僅供參考


在 phpMyAdmin 上設定 foreign key

進入 結構 -> 關聯清單,就可以看到以下畫面

ubike2 - 用 Python 抓取 Ubike 開放資料

將 data.sno 與 info.sno 關聯,並將型態設為 RESTRICT


把資料撈進資料庫

前面我們已經可以成功取得資料,接著利用 Python 的 MySQLdb 這個套件連接 MySQL,把資料丟進去。 透過 pip 安裝。

$ pip install MySQL-python

這裡寫兩隻程式,一隻(getInfo.py)是抓固定資料(只會執行一次),另一隻(getData.py)抓變動資料(每分鐘執行一次)

getInfo.py 程式碼

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import urllib
import gzip
import json
import MySQLdb

url = "http://data.taipei/youbike"
print "downloading with urllib"
urllib.urlretrieve(url, "data.gz")
f = gzip.open('data.gz', 'r')
jdata = f.read()
f.close()
data = json.loads(jdata)
conn = MySQLdb.connect(host="localhost", user="root", passwd="123456", db="bike")
c = conn.cursor()
conn.set_character_set('utf8')

for key,value in data["retVal"].iteritems():
    sno = value["sno"]
    sna = value["sna"]
    sarea = value["sarea"]
    lat = value["lat"]
    lng = value["lng"]
    ar = value["ar"]
    sareaen = value["sareaen"]
    snaen = value["snaen"]
    aren = value["aren"]

sql = "INSERT INTO info(sno,sna,sarea,lat,lng,ar,sareaen,snaen,aren) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
try:
    c.execute(sql,(sno,sna,sarea,lat,lng,ar,sareaen,snaen,aren) )
    conn.commit()
except MySQLdb.Error,e:
    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
conn.close()

getData.py 程式碼

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import urllib
import gzip
import json
import MySQLdb
from datetime import datetime

url = "http://data.taipei/youbike"
#print "downloading with urllib"
urllib.urlretrieve(url, "data.gz")
f = gzip.open('data.gz', 'r')
jdata = f.read()
f.close()
data = json.loads(jdata)
conn = MySQLdb.connect(host="localhost", user="root", passwd="123456", db="bike")
c = conn.cursor()
conn.set_character_set('utf8')

for key,value in data["retVal"].iteritems():
    sno = value["sno"]
    tot = value["tot"]
    sbi = value["sbi"]
    bemp = value["bemp"]
    act = value["act"]

sql = "INSERT INTO data(sno,tot,sbi,bemp,act,utime) VALUES(%s,%s,%s,%s,%s,%s)"
try:
    c.execute(sql,(sno,tot,sbi,bemp,act,datetime.now()))
    conn.commit()
except MySQLdb.Error,e:
    print "Mysql Error %d: %s" % (e.args[0], e.args[1])

sql = "DELETE FROM data WHERE TO_DAYS(NOW()) – TO_DAYS(utime) > 1"
try:
    c.execute(sql)
    conn.commit()
except MySQLdb.Error,e:
    print "Mysql Error %d: %s" % (e.args[0], e.args[1])

conn.close()

因為只記錄一天的資料,所以將大於一天的資料清掉

DELETE FROM data WHERE TO_DAYS(NOW()) - TO_DAYS(utime) > 1

使用 crontab 讓程式每分鐘執行一次

cron 是在 root 下執行,路徑與目前使用者不同。

建立一個 Script,裏頭會切換到抓資料程式的位置,並執行他。

vim autoGet.sh
#!/bin/bash
cd /home/user
python getData.py

打開 crontab,加入一條規則

crontab -e
*/1 * * * * /bin/bash /home/user/autoGet.sh

沒問題的話,就可以等著收資料囉


繼續閱讀

用 Python抓取 Ubike 開放資料 (顯示篇)

可以參考我的 Source code Github source code


參考資料

Jerry
Jerry

樂於分享的軟體工程師,曾在新創與大型科技公司實習,獲得黑客松競賽冠軍,擔任資安研討會講者。長期熱衷於資訊安全、雲端服務、網路行銷等領域,希望將科技知識分享給更多人。內容轉載請來信:jlee58tw@gmail.com

20 則留言

  1. 你好,按照你2018/06 更新所貼的程式碼去爬蟲,最近出現JSONDecodeError: Expecting value: line 1 column 1 (char 0)問題,錯誤的點在data = requests.get(url).json(),請問是不是因為網站改版而抓不到資料?還是open data中的json格式有改變才會發生錯誤?我爬其他種類的opendata用requests.get(url).json()都正常

  2. 你好
    我指的是載到資料庫裡的資料只有一筆且sno那欄編號是404
    其他欄位數據都正常,唯獨sno那欄數據不符合

    • Hi Chao Cheng,
      因為留言這邊會去掉空格,看不出來原本的縮排。不過我猜想,你應該是把 sql insert 寫在迴圈外了,以至於資料庫只有塞入一筆資料。你可以檢查一下你的縮排,看看問題是不是解決了

  3. 想請問為什麼資料抓到資料庫時sno都是404
    而且也只有這一筆而已
    程式碼應該沒差太多才對
    url = “http://data.taipei/youbike”
    print (“downloading with urllib”)
    data = requests.get(url).json()
    conn = MySQLdb.connect(host=”localhost”, user=”root”, passwd=”XXXXX”, db=”YouBike”)
    c = conn.cursor()
    conn.set_character_set(‘utf8’)

    for key,value in data[“retVal”].items():
    sno = value[“sno”]
    sna = value[“sna”]
    sarea = value[“sarea”]
    lat = value[“lat”]
    lng = value[“lng”]
    ar = value[“ar”]
    sareaen = value[“sareaen”]
    snaen = value[“snaen”]
    aren = value[“aren”]

    sql = “INSERT INTO info(sno,sna,sarea,lat,lng,ar,sareaen,snaen,aren) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)”
    try:
    c.execute(sql,(sno,sna,sarea,lat,lng,ar,sareaen,snaen,aren) )
    conn.commit()
    except MySQLdb.Error as e:
    print (“Mysql Error %d: %s”%(e.args[0], e.args[1]))
    conn.close()

廖威程 發表迴響取消回覆