OpenAI for Queries

Summary

• ORSANCO provided us with Access Databases containing multiple tables and complex queries.
• We also have little to no documentation on these tables and their association with queries, leaving us to understand it by looking at the ERD diagrams or analyzing queries.
• In order to simplify the process, we used OpenAI API to get a justified explanation of all the queries.

Data locations

• The SQLite database used in the process is on sharepoint as AI_database.db3
• The code used to process the DB is available on GitHub here: orsanco-db-django

Procedure

• We gathered all the queries along with the respective database and query names in AI_database.db3.
• The structure of the DB is as follows:

    TABLE "accessdb_queries" (
      "Database" TEXT,
      "QueryName" TEXT,
      "SQL" TEXT,
      "Results" TEXT
    )
• We tested multiple question prompts and arrived at this : Provide only the structure of the tables followed by justified explanation of this query, in 350 tokens?
• After customizing the locations and queries along with parameters sent to the API, we run the script use-openai.py.
• This runs the question pormpt across all rows in the DB, focusing on the SQL column which has queries and the responses are updated in the Results column.
• The code also has functionality to handle the rows which missed the previous run of updating, most likely due to model unavailability issue.
• Resulting DB with updated Results is then used for a better understanding of the data.

Possible future enhancements

• Clustering of queries or the query results
• Converting Access DB queries to Postgres queries using OpenAI/ChatGPT.