Execute query with multiline string

Question

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.

What doesn't work exactly? Do you see an error? If so, show us the stack trace of your error. You should be able to use a multiline string for your query, there's probably another problem. — dirkgroten, Feb 19 '19 at 10:24
Can you provide the multiline query? Try to add some spaces at the beginning of each lines. — Vincent, Feb 19 '19 at 10:39
@Vincent added my query to question! I have tried almost everything — Bagao, Feb 19 '19 at 11:20

score 4 · Accepted Answer · answered Feb 19 '19 at 15:44

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!

Thanks a lot! that one worked for me! Now I will try using procedures — Bagao, Feb 19 '19 at 18:18

Execute query with multiline string

1 Answers1