Skip to content

17. Using the PostgreSQL DBMS

The PostgreSQL DBMS is freely available. It is an alternative to the "community" version of MySQL.

We are using it here to demonstrate that it is fairly simple to migrate Python/MySQL scripts to Python/PostgreSQL scripts.

With the MySQL DBMS, the architecture of our scripts was as follows:

Image

With the PostgreSQL DBMS, it will be as follows:

Image

17.1. Installing the PostgreSQL DBMS

PostgreSQL DBMS distributions are available at the URL [https://www.postgresql.org/download/] (May 2019). We demonstrate the installation of the 64-bit Windows version:

Image

Image

  • In [1-4], download the DBMS installer;

Run the downloaded installer:

Image

  • In [6], specify an installation directory;

Image

  • In [8], the [Stack Builder] option is not needed for what we are doing here;
  • in [10], leave the default value;

Image

  • In [12-13], we entered the password [root] here. This will be the password for the DBMS administrator, who is named [postgres]. PostgreSQL also refers to this as the superuser;
  • In [15], leave the default value: this is the DBMS listening port;

Image

  • in [17], leave the default value;
  • In [19], the summary of the installation configuration;

Image

Image

On Windows, the PostgreSQL DBMS is installed as a Windows service that starts automatically. Most of the time, this is not desirable. We will modify this configuration. Type [services] in the Windows search bar [24-26]:

Image

  • in [29], you can see that the PostgreSQL DBMS service is set to automatic mode. Change this by accessing the service properties [30]:

Image

  • In [31-32], set the startup type to Manual;
  • in [33], stop the service;

When you want to start the DBMS manually, return to the [services] application, right-click on the [postgresql] service (34), and start it (35).

17.2. Administering PostgreSQL with the [pgAdmin] tool

Start the Windows service for the PostgreSQL DBMS (see previous paragraph). Then, in the same way you launched the [Services] tool, launch the [pgAdmin] tool, which allows you to administer the PostgreSQL DBMS [1-3]:

Image

You may be prompted for the superuser password at some point. The superuser is named [postgres]. You set this password during the DBMS installation. In this document, we assigned the password [root] to the superuser during installation.

  • In [4], [pgAdmin] is a web application;
  • in [5], the list of PostgreSQL servers detected by [pgAdmin], here 1;
  • in [6], the PostgreSQL server we started;
  • in [7], the DBMS databases, here 1;
  • in [8], the [postgresql] database is managed by the superuser [postgres];

First, let’s create a user [admpersonnes] with the password [nobody]:

Image

Image

  • in [17], we entered [nobody];

Image

  • in [21], the SQL code that the [pgAdmin] tool will send to the PostgreSQL DBMS. This is a way to learn PostgreSQL’s proprietary SQL language;
  • In [22], after confirming with the [Save] wizard, the user [admpersonnes] has been created;

Now we create the [dbpersonnes] database:

Image

Right-click on [23], then [24-25] to create a new database. In the [26] tab, define the database name [27] and its owner [admpersonnes] [28].

Image

  • In [30], the SQL code for creating the database;
  • In [31], after confirming with the [Save] wizard, the database [dbpersonnes] is created;

We will use the [dbpersonnes] database with Python scripts.

17.3. Installing the Python Connector for the PostgreSQL DBMS

Image

The diagram above shows a connector linking Python scripts to the PostgreSQL DBMS. There are several available. We will install the [psycopg2] connector. This is done in a Python terminal (regardless of the directory in which the terminal is open). The connector is installed using the command [pip install psycopg2]:


(venv) C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\troiscouches\v01\tests>pip install psycopg2
Collecting psycopg2
  Downloading psycopg2-2.8.5-cp38-cp38-win_amd64.whl (1.1 MB)
     || 1.1 MB 3.2 MB/s
Installing collected packages: psycopg2
Successfully installed psycopg2-2.8.5

17.4. Porting MySQL scripts to PostgreSQL scripts

Image

  • The folder [1] containing the MySQL scripts is duplicated (Ctrl-C / Ctrl-V), then the file names are changed to match their contents;

17.4.1. [pgres_module]

This module is a copy of the [mysql_module] module (see section |script [mysql-04]: executing an SQL command file|). Change the imports:

Instead of:


# imports
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor

we write:


# imports
from psycopg2 import DatabaseError, InterfaceError
from psycopg2.extensions import connection, cursor

The signature of the [display_info] function was:


def display_info(cursor: MySQLCursor):

It becomes:


def display_info(cursor: cursor)

The signature of the [execute_list_of_commands] function was:


def execute_list_of_commands(connection: MySQLConnection, sql_commands: list,
                             track: bool = False, stop: bool = True, with_transaction: bool = True)

It becomes:


def execute_list_of_commands(connection: connection, sql_commands: list,
                             tracking: bool = False, stop: bool = True, with_transaction: bool = True):

Otherwise, nothing else changes.

17.4.2. script [pgres_01]

The [pgres_01] script is a copy of the [mysql_01] script (see section |script [mysql-01]: connecting to a MySQL database - 1|). The following changes are made:

Instead of:


# Import the mysql.connector module
from mysql.connector import connect, DatabaseError, InterfaceError

We write:


# Import the psycopg2 module
from psycopg2 import connect, DatabaseError, InterfaceError

The rest remains unchanged. The results are the same as with MySQL.

17.4.3. script [pgres_02]

The [pgres_02] script is a copy of the [mysql_02] script (see section |script [mysql-02]: connecting to a MySQL database - 2|). Make the following changes:

Instead of:


# import the mysql.connector module
from mysql.connector import DatabaseError, InterfaceError, connect

we write:


# import the psycopg2 module
from psycopg2 import DatabaseError, InterfaceError, connect

The results are not the same as those of the [mysql_02] script:

1
2
3
4
5
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_02.py
MySQL connection successful to database=dbpersonnes, host=localhost under user=admpersonnes, passwd=nobody
MySQL disconnection successful

Process finished with exit code 0

The [pgres_02] script is as follows:


# Import the mysql.connector module
from psycopg2 import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connection(host: str, database: str, login: str, pwd: str):
    # connects and then disconnects (login, pwd) from the [database] on the [host] server
    # raises a DatabaseError exception if there is a problem
    connection = None
    try:
        # connection
        connection = connect(host=host, user=login, password=pwd, database=database)
        print(
            f"Connection successful to database={database}, host={host} as user={login}, passwd={pwd}")
    finally:
        # Close the connection if it was opened
        if connection:
            connection.close()
            print("Logout successful\n")


# ---------------------------------------------- main
# login credentials
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpeople"

# Log in an existing user
try:
    connect(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as error:
    # display the error
    print(error)

# connection for a non-existent user
try:
    connect(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as error:
    # display the error
    print(error)

Although lines 36–41 should have displayed an error message indicating that the connection to the DBMS failed, nothing is displayed. In fact, upon further investigation, we see that the code does indeed enter the [except] block in lines 35–37, but the [error] variable is set to [None]. This occurs with version 2.8.4 of the [psycopg2] connector.

We can work around this problem by writing a generic but less precise message:


# connecting a non-existent user
try:
    connection(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as error:
    # display the error
    print(f"Error connecting to database [{DATABASE}] by user [xx/yy]")

The results are as follows:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_02.py
Connection successful to database=dbpersonnes, host=localhost under user=admpersonnes, passwd=nobody
Disconnected successfully

Error connecting to the database [dbpersonnes] by user [xx/yy]

Process finished with exit code 0

17.4.4. script [pgres_03]

The script [pgres_03] is a copy of the script [mysql_03] (see section |script [mysql-03]: creating a MySQL table|). The following changes are made to it:

Instead of:


from mysql.connector import DatabaseError, InterfaceError, connect
from mysql.connector.connection import MySQLConnection

we write:


from psycopg2 import DatabaseError, InterfaceError, connect
from psycopg2.extensions import connection

Additionally, the signature of the [execute_sql] function, which was:


def execute_sql(connection: MySQLConnection, update: str):

becomes:


def execute_sql(connection: connection, update: str):

The rest remains unchanged. The result is as follows:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_03.py
create table people (id int PRIMARY KEY, first_name varchar(30) NOT NULL, last_name varchar(30) NOT NULL, age integer NOT NULL, unique(last_name, first_name))  : query successful

Process finished with exit code 0

You can verify the existence of the [people] table using the [pgAdmin] administration tool:

Image

17.4.5. script [pgres_04]

The [pgres_04] script is a copy of the [mysql_04] script (see section |script [mysql-04]: executing an SQL command file|). It uses the [pgres_module] module:


# retrieve the application configuration
import config_04

config = config_04.configure()

# the syspath is configured—we can now perform imports
import sys
from pgres_module import execute_file_of_commands
from psycopg2 import connect, DatabaseError, InterfaceError

The rest remains unchanged.

We create a configuration [pgres pgres-04 without_transaction] as was done in the section |script [mysql-04]: executing an SQL command file|. We also create a configuration [pgres pgres-04 with_transaction].

Executing the [pgres pgres-04 without_transaction] configuration yields the following results:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_04.py false
--------------------------------------------------------------------
Executing the SQL file C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\postgresql/data/commandes.sql without a transaction
--------------------------------------------------------------------
[drop table if exists personnes]: Execution successful
number of rows modified: -1
[create table people (id int primary key, first_name varchar(30) not null, last_name varchar(30) not null, age integer not null, unique (last_name, first_name))] : Execution successful
number of rows modified: -1
[insert into people(id, first_name, last_name, age) values(1, 'Paul','Langevin',48)] : Execution successful
Number of rows modified: 1
[insert into people(id, first_name, last_name, age) values (2, 'Sylvie','Lefur',70)] : Execution successful
Number of rows modified: 1
[select first_name, last_name, age from people]: Execution successful
first_name, last_name, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx: Error (ERROR:  syntax error at or near "xx"
LINE 1: xx
        ^
)
[insert into people(id, first_name, last_name, age) values (3, 'Pierre','Nicazou',35)] : Execution successful
number of rows modified: 1
[insert into people(id, first_name, last_name, age) values (4, 'Geraldine','Colou',26)] : Execution successful
Number of rows modified: 1
[insert into people(id, first_name, last_name, age) values (5, 'Paulette','Girond',56)] : Execution successful
number of rows modified: 1
[select first_name, last_name, age from people]: Execution successful
first_name, last_name, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
('Pierre', 'Nicazou', 35)
('Geraldine', 'Colou', 26)
('Paulette', 'Girond', 56)
*****************
[select last_name, first_name from people order by last_name asc, first_name desc] : Execution successful
last_name, first_name,
************
('Colou', 'Geraldine')
('Girond', 'Paulette')
('Langevin', 'Paul')
('Lefur', 'Sylvie')
('Nicazou', 'Pierre')
************
[select last_name, first_name, age from people where age between 20 and 40 order by age desc, last_name asc, first_name asc] : Execution successful
last_name, first_name, age,
*****************
('Nicazou', 'Pierre', 35)
('Colou', 'Geraldine', 26)
*****************
[insert into people(id, first_name, last_name, age) values(6, 'Josette','Bruneau',46)] : Execution successful
number of rows modified: 1
[update people set age=47 where last_name='Bruneau']: Execution successful
number of rows modified: 1
[select last_name, first_name, age from people where last_name='Bruneau']: Execution successful
last_name, first_name, age,
*****************
('Bruneau', 'Josette', 47)
*****************
[delete from people where last_name='Bruneau']: Execution successful
number of rows modified: 1
[select last_name, first_name, age from people where last_name='Bruneau']: Execution successful
last_name, first_name, age,
*****************
*****************
--------------------------------------------------------------------
Execution complete
--------------------------------------------------------------------
There was 1 error
xx: Error (ERROR: syntax error at or near "xx"
LINE 1: xx
        ^
)

Process finished with exit code 0
  • Line 5: We had to modify the command to drop the [people] table. Unlike the MySQL connector, the PostgreSQL connector throws an exception if the table to be dropped does not exist. The [drop table] command has a variant, [drop table if exists], which does not throw an exception if the table does not exist. We used it here. This is an example where two DBMSs do not behave the same way in similar situations;

The [people] table in the [pgAdmin] tool is as follows:

Image

Running the configuration [pgres pgres_04 with_transaction] yields the following results:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_04.py true
--------------------------------------------------------------------
Executing the SQL file C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\postgresql/data/commandes.sql with a transaction
--------------------------------------------------------------------
[drop table if exists personnes]: Execution successful
number of rows modified: -1
[create table people (id int primary key, first_name varchar(30) not null, last_name varchar(30) not null, age integer not null, unique (last_name, first_name))] : Execution successful
number of rows modified: -1
[insert into people(id, first_name, last_name, age) values(1, 'Paul','Langevin',48)] : Execution successful
Number of rows modified: 1
[insert into people(id, first_name, last_name, age) values (2, 'Sylvie','Lefur',70)] : Execution successful
number of rows modified: 1
[select first_name, last_name, age from people]: Execution successful
first_name, last_name, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx: Error (ERROR:  syntax error at or near "xx"
LINE 1: xx
        ^
)
--------------------------------------------------------------------
Execution complete
--------------------------------------------------------------------
There was 1 error(s)
xx: Error (ERROR: syntax error at or near "xx"
LINE 1: xx
        ^
)

Process finished with exit code 0

The [people] table in the [pgAdmin] tool is as follows:

Image

Here, the result differs from that obtained with MySQL. If we run the scripts under the same conditions—i.e., after running the script without a transaction—we get the following results:

  • With MySQL, the [people] table is empty;
  • with PostgreSQL, the [people] table is not empty;

The difference lies in the different ways these two DBMSs roll back the transaction:

  • MySQL does not roll back the [drop table] and [create table] commands. We end up with an empty [people] table;
  • PostgreSQL rolls back the [drop table] and [create table] commands. The table is restored to the state it was in before the script was executed with a transaction;

17.4.6. script [pgres_05]

The script [pgres_05] is a copy of the script [mysql_05] (see section |script [mysql-05]: using parameterized queries|). The script is modified as follows:

Instead of:


# imports
from mysql.connector import connect, DatabaseError, InterfaceError

we write:


# imports
from psycopg2 import connect, DatabaseError, InterfaceError

The rest remains unchanged.

The results obtained in [pgAdmin] are as follows:

Image

17.5. Conclusion

Porting the MySQL scripts to PostgreSQL scripts was relatively easy. This is an exception. The two DBMSs do not support the same naming conventions for SQL objects (databases, tables, columns, constraints, data types, etc.), and have incompatible SQL extensions… To ensure a simple port, one must adhere to the SQL standard in both cases without attempting to use the DBMS’s proprietary extensions. This comes at the expense of performance.

https://tahe.developpez.com
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.