Trying to use pandas Dataframes with sql select query

Question

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.

Since there is no aggregation happening here (which one would generally associate to an error of this type being "COUNT" related) it's likely then that you are not supplying the appropriate number of parameters as you have placeholders in your WHERE clause. Can you share relevant details around the parameter list `dfRaw`? — JNevill, Dec 07 '21 at 17:24
You're sending a dataframe into the sql, rather than a value. Use dfRaw['po_number'][0] dfRaw['cyt_part_number'][0] instead of data and dfRaw — Jonathan Leon, Dec 07 '21 at 20:53

score 1 · Accepted Answer · answered Dec 07 '21 at 21:48

1

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() )

answered Dec 07 '21 at 21:48

buddemat

4,552
14
29
49

So dfRaw wont be the same length each time. Should I use a while loop to iterate over each line? – adumbcoder Dec 08 '21 at 00:52
That depends what you're planning to do with the results. To execute the query multiple times with different parameter sets, iterating could be a way. If you want all results from the individual queries in one result set, you could also modify the SQL query to directly deliver you the appropriate result. Something like [this SO post](https://stackoverflow.com/q/8006901/14015737). But it seems to me that would be a follow up question. You should see if this question is resolved, [accept and/or vote if appropriate](https://stackoverflow.com/help/someone-answers) and then ask a new one if needed. – buddemat Dec 08 '21 at 09:05
I ended up doing a while loop. It made it easier to modify the data before it was added to the final files. But this answer worked completely. I've just recently started programming again so just a bit rusty. Thank you for the help! – adumbcoder Dec 08 '21 at 12:43

Trying to use pandas Dataframes with sql select query

1 Answers1