5 ms·

This is probably gonna be sacrilege to the Pythonians, but I often wish there was support for some SQL-like syntax when working with (pandas) data frames. It ce

This is probably gonna be sacrilege to the Pythonians, but I often wish there was support for some SQL-like syntax when working with (pandas) data frames. It certainly would make the process a lot smoother for some tasks.

truculent 7y ago
Isn't that just `pd.DataFrame.query` as outlined in the article? Or do you mean actual full-fat SQL?
wswope 7y ago
You could try dumping your dataframes to an in-memory SQLite instance using the to_sql method and then running queries on that. Not sure how performant the to_sql bit would be, but I'd expect joins in SQLite to be blazing fast compared to Panda's joins.
thenipper 7y ago
I'd love if someone took the next step from Pandas being influenced by R to port dplyr-esque syntax to it.
- alexilliamson 7y ago
  The dplyr syntax is IMO the best feature of the tidyverse
- closed 7y ago
  I've been working on a library over the past year that does exactly that, including generating dbplyr style SQL queries! Would love your feedback :) https://github.com/machow/siuba https://github.com/machow/siuba
  - bkfunk 7y ago
    This looks interesting! Can I ask, what's the origin of the name? Both "siu" and "siuba"? Would be a useful addition to the docs, IMO.
    - closed 7y ago
      Ah, thanks for pointing out the lack of explanation. I'll add one to the readme. It's a transliteration of the cantonese word for minibus, 小巴 :). edit: siu (小) means little!
Shelnutt2 7y ago
TileDB[1] offers an embedded SQL experience for python[2]. We use MariaDB built in embedded mode to allow running sql against TileDB Arrays. This can be combined with pandas.read_sql to load the results directly into a pandas array. I wrote this implementation for the embedded SQL, so happy to answer any questions on it. The embedded SQL in its early stages, but should be fully functional for any queries that MariaDB itself supports. TileDB and the embedded SQL are both open source, TileDB is MIT licensed and the embedded SQL is under GPL. [1] https://tiledb.com/ https://tiledb.com/ [2] https://docs.tiledb.com/developer/api-usage/embedded-sql https://docs.tiledb.com/developer/api-usage/embedded-sql (disclosure: TileDB, inc. employee)
blitmap 7y ago
Something like this: https://en.wikipedia.org/wiki/Language_Integrated_Query https://en.wikipedia.org/wiki/Language_Integrated_Query

truculent 7y ago

Isn't that just `pd.DataFrame.query` as outlined in the article? Or do you mean actual full-fat SQL?

wswope 7y ago

You could try dumping your dataframes to an in-memory SQLite instance using the to_sql method and then running queries on that. Not sure how performant the to_sql bit would be, but I'd expect joins in SQLite to be blazing fast compared to Panda's joins.

thenipper 7y ago

I'd love if someone took the next step from Pandas being influenced by R to port dplyr-esque syntax to it.

alexilliamson 7y ago
The dplyr syntax is IMO the best feature of the tidyverse
closed 7y ago
I've been working on a library over the past year that does exactly that, including generating dbplyr style SQL queries! Would love your feedback :) https://github.com/machow/siuba https://github.com/machow/siuba
- bkfunk 7y ago
  This looks interesting! Can I ask, what's the origin of the name? Both "siu" and "siuba"? Would be a useful addition to the docs, IMO.
  - closed 7y ago
    Ah, thanks for pointing out the lack of explanation. I'll add one to the readme. It's a transliteration of the cantonese word for minibus, 小巴 :). edit: siu (小) means little!

Shelnutt2 7y ago

TileDB[1] offers an embedded SQL experience for python[2]. We use MariaDB built in embedded mode to allow running sql against TileDB Arrays. This can be combined with pandas.read_sql to load the results directly into a pandas array. I wrote this implementation for the embedded SQL, so happy to answer any questions on it. The embedded SQL in its early stages, but should be fully functional for any queries that MariaDB itself supports. TileDB and the embedded SQL are both open source, TileDB is MIT licensed and the embedded SQL is under GPL. [1] https://tiledb.com/ https://tiledb.com/ [2] https://docs.tiledb.com/developer/api-usage/embedded-sql https://docs.tiledb.com/developer/api-usage/embedded-sql (disclosure: TileDB, inc. employee)

blitmap 7y ago

Something like this: https://en.wikipedia.org/wiki/Language_Integrated_Query https://en.wikipedia.org/wiki/Language_Integrated_Query