Trying to use pandas Dataframes with sql select query-CodePudding

So I'm new to Python and sql. Im using pandas and pyodbc.

firstQuery = pd.read_sql_query(
        '''
            SELECT  PORel.RelQty, PODetail.POLine, PORel.PORelNum, PORel.DueDate
            FROM *****.Erp.PODetail INNER JOIN *****.Erp.PORel ON 
            ((PODetail.Company = PORel.Company) and (PODetail.PONUM = PORel.PONum)) and 
            (PODetail.POLine = PORel.POLine)
            WHERE  PODetail.PONUM = ? and PODetail.Company= ? and PODetail.PartNum = ?
        ''', data, dfRaw )

dfRaw is a Dataframe with 3 columns.

  po_number company cyt_part_number
0    111111    TEST        abc12345

and this is the error I receive when I try to execute the code:

('07002', '[07002] [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

I was wondering if anyone could give me some pointers as why its failing and any helpful tips on how to get it working.

I can give more data if needed. Thanks in advance.

CodePudding user response：

You are passing a pandas dataframe as parameters while read_sql_query expects a list, tuple or dict. You can instead pass the first row as a list:

firstQuery = pd.read_sql_query(
        '''
            SELECT  PORel.RelQty, PODetail.POLine, PORel.PORelNum, PORel.DueDate
            FROM *****.Erp.PODetail INNER JOIN *****.Erp.PORel ON 
            ((PODetail.Company = PORel.Company) and (PODetail.PONUM = PORel.PONum)) and 
            (PODetail.POLine = PORel.POLine)
            WHERE  PODetail.PONUM = ? and PODetail.Company= ? and PODetail.PartNum = ?
        ''', data, params = dfRaw.iloc[0].tolist() )