[day17]使用者名称表格

一样先从建立表格开始，这张表格除了储存来自Line的使用者资讯，也预留了未来资料的输入栏位

CREATE TABLE IF NOT EXISTS public.customers
(
    uid text COLLATE pg_catalog."default" NOT NULL,
    "displayName" text COLLATE pg_catalog."default" NOT NULL,
    language text COLLATE pg_catalog."default",
    "pictureUrl" text COLLATE pg_catalog."default",
    "FirstName" text COLLATE pg_catalog."default",
    "LastName" text COLLATE pg_catalog."default",
    "phoneNumber" text COLLATE pg_catalog."default",
    "Address" text COLLATE pg_catalog."default",
    "Activate" boolean DEFAULT true,
    CONSTRAINT customers_pkey PRIMARY KEY (uid)
)

TABLESPACE pg_default;

实作纪录使用者资讯

def INS_UPD_cus(self, prof):
    # display_name (str) – Display name
    # user_id (str) – User ID
    # picture_url (str) – Image URL
    # status_message (str) – Status message
    # language (str) – Get user’s language

    cur = self.conn.cursor()
    query = sql.SQL("SELECT 1 AS isExists FROM {} WHERE uid = %s").format(sql.Identifier('customers'))
    cur.execute(query, ([prof.user_id]))
    r = cur.fetchone()
    cur.close()

    if(not r):
        cur = self.conn.cursor()
        query = sql.SQL("INSERT INTO {}(uid, \"displayName\", language, \"pictureUrl\") VALUES(%s, %s, %s, %s)").format(sql.Identifier('customers'))
        cur.execute(query, (prof.user_id, prof.display_name, prof.language, prof.picture_url))
        self.conn.commit()
        app.logger.debug(f"New User:{prof.display_name} - {prof.user_id}, Created")
        cur.close()
        return 1
    else:
        cur = self.conn.cursor()
        query = sql.SQL("UPDATE {} SET \"displayName\"=%s, language=%s, \"pictureUrl\"=%s, \"Activate\"=%s WHERE uid = %s").format(sql.Identifier('customers'))
        cur.execute(query, (prof.display_name, prof.language, prof.picture_url, "TRUE", prof.user_id))
        self.conn.commit()
        app.logger.debug(f"User:{prof.display_name} - {prof.user_id}, UPDATED")
        cur.close()
        return 2

prof = line_bot_api.get_profile(event.source.user_id)
INS_UPD_cus(prof)

在WebhookHandler被呼叫时，会依据讯息中的user_id搜寻资料库，如果没有这个Line UID，则会新增这个新使用者，反之如果是既有用户，则会更新资料库中使用者的显示名称等资讯

Postgresql采到的坑

query = sql.SQL("INSERT INTO {}(uid, \"displayName\", language, \"pictureUrl\") VALUES(%s, %s, %s, %s)").format(sql.Identifier('customers'))

为什麽UID不用加双引号「"」，displayName要加，language却又不用加，下一个pictureUrl又要加双引号，答案是大小写，如果你在postgresql中建立了带有大小写的栏位名称，请务必要加上双引号，这困扰了我快2个小时找错误.....