SQLAlchemy, at least, lets you switch from SQLite to Postgres in a configuration file.
Isn't there a useful SQL subset which allows you to switch from one database to another without rewriting? There seems to be such a subset for C, for example, which multiple compilers all interpret the same way, and SQL is a standardized language, too.
Using a wrapper like SQLAlchemy is probably an improvement for most medium and large projects, but it has costs of its own. Using portable ANSI SQL - above the Hello World level - is something basically nobody does unless they make a serious effort, lean on linting tools and forego some of the most useful features of their DBMS. sh is perhaps a closer comparison than C here: it's at least possible to write portable C accidentally.
And neither of these help you if you started with a SQLite database - an excellent choice for most - and decide that you need to support a dozen or a hundred concurrent users.
> Isn't there a useful SQL subset which allows you to switch from one database to another without rewriting?
Only if you're happy throwing away a lot of the features, which is taking away from what makes SQL an attractive solution in the first place. Plus, even some basic things aren't the same between different implementations (e.g., SELECT TOP 1 * FROM t vs SELECT * FROM t LIMIT 1), so you'd really have to be testing with different RDBMSes the whole way through to make sure you didn't accidentally break compatibility.