How to install PostgreSQL & pgAdmin

建立 PostgreSQL & pgAdmin 並使用 Colab 透過 Python 存取資料

PostgreSQL 是目前最流行的開源資料庫之一，建好資料庫後，通常也希望能有個 GUI 工具可以進行探索，以下就說明如何快速建立一台 PostgreSQL 及 pgAdmin，最後使用 Python 開發環境來連接看看 SQL 讀寫資料。

環境準備

Docker 容器化架構可以很快的建立想要的 App 應用，下面我們也透過 image 快速建立環境。詳細可以參考這篇：

Docker 從零到有

以 Ubuntu 為例，我們先安裝 docker、docker-compose：

sudo apt-get install docker.io
sudo apt-get install docker-compose

允與使用 docker 時不用再打 sudo：

sudo usermod -aG docker $USER

上面輸入完記得重登，然後我們來看一下 postgresSQL 和 pgAdmin 的 image 有哪些：

postgresql 的 images

pgAdmin 的 images

我們都選擇最多使用的 image 為例，撰寫 docker-compose.yaml 如下，port 號及 vol 路徑可以根據需求更改，其他有帳號密碼的地方，可以不用加引號，直接打上值即可：

使用語法運行環境：

docker-compose up

如果看到環境有正常執行，就可以打開瀏覽器，打上 URL 加上設定的 port 號，進入 pgAdmin 畫面：

點擊中間的 Add New Server 將資料庫加入環境，設定畫面要打的資訊大概如下，記得 port 號要打 container 對外的：

完成後就可以就進去 SQL 環境進行編譯囉，可以在 GUI 上進行 Select 或是 insert 等操作，大幅提升 SQL 操作效率：

DB 測試

為了測試 DB 是真的可以使用，我們透過 Colab 這個雲端開發環境，來進行程式撰寫，可以點下面或這個連結建立新的筆記本：

Google Colaboratory

先載入所需的套件，並載入範例資料 wine dataset：

import pandas as pd
import sklearn
import psycopg2
import sqlalchemy
df = datasets.load_wine()
df = pd.DataFrame(df.data, columns=df.feature_names)
df.head()

接下來建立與 SQL 的連線，使用下面語法測試連線：

engine = sqlalchemy.create_engine('postgresql://DB帳號:DB密碼@DB的IP:port號/DB名稱')
engine.connect()

沒跳 Error 代表連線成功囉，接下來將 Dataframe 轉爲 Table，使用以下語法：

df.to_sql('wine',con=engine,if_exists='replace',index=False,method='multi')

完成後到 pgAdmin 查看 Table，已經正常寫入囉，確認 DB 是可以用的：

如果今天想要讀取資料，只要用以下語法即可將 Table 讀出：

df2 = pd.read_sql('wine',engine)

Python 對 SQL 操作

如果想要透過 SQL 語法來操作 DB，可以使用以下方法執行：

conn = psycopg2.connect(database="postgres", user="account", password="password", host="35.221.234.54", port="5432")
cursor = conn.cursor()
cursor.execute('''select * from postgres.wine''')
result = cursor.fetchall()
conn.commit()
conn.close()
pd.DataFrame(result).head()

改一下欄位名稱就會跟上面語法有一樣效果，差別在於這樣做法 SQL 彈性會更大一點，可以做 insert 跟 update，但就要注意 commit 跟 close 連線，避免 Lock DB。

熟悉 Python 讀取 DB 資料後，後面的衍伸應用就會非常多，如配合 flask 建立網站，或是建立 API 對接 chatbot，後續有機會在做類似的範例吧。