ibis

Ibis has sqlglot for parsing and rewriting SQL query graphs; and there's sql-to-ibis: https://github.com/ibis-project/ibis/issues/9529 sqlglot: https://github.com/tobymao/sqlglot : > SQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine [written in Python]. It can be used to format SQL or translate between 24 different dialects like DuckDB, Presto / Trino, Spark / Databricks, Snowflake, and BigQuery. It aims to read a wide variety of SQL inputs and output syntactically and semantically correct SQL in the targeted dialects.

there is Ibis[0] as a fairly mature package. They recently adopted duckdb as the default execution engine and it can give you a nice python dataframe API ontop of duckdb, with hot-swappability towards heavier engines. With tools like this providing a comprehensive python API and the ability to always fall back to raw SQL, i am not sure DuckDB devs should focus on the python API at all beyond basic (to_table, from_table) features. Impressive progress and a real chance to shake up the data tool market, but still a way to go: There is is still much to do especially on large table formats (iceberg/delta) and memory management when running on bigger boxes on cloud. Eg the elusive "Failed to allocate ..." bug[1] is an inhibitor to the claim that big data is dead[2]. As it is, we tried and abandoned DuckDB as a cheaper replacement for some datab

`_` definitely clashes with IPython and Jupyter's use of it. However, if you import it from Ibis then it ceases to be used as "most recent result" and remains the ibis underscore object (unless of course you explicitly assign it to something else). Regarding backend compatibility, there are definitely a few kinds of things that we don't currently abstract over. One is regular expression syntax and another is floating point math (e.g., various algebraic properties that are violated that result in slightly different outputs). Hope you give it a go, and please report issues at https://github.com/ibis-project/ibis.

Please add Ibis Birdbrain https://ibis-project.github.io/ibis-birdbrain/ to the list. Birdbrain is an AI-powered data bot, built on Ibis and Marvin, supporting more than 18 database backends. See https://github.com/ibis-project/ibis and https://ibis-project.org for more details.

Check out ibis! https://github.com/ibis-project/ibis

Ibis might be an option. It has syntax similar to pandas and can compile to a number of types of sql, pyspark, or dask. https://github.com/ibis-project/ibis

I have a significant amount of experience with both of them. For pandas, it is fine for initial exploratory analysis (input, plot, reshape and export, etc). However, it's API has inconsistencies and subtle "features" around silent data coercion that make it hard to use in production. Seaborn is much nicer to work with than matplotlib and Jupyter is useful for making interactive presentations. So I believe Pandas/Jupyter have a place, but then there was a tendency to create a Pandas-like wrapper for all data retrieval such as: https://github.com/ibis-project/ibis https://github.com/blaze/blaze

Ibis uses windowing functions for aggregations if the database supports them. IDK when support for the new SQLite support will be implemented? http://docs.ibis-project.org/sql.html#window-functions [EDIT] I created an issue for this here: https://github.com/ibis-project/ibis/issues/1597

pandas is a great example of data science code at its best and its worst. If you look at the source code you will see every object and every function allows for way too many variations in input options and therefore about 20 conditional statements. For instance, I believe DataFrame's init method can take a dictionary, DataFrame, Series, etc. versus a class method for each one. Contrast that to requests where the public interface is a nice requests.get, requests.post. Yet, have a csv file you are only uploading once or twice to peak at? Then its super efficient. I think my biggest issue is all the effort that goes into pandas-like api's, i.e. https://github.com/ibis-project/ibis. To me, it doesn't make sense to take something stable and known (SQL) and build a complex DSL so it works like pandas.

Good to see I'm not crazy! I think this can be easier than maintain, and with tools like Datagrip now, seems to make a lot of sense. Coming from the data science side, I observed many talented people spending time on projects like https://github.com/cloudera/ibis to abstract SQL into something that I often thought was more verbose, less literal and less portible in reality.

You can do these with Ibis and various SQL engines: * http://docs.ibis-project.org/sql.html#aggregates-considering... * https://github.com/cloudera/ibis/tree/master/ibis/sql (PostgreSQL, Presto, Redshift, SQLite, Vertical) * https://github.com/cloudera/ibis/blob/master/ibis/sql/alchem... (SQLAlchemy)

And separate from the issue of whether it's a good or bad idea, it will shortly be possible to send raw SQL queries to the backend. https://github.com/cloudera/ibis/issues/412