Connect to PostgreSQL database in Python

Before starting to connect to PostgreSQL database in Python, the MySQL connector must be imported. In our examples, we’ll be using Visual Studio Code, which is one of the most powerful and widely used editors for developing in Python.

» More Python Examples

Build project in Visual Studio Code (VSC)

We will start by generating a new folder with the example name “PostgreSQLConnection”. Within the VSC terminal we will execute the following commands to create and activate the virtual environment in python3.

Create virtual environment:

virtualenv -p python3 env

If it was executed correctly, we will notice that a new folder with the name of “env” was generated.

Activate virtual environment:

source env/bin/activate

We will notice that it has been activated when (env) appears in the prompt at the beginning of our username, something like the following:

Install drivers

Before installing the necessary controls, we must create a new folder with the name “src” and inside it generate the file “postgresql_connection.py”.

Then, in the VSC terminal with the environment activated, we will execute the following commands to install the necessary connectors:

pip install psycopg2

If you got an error like: ERROR: Failed building wheel for psycopg2, you can fix it by running the following command and trying again:

sudo apt-get install gcc libpq-dev python3-dev

You can verify that it has been installed correctly with the following command:

pip list

Finally we add the code, remember that the parameters change depending on the user, password and database that you will use.

import psycopg2

# geekole.com

try:
    connection=psycopg2.connect(
        host='localhost',
        user='user',
        password='password',
        database='database',
    )

    print("Successful query")
    cursor=connection.cursor()
    cursor.execute("SELECT * FROM users")
    rows=cursor.fetchall()
    for row in rows:
        print(row)
except Exception as ex:
    print(ex)
finally:
    connection.close()
    print("Connection finished.")

Database configuration

To configure the database we need to access PostgreSQL from the Linux terminal with the following commands:

sudo su - postgres

And later when the prompt has changed, we execute the following code to access:

psql

Generate user and database:

Within PostgreSQL we will create a new user to manage the database, you can define the username and password you like:

create user name with password 'password';

Then we will create the database and define the user that will manage it, which is the one we created before:

create database database with owner name;

Finally we give super administrator permissions to our new user:

alter user name with superuser;

Install pgAdmin:

In a new Linux terminal we will install the pgAdmin database manager with the following commands:

sudo apt install curl

curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Access the database

For this example we will also use the DBeaber Community database administration tool, an excellent SQL client software application.

We will start by generating the new connection, remember that the connection is local, so the only fields that will be changed are the name of the database and the user:

Having selected the database, we will generate a new script with the new table:

CREATE TABLE Users (
  ID SERIAL PRIMARY KEY,
  NAME varchar(100) NOT NULL,
  PASSWORD varchar(100) NOT NULL
);

After we add some test records, remember that the tables are stored in Schemas. And it is important to refresh the information so that it updates correctly:

Finally in VSC having the active virtual environment “(env)” we access the “src” folder, and execute our project. Having the following result.

We hope that this example of how to Connect to PostgreSQL database in Python has been very useful for you.