Return Dynamic MSSQL query from python

I have this script below. I left out the connection details for security purposes but the code executes with out error in python and in MS SQL 2019

import pandas as pd
import pyodbc


sqlInsertScript = """
SELECT 'INSERT INTO dbo.table(' +
    STUFF ((
        SELECT ', [' + name + ']'
        FROM syscolumns
        WHERE id = OBJECT_ID('dbo.table') AND
            name <> 'me'
        FOR XML PATH('')), 1, 1, '') +
    ') 
Select ' +
    STUFF ((
        SELECT ', [' + name + ']'
        FROM syscolumns
        WHERE id = OBJECT_ID('dbo.table') AND
            name <> 'me'
        FOR XML PATH('')), 1, 1, '') + ' 
From dbo.QueryPerformance
where EntryID > Number'
"""

insertquery = pd.read_sql_query(sqlInsertScript,cnxn1)

My issue is that this query returns 0 None in python. I need it to return the string I am creating because I intend to use that query going forward. I know the query works it returns correct text when run from MSSQL SSMS.

Answer

I used pyodbc instead of pandas and your code worked for me.

import pyodbc
import pandas as pd
def connectToDatabase():   
    '''
    Connect to the SQL Server. Note the insecurity here
    '''
    try:
        conn = pyodbc.connect('Driver={SQL Server};'
                              r'your server;'
                              'Database=your db;'
                              'uid=your uid;'
                              'pwd=your pw;')
    except Exception as e: 
        print(e)
        conn = None
    return conn
def getQuery():
    '''
    The query we use to extract data from our SQL Server
    '''
    # This can't go wrong so we don't need error handling
    query = """
SELECT 'INSERT INTO dbo.table(' +
    STUFF ((
        SELECT ', [' + name + ']'
        FROM syscolumns
        WHERE id = OBJECT_ID('dbo.table') AND
            name <> 'me'
        FOR XML PATH('')), 1, 1, '') +
    ') 
Select ' +
    STUFF ((
        SELECT ', [' + name + ']'
        FROM syscolumns
        WHERE id = OBJECT_ID('dbo.table') AND
            name <> 'me'
        FOR XML PATH('')), 1, 1, '') + ' 
From dbo.QueryPerformance
where EntryID > Number'
"""
    return query
conn = connectToDatabase()
cursor = conn.cursor()
# Submit a query to the SQL Server instance and store the results in the cursor object
cursor.execute(getQuery())
for row in cursor:
    print(row)

The output:

('INSERT INTO dbo.table( [A], [B], [C]) nSelect  [A], [B], [C] nFrom dbo.QueryPerformancenwhere EntryID > Number', )

Here is the table I cobbled for this test case:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table](
    [A] [nchar](10) NULL,
    [B] [real] NULL,
    [C] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO