Running SQL query in pandas

  odbc, pandas, pyodbc, python, sql

I am newbie to sql query .. I am writing code and querying database in python.. Below is my code and I am not sure about the problem with the code:

    import pyodbc
    import pandas as pd
    import numpy as np
    #import datetime
    from pandas import ExcelWriter
    from pandas import ExcelFile
    df_lots=pd.DataFrame()
    
    sql = """
    DECLARE @carts NVARCHAR(25) = 'Large';
    DECLARE @item_type NVARCHAR(50)= 'Produce';
    DECLARE @read_point INT= 0;
    DECLARE @item_type NVARCHAR(MAX)= N'';
    SELECT @item_type+=N',' + [items]
        FROM
            (
                SELECT distinct D.items
                    FROM [dbo].[ITEM_SET_COMPACT_VIEW] D
                    JOIN [dbo].[Placement] E 
                    ON D.items=E.items
                    WHERE D.[items] LIKE 'Large'
                ) x
        ORDER BY items;
    SET @item_type = STUFF(@item_type, 1, 1, '');
    SELECT *
        FROM [dbo].[Get_Data](@item_type, @item_type, @read_point, 1, 0);
    
     
    
    
    """
    try:
        mssql_connection = pyodbc.connect('''
        DRIVER=ODBC Driver 17 for SQL Server;
        SERVER=XXXXXXX;
        DATABASE=XXXXXX;
        UID=XXXXXXXX;
        PWD=XXXXXXXX;
        ''')
    
        with mssql_connection.cursor() as cursor:
            cursor.execute(sql )
            lots=cursor.fetchall()
    
    except pyodbc.Error as ex:
        print(f"Unexpected error {ex}")
    df_lots=pd.DataFrame(np.array(lots))
      

When I run the above code, its giving me following error:

nexpected error No results. Previous SQL was not a query.

    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-e3e7b4c5dae4> in <module>
         45 except pyodbc.Error as ex:
         46     print(f"Unexpected error {ex}")
    ---> 47 df_lots=pd.DataFrame(np.array(lots))
    
    NameError: name 'lots' is not defined

Can anyone help me overcoming this problem??

Source: Python Questions

LEAVE A COMMENT