{"id":3206,"library":"pandasql","title":"Pandasql","description":"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.","status":"maintenance","version":"0.7.3","language":"python","source_language":"en","source_url":"https://github.com/yhat/pandasql/","tags":["pandas","sql","dataframe","sqlite","data analysis","query"],"install":[{"cmd":"pip install pandasql","lang":"bash","label":"Install with pip"}],"dependencies":[{"reason":"Core functionality relies on pandas DataFrames.","package":"pandas"}],"imports":[{"note":"This is the primary function used to execute SQL queries on DataFrames.","symbol":"sqldf","correct":"from pandasql import sqldf"}],"quickstart":{"code":"import pandas as pd\nfrom pandasql import sqldf\n\n# Create a sample pandas DataFrame\ndf = pd.DataFrame({\n    'name': ['Alice', 'Bob', 'Charlie'],\n    'age': [25, 30, 22],\n    'city': ['New York', 'Los Angeles', 'Chicago']\n})\n\n# Define an SQL query as a string\nquery = \"\"\"\nSELECT name, age\nFROM df\nWHERE age > 23\nORDER BY age DESC\n\"\"\"\n\n# Execute the SQL query using sqldf\nresult_df = sqldf(query)\n\nprint(result_df)","lang":"python","description":"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."},"warnings":[{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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())`.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Perform data modification (updates, inserts, deletes) or schema changes directly using pandas DataFrame methods.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"deprecated","affected_versions":"All versions (future-looking)"}],"env_vars":null,"search_vec":"'0.7.3':49 'activ':65 'allow':8 'altern':55 'analysi':38,75 'comfort':41 'current':46 'data':35,74 'datafram':13,72 'develop':66 'duckdb':57 'familiar':32 'function':18 'hood':28 'interfac':33 'leverag':24 'librari':6 'like':56 'limit':52 'manipul':36 'need':69 'often':61 'panda':12,70 'pandasql':1,2 'perform':68 'polar':59 'provid':30 'python':5 'queri':11,76 'r':23 'receiv':51 'recommend':62 'similar':19 'sql':15,43,60,71 'sqldf':21 'sqlite':25,73 'syntax':16 'updat':53 'use':14 'user':9 'version':48","created_at":"2026-04-11T09:25:33.504078+00:00","updated_at":"2026-04-16T17:52:20.236873+00:00","problems":[{"fix":"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())`).","cause":"The `sqldf` function is called without being properly imported or without `globals()`/`locals()` explicitly passed as the environment when used within a specific scope, such as a function.","error":"NameError: global name 'sqldf' is not defined"},{"fix":"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`.","cause":"The DataFrame variable name used in the SQL query string does not exactly match the Python DataFrame variable name, or the DataFrame is not in the scope provided to `sqldf`. This also occurs when attempting DDL/DML operations (like `CREATE`, `INSERT`, `UPDATE`, `ALTER TABLE`) as `pandasql` primarily supports DQL (SELECT statements) on in-memory DataFrames.","error":"OperationalError: no such table: <dataframe_name>"},{"fix":"Upgrade your Python environment to Python 3.x, as `pandasql` versions using this syntax are not compatible with Python 2.x.","cause":"This specific syntax error indicates that a version of `pandasql` (which uses Python 3 dictionary unpacking syntax `**`) is being run in a Python 2 environment where this syntax is not supported.","error":"SyntaxError: invalid syntax (pointing to `return {**outer_frame.frame.f_globals, ...}`)"},{"fix":"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.","cause":"This error occurs with large queries or very wide DataFrames because SQLite, the underlying database for `pandasql`, has a default limit (typically around 999) on the number of parameters or variables that can be used in a single SQL query.","error":"OperationalError: too many SQL variables"}],"ecosystem":"pypi","meta_description":null,"install_score":null,"quickstart_score":null,"quickstart_tag":null,"pypi_latest":"0.7.3","cli_name":"","cli_version":null,"type":"library","homepage":null,"github":"https://github.com/yhat/pandasql","docs":null,"changelog":null,"pypi":"https://pypi.org/project/pandasql/","npm":null,"openapi_spec":null,"status_page":null,"smithery":null,"categories":["data","database"],"base_url":null,"auth_type":null,"provenance":{"verified_status":"passing","verified_at":"2026-06-28","last_verified":"2026-08-28","next_check":"2026-07-28","install_tag":null}}