How do I extract values from a string and use those values in a database query?

Question

I'm trying to extract custom entities from a sentence/question and query them against a database, the problem is that I'm having trouble with the extraction of entities.

My table has 10,000s of rows and looks like this:

Car type | Owner
------------------
Sedan    | John
Hatchback| Mary

A sample question I would like the program to answer:

"Who purchased the sedan?"

Ideally, the correct answer here would be John.

Is it possible for me to get the program to understand the context behind the below sentence and answer it properly?

Which means the engine should:

Understand that "sedan" in the sentence "Who purchased the sedan" is an entity (car type) and translate it as Car Type = Sedan.
Understand that the word "purchased" in the sentence means the same as "owner".

Let's assume that the owner is the same as the person who purchased it, no leasing or anything of that kind.

The end goal is to understand the entities in this sentence and convert it into an SQL query.

Use mapping to map those words like sedan: 'car type' and Purchased: 'Owner' and then parse the sentence and replace with these matching words — min2bro, Apr 16 '19 at 04:32
Sounds like Paired t-test, when you have collected the data already. If you have not collected the data yet, you have to collect lists of car types and owners and words indicating owning/buying the car and making Paired t-test for car type and Owner filtered by the "owning" list. https://blog.minitab.com/blog/adventures-in-statistics-2/understanding-t-tests-1-sample-2-sample-and-paired-t-tests — Mika72, Apr 16 '19 at 04:36
@min2bro that's what I thought of as well, but wouldn't it be a bit difficult to identify all such similar words to 'Owned'? If the user asks the question - 'Who bought the sedan?' , ideally the engine should recognize that bought = owner, but it wouldn't as the word 'bought' is not mapped to 'owner'. — crossemup, Apr 16 '19 at 04:38
You are asking a deeper question here, I can suggest you to use some word vector algorithms to solve this where you can train and map those algo to find the contextual meaning of these words which are synonyms like Purchase, bought etc. relates to owner. Thats a harder way though you need to find already trained models for that. — min2bro, Apr 16 '19 at 04:42

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

What you're looking for is called NLTK, which stands for Natural Language (processing) Toolkit.

To give you an idea of what this library can do, here is the demo code from the NLTK homepage showing you how to tokenize and tag text:

import nltk
sentence = "At eight o'clock on Thursday morning Arthur didn't feel very good."
tokens = nltk.word_tokenize(sentence)
print(tokens)
tagged = nltk.pos_tag(tokens)
print(tagged[0:6])

Expected output:

['At', 'eight', "o'clock", 'on', 'Thursday', 'morning', 'Arthur', 'did', "n't", 'feel', 'very', 'good', '.']
[('At', 'IN'), ('eight', 'CD'), ("o'clock", 'JJ'), ('on', 'IN'), ('Thursday', 'NNP'), ('morning', 'NN')]

Now, given how simple your requirements are, you might not even need a library as complex as NLTK to solve your problems, you could use a simple pre determined string search insead.

For example, if you only need to answer a few questions like:

"Who owns [x] type of car?"

"How many people own [x] type of car?"

"What type of car does [x] own?"

You can use Regex to find matches for predetermined questions:

import re

# get the question
question = "What kind of car does Joe own?"

# use regex to find matches for predefined question formats
car_type_for_match = re.findall(r"What type of car does (.*?) own\?", question)

if car_type_for_match and len(car_type_for_match) > 0:
  print("Car type for: {}".format(car_type_for_match))

Which you can later expand with more if statements to add more questions.

Good luck.

How do I extract values from a string and use those values in a database query?

1 Answers1