iT邦幫忙

第 11 屆 iT 邦幫忙鐵人賽

DAY 16
0

第 16 天:Heroku Postgres:CRUD

  昨天我們從 Heroku 拿到了一個資料庫(database)並且創造了一個可以儲存草泥馬訓練資料的表格(table),今天就要來試著透過 psycopg2 將表格一點一滴的填滿囉!
  先來複習一下昨天的,為了要在 Heroku Postgres 資料庫中創造一個符合我們需求的表格,我們需要用 SQL 語法跟資料庫溝通,下面是創造表格的 SQL:

CREATE TABLE alpaca_training(
   record_no serial PRIMARY KEY,
   alpaca_name VARCHAR (50) NOT NULL,
   training VARCHAR (50) NOT NULL,
   duration INTERVAL NOT NULL,
   date DATE NOT NULL
);

  而 Python 透過 psycopg2 連接資料庫並傳達 SQL 指令:

In [1]: import os
        import psycopg2

        DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]

        conn = psycopg2.connect(DATABASE_URL, sslmode='require')
        cursor = conn.cursor()

        create_table_query = '''CREATE TABLE alpaca_training(
           record_no serial PRIMARY KEY,
           alpaca_name VARCHAR (50) NOT NULL,
           training VARCHAR (50) NOT NULL,
           duration INTERVAL NOT NULL,
           date DATE NOT NULL
        );'''
    
        cursor.execute(create_table_query)
        conn.commit()

        cursor.close()
        conn.close()

  SQL 的資料操作可分為四大類:存入(INSERT)、查詢(SELECT)、更新(UPDATE)、刪除(DELETE)。這幾種方法,又稱為 CRUD: create, read, update, delete。

存入資料(INSERT)

  首先,有了表格,弄清楚每一個欄位的資料型態之後,我們就可以開始存入資料囉。相同的,先來看看 SQL 的指令怎麼寫:

INSERT INTO alpaca_training (alpaca_name, training, duration, date)
VALUES ('吉姆', '肌力訓練', '1:30:00', '2019-09-24');

  首先是一隻健壯的草泥馬吉姆,牠一次就進行了 1 個半小時的肌力訓練。於是乎,我們在alpaca_training這個表格裡面,按照alpaca_nametrainingdurationdate這些欄位依序放入'吉姆''肌力訓練'1:30:002019-09-24這些資料。那麼要怎麼利用 psycopg2 連接到資料庫並傳達 SQL 的指令呢?

In [2]: import os
        import psycopg2

        DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]

        conn = psycopg2.connect(DATABASE_URL, sslmode='require')
        cursor = conn.cursor()

        record = ('吉姆', '肌力訓練', '1:30:00', '2019-09-24')
        table_columns = '(alpaca_name, training, duration, date)'
        postgres_insert_query = f"""INSERT INTO alpaca_training {table_columns} VALUES (%s, %s, %s, %s);"""

        cursor.execute(postgres_insert_query, record)
        conn.commit()

        count = cursor.rowcount

        print(count, "Record inserted successfully into alpaca_training")

        cursor.close()
        conn.close()
        1 Record inserted successfully into alpaca_training
  • 第九行:cursor.execute(postgres_insert_query, record)
      這行程式碼說明我們要執行存入資料的指令。這邊我用了一個 Python 的技巧,叫做清單分解(decouple)。上面我先令record = ('吉姆', '肌力訓練', '1:30:00', '2019-09-24'),讓record成為含有 4 個項目的清單,接著在創造postgres_insert_query的時候,預備了 4 個字串變數(%s)的位置。最後,在執行這一行指令的時候,record裡面的 4 個項目就會依序放入postgres_insert_query當中 4 個字串變數的位置了。

  • 第十行:conn.commit()
      記得,使用 SQT 存入(INSERT)動作時,必須要再加上conn.commit(),資料庫才會真正執行該指令。因此我們的資料是在這一行程式碼被執行完之後,才進到alpaca_training的表格裡。

  • 第十一行:count = cursor.rowcount
      最後,可以用cursor.rowcount來查詢究竟放入了幾筆資料。

  或許有人會問,'1:30:00'指的是什麼啊?是 1 天 30 小時又 0 分嗎?可以再表示得更清楚嗎?在alpaca_training當中,我們的duration這個欄位用的資料類型是INTERVAL,這類型的欄位在輸入資料的時候,除了可以用'1:30:00'之外,也接受像是'1 year 2 month''3 hours 20 minutes'等等像是在寫文章一樣的,對時間的表述方式。法蘭克看到吉姆做了 1 個半小時的肌力訓練,不甘示弱,一頭栽下去就進行了 1 個小時 35 分鐘的肌力訓練。

