Pandasql: Make python speak SQL | Hacker News Reader

Pandasql: Make python speak SQL

blog.yhat.com

66 pointsjonbaer15 comments

https://github.com/chmp/framequery does the same but without the intermediate sqlite step. Imho it makes much more sense to implement this directly on pandas dataframes.

The advantage of the intermediate step, I believe, is that you get everything in sql for free, without the need to re-implement. From the framequery docs:

The following limitations do exist: no support for non-numeric expressions, no support for outer joins with inequality conditions...and many, many more, SQL is crazy complex. The topics listed explicitly, however, are on the agenda to be fixed.

As the author of framequery: I agree that interpreting SQL on-top of pandas adds additional complexity. However, I belief it also has some benefits: Certainly, it should be more performant by side-stepping serialization and deserialization: "SELECT a + b FROM df " has only minimal overhead compared to "df['a'] + df['b']". It also offers the possibility to interpret queries on different dataframe implementations. Currently, I'm looking into implementing a dask executor (https://github.com/chmp/framequery/tree/feature/FQ-5-add-das...). Finally, I also feel as if "everything in sql" is a bit of stretch: sqlite has its limits, as does framequery. If there is anything in particular, that you are missing, please, feel free to open a GH issue. I would love feedback.

As an aside, framequey is a mere month old and developed in my spare time, the limitations you cited reflect my current priorities and are, as written, planned to be fixed.

Interesting. Previously when I've needed in-memory relational logic, I've used

    conn = sqlite3.connect(':memory:')

For example, an SPOJ context judge. https://github.com/pauldraper/spoj-judge/blob/master/judge.p...

This library is actually doing that under the hood. It loads your dataframes into an in-memory sqlite database as tables and then passes your queries along.

Ha ha, no way....

Well, now I don't feel so hackish.

I get that passing around `locals()` or `globals()` in some IDE isn't a big deal, but I wish the article would mention there's nothing special about the `env` param it expects. AFAIK, you can pass it a dict as to avoid unwanted side-effects.

The docstring for `sqldf` says "This function is meant for backward compatibility only. New users are encouraged to use the PandaSQL class". So I'm not sure why they'd advocate its use, especially if you're going to create a wrapper function anyway.

As I've read over this, I'm not sure I understand how this is different from df.read_sql_query?

read_sql_query queries an actual database, this library allows you to query from the dataframes in your current locals as if they where database tables.

    my_df = pd.DataFrame(...)
    queried_df = pysqldf("select * from my_df where col = 1")

Looks useful :)

EDIT: Actually, looking at the code, it loads your dataframes into SQLite memory tables and uses to_sql and from_sql under the hood to query from there.

I'm not sure why you'd use SQL to query a pandas dataframe when you could just use the actual pandas functions. As far as I know, pandas functionality is way more powerful than SQL, so I don't know why you'd want to limit yourself to just SQL.

Does this mean it is limited to my RAM size?

Yes, it looks like your data gets loaded into a SQLite DB while being retained in the ndarray-based data frame.

Yes. Pandas is still constrained by memory.

I think the HDF support is good enough to count as larger-than-memory.

Also checkout the Odo pydata project which is a conversion tool: http://odo.pydata.org/en/latest/overview.html

Reply on news.ycombinator.com