How to execute .sql file in pymssql

Question

I am trying to execute a sql file in python using pymssql, this file contains BEGIN TRANSACTION, COMMIT TRANSACTION and END, as well as some safety net before and after.

I am trying to open a file in memory and execute the contents:

file = open(options.sqlFile, 'r') sqlFileContents = file.read() file.close() cursor.execute(sqlFileContents) conn.commit() 

But it returns me errors:

 pymssql.ProgrammingError: (102, "Incorrect syntax near 'GO'.DB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 1 5:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102,severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n") 

So, I have two questions:

• Is it possible to execute the request the way I do it?
• is the sql query file itself?

Thanks for any help.

EDIT: Here is the SQL:

Here is the SQL:

 SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON GO IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors GO CREATE TABLE #tmpErrors (Error int) GO SET XACT_ABORT ON GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION PRINT N'Adding Release Version to [admin].[ReleaseHistory]' GO INSERT INTO [admin].[ReleaseHistory] VALUES (GetUTCDate(), '1.7') GO IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION GO IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END GO IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION GO IF @@TRANCOUNT>0 BEGIN PRINT 'The database update succeeded' COMMIT TRANSACTION END ELSE PRINT 'The database update failed' GO DROP TABLE #tmpErrors GO 

Answer 1

Yes, it can be done like this. I often do this; it's cleaner than hard coding the SQL in your code.

Can you add SQL to your post? there may be a spoiled character.

I do it like this (with pyodbc):

 with open('%smysql.sql' % SQL_DIR) as f: sql = f.read() % params # Don't do that with untrusted inputs cursor.execute(sql) cursor.commit() cursor.close() 

EDIT: delete all GO , this is not a real SQL statement. Check this answer: Using "GO" as part of a transaction .

Then it should be good.