Pandasql

0.7.3 · maintenance · verified Sat Apr 11

Pandasql is a Python library that allows users to query pandas DataFrames using SQL syntax. It functions similarly to `sqldf` in R, leveraging SQLite under the hood to provide a familiar interface for data manipulation and analysis for those comfortable with SQL. It is currently at version 0.7.3 and receives limited updates, with alternatives like DuckDB or Polars SQL often recommended for more active development or performance needs.

Warnings

gotcha Pandasql uses SQLite as its backend, which has inherent limitations, such as a default limit of around 999 SQL variables/parameters. This can lead to `OperationalError: too many SQL variables` when querying very wide DataFrames (many columns) or complex queries with numerous parameters.
Fix: Reduce the number of columns in your query, split complex queries into smaller parts, or consider processing data in chunks. For very large datasets or complex analytical needs, consider using dedicated database engines or more performant alternatives like DuckDB or Polars SQL.
gotcha For `sqldf` to correctly identify DataFrames, they must be available in the global or local scope where `sqldf` is called. If `sqldf` is used within a function and the DataFrame is not passed into the function's `locals()` or `globals()`, you might encounter `no such table` errors.
Fix: Ensure DataFrames are defined in the same scope where `sqldf` is called, or explicitly pass `globals()` or `locals()` as the second argument to `sqldf`, e.g., `sqldf(query, globals())` or `sqldf(query, locals())`.
gotcha Pandasql only supports Data Query Language (DQL) operations (e.g., SELECT statements). It does not support Data Manipulation Language (DML) like INSERT, UPDATE, DELETE, or Data Definition Language (DDL) like CREATE, ALTER, DROP for modifying DataFrames or their structure.
Fix: Perform data modification (updates, inserts, deletes) or schema changes directly using pandas DataFrame methods.
deprecated The `pandasql` library receives limited updates and is less actively maintained compared to other SQL-on-DataFrame solutions. For new projects or performance-critical applications, modern alternatives like `duckdb` (via `duckdb.query_df`), Polars SQL, or even native `pandas.DataFrame.query` are often recommended for better performance and ongoing development.
Fix: Evaluate and consider migrating to more actively maintained libraries such as DuckDB or Polars if performance, new features, or long-term support are critical.

Install

pip install pandasql Install with pip

Imports

sqldf
```
from pandasql import sqldf
```
This is the primary function used to execute SQL queries on DataFrames.

Quickstart

This quickstart demonstrates how to import `sqldf`, create a pandas DataFrame, define a SQL query as a string referencing the DataFrame by its variable name, and then execute the query to get a new DataFrame as output.

import pandas as pd
from pandasql import sqldf

# Create a sample pandas DataFrame
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 22],
    'city': ['New York', 'Los Angeles', 'Chicago']
})

# Define an SQL query as a string
query = """
SELECT name, age
FROM df
WHERE age > 23
ORDER BY age DESC
"""

# Execute the SQL query using sqldf
result_df = sqldf(query)

print(result_df)

view raw JSON →