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

There is a remote database with tables of:

◼
  • sales orders
  • ◼
  • customers
  • ◼
  • products
  • for a mock heavy equipment manufacturing company with customers in Wyoming.

    Database Query Example Questions

    What were our total sales for the last quarter of 2022?

    Who is our biggest customer?

    Using Wolfram, convert that to yen.

    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.

    Make that a DateListPlot

    Add a three month moving average to the plot

    What is our best selling product?

    What are the total sales in dollars for this product?

    Convert that to Euros

    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.

    What is our most expensive product?

    Tell me what you know about the P900.

    Example Chat session

    Install and load “Wolfram/ChatGPTPluginKit”

    URL: https://resources.wolframcloud.com/PacletRepository/resources/Wolfram/ChatGPTPluginKit/
    PacletInstall["Wolfram/ChatGPTPluginKit"]
    PacletObject
    Name: Wolfram/ChatGPTPluginKit
    Version: 1.3.0
    
    Needs["Wolfram`ChatGPTPluginKit`"]

    QueryDatabase plugin deployed at localhost:18013

    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

    $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

    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

    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:

    This should be installed at localhost:18013 on the ChatGPT Plugins location.