The virtual address space of a process is allocated and manged in pages (typically 4 KiB blocks). A page can allow a combination of read, write, and execute access, or it may allow no access at all, or no access to user-mode code (i.e. a page in kernel space). If an attempt to access an address in memory fails because the address isn’t allocated or because the type of access isn’t allowed for the page in which it resides, the Windows kernel raises an access violation exception on the faulting thread. Tracking down the cause usually requires testing a debug build, attaching a native debugger to the process, and running with special options such as full page-heap protection. Note that the immediate problem of the access violation may only be a symptom of a bug that leads to a bad pointer value, which could be indirectly from stack or heap corruption.
OS exceptions generally are unrelated to Python runtime exceptions. If an OS exception isn’t handled at a low level by the interpreter or an extension module, the process crashes hard. The _ctypes extension module has a structured exception handler (SEH) that raises OSError if an OS exception occurs during an FFI function call. This is what gets tested by the test_SEH() function that you referenced. The test calls GetModuleHandleA() with an invalid address in order to intentionally trigger an access violation exception.