Python 3.x中pandasql报TypeError问题及代码修改求助

Let's break down what's causing that error and fix your code step by step:

Root Causes of the Error #

• You referenced a table named df_city in your SQL query, but the DataFrame you loaded is called df—this mismatch confuses pandasql's table parser, triggering the regex-related TypeError.
• You used StringIO without proper importing (you have import io but didn't reference io.StringIO or import it directly).
• Defining the pysql lambda inside your function is unnecessary and creates scope issues with global variables.
• Your original SQL selects all columns, but you mentioned wanting distinct customer numbers—we'll adjust that to match your goal.

Corrected Code #

# Fix imports first
from io import StringIO
import pandas as pd
import os
import pandasql as pdsql

# Set your working directory (replace with your actual path)
os.chdir("your/actual/path")

# Load data into a DataFrame named df_city (matches your SQL reference)
df_city = pd.read_csv(StringIO("""CUSTOMER_ID, City
21397845, Birmingham
26396841, Anchorage
52396841, Bullhead
67896841, Flagstaff"""))

# Define the pysql helper once outside the function for reusability
pysql = lambda q: pdsql.sqldf(q, globals())

def get_distinct_customers_by_city(city_value):
    # Use an f-string for clear, readable SQL (adds quotes safely)
    query = f"""
        SELECT DISTINCT CUSTOMER_ID 
        FROM df_city 
        WHERE City = '{city_value}'
    """
    # For untrusted input, use parameterized queries to avoid SQL injection:
    # query = "SELECT DISTINCT CUSTOMER_ID FROM df_city WHERE City = ?"
    # df1 = pysql(query, params=(city_value,))
    
    df1 = pysql(query)
    return df1

# Test the function
result = get_distinct_customers_by_city("Birmingham")
print(result)

Key Improvements #

1. Fixed table name mismatch: Renamed the loaded DataFrame to df_city to align with your SQL query, resolving the regex parsing error.
2. Proper StringIO import: Changed to from io import StringIO so CSV loading works as expected.
3. Reusable pysql helper: Moved the lambda outside the function to avoid redundant definitions and scope confusion.
4. Distinct customer IDs: Added DISTINCT to the SQL query to return unique customer numbers, matching your original goal.
5. Safer SQL handling: Used an f-string for clarity, with a note on parameterized queries for untrusted input to prevent SQL injection risks.

Why the Original Error Occurred #

pandasql uses regex to extract table names from your query. When it couldn't find the df_city table in the global scope, the regex parser received invalid input, leading to the TypeError: expected string or bytes-like object.

内容的提问来源于stack exchange，提问作者JDoe