DAY 20 我要+1 群组团购辅助机器人(补)

上篇只做出了大部分功能，有很多细节没做好，这篇会修正细节与优化

PostgreSQL IF statement

我会用到PostgreSQL的IF statement，来改掉上篇的INSERT部分

DO
$do$
BEGIN
   IF EXISTS (SELECT FROM orders) THEN
      DELETE FROM orders;
   ELSE
      INSERT INTO orders VALUES (1,2,3);
   END IF;
END
$do$

DO
$do$
BEGIN
IF EXISTS(SELECT uid FROM group_buying_user WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}') AND uid='{profile.user_id}' ) THEN
	UPDATE group_buying_user SET name='{profile.user_id}',quantity='{event.message.text[4::]}' WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}' and uid='{profile.user_id}');
ELSE
	INSERT INTO group_buying_user (mid, uid, name, quantity) VALUES ((SELECT mid FROM group_buying_message WHERE keyword='{key}'),'{profile.user_id}','{profile.display_name}','{event.message.text[4::]}');
END IF;
END
$do$			

修改

再+1时如果同时要显示团购讯息跟名单，延迟太多我造成服务器错误
我把显示团购讯息跟名单加到关键字的if里
+1 时资料会传至资料库，并显示 关键字XXX+1
关键字
关键字 +1

if(event.message.text in keywords): 
        key=event.message.text
        profile = line_bot_api.get_profile(event.source.user_id)
        cursor=conn.cursor()
        #将取得的key作为关键字在group_buying_message里找到讯息文字
        cursor.execute(f"SELECT message_text FROM group_buying_message WHERE keyword='{key}';")
        message_text = "".join(cursor.fetchone())
        #用key在group_buying_message里找到讯息文字的mid，再用mid从message_emoji里找到LINE emoji资讯
        cursor.execute(f"SELECT index,product_id,emoji_id FROM message_emoji WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}');")
        rows = cursor.fetchall()           
        emojis=[]
        #将资料一笔一笔写入list中
        for row in rows:
            emojis.append({'index': row[0],'productId': row[1],'emojiId': row[2]})
        cursor.execute(f"SELECT uid,name,quantity FROM group_buying_user WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}');")
        users = cursor.fetchall()

        for user in users:
            message_text=message_text+"\U0001F449"+"".join(user[1])+" "+"".join(user[2])+"\n"
        message=TextSendMessage(message_text,emojis)
        line_bot_api.reply_message(event.reply_token,message)

if(event.message.text[:event.message.text.find(" "):] in keywords and len(event.message.text)>event.message.text.find(" ")):
        conn.commit()
        lastLength=event.message.text.find(" ")
        key=event.message.text[:lastLength:]
        profile = line_bot_api.get_profile(event.source.user_id)
        cursor=conn.cursor()
        cursor.execute(f"DO $do$ BEGIN if EXISTS(SELECT uid FROM group_buying_user WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}') and uid='{profile.user_id}' ) then UPDATE group_buying_user SET name='{profile.display_name}',quantity='{event.message.text[lastLength+1::]}' WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}' and uid='{profile.user_id}'); else INSERT INTO group_buying_user (mid, uid, name, quantity) VALUES ((SELECT mid FROM group_buying_message WHERE keyword='{key}'),'{profile.user_id}','{profile.display_name}','{event.message.text[lastLength+1::]}'); end if; END $do$")
        conn.commit()
        message=key+" "+profile.display_name+" "+event.message.text[lastLength+1::]
        message=TextSendMessage(message)
        line_bot_api.reply_message(event.reply_token,message)