- The database is stored in a single file, making it portable.
- You can use a SQLite database directly from Python, and don't need to run a separate program.
- It implements most SQL commands, enabling you to use most of the statements you're familiar with.
However, particularly when developing larger applications, SQLite has a few downsides that make other database engines more attractive:
- Only one process at a time can write to the database. When you have a complex web application, you may have multiple processes updating information in the database at the same time. For example, on Facebook, one process might handle updating user information, and another might handle generating the news feed.
- You can't take advantage of performance features, such as caching. Because a SQLite database is a single file, and it doesn't require a special program to run, it can't have performance optimizations like caching. When running a site like Facebook that has a ton of traffic, it's important to be able to lookup data quickly.
- SQLite doesn't have any built-in security. With a production website, it's common to want some people to be able to modify tables in a database (write), and others to only be able to make SELECT queries to tables in the database (read). This is because giving someone write access to the database can be a security risk, in that they can update or overwrite data. SQLite doesn't allow for restricting access to a database in this way.
In cases where there will be multiple users or performance is important, PostgreSQL is the most commonly used database engine. PostgreSQL is open-source and is free to download and use.
In this lesson, we'll look at the basics of PostgreSQL, then dive into creating a database, querying data, and some advanced features.
1. SQLite vs PostgreSQL
2. PostgreSQL overview
4. Creating a table
5. SQL Transactions
7. Executing queries
8. Creating a database
9. Deleting a database
10. Next steps