INSERT INTO alpaca_training (alpaca_name, training, duration, date)
VALUES ('法蘭克', '肌力訓練', '1 hours 35 minutes', '2019-09-24')
In [3]: import os
        import psycopg2

        DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]

        conn = psycopg2.connect(DATABASE_URL, sslmode='require')
        cursor = conn.cursor()

        record = ('法蘭克', '肌力訓練', '1 hours 35 minutes', '2019-09-24')
        table_columns = '(alpaca_name, training, duration, date)'
        postgres_insert_query = f"""INSERT INTO alpaca_training {table_columns} VALUES (%s, %s, %s, %s);"""

        cursor.execute(postgres_insert_query, record)
        conn.commit()

        count = cursor.rowcount

        print(count, "Record inserted successfully into alpaca_training")

        cursor.close()
        conn.close()
        1 Record inserted successfully into alpaca_training

  或是這樣寫'1 hours 35 minutes'覺得太囉嗦,想將時間單位縮寫,INTERVAL 的欄位也願意接受:

INSERT INTO alpaca_training (alpaca_name, training, duration, date)
VALUES ('藍道', '肌力訓練', '1 h 37 m', '2019-09-24')
In [4]: import os
        import psycopg2

        DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]

        conn = psycopg2.connect(DATABASE_URL, sslmode='require')
        cursor = conn.cursor()

        record = ('藍道', '肌力訓練', '1 h 37 m', '2019-09-24')
        table_columns = '(alpaca_name, training, duration, date)'
        postgres_insert_query = f"""INSERT INTO alpaca_training {table_columns} VALUES (%s, %s, %s, %s);"""

        cursor.execute(postgres_insert_query, record)
        conn.commit()

        count = cursor.rowcount

        print(count, "Record inserted successfully into alpaca_training")

        cursor.close()
        conn.close()
        1 Record inserted successfully into alpaca_training

  我們當然可以這樣一筆一筆資料慢慢放進去。但是想想我養的草泥馬的數量,就忽然覺得這不是太有效率的手段,怎麼辦呢?沒關係,psycopg2 提供了cursor.executemany()的方法,讓我們可以一次傳達多個命令。

In [5]: import os
        import psycopg2

        DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]

        conn = psycopg2.connect(DATABASE_URL, sslmode='require')
        cursor = conn.cursor()

        records = [('大衛', '肌力訓練', '1:00:00', '2019-09-24'),
                   ('威廉', '肌力訓練', '1:00:00', '2019-09-24'),
                   ('彼得', '肌力訓練', '1:00:00', '2019-09-24'),
                   ('大衛', '咬合訓練', '1:00:00', '2019-09-24'),
                   ('威廉', '咬合訓練', '1:00:00', '2019-09-24'),
                   ('彼得', '咬合訓練', '1:00:00', '2019-09-24'),
                   ('大衛', '牧草訓練', '1:30:00', '2019-09-24'),
                   ('威廉', '牧草訓練', '1:30:00', '2019-09-24'),
                   ('彼得', '牧草進食', '1:30:00', '2019-09-24')]
        table_columns = '(alpaca_name, training, duration, date)'
        postgres_insert_query = f"""INSERT INTO alpaca_training {table_columns} VALUES (%s,%s,%s,%s)"""

        cursor.executemany(postgres_insert_query, records)
        conn.commit()

        count = cursor.rowcount

        print(count, "Record inserted successfully into alpaca_training")

        cursor.close()
        conn.close()
        9 Record inserted successfully into alpaca_training

  原本我們用record將資料放進 tuple 當中。現在用cursor.executemany(),則將代表一筆資料的 tuple 整理好一起放入 list 內。最後用cursor.rowcount做查詢時,就會發現,原來我們真的一次存入了 9 筆資料啊。一次操作多筆資料,就是這麼簡單!

