在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處理巨量數據。