Postgres is one of the most popular online databases in existence. It powers countless web applications around the world. In this tutorial, we're going to cover how to set up and manipulate this relational database, creating a foundation for a future web application based around Python's Flask framework.
To start, let's install Postgres. The PostgreSQL project provides ready-to-use packages or installers for Linux, macOS, Windows, Solaris, and BSD, but on our Debian-based Linux system we'll just use the package manager by typing: apt-get install postgresql.
Installing the system should start Postgres running as a server daemon. Check this with systemctl status postgresql. If it isn't running, you can start it yourself:
| sudo service postgresql start |
If you want to stop it, then:
| sudo service postgresql stop |
But don't do that just now, because we want to play with it. To start, we're going to become the postgres user. In Linux, you do that with a simple sudo -u postgres -i.
From here, we can access psql, which is a terminal-based interface to Postgres that lets us enter queries and see the responses. To do this, just type psql.
The default user doesn't ship with a password, but leaving it that way will trip us up later when we use Python to access the database. Let's use psql to create a password for Postgres. For demonstration purposes, we'll use one you'd never use in production:
| ALTER USER postgres PASSWORD 'postgres'; |
If the system returns ALTER ROLE, you're all set.
Psql uses the \list or \l command to show the current databases.
| Name | Owner | Encoding | Collate | Ctype | Access privileges |
| postgres | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | Row 1 - Cell 5 |
| template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +postgres=CTc/postgres |
| template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +postgres=CTc/postgres |
postgres is the default database that the database is created with. The other two databases are templates. Postgres uses template1 when you create a database yourself, enabling you to define a standard schema by editing this template. template0 is a standard schema that you can use to replace template1 with an out-of-the-box template in case it becomes corrupted.
Rather than using the standard Postgres database, we'd like to create our own. We're going to document old video games, so let's call it retronerds.
| CREATE DATABASE retronerds; |
\list your databases again and there it is:
| Name | Owner | Encoding | Collate | Ctype | Access privileges |
| postgres | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | Row 1 - Cell 5 |
| retronerds | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | Row 2 - Cell 5 |
| template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +postgres=CTc/postgres |
| template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +postgres=CTc/postgres |
Our video games database needs a table to contain its data. We'd like a title, the game's description, its year of release, a publisher, and a genre.
We'll start by connecting to our database. From within psql, type \c retronerds. Then:
| CREATE TABLE games (id SERIAL PRIMARY KEY, title VARCHAR(255) NOT NULL, description VARCHAR(1024) NOT NULL, yor INTEGER NOT NULL, publisher VARCHAR(255) NOT NULL, genre VARCHAR(255) NOT NULL); |
id gives us an ID unique to each record, called a PRIMARY KEY. The VARCHAR instructions tell us the maximum characters allowed in that field. NOT NULL means that we can't leave that field blank.
Now, check that the table is there with a \dt (for database tables):
| List of relations | |||
|---|---|---|---|
| Schema | Name | Type | Owner |
| public | games | table | postgres |
You can also examine the structure of the table - its schema - with \d games:
| Table "public.games" | ||||
|---|---|---|---|---|
| Column | Type | Collation | Nullable | Default |
| id | integer | Row 1 - Cell 2 | not null | nextval('games_id_seq'::regclass) |
| title | character varying(255) | Row 2 - Cell 2 | not null | Row 2 - Cell 4 |
| description | character varying(1024) | Row 3 - Cell 2 | not null | Row 3 - Cell 4 |
| yor | integer | Row 4 - Cell 2 | not null | Row 4 - Cell 4 |
| publisher | character varying(255) | Row 5 - Cell 2 | not null | Row 5 - Cell 4 |
| genre | character varying(255) | Row 6 - Cell 2 | not null | Row 6 - Cell 4 |
Note that because we used the SERIAL descriptor for id when creating our table, the schema tells us that it will automatically increase id every time we add a record. It has also set up the database to index the records using id.
So far, so good, but there's nothing in our table yet. Querying the table in psql with SELECT * FROM games; gives us:
| id | title | description | yor | publisher | genre |
|---|---|---|---|---|---|
| Row 0 - Cell 0 | Row 0 - Cell 1 | Row 0 - Cell 2 | Row 0 - Cell 3 | Row 0 - Cell 4 | Row 0 - Cell 5 |
Let's change that. We could use psql to manually add records at this point, but eventually we want to use a Flask app to control our database, which means getting a Python program to do it for us. To make that work, we'll use a python library called psycopg2.
Start a new terminal to keep your psql session alive in the original window. In our new terminal, with Python 3 installed, we'll make a virtual environment to support psycopg2. We don't technically need to do this, but it's a way to keep our system tidy and reliable. Think of it as a room where Python can keep all the libraries it needs for one project without cluttering things up for other projects. Python 3 uses the venv command for this, which we had to install manually on Ubuntu:
| sudo apt-get install python3-venv |
Then we'll create the virtual environment, calling it flask-app.
| python3 -m venv flask-app |
Then, 'enter' that room by activating the virtual environment:
| source flask-app/bin/activate |
Your terminal prompt will change to reflect the virtual environment that you're using. Don't forget to deactivate the virtual environment when you want to install Python libraries for projects other than this one.
For a production system, you'd install the library from source. For our purposes, we can use the Python package manager to do it for us.
| python -m pip install psycopg2-binary |
Now it's time to connect to the database. Here's a Python program that will import psycopg2 and create a database connection before creating a nested list with three video game records. For the purposes of the exercise, these are currently hard-coded into the program, although future tutorials will look at cleaner ways to automate the process of adding more entries.
The program then loops through each of those records, building an SQL query for each one by using string formatting to insert the necessary values from each list into the query. The cursor.execute command queues each of these queries, and the conn.commit command writes them to Postgres:
| import psycopg2#set up db connectionconn = psycopg2.connect('dbname=retronerds user=postgres host=localhost password=postgres')cursor = conn.cursor()#create and run transactionsvideogames = [ ['Jet Set Willy', 'Split-screen platformer in which Miner Willy has to collect all the items left in his house after a party.', '1984', 'Software Projects','Platform'], ['Valhalla', 'Collect six mythical Norse objects while kicking around Asgard', '1983', 'Legend', 'Adventure'], ['Chuckie Egg', 'Collect all the eggs before the time expires. Watch out for the hens!', '1983', 'A&F Software', 'Platform']]cols = ['title', 'description', 'yor', 'publisher', 'genre']for game in videogames: SQL = "INSERT INTO games (title, description, yor, publisher, genre) VALUES (%s, %s, %s, %s, %s);" cursor.execute(SQL, game)#commit the transactionsconn.commit()#close the dbconn.close() |
Save this as insert-game.py and then run it with python3 insert-game.py. Now, pop back into Postgres and connect to the retronerds database using the steps outlined above. A quick SELECT * FROM games; reveals that our titles are all there.
| id | title | description | yor | publisher | genre |
|---|---|---|---|---|---|
| 1 | Jet Set Willy | Split-screen platformer in which Miner Willy has to collect all the items left in his house after a party. | 1984 | Software Projects | Platform |
| 2 | Valhalla | Collect six mythical Norse objects while kicking around Asgard | 1983 | Legend | Adventure |
| 3 | Chuckie Egg | Collect all the eggs before the time expires. Watch out for the hens! | 1983 | A&F Software | Platform |
Notice how our Python program didn't specify an ID for any of these records. The database schema knew to insert one each for them and increment it automatically.
We've set up Postgres and shown how to manipulate it using both the native psql tool and Python, via a special library. It's the basis for building a create/read/update/delete (CRUD)-style system that you could use to keep records of any kind.
Other things that you could explore in the future include adding more tables to create a more complex record system with more entities. For example, you might pull the publisher field into its own table, so that you could store more information about each publisher. You could then use a foreign key to link a game to one or more publishers.
However, a system like this also needs a front end so that people who aren't psql ninjas can use it, and in a future tutorial, we'll look at the basics of creating a Python application that will interact with the system in more meaningful ways, including being able to add extra records without directly coding them into a Python program.
