Instructions
Instructions
Instructions: execute the full notebook and then deploy the manifest to https://chat.openai.com/ in the usual manner. Some example questions are in the “Database Query Example Questions” section below.
Summary
Summary
There is a remote database with tables of:
There is a remote database with tables of:
◼
sales orders
◼
customers
◼
products
for a mock heavy equipment manufacturing company with customers in Wyoming.
for a mock heavy equipment manufacturing company with customers in Wyoming.
Database Query Example Questions
Database Query Example Questions
What were our total sales for the last quarter of 2022?
What were our total sales for the last quarter of 2022?
Who is our biggest customer?
Who is our biggest customer?
Using Wolfram, convert that to yen.
Using Wolfram, convert that to yen.
Which product did Badger Mining buy the most of?
Which product did Badger Mining buy the most of?
Show me a table of sales to Badger Mining Corporation, aggregated by year, over the last 10 years.
Show me a table of sales to Badger Mining Corporation, aggregated by year, over the last 10 years.
Make that a DateListPlot
Make that a DateListPlot
Add a three month moving average to the plot
Add a three month moving average to the plot
What is our best selling product?
What is our best selling product?
What are the total sales in dollars for this product?
What are the total sales in dollars for this product?
Convert that to Euros
Convert that to Euros
Get a table of total monthly sales (in dollars) for this product in 2022
Get a table of total monthly sales (in dollars) for this product in 2022
Take this data and use DateListPlot to show monthly sales for 2022.
Take this data and use DateListPlot to show monthly sales for 2022.
What is our most expensive product?
What is our most expensive product?
Tell me what you know about the P900.
Tell me what you know about the P900.
Example Chat session
Example Chat session
Install and load “Wolfram/ChatGPTPluginKit”
Install and load “Wolfram/ChatGPTPluginKit”
URL: https://resources.wolframcloud.com/PacletRepository/resources/Wolfram/ChatGPTPluginKit/
PacletInstall["Wolfram/ChatGPTPluginKit"]
PacletObject
Needs["Wolfram`ChatGPTPluginKit`"]
QueryDatabase plugin deployed at localhost:18013
QueryDatabase plugin deployed at localhost:18013
Create the DatabaseReference
Create the DatabaseReference
databaseReference=Module[{connection,location="XXXX",dbname="XXXX",uname="XXXX",password="XXXX"},DatabaseReference[<|"Backend"->"postgres","Host"->location,"Name"->dbname,"Username"->uname,"Password"->password|>]];
Design the prompt
Design the prompt
$promptSQL="You have access to a PostgreSQL database (called postgres) with tables of sales, product, and customer data. All SQL code should be written in a single line. First, look up the public table names. Then look up the column names for each table. If you use Wolfram Language code keep variable names brief and do not include comments (i.e. any text between `(*` and `*)`), extra whitespaces or tabs. Keep human language replies brief. Please don't ask me if I have more questions or need further assistance.";
Create a helper function to the the database query in the plugin
Create a helper function to the the database query in the plugin
queryDatabase[code_String]:=Module[{response},response=ExternalEvaluate[databaseReference->"NamedRows",code];If[FailureQ[response],StringTemplate["It looks like your query failed. Here is the message from the database:\n\n``\n"][response["Message"]],ExportForm[response,"JSON","ConversionFunction"->(TextString[#]&)]]]
Specify the plugin definition
Specify the plugin definition
pluginSQL=ChatGPTPlugin[<|"Name"->"QueryDatabase","Prompt"->$promptSQL,"Description"->"This plugin generates SQL code based on the table names and columns it finds in the database.","Endpoints"-><|"queryDatabase"->APIFunction["code"->"TextArea",queryDatabase[#code]&]|>|>]
Deploy and create the SocketListener:
Deploy and create the SocketListener:
This should be installed at localhost:18013 on the ChatGPT Plugins location.