How to execute raw sql statement for SQL server from django?

Question

Original db query that works:

SELECT *
FROM [db_name].[dbo].[table]
WHERE name IN (
SELECT name
FROM [db_name].[dbo].[table]
WHERE active = 1
Group by name
Having count(*) > 1
)
order by name

and when I try to execute it from django as

from django.db import connection

def fetch_matching_pn_products():
    with connection.cursor() as cursor:
        cursor.execute("SELECT *"
                        "FROM db_name.dbo.table"
                        "WHERE name IN ("
                        "SELECT name"
                        "FROM db_name.dbo.table"
                        "WHERE active = 1"
                        "Group by name"
                        "Having count(*) > 1"
                        ")"
                        "order by name")
        data = cursor.fetchall()
    return data

returns this error

django.db.utils.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'IN'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '.'. (102)")

I have also tried changing db_name.dbo.table in django to [db_name].[dbo].[table] and table with no success. What needs to be changed so the raw query executes correctly?

pts · Accepted Answer · 2021-10-18 11:16:38Z

2

Add a space after each ".

Without a space the string contains SELECT nameFROM etc., which is incorrect, because FROM should be a separate word.

answered Oct 18, 2021 at 11:16

pts

88.7k26 gold badges117 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ajibola L'Don Jibson Olayanju · Accepted Answer · 2022-09-13 05:21:53Z

0

Why didnt you concatenate it, you can re-write to something like this, check below

from django.db import connection

def fetch_matching_pn_products():
    with connection.cursor() as cursor:
        cursor.execute('SELECT * FROM db_name.dbo.table \
                        WHERE name IN (SELECT name\
                        FROM db_name.dbo.table WHERE active = 1\
                        Group by name Having count(*) > 1)\
                        order by name')
        data = cursor.fetchall()
    return data

refer to this: https://docs.djangoproject.com/en/4.1/topics/db/sql/

answered Sep 13, 2022 at 5:21

Ajibola L'Don Jibson Olayanju

4705 silver badges7 bronze badges

Collectives™ on Stack Overflow

How to execute raw sql statement for SQL server from django?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related