To understand how unit tests are useful, you look at how code is developed. Typically there's a write/compile/run cycle that you iterate on as you write code (or you do it in that order if you're a coding god). Then you test it with some sample inputs to validate that it works correctly. The "test it with some sample inputs" is simply what a unit test is. This is frequently a simpler environment to do so as you can control the inputs in a more fine-grained manner than you might otherwise. If you submit this then at the very least reviewers can have more confidence in the quality of your code or perhaps see some corner cases that may have been missed in your testing as devs in my experience are horrible at communicating exactly what was tested in a change (moreover, they tend to be high-level descriptions that can contain implicit information that's omitted whereas unit tests do not). Once you get it in, pre-submit validation enforces that someone else can't break the assumptions you've made. This is a double-edged sword because sometimes you have to rewrite sections of code that can invalidate a lot of unit tests. However, the true value-add of unit tests is much longer-term. When you fix a bug, you write a regression test so that the bug won't resurface as you keep developing. Importantly you have to provide a comment that links to the bug system you're using that can provide further contextual information about the bug.
Unit tests aren't free as they can be over-engineered to the point of basically being another parallel code base to maintain or they can be over-specified and duplicated so that minor alterations causes a cascading sequence of failures. However, for complex projects with lots of moving parts it can be used to obtain the super useful result of always being able to guarantee a minimum level of quality before you hand off to a manual QA process. Moreover, the unit tests can serve a very useful role of on-boarding less experienced engineers more quickly (even quality coders take time to ramp up) or handing off the software to less motivated/inexperienced/lower quality contractors if the SW has transitioned into maintenance mode. Additionally, code reviews can be hit or miss with respect to catching issues so automated tests ensure that developers can focus on other higher-level discussions rather than figuring out if the code works.
Sure unit tests can go insane by having mocks/stubs everywhere to the point of insanity. I prefer to keep test code minimal & only use mocks/stubs when absolutely necessary because the test environment has different needs (e.g. not sending e-mails, "shutdown" meaning something else, etc). There's no free lunch but I have yet to see a decent combination of well thought-out automation & unit tests failing to ensure the quality maintains over time (the pre-submit automation part is a 100% prerequisite for unit tests to be useful).
One of the things that really sold me on unit tests for Django development was realising that it was quicker to write a test than to open a shell, import what I was working on and run the code manually.