import pyodbc

driver = '{SQL Server Native Client 11.0}'
host = ''
database = ''
user = ''
password = ''

conn = pyodbc.connect(driver=driver, host=host, database=database,
                      user=user, password=password)

cursor = conn.cursor()

# Setup - Create table
cursor.execute("IF OBJECT_ID('testTable') IS NOT NULL DROP TABLE testTable")
cursor.execute("CREATE TABLE testTable (col1 INT, col2 INT)")
cursor.commit()

# Setup - Create procedures
cursor.execute("IF OBJECT_ID('usp_insert_and_select') IS NOT NULL DROP PROCEDURE usp_insert_and_select")
cursor.execute(r"""
    CREATE PROCEDURE usp_insert_and_select
    AS
        INSERT INTO testTable (col1, col2)
            VALUES (1, 2)
        SELECT '1', '2'
    """
)

cursor.execute("IF OBJECT_ID('usp_just_select') IS NOT NULL DROP PROCEDURE usp_just_select")
cursor.execute(r"""
    CREATE PROCEDURE usp_just_select
    AS
        SELECT '1', '2'
    """
)

# Run the tests
print("Execute a stored procedure and fetch the restult")
print(" This will work because the sp does not insert anything")
cursor.execute("usp_just_select")
print(cursor.fetchall())

print()
print()

try:
    print("Execute the stored procedure and fetch the restult")
    print(" This will not work because the sp inserts something")
    cursor.execute("usp_insert_and_select")
    print(cursor.fetchall())

except pyodbc.ProgrammingError:
    print()
    print("We raised pyodbc.ProgrammingError!")

finally:
    # Cleanup
    cursor.execute("IF OBJECT_ID('usp_just_select') IS NOT NULL DROP PROCEDURE usp_just_select")
    cursor.execute("IF OBJECT_ID('usp_insert_and_select') IS NOT NULL DROP PROCEDURE usp_insert_and_select")
    cursor.execute("IF OBJECT_ID('testTable') IS NOT NULL DROP TABLE testTable")
    cursor.close()
    conn.close()