I would (actually I probably will) use it in 2 cases:

• mono-user open-source software that needs a database: why impose installing MySQL for storing and analyzing log files? SQLite increases the potential audience for such a software. I am thinking about software that needs more than GDBM, SQL queries can be really useful, but less than MySQL,
• software that uses a read-only database: the fact that the DB is a single file makes it easier to duplicate it or move it, installation is much simpler, the fact that it is fast is a plus, and the lack of type is not a problem, as the data is generated outside of the DB. A display/search engine on top of "frozen" XML data for example (will you take the bait Matts?).