用 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 裡面


用 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

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

$ ./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

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

將 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

    您好
    前面有設定 # -*- coding: utf-8 -*-避免中文出問題
    其實處理中文還是用python3比較方便,我之後會再修改範例
    謝謝你的詢問唷

  • mondeosa Chen

    謝謝你,測試這樣的寫法在python2.7沒問題了
    另外想再請教,你原本程式再讀取後再寫出,並沒看到轉碼,
    但是你print出來都是中文??

  • Jerry

    Hi 你好~
    我想應該是型別問題,以下是我用python3執行正常的程式碼
    給你參考看看,用requests方便許多

    import requests
    import json

    url = “xxx”
    data = requests.get(url).json()

    for value in data[“result”]:
    region = value[“region”]
    srcdetail = value[“srcdetail”]
    areaNm = value[“areaNm”]
    UID = value[“UID”]
    direction = value[“direction”]
    y1 = value[“y1”]
    happentime = value[“happentime”]
    roadtype = value[“roadtype”]
    road = value[“road”]
    modDttm = value[“modDttm”]
    comment = value[“comment”]
    happendate = value[“happendate”]
    x1 = value[“x1”]
    print(“NO.”, region, srcdetail, areaNm, UID, direction)

  • Chen Jiung-Nan

    你好:
    我依照你程式範本,修改為直接網頁抓取json資料,整個網頁抓取無問題,但是抓網頁內部資料執行程式後會出現”TypeError: string indices must be integers, not str” ,想請教我是哪邊錯誤?

    import urllib
    import json
    web = urllib.urlopen(“http://od.moi.gov.tw/data/api/pbs”)
    data = web.read()
    jdata = json.loads(data)
    for key,value in data[“result”].iteritems():
    region = value[“region”]
    srcdetail = value[“srcdetail”]
    areaNm = value[“areaNm”]
    UID = value[“UID”]
    direction = value[“direction”]
    y1 = value[“y1”]
    happentime = value[“happentime”]
    roadtype = value[“roadtype”]
    road = value[“road”]
    modDttm = value[“modDttm”]
    comment = value[“comment”]
    happendate = value[“happendate”]
    x1 = value[“x1”]
    print “NO.” + region + ” ” + srcdetail + ” ” + areaNm +” “+UID+” “+direction+” “