查詢(SELECT)資料

  雖然說每次存入資料,我們都用cursor.rowcount做確認,但果然還是放心不下。所謂眼見為憑,有沒有什麼方法可以顯示我們存入的資料呢?SQL 提供了查詢(SELECT)的關鍵字,讓我們可以從表格裡面讀出資料,甚至按照指定的要求選擇要呈現的資料。

SELECT * FROM alpaca_training

  這是最簡單的一項指令,翻譯出來就是:「從alpaca_training這個表格選取所有欄位的資料」。因為是選擇所有欄位,所以用*來代表。用 psycopg2 來執行看看吧:

In [6]: import os
        import psycopg2

        DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]

        conn = psycopg2.connect(DATABASE_URL, sslmode='require')
        cursor = conn.cursor()

        postgres_select_query = f"""SELECT * FROM alpaca_training"""

        cursor.execute(postgres_select_query)

  接著,為了要顯示cursor裡面抓取出來的資料,我們可以用fetchone()fetchmany()、跟fetchall()

In [7]: cursor.fetchone()
Out[7]: (1, '吉姆', '肌力訓練', datetime.timedelta(seconds=5400), datetime.date(2019, 9, 24))

  我們昨天已經稍微介紹過fetchone()了。顧名思義,fetchone()會從cursor當中抓出一筆資料,所以每執行一次fetchone()cursor當中的資料就會少一筆。一筆資料就是一個 tuple,而不同欄位的資訊則是 tuple 中的項目。

In [8]: cursor.fetchmany(3)
Out[8]: [(2,  '法蘭克',  '肌力訓練',  datetime.timedelta(seconds=5700),  datetime.date(2019, 9, 24)),
         (3,  '藍道',  '肌力訓練',  datetime.timedelta(seconds=5820),  datetime.date(2019, 9, 24)),
         (4,  '大衛',  '肌力訓練',  datetime.timedelta(seconds=3600),  datetime.date(2019, 9, 24))]

  當然,我們也可以用fetchmany()一次抓取特定筆數的資料。相同的,一筆資料就是一個 tuple,而fetchmany()會將一筆一筆的資料整理起來放進 list 回傳給你。

In [9]: cursor.fetchall()
Out[9]: [(5,  '威廉',  '肌力訓練',  datetime.timedelta(seconds=3600),  datetime.date(2019, 9, 24)),
         (6,  '彼得',  '肌力訓練',  datetime.timedelta(seconds=3600),  datetime.date(2019, 9, 24)),
         (7,  '大衛',  '咬合訓練',  datetime.timedelta(seconds=3600),  datetime.date(2019, 9, 24)),
         (8,  '威廉',  '咬合訓練',  datetime.timedelta(seconds=3600),  datetime.date(2019, 9, 24)),
         (9,  '彼得',  '咬合訓練',  datetime.timedelta(seconds=3600),  datetime.date(2019, 9, 24)),
         (10,  '大衛',  '牧草訓練',  datetime.timedelta(seconds=5400),  datetime.date(2019, 9, 24)),
         (11,  '威廉',  '牧草訓練',  datetime.timedelta(seconds=5400),  datetime.date(2019, 9, 24)),
         (12,  '彼得',  '牧草進食',  datetime.timedelta(seconds=5400),  datetime.date(2019, 9, 24))]

  最後,我們直接了當地用fetchall()將還放在cursor中的資料一次拿出來。和fetchmany()一樣,一筆資料由 tuple 表示,而一筆一筆的資料則用 list 包裝起來。原來我們真的存入了 12 筆資料啊。
  為什麼最後幾筆資料都是'大衛''威廉''彼得''大衛''威廉''彼得',這樣子不斷重複呢?連訓練的內容都一模一樣,是不是我快想不到還能瞎掰什麼訓練資料了呢?當然不是。這是因為這三隻草泥馬是最新加入的夥伴,而面對還沒受過訓練的草泥馬,我都會從最基礎的菜單開始,先增強肌力,並慢慢提升牠們的食慾。因此菜單都是固定的('肌力訓練', '1:00:00')('咬合訓練', '1:00:00')、以及('牧草進食', '1:30:00')
  誒,好像有個地方怪怪的?我們看仔細一點:

