Creating a virtual environment

First, open the Command Prompt on Windows or Terminal on Unix-like systems.

Second, create a new directory to store the project files such assuppliers:

mkdir suppliers

Third,create a new virtual environment calledvenv using the built-invenv module:

python -m venv venv

Finally, activate the virtual environment on Windows:

venv/scripts/activate

on Unix-like systems:

source venv/bin/activate

Installing the psycopg2 module

First, install thepsycopg2 package using the followingpip command:

pip install psycopg2

Second, create therequirements.txt file:

pip freeze> requirements.txt

Creating a new database

First,connect to the PostgreSQL server using thepsql client tool:

psql-U postgres

Second,create a new database calledsuppliers:

CREATE DATABASE suppliers;

Third, exit thepsql:

exit

Connecting to the PostgreSQL database from Python

First, create a configuration file calleddatabase.ini in the project directory to store database connection parameters:

[postgresql]host=localhostdatabase=suppliersuser=YourUsernamepassword=YourPassword

In thedatabase.ini file, you need to replace theYourUsername andYourPassword with the real ones.

Second, create a new file calledconfig.py in the project directory and define a function calledload_config() that reads configuration data from thedatabase.ini file:

from configparserimport ConfigParserdef load_config(filename='database.ini', section='postgresql'):    parser= ConfigParser()    parser.read(filename)    # get section, default to postgresql    config= {}    if parser.has_section(section):        params= parser.items(section)        for paramin params:            config[param[0]] = param[1]    else:        raise Exception('Section{0} not found in the{1} file'.format(section, filename))    return configif __name__ == '__main__':    config= load_config()    print(config)

Theconfig.py module uses the built-inconfigparser module to read data from thedatabase.ini file.

By using thedatabase.ini, you can change the PostgreSQL connection parameters when moving the code to different environments such as testing or production.

Notice that if you git source control, you need to add thedatabase.ini to the.gitignore file to avoid committing sensitive information to a public repository like GitHub:

database.ini

Third, create a new file calledconnect.py that uses theconfig.py module to read the database configuration and connect to the PostgreSQL:

import psycopg2from configimport load_configdef connect(config):    """ Connect to the PostgreSQL database server """    try:        # connecting to the PostgreSQL server        with psycopg2.connect(**config) as conn:            print('Connected to the PostgreSQL server.')            return conn    except (psycopg2.DatabaseError, Exception)as error:        print(error)if __name__ == '__main__':    config= load_config()    connect(config)

To connect to thesuppliers database, you use theconnect() function of thepsycopg2 module.

Theconnect() function creates a new database session and returns a new instance of theconnection class.

To call theconnect() function, you specify the PostgreSQL database parameters as a connection string and pass it to the function like this:

conn= psycopg2.connect("dbname=suppliers user=YourUsername password=YourPassword")

Alternatively, you can use keyword arguments:

conn = psycopg2.connect(    host="localhost",    database="suppliers",    user="YourUsername",    password="YourPassword")

The following is the list of the connection parameters:

• database: the name of the database that you want to connect.
• user: the username used to authenticate.
• password: password used to authenticate.
• host: database server address e.g., localhost or an IP address.
• port: the port number that defaults to 5432 if it is not provided.

Since we use theconfig.py module, we can pass the configuration to theconnect() function and unpack it using the** operator:

with psycopg2.connect(**config) as conn:

Inpsycopg2, thewith statement doesn't automatically close the database connection — only the transaction is closed. However, inpsycopg3, usingwith connection will close the connection at the end of thewith block. Seewith connection in thepsycopg documentation for details.

Executing the connect.py module

To execute theconnect.py file, you use the following command:

python connect.py

Output:

Connected to the PostgreSQL server.

The output indicates that you have successfully connected to the PostgreSQL server.

Download the project source code

Summary

• Use thepsycopg2 package to connect to the PostgreSQL server from Python.
• Call theconnect() function of thepsycopg2 module to connect to the PostgreSQL server.

Last updated onMay 19, 2024

On this page

• Creating a virtual environment
• Installing the psycopg2 module
• Creating a new database
• Connecting to the PostgreSQL database from Python
• Summary