import mssql_python

MSSQLPYTHON = (
    "Server=mssql2022,1433;Database=test;UID=scott;PWD=tiger^5HHH;Encrypt=No"
)


def get_isolation_level(dbapi_connection):
    cursor = dbapi_connection.cursor()
    view_name = "sys.system_views"
    cursor.execute(
        (
            "SELECT name FROM {} WHERE name IN "
            "('dm_exec_sessions', 'dm_pdw_nodes_exec_sessions')"
        ).format(view_name)
    )
    row = cursor.fetchone()

    view_name = f"sys.{row[0]}"

    cursor.execute(
        """
            SELECT CASE transaction_isolation_level
            WHEN 0 THEN NULL
            WHEN 1 THEN 'READ UNCOMMITTED'
            WHEN 2 THEN 'READ COMMITTED'
            WHEN 3 THEN 'REPEATABLE READ'
            WHEN 4 THEN 'SERIALIZABLE'
            WHEN 5 THEN 'SNAPSHOT' END
            AS TRANSACTION_ISOLATION_LEVEL
            FROM {}
            where session_id = @@SPID
        """.format(
            view_name
        )
    )
    row = cursor.fetchone()

    cursor.close()

    return row[0].upper()


connection = mssql_python.connect(MSSQLPYTHON)

print(
    f"New connection {connection}: "
    f"isolation level: {get_isolation_level(connection)}"
)

cursor = connection.cursor()
cursor.execute(f"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
cursor.close()

print(
    f"Changed isolation level for {connection}: "
    f"now it's: {get_isolation_level(connection)}"
)

connection.close()

connection = mssql_python.connect(MSSQLPYTHON)
print(
    f"Made another new connection {connection}: "
    f"isolation level: {get_isolation_level(connection)}"
)
connection.close()


mssql_python.pooling(enabled=False)
connection = mssql_python.connect(MSSQLPYTHON)
print(
    f"Disabled pooling and connected again: {connection}: "
    f"isolation level: {get_isolation_level(connection)}"
)