Adding sql functionality to pandas similar to spark sql

[zbrookle]

I would like to add functionality to pandas so that data frames can be queried like database tables, similar to the way that they can be in spark-sql.

I think it should work in a similar fashion.

A table can be registered using register_temp_table(dataframe, table_name).

Then using pandas.query("select * from table_name") you can query the data frame or any other ones registered using standard sql syntax.

I've already implemented the entire thing, but I was told to open an issue for it.

Also I'm aware that there is a package called pandassql but this package actually just puts a data frame into a sql lite database, as opposed to querying a data frame directly, and transforming the sql into pandas methods that are then applied to the data frame.

Motivation:
The motivation for this enhancement is to make pandas more accessible to a crowd of users that may not be as technical and also to provide ease of transition for legacy code in systems like sas that have SQL already embedded in their programs. I'll supply a context free grammar in my documentation to show exactly what this system can handle, but it can basically handle any traditional SQL select statement, including subqueries, joins, where clauses, group by clauses, any aggregate function already supported by pandas, limit, and order by clauses. It also has support for rank and dense_rank window functions. It can't do things that sql wouldn't normally do like cross tab and you can't use a user defined function in it although I think that could be a good add-on.

Datatypes:
The interface supports all pandas datatypes, so to cast something as an integer the syntax would currently be cast(some_number as int64) or cast(some_int as object). I've played around with the idea of varchar, char, bigint and smallint, but I think those would be misleading as those aren't datatypes that are supported by pandas currently.

Errors:
Currently the exceptions that it will throw that come this api are based solely around trying to select from an unregistered table, or from submitting an improperly written sql query, both of which you wouldn't want to silence so there's only one error mode.

Api Choices:
The reason I made the register_temp_table section of the api top level was to avoid attaching a method to DataFrame although if others think it might be better as a method, I would change it in that manner (DataFrame.register_temp_table(table_name)). The reason pandas.query is a top level method is that it's relational in structure. You can select from multiple tables and join them and such and so it wouldn't make sense for it to be on a DataFrame level. The only similarity to the .query DataFrame method though is the name. DataFrame.query is just an alternate way of expressing things like DataFrame[some_condition] whereas my .query encompasses a large amount of the pandas api.

Built In:
I have two reasons that I think this would be better built in. The first is that the target audience for this is less technical pandas users. Part of making this api easier to use is lessening the burden of researching code and learning how python works, so I think that for them to go looking for an external package may be hard to begin with and they would also need to know to look for one.
My second reason is that, from using what I've built, I've found pandas a lot easier to use just as a developer.
Suppose we have a DataFrame with one column called A, it goes from
This code:

dataframe[name_1] = dataframe[a] - 1
dataframe[name_2] = dataframe[a] + 1
dataframe = dataframe[dataframe[name_1] == dataframe[name_2]]
dataframe.drop(columns=['a'], inplace=True)

To this code:
pd.query("select a - 1 as name_1, a + 1 as name_2 from some_table where name_1 = name_2")

Also although I did implement register_temp_table as an api level function, it would serve best as a method on a DataFrame so that's another thing to consider.

I can't really provide any support for the lark part, other than that it seemed like the best tool for what I was making.

I apologize for the style and such, I'll be fixing all that before I'm done. I implemented this outside of pandas first, so that's why there are so many style and documentation discrepancies.

[jreback]

why do you think this would be good to include main pandas itself? this would then present an sql interface to the user. i am not sure this actually adds any value. this might be ok as an external package.

[zbrookle]

You could say the same thing about spark, and yet it does include it, because sql is such a common language and tool for people to use when working with data

[jreback]

This needs some discussion from the core team. Please address the following points, by updating the top and/or in-line responses

