在Python中使用SQL語法的一些體驗(3)--讀入.sql

        前面兩篇介紹了SQL在python裡面的應用，這篇算是一個實戰經驗，如何依照你的編寫習慣，讓你的代碼自由縮排、換行。

在Python中使用SQL語法的一些體驗(1)--建立數據結構的觀念

在Python中使用SQL語法的一些體驗(2)--連接數據庫

        Python語法最讓人頭疼的問題之一，就是換行與縮排是有意義的，你是不能隨意縮排、換行。python很容易出現一行的代碼很長，或是method裡面的參數太多，當你編寫和閱讀這些沒法換行或是對齊的代碼和都是很傷眼力的事情。

        對於一個非computer science的data scientist來說，看一下分布、分析目標的輪廓、數據格式、數據缺失等前期分析是基本，經常是看一下分析結果立刻修改代碼，再看一下結果再修改。一天跑個好幾十次修改是很平常的事。這時候我沒辦法只取出一點數據後在pycharm運行，確定無誤後在後台運行全量數據。有時候比較常發生的問題是數據量太大跑不動，我必續切出一個月、一周、一天的數據量跑看看，然後分批讀入與存儲結果。因此代碼的可閱讀性和彈性便相當重要。

        以上這些問題，加上前幾篇提到的pass through的優點，我在數據分析前期需要從原始數據庫讀回數據時，會在python面大量使用sql語法。

• 存成.sql

      我個人偏好是將SQL的編程另外存成.sql的檔案，然後再讀入。這樣的好處是.sql裡面的代碼能依照你習慣的編程習慣能縮排、對齊。而且當你存成.sql時，關鍵詞自動上顏色而且tab對齊是有標識的的。

 

• 格式化format vs  parms

        當你要讀入讀入這段sql，且去除換行時，只需要在開啟並讀入.sql的時候，同時將\n替換成空白，就能將.sql裡面你自己換行的sql語法轉換成一行，同時兼顧了sql的可閱讀性和可執行性。

        有注意到兩個很提別的地方嗎？.sql裡面有’{0}’，而read_sql裡面有 .format(‘phone’)。這是python裡面的格式化，.sql裡面的’{0}’會被替換成’phone’。這在寫循環，或是自建函數def時，能夠帶入你設定的參數。不過請注意，format存在 SQL Injection的風險。請注意使用場景，當在正式環境或是對外開放的接口時，請使用params。

比較安全的方式是加入個params 。但比較麻煩的是params的語法會因為數據庫驅動程式不一樣而有不同的寫法。以上這個例子是使用sqlalchemy的 create_engine連接mysql，如果你是使用其它驅動，%(參數)s 的寫法就不一定能運行了。

在Python中使用SQL語法的一些體驗(2)--連接數據庫

        在python上使用SQL語法是一整套的操作流程。這包括了：連接資料庫、SQL程式設計、SQL存儲。這篇先介紹連接資料庫的方法：

在Python中使用SQL語法的一些體驗(1)--建立數據結構的觀念

在Python中使用SQL語法的一些體驗(3)--讀入.sql

        PyMySQL 是在 Python3.x 版本中用於連接 MySQL 伺服器的一個庫，Python2中則使用mysqldb。PyMySQL雖然是為了連接MySQL。事實上PyMySQL能連接的資料庫不限於MySQL。接下來的介紹會有詳細的說明。就好比你在你電腦上的ODBC安裝了某某數據庫的driver，安裝好之後你可以用任何你想用的分析工具(如excel)連接到你安裝了driver的數據庫。本文第二部分<連接其它數據庫>蒐集了我幾比較常用的driver。

我們可以使用pip3。安裝最新的PyMySQLcursor() v.s. read_sql()

# 安裝PyMySQL

pip3 install PyMySQL

 

#!/usr/bin/python3

import pymysql

 

# 打開資料庫連接

db = pymysql.connect("localhost","testuser","test123","TESTDB" )

 

# 執行查詢

        一般比較常見的查詢方法是使用cursor()創建一個游標物件cursor。這邊除了cursor外，會再介紹一種我比較偏好的方法

