Learn Web Development with Python (2): Use PostgreSQL and SQLAlchemy

Background

In the previous article “Learn Web Development with Python (1): Use Flask Framework”, we used the Flask framework to develop a simple web application to display weather information for some cities. But these weather information are hard-coded in the code. If we have a lot of weather information, it is not convenient to hard-code them in the code. Therefore, we need a database to store these weather information, and then read these weather information from the database and display them on the web page.

This article will introduce how to use the PostgreSQL database and SQLAlchemy to operate the database to read weather information from the database.

Prerequisites

Before starting this article, make sure you understand how to use the Flask framework to develop a simple web application. If you don’t know, you can refer to the previous article “Learn Web Development with Python (1): Use Flask Framework”.

PostgreSQL

Introduction to PostgreSQL

PostgreSQL is an open source relational database management system. It is characterized by powerful functions, good scalability, support for SQL standards, support for transactions, support for complex queries, support for multiple programming languages, and support for multiple operating systems. PostgreSQL is one of the most popular relational database management systems, so we choose to use the PostgreSQL database as the database for the backend of the web application.

Install and configure PostgreSQL

Installing PostgreSQL on Ubuntu 22 is very simple, just run the following command in the terminal:
1

sudo apt install postgresql
You can use the following command to view the installed version of PostgreSQL:
1

psql --version
After the installation is complete, PostgreSQL will automatically create a user named postgres. This user is a super user and can be used to manage the database. We can use the following command to switch to the postgres user:
1

sudo su postgres
After logging in, we log in to the PostgreSQL database in the command line:
1

psql
The following are commonly used commands in PostgreSQL:
- \l：List all databases
- \?：List all commands
- \c <database_name>：Connect to the specified database
- \d：List all tables in the current database
- \d <table_name>：List the structure of the specified table
- \q：Exit PostgreSQL
- \du：List all users
- \du+：List all users and user details
- \password <user_name>：Change the password of the specified user
- \conninfo：Display the information of the current connected database
Use \du to list all users, you can see that there is currently only one user postgres. We need to create a new user to manage our database. Use the following command to create a new user:
1

CREATE USER test WITH PASSWORD 'test_password';
Here test is the user name, and test is the password. Then use the following command to grant superuser permissions to this user:
1

ALTER USER test WITH SUPERUSER;
Then use the following command to exit PostgreSQL:
1

\q
Then use the following command to exit the postgres user:
1

exit

Then we log in to the PostgreSQL database again with the newly created user test:

1

psql -U test

You may encounter the following error message:

1

FATAL:  Peer authentication failed for user "test"

