Repository: pandas-dev/pandas
Pull request: Read from HDF with empty `where` throws an error
- [x] closes #26610
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Problem statement:
Read from HDF with empty `where` throws an error
#### Code Sample
```python
df = pd.DataFrame(np.random.rand(4,4))

where = ''
with pd.HDFStore('test.h5') as store:
    store.put('df', df, 't')
    store.select('df', where = where)
```
#### Problem description
Wanted to be able construct "by hands" and save `where` condition for later, so declare it as variable. But some times constructed `where` becomes empty and code throws an error. 

```python-traceback
Traceback (most recent call last):

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/IPython/core/interactiveshell.py", line 3267, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)

  File "<ipython-input-101-48181c3b59fb>", line 6, in <module>
    store.select('df', where = where)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 740, in select
    return it.get_result()

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 1518, in get_result
    results = self.func(self.start, self.stop, where)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 733, in func
    columns=columns)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 4254, in read
    if not self.read_axes(where=where, **kwargs):

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 3443, in read_axes
    self.selection = Selection(self, where=where, **kwargs)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 4815, in __init__
    self.terms = self.generate(where)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 4828, in generate
    return Expr(where, queryables=q, encoding=self.table.encoding)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/core/computation/pytables.py", line 548, in __init__
    self.terms = self.parse()

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/core/computation/expr.py", line 766, in parse
    return self._visitor.visit(self.expr)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/core/computation/expr.py", line 331, in visit
    return visitor(node, **kwargs)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/core/computation/expr.py", line 335, in visit_Module
    raise SyntaxError('only a single expression is allowed')

  File "<string>", line unknown
SyntaxError: only a single expression is allowed
```

#### Expected Output
When empty string is passed to `where` - just select whole DataFrame. It may be easily achieved by changing last statement to `store.select('df', where = where if where else None)`. But it would be better to add this checking  inside pandas, so user may not worry about it all the times using selection from HDF with `where`.
#### Output of ``pd.show_versions()``

<details>
INSTALLED VERSIONS
------------------
commit: None
python: 3.7.3.final.0
python-bits: 64
OS: Linux
OS-release: 5.0.0-16-generic
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.24.2
pytest: 4.5.0
pip: 19.1.1
setuptools: 41.0.1
Cython: 0.29.7
numpy: 1.16.3
scipy: 1.2.1
pyarrow: None
xarray: 0.12.1
IPython: 7.2.0
sphinx: None
patsy: None
dateutil: 2.8.0
pytz: 2019.1
blosc: None
bottleneck: None
tables: 3.5.1
numexpr: 2.6.9
feather: None
matplotlib: 3.0.3
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10.1
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None
</details>