# （1）使用 cursor

cursor = db.cursor()

cursor.execute("show databases")

data = cursor.fetchall()

print(data)

        如果你要將結果轉成DataFrame，需要再經過轉換:

pd.DataFrame(list(cur.fetchall()),columns=['Database'])

# （2）使用 pd.read_sql

        cursor()使用起來是有點麻煩，而且輸出是tuple,如果要做篩選、並表、匯總等操作，是要先轉成DataFrame。我個人比較偏好的方式是使用pd.read_sql()

# 安先import   pandas

import pandas as pd

有沒有發現：

1. 語法簡單多了，一行語法就完成了
2. 接運行SQL語法
3. 輸出就是DataFrame
4. 遠程運算的Pass through

        也就是你能直接在python上運行遠端的資料庫。前一篇裡面有介紹過pass through，透過使用遠端資料庫的資源和減少傳輸資料量的方法，pass through能大幅提升運算效能。當你是連接的是hive這類的big data時，運算效能的差異更是明顯。

• 連接其它數據庫

pymysql能連接的資料不限於mysql，它能連接hive、impala、JDBC等等各種數據庫

(1)hive

(2)impala

(3)JDBC

你也能透過先下載driver，透過jdbc連接資料庫。這邊的例子是透過jdbc連接moonbox

• Datafram使用SQL語法sqldf

還有一種請況是不管是從數據庫讀入或是pd.read_csv()等各種方式，你面對的已經是導入成DataFrame的數據了，但你又想直接使用簡單清楚的SQL語法來整合一些合併或條件篩選，這時候不妨使用sqldf或pandasql。

from pandasql import sqldf

sqldf("select * from table_a")

• DataFrame寫入MySQL

在將csv和SQL轉成python的DataFrame後，最終還是需要面對存儲的問題。一種選擇是存成csv ，csv當作臨時單純的存儲格只能應付單機的需求，當其他service需要協同合作，一棒接一棒的從外部數據讀入，中端的數據運算，到前端的報表呈時，便沒法一步接一步協同完成。因此存入MySQL是一個或是其他數據庫是必然的選擇。

既然讀入數據時是DataFrame，處理運算是DataFrame，存入的步驟自然也偏好以DataFrame直接存入MytSQL或是其他數據庫。DataFrame 存入MySQL可以參考以下的語句:

pd.io.sql.to_sql(m.reset_index(drop=True), "m", yconnect, if_exists='append', index=False, chunksize=10000)

在Python中使用SQL語法的一些體驗(1)--建立數據結構的觀念

        學習python這麼強大的編程語言，還需要學習SQL嗎？如果你需要對結構式數據進行處理，如果你需要對數據處理合併、篩選、匯總，那麼學習SQL會讓你處理這些需求時，有相當顯著的成果。這些幫助可能來自於快速完成需求，也可能幫你分析需求，整理數據之間的關係，對你未來跨平台的學習也相當有幫助。

在Python中使用SQL語法的一些體驗(2)--連接數據庫

在Python中使用SQL語法的一些體驗(3)--讀入.sql

• 建立數據結構的觀念

        學習SQL最重要的价值，並不是如何寫代碼或是完成需求，而是對數據結構有基本的認識。特別是健值的觀念(key)，例如主鍵(primary key)、外鍵(foreign key)、唯一鍵(unique)。要讓兩張表產生關連時應該要用哪些欄位連接？計算加總、最大值、平均數時應該要用哪些欄位做分群依據？

        學習SQL除了你對數據結構有基礎的認識之外，也讓你能夠讓將完整思考整體需求，而不是單獨處理個別。以上面這個數據結構圖來說。在整個銷售業績表中(Fact Sales)，與之相關的有日期維度表（Dim_Date）、產品維度表(Dim_Product)、分店維度表(Dim_Store)。如果我們想要找出第三季的手機周平均銷售量中，扣除單周周售量小於1000的店家後的最大的國家，你要如何思考這需求？學習SQL會讓你對這需求有很清楚的概念。以下面這個代碼為例，在編寫的過程中你會逐步思考各個條件之間的關係。如果你沒學過SQL而是先接觸python，單純使用python單行代碼的觀念來理解需求，你的思緒很容易一開始就會迷路了。

