Exploring a DB with natural language
1. Exploring a DB with natural language
In this video, we'll build up the tools we need to convert a range of natural language questions into valid SQL queries. We will do this by combining strings, but in many cases it might make sense to write these more complicated queries using a python module written specifically to interact with your database or API. We will use string operations in these exercises because they're the most general approach, but that doesn't mean this is the best way to do it in your particular case.2. Example messages
We want to generate queries that correspond to the following messages: "show me a great hotel" "I'm looking for a cheap hotel in the south of town" "anywhere so long as it's central"3. Parameters from text
We don't know beforehand which criteria users are going to ask for, and this adds a bit of complexity to how we write our queries. First, let's extract the query parameters from a message. We have an interpreter object which has an already-trained rasa NLU model loaded. We pass the message to interpreter dot parse to get the intent and entities. Now we'll initialized an empty dict called params, and we'll save the entities as key-value pairs in there. To do this we iterate over the entities in the data dict, and use the entities as keys and the values as values in the params dict.4. Creating a query from parameters
Each of our queries has the same base query: "SELECT name from hotels" Recall that to filter the results, we add a "WHERE" clause with a filter like location equals north. The entities we extract from the user request correspond to the filters we want to apply, so we create a list of filters by iterating over the keys in the params dict, and creating a string with the name of the entity, and equals sign, and a question mark. The values will be added later when we call execute. Since there are multiple filters to be applied, we define a string called conditions which contains all of the filters with the string "and" inbetween. We use the string's join method for this, which takes a list of strings as its sole argument. We can use the string's join method again to glue together the base query and the conditions with a WHERE keyword. We save the final query string as final_q. If our SQL is not valid, the execute function will return an error message.5. Responses
Now we want to give appropriate responses depending on what we found in the database. If nothing was found, we might say something like "I'm sorry :( I couldn't find anything like that" , but if we found more results than we would like to show, we should also indicate that to the user. We could say something like "this is one option, but I know others too :)" To map these responses to the number of results, we can put them in a list, and use the number of results as the index to choose a response. To use the final list element as a catch-all, we can use the min function. This chooses the smallest value out of its arguments. The index is then set by calling min with the number of results, and the index of the final element of the list, keeping in mind that python lists are 0-indexed. You can then use the string's format method to insert the actual result from the database.6. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.