Repository: RobinL/fuzzymatcher

Issue:
OperationalError: malformed MATCH expression
Using `fuzzy_left_join`, I came across the above error while matching addresses.

It seems to relate to the following string: `'16 Orchard Court, Near Stepaside Park, Stepaside, Dublin, ireland'`

(note: I swapped out the street name for another for privacy reasons, but string length and structure is the same)

Code which I ran:
```python
import fuzzymatcher
import pandas as pd

df_left = contacts_unique_addresses
df_right = register
left_on = ["match_string"]
right_on = ["match_string"]

matched = fuzzymatcher.fuzzy_left_join(df_left, df_right, left_on, right_on)
```
And the error. Is it possible that my string is too long? 
```python
OperationalError                          Traceback (most recent call last)
<ipython-input-78-6bd0c667558c> in <module>
      7 right_on = ["match_string"]
      8 
----> 9 matched = fuzzymatcher.fuzzy_left_join(df_left, df_right, left_on, right_on)

~\Anaconda3\lib\site-packages\fuzzymatcher\__init__.py in fuzzy_left_join(df_left, df_right, left_on, right_on, left_id_col, right_id_col)
     39     m = Matcher(dp, dg, s)
     40     m.add_data(df_left, df_right, left_on, right_on,  left_id_col, right_id_col)
---> 41     m.match_all()
     42 
     43     return m.get_left_join_table()

~\Anaconda3\lib\site-packages\fuzzymatcher\matcher.py in match_all(self)
     90 
     91         # Get a table that contains only the matches, scores and ids
---> 92         self.link_table = self._match_processed_data()
     93 
     94     def get_formatted_link_table(self):

~\Anaconda3\lib\site-packages\fuzzymatcher\matcher.py in _match_processed_data(self)
    134                 log.debug(str_template.format(counter, (counter/total)*100, diff.minutes, diff.seconds))
    135 
--> 136             this_record.find_and_score_potential_matches()
    137             link_table_list.extend(this_record.get_link_table_rows())
    138 

~\Anaconda3\lib\site-packages\fuzzymatcher\record.py in find_and_score_potential_matches(self)
     74     def find_and_score_potential_matches(self):
     75         # Each left_record has a list of left_record ids
---> 76         self.matcher.data_getter.get_potential_match_ids_from_record(self)
     77 
     78     def get_link_table_rows(self):

~\Anaconda3\lib\site-packages\fuzzymatcher\data_getter_sqlite.py in get_potential_match_ids_from_record(self, rec_left)
     91 
     92         for token_list in token_lists:
---> 93             self._search_specific_to_general_single(token_list, rec_left)
     94             if not self._found_enough_matches(rec_left):
     95                 self._search_specific_to_general_band(token_list, rec_left)

~\Anaconda3\lib\site-packages\fuzzymatcher\data_getter_sqlite.py in _search_specific_to_general_single(self, token_list, rec_left)
    115         for i in range(len(token_list)):
    116             sub_tokens = token_list[i:]
--> 117             new_matches = self._tokens_to_matches(tuple(sub_tokens))
    118 
    119             self._add_matches_to_potential_matches(new_matches, rec_left)

~\Anaconda3\lib\site-packages\fuzzymatcher\data_getter_sqlite.py in _tokens_to_matches(self, tokens, misspelling)
    190 
    191         cur = self.con.cursor()
--> 192         cur.execute(sql)
    193         results = cur.fetchall()
    194 

OperationalError: malformed MATCH expression: [NEAR IRELAND STEPASIDE STEPASIDE ORCHARD 16 COURT PARK DUBLIN]
```

Discussion:

Patch:
diff --git a/fuzzymatcher/data_getter_sqlite.py b/fuzzymatcher/data_getter_sqlite.py
index c54eec8..e97808e 100644
--- a/fuzzymatcher/data_getter_sqlite.py
+++ b/fuzzymatcher/data_getter_sqlite.py
@@ -177,10 +177,21 @@ class DataGetter:
             """
 
         # This fails if the special tokens 'and' or 'or' are in fts string!  See issue 35!
-        tokens_to_remove = ["AND", "OR"]
-        tokens = [t for t in tokens if t not in tokens_to_remove]
+        tokens_to_escape = ["AND", "OR", "NEAR"]
+
+        def escape_token(t):
+            # return t
+            if t in tokens_to_escape:
+                return '"' + t + '"'
+            else:
+                return t
+
+
+        tokens = [escape_token(t) for t in tokens]
+
         fts_string = " ".join(tokens)
 
+
         if misspelling:
             table_name = "_concat_all_alternatives"
         else:
@@ -188,6 +199,7 @@ class DataGetter:
 
         sql = get_records_sql.format(table_name, fts_string, self.return_records_limit)
 
+
         cur = self.con.cursor()
         cur.execute(sql)
         results = cur.fetchall()


Test patch:
diff --git a/tests/data/left_token_escape.csv b/tests/data/left_token_escape.csv
new file mode 100644
index 0000000..6da12f6
--- /dev/null
+++ b/tests/data/left_token_escape.csv
@@ -0,0 +1,5 @@
+id,fname,mname,lname,dob,another_field
+1,or,or and,and,20/05/1980,other data
+2,or,or,or smith or,15/06/1990,more data
+3,near,and,near,20/05/1960,another thing
+
diff --git a/tests/data/right_token_escape.csv b/tests/data/right_token_escape.csv
new file mode 100644
index 0000000..1b3232b
--- /dev/null
+++ b/tests/data/right_token_escape.csv
@@ -0,0 +1,4 @@
+id,name,middlename,surname,date,other
+1,or,or,or smith or,15/06/1990,more data
+2,near,and,near,20/05/1960,another thing
+3,or,or and,and,20/05/1980,other data
\ No newline at end of file
diff --git a/tests/test_misc.py b/tests/test_misc.py
index cbb1668..2377c62 100644
--- a/tests/test_misc.py
+++ b/tests/test_misc.py
@@ -17,4 +17,31 @@ class TestNulls(unittest.TestCase):
 
         on = ["first_name", "surname", "dob", "city"]
 
-        flj = link_table(df_left, df_right, on, on)
\ No newline at end of file
+        flj = link_table(df_left, df_right, on, on)
+
+
+class TestNulls(unittest.TestCase):
+    """
+    Test what happens when the user provides input data with
+    fts4 match expression keyworks like AND, OR, NEAR
+    """
+
+    def test_nulls_no_errors(self):
+        """
+
+        """
+
+
+        df_left = pd.read_csv("tests/data/left_token_escape.csv")
+        df_right = pd.read_csv("tests/data/right_token_escape.csv")
+
+        # Columns to match on from df_left
+        left_on = ["fname", "mname", "lname"]
+
+        # Columns to match on from df_right
+        right_on = ["name", "middlename", "surname"]
+
+        on = ["first_name", "surname", ]
+
+        flj = link_table(df_left, df_right, left_on, right_on,
+                         left_id_col="id", right_id_col="id")