Hints from issue discussion:
Where is documented as accepting a list so if you use an empty list instead of the string you should be able to manage this the way you want
In API reference it is stated it accepts list, yes. But in [user_guide](http://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#querying-a-table) all examples are with using `where` as string. And also user_guide states: "If a list/tuple of expressions is passed they will be combined via &". The latter may become problem if one would create empty `where = []`, and starts to populate it with conditions - all of them will be forced to be combined via '&' (not '|' as may be wished). So in this case it would be ended to amending single condition inside `where = [condition]` list.

But anyway even here problem is the same. If `where` will ends up as empty list after all processing:  
```python
df = pd.DataFrame(np.random.rand(4,4))

where = []
with pd.HDFStore('test.h5') as store:
    store.put('df', df, 't')
    store.select('df', where = where)
```
Same error will be raised:
```python
Traceback (most recent call last):

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/IPython/core/interactiveshell.py", line 3267, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)

  File "<ipython-input-90-507edb4b117e>", line 6, in <module>
    store.select('df', where = where)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 740, in select
    return it.get_result()

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 1518, in get_result
    results = self.func(self.start, self.stop, where)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 733, in func
    columns=columns)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 4254, in read
    if not self.read_axes(where=where, **kwargs):

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 3443, in read_axes
    self.selection = Selection(self, where=where, **kwargs)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 4815, in __init__
    self.terms = self.generate(where)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/io/pytables.py", line 4828, in generate
    return Expr(where, queryables=q, encoding=self.table.encoding)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/core/computation/pytables.py", line 548, in __init__
    self.terms = self.parse()

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/core/computation/expr.py", line 766, in parse
    return self._visitor.visit(self.expr)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/core/computation/expr.py", line 331, in visit
    return visitor(node, **kwargs)

  File "/home/beforeflight/Coding/Python/_venvs_/main/lib/python3.7/site-packages/pandas/core/computation/expr.py", line 335, in visit_Module
    raise SyntaxError('only a single expression is allowed')

  File "<string>", line unknown
SyntaxError: only a single expression is allowed
```
Thanks for the additional references. If you'd like to take a look and clean up implementation / documentation PRs would certainly be welcome!
To make sure I understand, the proposed fix is for `where=[]` to be treaded the same as `where=None`, i.e. no filtering?
@TomAugspurger, if you are asking me, yes I think it should be that way. Empty `where=[]` -> no filtering  -> whole df will be return.
Sounds right. Can you submit a PR with tests?

> On Jun 3, 2019, at 17:17, BeforeFlight <notifications@github.com> wrote:
> 
> If you are asking me, yes I think it should be that way. Empty where=[] -> no filtering -> whole df will be return.
> 
> —
> You are receiving this because you commented.
> Reply to this email directly, view it on GitHub, or mute the thread.

@TomAugspurger as I explained in neighbour theme with groupby issue - just don't know how to do it correctly. 
@BeforeFlight we have a contributing guide which could be helpful:

https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#testing-with-continuous-integration

If you would like to try but run into specific issues we are of course here to help. You can also use Gitter for development questions
@WillAyd well I would like to. But will start only tomorrow (now is 2pm here). If this api design proposal may serve for my 'github environment understanding' for some time - I would like to try for sure.
I'm not sure how should I test it. While putting my test into pandas context get following for now:
```python
def test_empty_where_lst():
    with tm.ensure_clean() as path:
        df = pd.DataFrame([[1, 2, 3], [1, 2, 3]])
        with pd.HDFStore(path) as store:
            store.put("df", df, "t")
            store.select("df", where=[])
```
But this code raises very specific exception - `SyntaxError`. So should I prefix function with `@pytest.mark.xfail(raises=SyntaxError)`? So be more explicit on what exception is expected.

The reason why I'm asking is [discourage](https://github.com/pandas-dev/pandas/wiki/Testing#additional-imports) of checking for Exceptions.

Commit message: TST: issue #26610. Tests and fix.

Patch to review:
diff --git a/doc/source/whatsnew/v0.25.0.rst b/doc/source/whatsnew/v0.25.0.rst
index 3096a13ac0767..0b2d880f4d395 100644
--- a/doc/source/whatsnew/v0.25.0.rst
+++ b/doc/source/whatsnew/v0.25.0.rst
@@ -649,6 +649,7 @@ I/O
 - Bug in :func:`read_json` where date strings with ``Z`` were not converted to a UTC timezone (:issue:`26168`)
 - Added ``cache_dates=True`` parameter to :meth:`read_csv`, which allows to cache unique dates when they are parsed (:issue:`25990`)
 - :meth:`DataFrame.to_excel` now raises a ``ValueError`` when the caller's dimensions exceed the limitations of Excel (:issue:`26051`)
+- Bug while selecting from HDF store with `where`='' specified (:issue:`26610`). Now the whole DataFrame will be returned from store.
 
 Plotting
 ^^^^^^^^
diff --git a/pandas/io/pytables.py b/pandas/io/pytables.py
index 53ef2395a302a..ca333f040aae9 100644
--- a/pandas/io/pytables.py
+++ b/pandas/io/pytables.py
@@ -95,10 +95,10 @@ def _ensure_term(where, scope_level):
                 wlist.append(w)
             else:
                 wlist.append(Term(w, scope_level=level))
-        where = wlist
+        where = wlist if wlist else None
     elif maybe_expression(where):
         where = Term(where, scope_level=level)
-    return where
+    return where if where != "" else None
 
 
 class PossibleDataLossError(Exception):
diff --git a/pandas/tests/io/test_pytables.py b/pandas/tests/io/test_pytables.py
index 8b5907b920cca..b2ac1f88cfd3a 100644
--- a/pandas/tests/io/test_pytables.py
+++ b/pandas/tests/io/test_pytables.py
@@ -4731,6 +4731,34 @@ def test_read_py2_hdf_file_in_py3(self, datapath):
             result = store['p']
             assert_frame_equal(result, expected)
 
+    @pytest.mark.parametrize("call", [
+        "read_hdf", "select", "select_as_coordinates", "select_as_multiple"
+    ])
+    @pytest.mark.parametrize("where", ["", (), (None, ), [], [None]])
+    def test_select_empty_where(self, call, where):
+        # GH26610
+
+        # Using keyword `where` as '' or (), or [None], etc
+        # while reading from HDF store raises
+        # "SyntaxError: only a single expression is allowed"
+
+        df = pd.DataFrame([[1, 2], [1, 2]], columns=list("ab"))
+        with ensure_clean_path("empty_where.h5") as path:
+            with pd.HDFStore(path) as store:
+                key1 = "df1"
+                key2 = "df2"
+                store.put("df1", df[["a"]], "t")
+                store.put("df2", df[["b"]], "t")
+                if call == "read_hdf":
+                    pd.read_hdf(store, key1, where=where)
+                elif call == "select":
+                    store.select(key1, where=where)
+                elif call == "select_as_coordinates":
+                    store.select_as_coordinates(key1, where=where)
+                elif call == "select_as_multiple":
+                    store.select_as_multiple(
+                        [key1, key2], where=where, selector=key1)
+
 
 class TestHDFComplexValues(Base):
     # GH10447


Code review comments:
diff_hunk: @@ -95,10 +95,10 @@ def _ensure_term(where, scope_level):
                 wlist.append(w)
             else:
                 wlist.append(Term(w, scope_level=level))
-        where = wlist
+        where = wlist if wlist else None
path: pandas/io/pytables.py
text: @user1:
this is not needed

@author:
This is connected with next request. Check it please.

diff_hunk: @@ -4731,6 +4731,34 @@ def test_read_py2_hdf_file_in_py3(self, datapath):
             result = store['p']
             assert_frame_equal(result, expected)
 
+    @pytest.mark.parametrize("call", [
+        "read_hdf", "select", "select_as_coordinates", "select_as_multiple"
+    ])
+    @pytest.mark.parametrize("where", ["", (), (None, ), [], [None]])
+    def test_select_empty_where(self, call, where):
+        # GH26610
+
+        # Using keyword `where` as '' or (), or [None], etc
+        # while reading from HDF store raises
+        # "SyntaxError: only a single expression is allowed"
+
+        df = pd.DataFrame([[1, 2], [1, 2]], columns=list("ab"))
+        with ensure_clean_path("empty_where.h5") as path:
+            with pd.HDFStore(path) as store:
+                key1 = "df1"
+                key2 = "df2"
+                store.put("df1", df[["a"]], "t")
path: pandas/tests/io/test_pytables.py
text: @user1:
use

result = read_hdf(....)

assert_frame_equal(result, df)

@author:
Done.

diff_hunk: @@ -95,10 +95,10 @@ def _ensure_term(where, scope_level):
                 wlist.append(w)
             else:
                 wlist.append(Term(w, scope_level=level))
-        where = wlist
+        where = wlist if wlist else None
     elif maybe_expression(where):
         where = Term(where, scope_level=level)
-    return where
+    return where if where != "" else None
path: pandas/io/pytables.py
text: @user1:
you can just do
```where if where else None``` as bool('') and bool([]) evaluate to False

@author:
I did id at first, but in case some input `where` is some valid expression as string - get after `elif` `where` as `Index`. So this comparsion throws an `The truth value of a Int64Index is ambiguous`.

@user1:
then just do

```
return where if len(where) else None
```

diff_hunk: @@ -4731,6 +4731,34 @@ def test_read_py2_hdf_file_in_py3(self, datapath):
             result = store['p']
             assert_frame_equal(result, expected)
 
+    @pytest.mark.parametrize("call", [
path: pandas/tests/io/test_pytables.py
text: @user1:
it is not necessary to parameterize over the method calls, read_hdf is enough

@author:
Simplified.