在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)