SyntaxError on HDF queries where right-hand contains string delimiter

[ariddell]

If your index is a string that contains a ' or " there is potentially no consistent way to do an HDF query from disk. The following is real-ish data from the Wikipedia pagecounts dataset. Worked in 0.12, SyntaxError in 0.13.

Easy to reproduce:
This is test.csv

title,hits
Al Lawson',30
'Blind' Willie McTell,20

df = pd.read_csv('test.csv', index_col=0)
store = pd.HDFStore('test.h5')
store.append('df', df)

# bug, SyntaxError
title = """Al Lawson'"""
result = store.select('df', pd.Term('index', '=', title))
result = store.select('df', 'index == {}'.format(title))

# fancy escaping helps, but it isn't a cure all
result = store.select('df', 'index == """{}"""'.format(title))
# SyntaxError
result = store.select('df', "index == '''{}'''".format(title))

[ariddell]

You know, I think it might be as simple as adding a repr() in the right place. (But I think pandas should be doing this, not the user.)

This works:

result = store.select('df', pd.Term('index', '=', repr(title)))

[jreback]

this is fixed in 0.13.1 iirc

in any event you should use the new syntax in your queries rather than Term directly

http://pandas.pydata.org/pandas-docs/stable/io.html#querying-a-table

[cpcloud]

This isn't and never was a bug. The query contains unmatched quotes therefore it's invalid syntax

[cpcloud]

There was an issue with strings containing or or and, but never with querying strings without those tokens

[cpcloud]

The first and second case are really the same thing. If you were to write them outside of the query string they would be the following (invalid) code:

index == Al Lawson'

The third works because it results in

index == """Al Lawson'"""

And the fourth doesn't work because you have the following invalid code

index == '''Al Lawson''''

notice there are 3 quotes on the lhs and 4 on the rhs

[ariddell]

So the user is responsible for doing repr in this case?
So the programmatic way to do it would be this?

title = "Al Lawson'"
result = store.select('df', "index={}".format(repr(title)))

[cpcloud]

@ariddell Yep. Or "index=%r" % title, which is less verbose or "index={!r}".format(title)

[ariddell]

Ok. Thanks!

[jreback]

@ariddell it's impossible for the parser to know that you meant an exact string (and not just an error) if u don't quite it

just like python syntax (which is doing the parsing)

[cpcloud]

@jreback u stole my ⚡ ! :)

[ariddell]

Might be nice to have a note in the docs? (especially since it worked in 0.12)

[cpcloud]

@ariddell Agreed! What did it do there? automatically quote? care to do a doc PR?

[ariddell]

It must have automatically done the repr since it didn't raise an error. I'll admit I don't understand the underlying code or what change happened between 0.12 and 0.13 so perhaps I'm not the best person to update the docs.

[cpcloud]

@ariddell Ok np. I'll take this one then.

[cpcloud]

Reopening for doc issue

[jreback]

yah I guess just make a mention that u need to quote strings (although all strings are quoted in examples) - it IS python syntax - but I suppose u can clarify

[cpcloud]

@ariddell @jreback

I just realized that none of this is even necessary if you just pass in the variable that you're interested in

In [7]: title
Out[7]: "Al Lawson'"

In [8]: store
Out[8]:
<class 'pandas.io.pytables.HDFStore'>
File path: test.h5
/df            frame_table  (typ->appendable,nrows->2,ncols->1,indexers->[index])

In [9]: store.select('df', 'index == title')
Out[9]:
            hits
title
Al Lawson'    30

[1 rows x 1 columns]

I'll make a doc note about this, but there's really no need to mention quoting

[ariddell]

Good to know. I have to say, I'm a little concerned about grabbing the contents of local variables by name. It strikes me as something borrowed from R.

In any event, thanks for your work on this.

[cpcloud]

@ariddell What are you concerned about? I'm happy to explain any of the machinery to you. There's actually a full parse of the query string into something like

expr = Eq(lhs=Term('index', df.index), rhs=Term('title', "Al Lawson'"))

which allows pandas to do some alignment if necessary but ultimately it's passed to PyTables (which passes to numexpr). numexpr's evaluate function does a lookup of the global and local variables of the calling stack frame. We do something similar with the DataFrame.query()/eval() methods.

[ariddell]

numexpr seems like a special case -- since it's departing from the land of Python. But I suppose that's true for PyTables too.

What causes me concern is just the "principle of least surprise" -- in Python you never refer to a local variable in a string.