Is there a way to run a T-SQL script using pyodbc in Python?-CodePudding

I could not find a way to run a T-SQL Script using pyodbc without having to change the script content.

Here's the simple SQL script file I'm trying to run:

SET NOCOUNT ON

USE db_test
GO

CREATE OR ALTER FUNCTION usf_test() 
RETURNS NVARCHAR(10)
BEGIN 
    DECLARE @var NVARCHAR(10)
    SELECT @var = 'TEST'
    RETURN @var
END

and this is the python code I'm using:

cursor.execute(file_content)
cursor.fetchall()

This is the error message: pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'GO'. (102) (SQLExecDirectW)") and yes, this runs with no errors directly on SSMS.

Is there a way to run a T-SQL script using pyodbc (or any other lib that supports SQL Server) in Python?

Notice I can't change the script content, so splitting it into multiple statements is not an option. I have to run the script just as it is on the file.

CodePudding user response：

As mentioned in the comment session "The GO keyword is not standard T-SQL, it's something that's specific to SQL Server Management Studio (the management client, not the underlying SQL Server database server/s) and sqlcmd for separating batches."

I really wanted my app not to change the file logic and just run it, but since it doesn't look possible, I will probably go for this approach to solve this issue.

CodePudding user response：

Establish a Connection:

cnxn_str = ("Driver={SQL Server Native Client 11.0};"
            "Server=USXXX00345,67800;"
            "Database=DB02;"
            "Trusted_Connection=yes;")
cnxn = pyodbc.connect(cnxn_str)

cnxn_str = ("Driver={SQL Server Native Client 11.0};"
            "Server=USXXX00345,67800;"
            "Database=DB02;"
            "UID=Alex;"
            "PWD=Alex123;")
cnxn = pyodbc.connect(cnxn_str)

Query Results to Pandas dataframe:

data = pd.read_sql("SELECT TOP(100) * FROM associates", cnxn)

Modifications Using SQL Server:

cursor = cnxn.cursor()
# first alter the table, adding a column
cursor.execute("ALTER TABLE associates "  
               "ADD fullName VARCHAR(20)")
# now update that column to contain firstName   lastName
cursor.execute("UPDATE associate "  
               "SET fullName = firstName   " "   lastName")

Also, see this link.

https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver16