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.
pip install pandasqlVerified import paths — ran on the pinned version, not inferred.
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.
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.
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())`.
Perform data modification (updates, inserts, deletes) or schema changes directly using pandas DataFrame methods.
Evaluate and consider migrating to more actively maintained libraries such as DuckDB or Polars if performance, new features, or long-term support are critical.
Ensure `sqldf` is imported using `from pandasql import sqldf`. When calling `sqldf`, always pass `globals()` or `locals()` as the second argument to make DataFrames available in the SQL query's scope (e.g., `sqldf(query, globals())`).
Verify that the DataFrame variable name in your Python code precisely matches the 'table' name in your SQL query (it is case-sensitive). For data modification (e.g., `UPDATE`, `INSERT`), use pandas DataFrame methods directly instead of SQL through `pandasql`.
Upgrade your Python environment to Python 3.x, as `pandasql` versions using this syntax are not compatible with Python 2.x.
Reduce the number of columns selected in your query, split complex queries into smaller, more manageable parts, or process data in chunks. For very large datasets, consider using alternatives like DuckDB or Polars SQL, which offer better performance and handle larger query complexities.