SQLite is not intended to be a complete replacement to standard Database Management Systems. It IS, however, a good alternative to a full-blown database in some situations. Think of it as a simple database management system with NO RELATIONS - this may or may not be a problem for you and your project.
I would never use SQLite for a web site of any complexity - MySQL and other RDBMS's are simply too plentiful, powerful and easy to use. One great application I've found is to use SQLite in, say, a C++ application such as a game for saving information like player stats and scores. This saves you from having to do file i/o and parsing through text files. In such a situation, talking to an independent database server would cut the player off from the data if he was offline or otherwise not able to communicate with the server.