Concurrency is not a problem, as long as you only read. SQLite doesn't allow concurrency on writes, because it never was designed for that. I don't know the internals of the python driver and DBVisualized, but expect both smb, your os and any middleware to cache automatically at least parts of the file, because unless requested so, it is done automatically to speed up filesystem access. Details would depend on os configuration and access details.
I would like to comment on something you didn't ask about, which is the fit of your architecture:
- You need to access a database over the network
- You need to have certain level of concurrency
- You need to control access, because policy reasons (and probably some of those policy reasons could be backups)
- You have expectations about performance
- You need to control in a more fine-grained way what is on memory and what is on disk
- Your database has grown beyond a trivial size
(and those are only the ones that are perceived from your question)
SQLite is a great piece of software, but please consider if it is the right technology for you, as this is almost a paradigmatic case of when not to use it. Sure, you can create a loop device and copy it in memory, and you may get some improvements, but you could also migrate to a distributed system with an integrated cache system (like any other multi-user relational database) and let the technology solve all this issues for you.
Accessing sqlite using a network share is really a hack, and network filesystems are known to have caused data loss due to its limitations towards write consistency.