7 ms·
Pandasql: Make python speak SQL
- tomrod 10y agoAs I've read over this, I'm not sure I understand how this is different from df.read_sql_query?
- filmor 10y agoread_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.
- aw3c2 10y agoDoes this mean it is limited to my RAM size?
- IndianAstronaut 10y agoYes. Pandas is still constrained by memory.
- aw3c2 10y agoI think the HDF support is good enough to count as larger-than-memory.
- sevensor 10y agoYes, it looks like your data gets loaded into a SQLite DB while being retained in the ndarray-based data frame.
- alexpetralia 10y agoI'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.
- deleted 10y ago[deleted]
- paulddraper 10y agoInteresting. 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.py https://github.com/pauldraper/spoj-judge/blob/master/judge.p...
- Wilduck 10y agoThis 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.
- paulddraper 10y agoHa ha, no way.... Well, now I don't feel so hackish.
- guitarbill 10y agoI 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.
- thefoobar 10y agohttps://github.com/chmp/framequery 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.
- goyglcgbbb 10y agoThe 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.
- chpr 10y agoAs 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-dask-support 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.
- jlas 10y agoAlso checkout the Odo pydata project which is a conversion tool: http://odo.pydata.org/en/latest/overview.html http://odo.pydata.org/en/latest/overview.html