• 低學習障礙

        對於SQL來說，資料的合併、篩選、條件判斷、彙總都是基本功能。SQL也是學習障礙最低的數據庫語言，你可能學一個早上就能開始動手。旁邊一個沒學過寫代碼的職場新鮮人也可能是先學SQL。基本上你只要會Excel，對於SQL的語法是很容易理解的。SQL常用的就join(left、right、inner)、where、on 、group、by、in、not、having、case、when，學完大概15個左右你就會了。你比較不會遇到忽然看到某個問題需要另一個函式來處理的情況。

        SQL是個跨平台的語言，基本上能處理結構式數據的語言都會支持SQL語法，python上能寫，hive、SAS、SQLite、Access都可以寫SQL語法。如果你是初入門的新手，學SQL最基礎也是最划算的。SQL也是最廣泛的數據庫語言，也就是你能求助的地方是最多的，各種神奇問題都能迅速釣到神人相助。

• Pass Through

        所謂的Pass Through是指代碼是在你連接的平台運行。假設你在python透過API、JDBC或ODBC連結到一個數據庫，Pass Through讓你可以在python上編寫這個平台的代碼送到這個數據庫運行，運行完畢後才將結果送回來python。

        可能很多新同學看到關於python的一些神奇報導來學python，誤以為python各種能力都強。抱歉，種語言都有它的強項和弱項，計算效能是python最弱的能力之一。

        你可以試試看，在Excel上寫一個公式，但是數據有50萬筆，你的電腦可能就掛了。但是如果你在你的電腦上裝個MySQL或是SQLite在來處理這50筆數據，你會發現同樣的語法跑起來順滑多了。

        你需要處理的數據可能是幾萬筆、幾百萬筆、幾億筆，這時候你就要透過連接的數據庫先進行處理。一般而言，數據庫會採用MySQL或是hive或是其他架構，都是當初在設計架構時，都考慮了要處理的數據量，也就是你連接的數據庫本身的架構能夠處理你的計算量。

        另一個Pass Through很重要的特色是，數據庫只會回傳最終結果。假設你的原始數據有1億筆，但是經過篩選計算只會剩下1萬筆，如果你先將數據讀回來，你得先讀完1億筆的數據量再計算。如果你透過Pass Through，那麼你只需要傳輸最終1萬筆的數據量。很多時候你跑代碼的時間都花在傳資料上，而不是計算上。

        為什麼會說到Pass Through?剛剛才說到很多平台都是用SQL語法，那麼你就能用Pass Through處理巨量數據。