To solve this problem, you need to modify the configuration file of PostgreSQL (refer to https://zhuanlan.zhihu.com/p/467644334). Use the following command to open the configuration file of PostgreSQL:

1

sudo vim /etc/postgresql/14/main/pg_hba.conf

Here 14 is the version number of PostgreSQL. If your version number is not 14, please modify it to your version number. At the end of the configuration file, you can see the following lines:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

We need to change the peer in the line local all all peer to md5, and then save and exit. Finally, use the following command to restart PostgreSQL:

1

sudo service postgresql restart

Now try to log in to PostgreSQL again, and there will be no error:

1

psql -U test

Finally, create a new database for our web application:
1

CREATE DATABASE weather_db;
Here weather_db is the name of the database. Then use the following command to exit PostgreSQL:
1

\q

Use PostgreSQL in Flask

Install psycopg2 and SQLAlchemy

psycopg2 is a Python library for connecting to the PostgreSQL database, and SQLAlchemy is a Python library for operating the database. We need to install these two libraries. First make sure that we have activated the Python virtual environment, and then use the following command:

1
2


pip install psycopg2-binary
pip install SQLAlchemy

Create database model

Connect to the PostgreSQL database in the Flask application, use the following code:

1
2
3
4
5
6
7


from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://test:test_password@localhost:5432/weather_db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

In the above code, we first import the Flask class and the SQLAlchemy class, then create a Flask application, and then use app.config to configure the database connection information, where SQLALCHEMY_DATABASE_URI is the database connection address, SQLALCHEMY_TRACK_MODIFICATIONS is used to turn off the monitoring of model modifications, because this function will consume extra memory, but we don’t need this function. Finally, we create a db object to operate the database.

Note: The database uses port number 5432 here, which is the default port number of PostgreSQL. If the port number used by your PostgreSQL is not 5432, please change it to your port number. You can view the port number used by PostgreSQL in the configuration file of PostgreSQL. The path of the configuration file is /etc/postgresql/14/main/postgresql.conf, where 14 is the version number of PostgreSQL. If your version number is not 14, please change it to your version number.

Then we need to create a database model to use SQLAlchemy to operate the database in Flask. We first import the SQLAlchemy library, and then create a db object to operate the database. Then we need to define a Weather class to represent the weather information of the city. The Weather class inherits from the db.Model class, so the Weather class can use the db object to operate the database. The Weather class has several attributes, which are similar to the dictionary we defined before. Finally, we need to use the db.create_all() method to create a database table. This method will create a database table according to the database model we defined. The code is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


db = SQLAlchemy(app)

class Weather(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    country = db.Column(db.String(50), nullable=False)
    temp = db.Column(db.Integer, nullable=False)
    feels_like = db.Column(db.Integer, nullable=False)
    icon = db.Column(db.String(50), nullable=False)
    description = db.Column(db.String(50), nullable=False)

db.create_all()

Use the database in the application

Previously we used a dictionary to save weather information, and now we change to use the database to save. The code is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


db.session.add(Weather(
    name = 'London',
    country = 'UK',
    temp = '12',
    feels_like = '11',
    icon = '10d',
    description = 'Moderate rain'
))
db.session.add(Weather(
    name = 'New York',
    country = 'US',
    temp = '20',
    feels_like = '19',
    icon = '01d',
    description = 'Sunny'
))
db.session.commit()

Then we need to read the weather information from the database and pass it to the template. Note that the HTML template previously received an array of dictionaries as a parameter, and now we need to pass an array of Weather objects, so we need to modify the template:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Weather App</title>
    <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}" type="text/css">
</head>
<body>
    <section class="ajax-section">
        <div class="container">
            <ul class="cities">
                {% for wd in weather_data %}
                <li class="city">
                    <h2 class="city-name">{{ wd.name }}, <sup>{{ wd.country }}</sup></h2>
                    <div class="city-temp">{{ wd.temp }}<sup>°C</sup></div>
                    <figcaption>{{ wd.description }}</figcaption>
                    <img class="city-icon" src="https://s3-us-west-2.amazonaws.com/s.cdpn.io/162656/{{ wd.icon }}.svg" alt="{{ wd.description }}">
                </li>
                {% endfor %}
            </ul>
        </div>
    </section>
</body>
</html>

The code for getting database data in Python and passing it to the template is as follows:

1
2


cities_data = Weather.query.all()
return render_template('weather.html', cities_data=cities_data)

Here cities_data is a list, and the elements in the list are Weather objects. We can use the attributes of this object in the template to get weather information.

Run the web application

Try to run our web application with the following command:

1

python app.py

You may encounter the following error message:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


Traceback (most recent call last):
  File "/home/lijin/Documents/www/learn_flask/app.py", line 21, in <module>
    db.create_all()
  File "/home/lijin/Documents/www/learn_flask/env/lib/python3.11/site-packages/flask_sqlalchemy/extension.py", line 900, in create_all
    self._call_for_binds(bind_key, "create_all")
  File "/home/lijin/Documents/www/learn_flask/env/lib/python3.11/site-packages/flask_sqlalchemy/extension.py", line 871, in _call_for_binds
    engine = self.engines[key]
             ^^^^^^^^^^^^
  File "/home/lijin/Documents/www/learn_flask/env/lib/python3.11/site-packages/flask_sqlalchemy/extension.py", line 687, in engines
    app = current_app._get_current_object()  # type: ignore[attr-defined]
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/lijin/Documents/www/learn_flask/env/lib/python3.11/site-packages/werkzeug/local.py", line 508, in _get_current_object
    raise RuntimeError(unbound_message) from None
RuntimeError: Working outside of application context.

This typically means that you attempted to use functionality that needed
the current application. To solve this, set up an application context
with app.app_context(). See the documentation for more information.

This is because our application needs an application context. We need to add the following code to app.py:

1
2


with app.app_context():
    db.create_all()

The final code of app.py is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48


from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://test:test_password@localhost:5432/weather_db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

class Weather(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    country = db.Column(db.String(50), nullable=False)
    temp = db.Column(db.Integer, nullable=False)
    feels_like = db.Column(db.Integer, nullable=False)
    icon = db.Column(db.String(50), nullable=False)
    description = db.Column(db.String(50), nullable=False)

@app.route('/')
def index():
    db.session.add(Weather(
        name = 'London',
        country = 'UK',
        temp = '12',
        feels_like = '11',
        icon = '10d',
        description = 'Moderate rain'
    ))
    db.session.add(Weather(
        name = 'New York',
        country = 'US',
        temp = '20',
        feels_like = '19',
        icon = '01d',
        description = 'Sunny'
    ))
    db.session.commit()
    wd = Weather.query.all()
    print(len(wd))

    return render_template('weather.html', weather_data=wd)

if __name__ == "__main__":
    with app.app_context():
        db.create_all()
        app.run()

Then we run the application again:

1

python app.py

If everything goes well, we can see that our website displays the weather information of two cities in two cards: weather app

Note: Every time you run the application, two pieces of data will be added to the database, so if you run the application multiple times, it may cause multiple duplicate data in the database, and multiple duplicate cards will appear on the web page. At this time, we can use the following code to delete duplicate data:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


from sqlalchemy import text

sql_text = text(
     """DELETE FROM weather
    WHERE id IN (
        SELECT id
        FROM (
            SELECT
                id,
                ROW_NUMBER() OVER (PARTITION BY name) AS row_num
            FROM
                weather
        ) AS numbered_rows
        WHERE row_num > 1
    );
    """)
db.session.execute(sql_text)
db.session.commit()

Summary

So far, we have successfully created a PostgreSQL database, and then used SQLAlchemy to operate the database in the Flask application. But the data in the database is still manually added one by one, which is very inflexible and inconvenient. In the next article, we will introduce how to get user input, and then use the API to get weather information based on the city entered by the user, and then save the obtained weather information to the database.