Return Dynamic MSSQL query from python

Question

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.

read_sql_query returns a DataFrame containing the cursor created by the query. Why do you think it returns the original query? — nicomp
– nicomp, Commented Jan 23, 2023 at 19:08
Not sure I follow your comment. This query is to return a string. I am getting a dataframe with NaN value. — Leo Torres
– Leo Torres, Commented Jan 23, 2023 at 19:22
Ok, should the string result not be returned as a result set in the dataframe? This is my question if I am not using the correct method please advise as to what package should I use for it to return my query string. — Leo Torres
– Leo Torres, Commented Jan 23, 2023 at 20:51
There is no string result. There is a DataFrame. You need to examine the DataFrame and you will see rows and columns resembling the cursor that your query returns. — nicomp
– nicomp, Commented Jan 24, 2023 at 10:07

nicomp · Accepted Answer · 2023-01-25 12:13:42Z

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.QueryPerformance\nwhere 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

Collectives™ on Stack Overflow

Return Dynamic MSSQL query from python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related