關於隱私權

                 在2002年上映，由Tom Cruise主演關鍵報告(Minority Report) 是一部探討隱私權的電影。故事背景設定在2054年一個全面被監控，毫無隱私權、犯罪能被預知、罪犯能被事先逮捕的科幻世界，電影裡面對於隱私權的探討。

                關鍵報告讓我印象最深的並不是主角在逃亡時處處的科技監控 ：進入服裝店時雲端數據庫立刻幫你挑選適合服裝與尺寸、城市各角落的監視器。而是對於你未來行為的預測，它監控的，是你的自由意志、你的未來行為。

                雖然在法律與哲學領域有很多關於隱私權的討論與案例，但這些專業人士的討論對於一般人而言是簡直是外星人的對話。隱私權對一般人是個很抽象的概念，陸續發生的隱私權洩漏事件也沒有一個完整的討論架構與比較，當討論某個單一隱私權相關案例時，每個都很重要每種情況都要防範，但如果我們整體討論並考量科技技術時，哪些情況對隱私權的侵犯更嚴重哪些情況相當輕微， 或許會有不同的角度與思維。
                在往下談論隱私權之前，先表明一下本篇的談論目的是在現有的科技環境之下，不同情況下的社會安全、生活便利與隱私權該如何均衡：這包括了路口的監視器、出入境時的指紋採集、留在網路時代的足跡、悠遊卡/一卡通、信用卡，甚至銀行存款

        一般看到對於隱私權的討論，著重在於探討那條線在哪，但是不同隱私權的侵害嚴重度與目前的科技發展卻很少討論。法律是法律、科技是科技，法律與科技似乎是兩個不相交的領域。特別是越來越常聽到所謂的IBM之後：人工智慧(AI)、大數據(Big Data)、機器學習(Machine Learning)，數位科技的發展與隱私權的界線應該如何劃定?

           就法律上的定義來說嚴格定義，隱私權是任何一項你不想讓別人知道紀錄，包括電話號碼與通話紀錄、位置、銀行存款、醫療紀錄、上網紀錄等等都該保密，但這種加法的涵蓋方式並無法應付科技的發展。而在數據科學家的眼中，不同的數據類型，數據含金量、可串接性、涵蓋率、清洗方法等等方面，都有相當大的差異。有些數據可以直接挖掘出有價值的訊息，能直接進行商業、政治目的的應用，但有些你認為很重要的數據就算外流也無法應用。一般民眾對於隱私權的認知與應用上的價值顯然有著差異。

         或許你會認為任何一項關於隱私權的數據都不該外洩，但在實務上很多你認為的隱私數據事實上是不具探索價值，或者需要大量的加工。這裡並非是要否認數據的外洩或不當使用對於隱私權侵害，而是要瞭解哪些才是隱私權保護的核心工作在哪，以及如何在隱私權保護與科技帶來的便利中取得均衡，也才不會發生侵害隱私權最淺的科技時處處反抗，但卻深度擁抱侵害隱私權最嚴重的科技的奇特現象。

隱私權的層次

         我個人把隱私權分成三個層次：第一層是外顯層，第二層是隱藏層，第三層是意志層，這三個隱私權層次是以一個陌生人或是機構的角度來分析，如果以不同的角度來分析會有不同的分層方式，例如以朋友的角度來分析，婚姻、家庭、工作是已知道的，但你幾點幾分在哪就是隱藏層了。

1. 外顯層：可以觀察到的個人行為：包括外表、今天穿著打扮。另外還有一個半外顯層：需要經過查詢詢問才能獲知的資訊，包括婚姻狀態、家庭狀態、工作、位置
2. 隱藏層：通話記錄、上網記錄、金融交易記錄、財產、醫療記錄、消費及交友圈
3. 意志層：包括你的政治立場、興趣、未來的旅遊計畫等。意志層與隱藏層最大的差別在意志層主要決定未來的行為，而隱藏層是已經發生的行為或屬性數據

                 在一些科幻電影中，常常會有讀取對方意志的情節。而在現實生活中，科技技術可以藉由你綜合你地各項資訊來進行判讀，來監控你意志層、影響你未來的意志與決策。例如你的交友群、行事曆、通話記錄、對話內容、發表的言論等等，監控意志層的挑戰在於跨數據來源的整合以及清理，這部分是數據科學家每天的主要工作，有機會會再做個簡單的介紹。

              透過機器學習的預測，這些跨平台的數據能夠構件出預測你行為的模型，例如你喜歡吃的料理、你最近是不是想去旅遊等等、需要哪些理財或借貸產品，蒐集到的資料類別越多，對你行為的預測也就越精準。一個大家比較孰悉的例子是所謂的三寶，當你用性別與年齡兩個維度的去預測，準確度會比用一個維度還準確。

• 技術發展與限制

         第一個核心點是技術上的限制。目前常聽到的技術名詞人工智慧(AI)、大數據(Big Data)、機器學習(Machine Learning)。這些很炫但一般人其實很陌生的名詞是很容易被誤解的，很容易片段的印象被拼湊在一起而讓你有科技萬能的錯誤印象。以大家比較關心的監視器為例，快速通關的人臉識別+路上到處都有的監視器+關於大陸公安局在路上識別的報導=路上的監視器能識別每個路人。這樣的想像在技術上是可能的，但在實務上有不少的挑戰。讓我們先看看這次中美貿易大戰中，被美國點名的海康衛視的公安監控平台架構。

　　   監視器是否能人臉識別，並非只有監視器一項設備而已。要達到這樣大面積的人臉識別有許多條件，首先是要有一個覆蓋率完整的人臉識別數據庫，中國有這樣的社會背景與數據建設條件，但在中國以外的其他地區與國家要達成這樣高完成度的人臉識別數據庫都有不同的困難。起次是要有一個集中的運算平台，能夠滿足各個端點監視器的運算需求，每個平台單獨運作不僅浪費資源，也降低人臉辨識的成功率。

                比較常被忽略的因素是是適用場景。一個城市裡的監視器是十萬、百萬等級的數量，監視器的類型與數量以及影像質量的增長，遠超過核心系統的擴建速度且無法滿足所有監視器的辨識需求，因此連線監視器的設置需要搭配場景，例如車站、重要路口等行人流量大的地點。至於社區裡的監視器只是留存目的，以複寫的方式循環紀錄，等需要查詢時才從存檔裡面調出錄影並以人眼方式檢視。另外還有測速器、路況監視器也是類視的使用狀況。
                因此，政府如果真要使用路口的監視器進行人臉識別，是可以從預算、系統供應商、硬體設備、對接的人臉別數據庫、監視器等級及架設地點看監控的目的及範圍。而非架上監視器就能進行大規模的人臉識別。

 

• 技術上的限制

              在以上這樣隱私權分類下，更要關心的是不同類別的隱私權要如何保護，這結合了對隱私權的分類、數位科技的發展程度、實際開發的限制等等。

              首先一個原則是數據彙整上的限制：單一數據源的洩漏對隱私權造成的傷害並沒有想像的大。當然，這樣的評論是得加上類別的嚴重度，例如醫院洩漏你的醫療紀錄會比電信公司洩漏你的通話記錄嚴重。路上的監視器對隱私權的損害並不大，但如果監視器加上人臉識別數據庫進行即時識別，對隱私權的侵害是指數方式的增長，因此防治的重點應該是避免路上這些監視器與其他人臉識別技術或數據庫的連接，或是監控政府對於生物特徵的蒐集行為，而不是這些監視器有多少。

                 數據挖掘（Data Mining）是從數據中探勘出有用資料的一門計算機科學，在大數據時代來臨之前，數據是有針對性的，例如你填寫的貸款申請資料。但在大數據時代來臨之後，數據來自不同的數據源而且沒有針對性，例如你在google上搜尋了一個關鍵字之後， google演算法根據你的歷史行為推薦不同的廣告。大數據時代下的數據探勘最大的差異之一，是數據裡的含金量大幅降低。以前或許1G的原始數據就能產出一個預測力不錯的模型，現在可能1T才勉強能夠產出預測力還行的模型，這還不包括語音、照片、影片這些含金量更低的多媒體數據。語音、照片、影片這些多媒體數據如果不能即時串接到ID數據庫（人臉識別數據庫、音頻識別數據庫）轉換成具有意義的數據（例如身份證字號A122xxxxxxxx在2017年2月2日上午11點15分39秒敦化南忠孝東路路口闖紅燈），這些大數據就是個佔量驚人的垃圾數據了，或許你會擔心數年之後政府蒐集到了你的生物特徵後，再去回掃這些積累的數據。關於這點回想一下你會整理硬碟裡幾年前的照片和影片嗎？商業上的數據回掃更加困難，光歷史數據分別存在哪個硬碟裡都得找半天了，實務上是很難大規模的回掃歷史數據。
　　在大數據的環境下，數據越來越多元，對數據科學家來說產生了一個新問題：數據孤島。也就是多樣性的數據並無法相互鏈結。這呈現在幾個方面：法令遵循的要求、數據缺失率的加乘、各種數據的鍵值（Key）不一、各數據源的格式不統一、各數據源無法對接。除了法令遵循的要求之外，數據缺失率是最大問題，假設有辦法蒐集到中華電信的通話紀錄（市佔率35%），出入境管理局辦理快速通關留存臉部識別及指紋的人數約60萬人（約成年人口6%），兩種數據一匹配只剩下2%，這樣的數據覆蓋率在實務上是不具使用價值的。

