用 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

[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

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

將 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

參考資料

• Django Girls 學習指南
• [MySQL] 在phpMyAdmin 設定 foreign key
• mysql日期和時間函數不求人