MISSION 114

Using PostgreSQL

In SQL Fundamentals and SQL Intermediate, we've used a database engine called SQLite. SQLite is one of the most common database engines, and has many advantages:

  • 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. 

Objectives

  • Learn the basics of PostgreSQL.
  • Learn to create and manipulate tables in PostgreSQL.

Mission Outline

1. SQLite vs PostgreSQL
2. PostgreSQL overview
3. Psycopg2
4. Creating a table
5. SQL Transactions
6. Autocommitting
7. Executing queries
8. Creating a database
9. Deleting a database
10. Next steps
11. Takeaways

sql-databases-advanced

Course Info:

Advanced

The median completion time for this course is 6.1 hours. View Details

This course requires a basic subscription. This course includes four missions and one installation tutorial.  It is the 13th course in the Data Analyst in Python path and Data Scientist in Python path.

START LEARNING FREE

Take a Look Inside