dataframe_sql

dataframe_sql is a Python package that translates SQL syntax into operations on pandas DataFrames, a functionality which is not available in the central pandas package.

Installation

pip install dataframe_sql

Usage

In this simple example, a DataFrame is read in from a csv and then using the query function you can produce a new DataFrame from the sql query.

from pandas import read_csv
from dataframe_sql import register_temp_table, query

my_table = read_csv("some_file.csv")

register_temp_table(my_table, "my_table")

query("""select * from my_table""")

The package currently only supports pandas but there are plans to support dask, rapids, and modin in the future.

SQL Syntax

The SQL syntax for dataframe_sql is exactly the same as the syntax in sql_to_ibis, its underlying package.

You can find the full SQL syntax here

Why use dataframe_sql?

While there are other packages that accomplish the goal of using SQL with pandas DataFrames, all other packages such as pandasql actually use a database on the backend which defeats the purpose of using pandas to begin with. In the case of pandasql which uses SQLite, this can result in major performance bottlenecks. dataframe_sql actually performs native pandas operations in memory on DataFrames, which avoids conflicts that may arise from using external databases.