Pandas sql
State: closed

closes #30480 

This merge will add in a functionality that works as follows:

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. 


Labels:
name: Enhancement
name: IO SQL
description: to_sql, read_sql, read_sql_query
name: Needs Discussion
description: Requires discussion from core team before further action

Comments:
Hello @zbrookle! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:



















There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2019-12-26 06:48:34 UTC
Thanks for the PR - @zbrookle ! Is there an issue open for this?
The pandas sql package actually just puts dataframes in a SQLite database whereas this actually collects metadata and parses the sql string, and then transforms it into actual pandas commands. All of the functionality in this is native to pandas 
@zbrookle see the link i added; this would be interesting if it generated an ibis expression directly 
@jreback I agree that ibis would be interesting, but I don't think it will provide the value that I'm looking to add in this merge. Part of the reason that I think sql would be so useful in pandas is because it will make it more accessible to data analysts that know only sql and not too much about coding, and it will also make it easier for legacy systems that have code written in sql to transition over to pandas from something like SAS. I realize this could be another package, but the same could be said for spark sql and really for certain parts of pandas that already exist, but they're in one cohesive package for ease of use. Putting this into another package will make it just that much harder for the audience that I think this enhancement will help to utilize it
Thanks for the contribution @zbrookle 

But as being said, there needs to be some discussion before we consider accepting a PR for this.

I'm -1 on several things here:
- The `query` function is ambiguous with `DataFrame.query`
- `register_temp_table` doesn't seem important enough to live as a top-level function
- There is no documentation
- Adding new csv files (pandas code is already huge, takes a long time to download, and is we are not careful  it'll grow too big)

I think the best approach here is to implement this as a third-party package. If you require changes in pandas itself to develop this properly as an extension, PRs are more than welcome (open an issue first). If you create an extension make sure you add it to the ecosystem page, so people is aware of it. If eventually this becomes popular and mature enough, we can surely discuss moving it to pandas.

Closing this, as I don't think we'll be merging, and having too many open pull requests makes our life more difficult as maintainers.
I actually like this idea as I can see it being much more useful than the existing `.query` method, though noted on all concerns above. @zbrookle is this something you have actually used? Perhaps in a standalone package?
see my comments / questions [here](https://github.com/pandas-dev/pandas/issues/30480#issuecomment-569055323) which need resolution.
@datapythonista Those are all really valid points, I actually implemented this outside of pandas first, which is why there are so many differences in style. The reason register_temp_table is a top level function is because that seemed like the best way to not interfere with the structure of dataframe objects, although I would prefer if it was dataframe.register_temp_table().
The documentation is something I can definitely add. 
Also in terms of CSVs I can redo my unit testing to use ones that are already present 
> @datapythonista Those are all really valid points, I actually implemented this outside of pandas first, which is why there are so many differences in style. The reason register_temp_table is a top level function is because that seemed like the best way to not interfere with the structure of dataframe objects, although I would prefer if it was dataframe.register_temp_table().
> The documentation is something I can definitely add.
> Also in terms of CSVs I can redo my unit testing to use ones that are already present

Let’s wait for the discussion to reach a conclusion in the issue before writing anymore code. (In the interest of your time)