postgresql - 将 DialogflowChatbot 与 PostgreSQL 数据库连接
问题描述
我想将 Dialogflow 中的自定义聊天机器人与 PostgreSQL 数据库连接起来。场景是用户向机器人提交他/她的查询,然后它向数据库提交 SQL 查询。我知道应该使用 Webhook/Fulfillment 和 Integration,但问题是如何使用.
直到现在,我尝试通过将...
const pg = require('pg');
const connectionString = process.env.DATABASE_URL || 'postgres://un:pass@localhost:5432/postgres';
const client = new pg.Client(connectionString);
client.connect();
const query = client.query('CREATE TABLE items(id SERIAL PRIMARY KEY, text VARCHAR(40) not null, complete BOOLEAN)');
query.on('end', () => { client.end(); });
...进入 index.js。此外,在尝试使用 Google Assistant 时,我总是会收到一个 Webhook 错误,但没有任何指示性解释:
textPayload: "MalformedResponse: Webhook error (206)"
我不想将机器人连接到其他中间网站;聊天机器人应该能够自己进行查询和检查数据库。
有人对我有建议吗?谢谢!
日志摘录如下:
7:10:03.013 PM
dialogflowFirebaseFulfillment
Ignoring exception from a finished function
7:10:02.995 PM
dialogflowFirebaseFulfillment
Function execution took 10 ms, finished with status code: 200
7:10:02.986 PM
dialogflowFirebaseFulfillment
Billing account not configured. External network is not accessible and quotas are severely limited. Configure billing account to remove these restrictions
7:10:02.986 PM
dialogflowFirebaseFulfillment
Function execution started
7:09:49.540 PM
dialogflowFirebaseFulfillment
Ignoring exception from a finished function
7:09:48.543 PM
dialogflowFirebaseFulfillment
Function execution took 865 ms, finished with status code: 200
7:09:47.678 PM
dialogflowFirebaseFulfillment
Billing account not configured. External network is not accessible and quotas are severely limited. Configure billing account to remove these restrictions
7:09:47.678 PM
dialogflowFirebaseFulfillment
Function execution started
7:09:12.659 PM
dialogflowFirebaseFulfillment
Warning, estimating Firebase Config based on GCLOUD_PROJECT. Initializing firebase-admin may fail
7:08:41.442 PM
dialogflowFirebaseFulfillment
Warning, estimating Firebase Config based on GCLOUD_PROJECT. Initializing firebase-admin may fail
7:04:51.279 PM
dialogflowFirebaseFulfillment
Ignoring exception from a finished function
7:04:51.238 PM
dialogflowFirebaseFulfillment
Function execution took 896 ms, finished with status code: 200
7:04:50.343 PM
dialogflowFirebaseFulfillment
Billing account not configured. External network is not accessible and quotas are severely limited. Configure billing account to remove these restrictions
7:04:50.343 PM
dialogflowFirebaseFulfillment
Function execution started
7:04:33.195 PM
dialogflowFirebaseFulfillment
Warning, estimating Firebase Config based on GCLOUD_PROJECT. Initializing firebase-admin may fail
解决方案
我最近在这里写了关于将 MongoDB 与 Dialogflow 连接起来的文章。
正如我之前所说,您将无法连接到在 localhost:5432 上运行的 Postgres 的本地实例(localhost:8080 上的 MySQL 也没有)。您必须使用ElephantSQL之类的托管Postgres 服务(也适用于除 Firebase/Firestore 之外的其他数据库。)
现在转向您的答案,首先,认真对待此日志消息很重要:
未配置结算帐号。外部网络无法访问,配额受到严格限制。配置结算帐户以删除这些限制
为了解决这个问题,您使用计费帐户非常重要,例如,firebase 的 Blaze 计划来访问外部网络(在您的情况下是作为服务托管的数据库)请参阅此。
代码
'use strict';
const functions = require('firebase-functions');
const {WebhookClient} = require('dialogflow-fulfillment');
const {Card, Suggestion} = require('dialogflow-fulfillment');
const pg = require('pg');
const connectionString = process.env.DATABASE_URL || 'postgres://fsdgubow:K4R2HEcfFeYHPY1iLYvwums3oWezZFJy@stampy.db.elephantsql.com:5432/fsdgubow';
const client = new pg.Client(connectionString);
client.connect();
//const query = client.query('CREATE TABLE items(id SERIAL PRIMARY KEY, text VARCHAR(40) not null, complete BOOLEAN)');
});
process.env.DEBUG = 'dialogflow:debug'; // enables lib debugging statements
exports.dialogflowFirebaseFulfillment = functions.https.onRequest((request, response) => {
const agent = new WebhookClient({ request, response });
console.log('Dialogflow Request headers: ' + JSON.stringify(request.headers));
console.log('Dialogflow Request body: ' + JSON.stringify(request.body));
function welcome(agent) {
const text = 'INSERT INTO items(text, complete) VALUES($1, $2) RETURNING *'
const values = ['some item text', '1' ]
// insert demo in your items database
client.query(text, values)
.then(res => {
console.log(res.rows[0])
//see log for output
})
.catch(e => console.error(e.stack))
// sample query returning current time...
return client.query('SELECT NOW() as now')
.then((res) => {
console.log(res.rows[0]);
agent.add(`Welcome to my agent! Time is ${res.rows[0].now}!`);
})
.catch(e => console.error(e.stack))
}
function fallback(agent) {
agent.add(`I didn't understand`);
agent.add(`I'm sorry, can you try again?`);
}
// Run the proper function handler based on the matched Dialogflow intent name
let intentMap = new Map();
intentMap.set('welcome', welcome);
intentMap.set('Default Fallback Intent', fallback);
// intentMap.set('your intent name here', yourFunctionHandler);
// intentMap.set('your intent name here', googleAssistantHandler);
agent.handleRequest(intentMap);
});
ElephantSQL 控制台
Firebase 日志
谷歌助理
我在这里使用 ElephantSQL 进行演示。寻找更多查询。确保在他们之前放置return以成功执行并避免"MalformedResponse: Webhook error (206)"
!
希望能回答您的所有疑问。祝你好运!
推荐阅读
- amazon-ec2 - 如何在 Ec2 ubuntu 中使用 Django-Crontab
- python - 无法在我的 realsense 相机 D435i 中获取 imu 流
- python - 如何在python中将字符串列表转换为格式正确的json?
- rpc - 如何使用 web3.js 获得 RSK 的最低 gas 价格?
- javascript - 将 JSON 中的函数解析为静态值
- algorithm - 从元素的非重复组合(两个)创建数组,以便在每个集合中所有元素仅出现一次
- react-native - 我正在尝试使用 GET 方法和 Post 方法 React Native 的 AsyncStorage
- graphql - 使用 Apollo + GraphQL 将 Prisma 1 升级到 Prisma 2
- javascript - Console.log() 在给变量赋值后返回 undefined
- python - 忽略 .DS_Store 看门狗