1. What is the motivation for this? Provide links / documentation & examples of usage. What can this api handle? What can it not handle. What datatypes are supported / not supported? As a user using this, what kind of coverage do I get of the pandas api. What are the error modes?
2. What is the rationale for this partciular api (top-level functions), as oppossed to DataFrame methods; this looks a lot like query and eval. Why is this not simply an engine='sql'?
3. Why is this better as an in-built pandas package, compared to an externally maintained library that has a hook into the main pandas api. This would present substantially less burden on the maintainers.
4. This needs lark as a dependency, this adds a burden to the system (CI, user facing), and would make this experimental / optional for a while.

I have quite a number of questions about the implementation, testing and code style. Please review the contributing guide to conform.

cc @pandas-dev/pandas-core

I actually find the idea of this interesting as it provides a low friction path for non-python but SQL users to using python / pandas proper.

[datapythonista]

+1 on this, seems like an interesting feature, but as said in the PR, I think this should be implemented outside pandas, at least for now.

Worth noting that Modin is also experimenting on this: https://modin.readthedocs.io/en/latest/UsingSQLonRay/index.html

@devin-petersohn thoughts on this based in your experience?

[zbrookle]

@jreback I updated my description at the top to answer most of your questions and I'm also in the process of writing some more comprehensive documentation

[mrocklin]

Hi All,

A few comments from the Dask side

1. Processing SQL queries is one of our more common feature requests. I agree with @zbrookle
   that there are a ton of people who would use this.
2. Has this been done before? Are there other projects that have tried this in the past? If so, what happened to them?
3. It might make sense to release this outside of Pandas first, but to have strong connections between such a library and the Pandas library with things like references to docs, and maybe review from Pandas maintainers.
4. I'm curious how extensive this parser is. SQL can be intense. It might be interesting to try it against some of the TPC benchmark suites and see what it can and can not handle.
5. I personally would love it if this could handle both Pandas dataframes, and other projects that followed the Pandas API, like Vaex, RAPIDS, Modin, or Dask Dataframes.

Thank you for your effort @zbrookle . I'm excited to see where this goes!

[zbrookle]

@mrocklin I'm almost positive this hasn't been done before, and definitely the need is there. From a quick google search of "pandas sql" I think the need is very apparent, since there are hundreds of guides explaining how to use pandas if you only know sql and when/how to use pandas vs. sql (although I think they mean databases)

In terms of the projects following the pandas api, a few lines of code would make my sql parser compatible with all of them, ie DataFrame would just need to be imported from a different package

[devin-petersohn]

Thanks for the ping @datapythonista I will give some thoughts here.

Dataframes are not relational tables, which means that the SQL equivalents of pandas API calls will be slightly different semantically. Spark does not have a "true" dataframe in the pure sense like pandas or R, but that is a different discussion. It is worth mentioning here because there was a comparison to Spark in the thread.

First there is the question of optimization. If you are simply translating SQL into pandas, there will be no optimization because pandas does not have a query optimizer. The purpose to writing SQL is to provide the system with the entire query upfront to get the optimal execution runtime. You will either have to: (1) accept lower performance for suboptimally written SQL queries, or (2) have to write your own query optimizer. There are some simple query rewriting steps that can be implemented with relatively little engineering overhead.

Second, SQL has different semantics than dataframes. For example, T1 JOIN T2 != T2 JOIN T1. This is because in a dataframe there is an implicit order that the relational data model does not have.

Modin is an academic project, so we are taking a more principled approach to solving some of these problems. We started with treating all APIs as a separate layer and querying the underlying, drilled-down version of the dataframe API, and putting the optimizations at that layer. I'm familiar with the differences between relational tables and dataframes because we are formalizing them.

I think the biggest reason @datapythonista and others would like it not to be in pandas is because there is the question of who will maintain it. If they pull it in, they are agreeing to maintain it themselves, which is a big ask. This is why it should start as an outside project and potentially be brought in later. That said, a lot of people would like this so it would probably generate a large amount of interest if you are willing to maintain it longer term, especially if you made it possible to use with the other projects @mrocklin mentioned.