pythondjangodjango-pyodbc

Execute query with multiline string


I try to directly access DB and render data on my webpage. Evereything works properly with one line SQL query but multiline queries. My SQL Query consist of many joins and uses temp tables! I tried

def merchant_ajax(request):
    data = dict()
    with connections['mssql'].cursor() as cursor:
        query_head = ' '.join((
            "DECLARE @Date_From datetime, @Date_To datetime",
            "SET @Date_From = DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1, 0)",
            "SET @Date_To = dbo.dayEnd(GETDATE())",
            "SELECT mt.MarketTourID, sku.SKUPlanID AS SKUPlanID, IIF(InventoryStatus=1,1,0) AS InventoryStatus,mt.CreationDate",
            "INTO #MT FROM MTMerchandasingREF AS mt",
            "JOIN SKUMainREF AS sku ON sku.ID = SKUMainId",
            "WHERE mt.CreationDate BETWEEN @Date_From AND @Date_To",
            "SELECT mt.MarketTourID,skuP.Name AS PlanName,mt.InventoryStatus,mt.CreationDate",
            "FROM #MT AS mt",
            "JOIN SKUPlanREF AS skuP ON skuP.ID = mt.SKUPlanID",
            "WHERE skuP.MerchGroupID IS NOT NULL",                

        ))        
        cursor.execute(query_head)
        data['data'] = cursor.fetchall()
    return JsonResponse(data,safe=False)

My query works properly on SQl Operations Studio, but I can't realize it on django.

I get the Error django.db.utils.ProgrammingError: No results. Previous SQL was not a query.


Solution

  • Your problem here is that pyodbc can only execute one query at a time. You'll need to do something like this:

    from django.db import connections
    
    with connections['mssql'].cursor() as cursor:
        query = """
            DECLARE @Date_From datetime, @Date_To datetime;
    
            SET @Date_From = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0);
    
            SET @Date_To = GETDATE();
    
            SELECT @Date_From AS date_from, @Date_To AS date_to
        """
    
        cursor.execute(query)
        result = cursor.fetchall()
    
        date_from = result[0][0]
        date_to = result[0][1]
    
        query = """
            SELECT %s AS date_from, %s AS date_to
            INTO #temptable
        """
        cursor.execute(query, (date_from, date_to))
    
        query = """
            SELECT * FROM #temptable
        """
    
        cursor.execute(query)
        result = cursor.fetchall()
    

    However, it might be easier to create a stored procedure in this case, and call that from Django.

    I've refactored processes like these in the past to use the Django ORM and Python instead of relying on T-SQL and it has made my life much easier; it just takes a little while to learn. Good luck!