SELECT * FROM alpaca_training
WHERE traing = '牧草進食'

  翻譯一下:我們只拿training欄位中內容為'牧草進食'的資料,並且顯示出該筆資料的所有欄位(*)。

In [10]: import os
         import psycopg2

         DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]

         conn = psycopg2.connect(DATABASE_URL, sslmode='require')
         cursor = conn.cursor()

         training = '牧草進食'
         postgres_select_query = f"""SELECT * FROM alpaca_training WHERE training = %s"""

         cursor.execute(postgres_select_query, (training,))```

In [11]: cursor.fetchall()
Out[11]: [(12,  '彼得',  '牧草進食',  datetime.timedelta(seconds=5400),  datetime.date(2019, 9, 24))]

  結果只有'彼得''大衛''威廉'的資料跑哪裡去了呢?這次我想要一次調1011兩筆資料出來,可是executemany()並沒有用在SELECT這個指令上,怎麼辦呢?

SELECT * FROM alpaca_training 
WHERE record_no IN (10, 11)

  沒關係,我們可以這樣下 SQL 的指令:

In [12]: import os
         import psycopg2

         DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]

         conn = psycopg2.connect(DATABASE_URL, sslmode='require')
         cursor = conn.cursor()

         record_no = (10, 11)
         postgres_select_query = f"""SELECT * FROM alpaca_training WHERE record_no IN %s"""

         cursor.execute(postgres_select_query, (record_no,))

  然後再用fetchall()抓出來看看我們找到什麼。

In [13]: cursor.fetchall()
Out[13]: [(10,  '大衛',  '牧草訓練',  datetime.timedelta(seconds=5400),  datetime.date(2019, 9, 24)),
          (11,  '威廉',  '牧草訓練',  datetime.timedelta(seconds=5400),  datetime.date(2019, 9, 24))]

  原來真的把'牧草進食'打成'牧草訓練'啦!糟糕。

更新(UPDATE)資料

  還好 SQL 有提供更新(UPDATE)的操作,讓我們可以更改已經記錄在表格中資料。不囉嗦,直接來看段程式碼:

UPDATE alpaca_training
SET training = '牧草進食'
WHERE training = '牧草訓練'

  上面那段指令,可以很輕易地就將我們不小心輸入錯誤的訓練名稱從'牧草訓練'更改為'牧草進食',好喔,讓我們來試試:

In [14]: import os
         import psycopg2

         DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a 你-APP-的名字').read()[:-1]

         conn = psycopg2.connect(DATABASE_URL, sslmode='require')
         cursor = conn.cursor()

         training = '牧草訓練'
         postgres_update_query = f"""UPDATE alpaca_training SET training = '牧草進食' WHERE training = %s"""

         cursor.execute(postgres_update_query, (training,))
         conn.commit()

         count = cursor.rowcount

         print(count, "Record updated successfully into alpaca_training")
         2 Record updated successfully into alpaca_training

  太好了,兩筆登記錯誤的資料成功更新了。

刪除(DELETE)資料

  SQL 提供的最後一種資料操作,是刪除(DELETE)。功能?沒錯,就跟大家想的一樣,把表格裡面的資料刪除。按照 SQL 的語法寫起來會像是:

DELETE FROM table
WHERE condition

  舉例來說,如果我想刪除record_no12的資料:

DELETE FROM alpaca_table
WHERE record_no = 12

  不過我只是舉個例子而已,並沒有真的想刪除,所以就不示範怎麼用 psycopg2 向資料庫傳達指令囉?今天的內容蠻大一部分是參考網路上關於 psycopg2 的教學,大家有興趣可以點過去看看➀。今天我們把資料庫提供的各種方法都試著操作了一遍,明天就可以試著讓資料庫跟 LINE 聊天機器人互相連接囉。今天的內容若有不清楚的地方,歡迎直接在下面留言,我會盡可能地回覆大家的。謝謝大家!

參考資料

➀ Psycopg2 使用教學
➁ PostgreSQL 使用教學
➂ PostgreSQL 中文使用手冊


上一篇
第 15 天:LINE BOT SDK:Heroku Postgres 資料庫
下一篇
第 17 天:Heroku Postgres:連接 LINE 聊天機器人
系列文
從LINE BOT到資料視覺化:賴田捕手30

1 則留言

0
tailikhk
iT邦新手 5 級 ‧ 2020-03-22 03:27:12

請問有沒有試過用LocationSendMessage? 如何可以將floating point 参數從database 傳到函數?

您好:

之前沒有用過LocationSendMessage這個函式,最近也還沒時間嘗試。參考了line-bot-sdk的官方文件:

location_message = LocationSendMessage(
    title='my location',
    address='Tokyo',
    latitude=35.65910807942215,
    longitude=139.70372892916203
)

知道要使用LocationSendMessage需要準備latitudelongitude兩個浮點數資料。

假設您的問題是,如何將浮點數資料存到 Heroku Postgres 再拿出來嗎?

若是這樣的話,我們可以先在資料庫中準備一個新的表格:

CREATE TABLE IF NOT EXISTS location (
    id serial PRIMARY KEY,
    title VARCHAR NOT NULL,
    address VARCHAR NOT NULL,
    latitude NUMERIC (9, 6) NOT NULL,
    longitude NUMERIC (9, 6) NOT NULL
);

利用上面的 SQL 語法應該可以做出一個名為location的表格,該表格中有latitudelongitude這兩個欄位負責儲存經緯度的浮點數資料。

其中,NUMERIC (9, 6)表示我們預定存進的浮點數資料最多總共允許 9 個數字,而小數點以下的位數設定為 6 位。所以說,35.65910807942215存進表格後,會成為35.659108,而139.70372892916203則會成為139.703729

有了表格之後,下一步就是存資料:

# 表格的欄位名稱
location_columns = 'title, address, latitude, longitude'

# 存資料所用的 SQL 語法
postgres_insert_query = f"""INSERT INTO location ({location_columns}) VALUES (%s,%s,%s,%s)"""

# 準備存入的資料
data_to_insert = ('my location', 'tokyo', 35.65910807942215, 139.70372892916203)

# 存資料
cursor.execute(postgres_insert_query, data_to_insert)
conn.commit()

最後,拿出來看看我們存進去的資料:

# 提取資料所用的 SQL 語法
postgres_select_query = f"""SELECT * from location"""

# 提取資料
cursor.execute(postgres_select_query)
dataclip = cursor.fetchone()

# 查看我們提取出來的資料
print('dataclip:', dataclip)
print('latitude:', float(dataclip[3]))
print('longitude:', float(dataclip[4]))
dataclip: (1, 'my location', 'tokyo', Decimal('35.659108'), Decimal('139.703729'))
latitude: 35.659108
longitude: 139.703729

我想應該可以得到如上述的結果。用float()轉換成 Python 的浮點數物件,再放進LocationSendMessage應該就可以用了?

我覺得大略的流程應該是這樣,不過沒有實際用過。若這是您想討論的應用,那可以這麼試試看,如果有其他狀況,可以再留言討論!

我要留言

立即登入留言