　　 數據孤島對於數據科學家的惡夢，但對於隱私權的保護來說卻是福音。在檢視各種有隱私權隱憂數據蒐集方式時，我們不妨將現數據上的對接限制納入考慮。數據只要被隔離，就能有限度的控制對隱私權的侵害。

               目前國內並沒有一個完整的人臉數據庫，人臉辨識實際上對隱私權的侵犯相對而言是比較輕微的，對人臉辨識的恐懼主要是來自於心理上，這並不是說就個人而言，路上的監視器並不會有隱私權方面的隱憂。而是從政府的角度來說，路上的監視器連結上人臉識別數據庫的成本和效益並不值得投入，而是應該將預算投入到效益明顯的場景上如機場的快速通關或是上下班的打卡。

• 你所忽視的

                 在真實世界中，對隱私權侵犯較深的反倒是很多人都知道但都忽視的社群軟體例如google+、臉書。臉書有你的性別、年齡、婚姻狀態、電話、照片、打卡地點、興趣、朋友圈、通話、甚至評論的主題與內容。就數據的蒐集和分析來說，這是最完善的數據市集，不須打通各種不同的數據庫、沒有無止盡的數據清洗、長達數年的高頻率歷史使用數據，對數據探勘的數據科學家來說，臉書根本就是個數據金山，加上臉書的營業模式是透過利用隱私權精準發布廣告，臉書關於侵犯隱私權的新聞也層出不窮。

               從上述數據分析的實際挑戰來說，我們可以將隱私權分成深度、廣度、嚴重度三個維度來分析。路上的監視器分布雖廣，但能挖掘出的信息有限。金融信息可以挖掘出較多的隱私信息，但各個金融機構都有嚴密的資安系統，無法擴大觸及面。雖然我使用的是Android，但從業經驗讓我不得不佩服ios系統對於數據保密的嚴謹。Google產品眾多能蒐集到的數據廣泛，也曾發生侵犯隱私權的案例，但基本上在企業文化與商業性質的主導下，對於隱私權的侵害程度還不算嚴重。相對而言，facebook有動機有技術有數據，才是你我最該小心的社交工具。

         回歸到一開始的主題，面對I、B、M時代的來臨，在不同層次的侵犯隱私權形式中，我們更該關心關於意志層的隱私權侵犯：你是怎樣的一個人、你對某些主題的意見、你對某些行為的反應等。應該思考的是如何防止 你的動態被蒐集、你的思想被預測的、你的行為被操控。這並不代表在一些社交工具上要限制你的言論，而是要慎選你發表意見的平台，儘量選擇留存數據少、社交關聯度低的平台或工具。套句最近最流行的一句話：不要讓你的感性壓過理性。

註：

使用"數據"一詞而非"資料"，主要是"資料＂一詞包含了數位化的資料、紙本、ppt等。與兩岸使用習慣無關。

Lawson Loppi購票攻略

　　日本慶典活動很多，常常在這些慶典或納涼花火的現場，你會發現會有些區塊圍起來標示為有料座。日本使用者付費的觀念很普及，幾乎所有的活動都有這種花點錢，就可以買到不用事先排隊，而且視野角度最棒的位置。

Loppi購票

印度慶典之旅－Pushkar駱駝節

　　這幾年來的海外旅行都會配合當地的一些慶典，一方面在蒐集資料的過程中可以發掘到更多的樂趣，另一方面也可以體驗書本照片和影片無法形容的真實體驗。

Pushkar駱駝節

印度慶典之旅－Agra印度教迎親&伊斯蘭新年

　　 幾乎所有的遊客到Agra來是為了看世界七大奇景的泰姬瑪哈陵以及附近和紅堡，由於時間上的巧合，此行在Agra巧遇印度教的婚禮迎娶隊伍以及伊斯蘭曆的新年元旦，使得Agra成為印度行的最大的驚喜。

印度教迎親隊伍中的新郎