CREATE PROCEDURE fails silently when called from pyodbc

Question

I am unable to create a SQL Server stored procedure using Python's pyodbc. The command executes correctly and I get no error message however the stored procedure does not appear on the server

import pyodbc

host = 'myServer'
database = 'model'
conn = pyodbc.connect(
        r'DRIVER={SQL Server Native Client 11.0};' +
        r'SERVER=' + host + ';' +
        r'DATABASE=' + database + ';' +
        r'Trusted_Connection=yes'
    )
cursor = conn.cursor()

sql = """
        CREATE OR ALTER PROCEDURE [dbo].[Test] AS 
        SELECT 1  
        """
cursor.execute(sql)
conn.close()

Try doing `conn.commit()` before closing the connection. – Gord Thompson Jul 23 '19 at 08:29 — Gord Thompson, Jul 23 '19 at 08:29
well that's an easy fix! feel free to post it as an answer! – michal111 Jul 23 '19 at 08:37 — michal111, Jul 23 '19 at 08:37

score 1 · Accepted Answer · answered Jul 23 '19 at 13:00

pyodbc connections default to having autocommit disabled as specified in Python's DB API 2.0 spec. In that mode, any changes to the database must be committed by calling commit() on the Connection.

If you want a connection with autocommit enabled, see this answer for details.

CREATE PROCEDURE fails silently when called from pyodbc

1 Answers1