How to Use PostgreSQL Database with Python

Francis Ndungu
March 1, 2025

Introduction

PostgreSQL is a powerful, open-source relational database system that supports a wide range of data types and advanced features. Python provides excellent libraries to interact with PostgreSQL, allowing you to perform database operations such as connecting to the database, executing queries, and managing data. By learning how to use PostgreSQL with Python, you can build robust and scalable database applications.

This guide explains how to use PostgreSQL database with Python.

Prerequisites

Before you start:

Deploy a VPS server. For instance, Ubuntu 24.04.
Create a non-root sudo user.
Install PostgreSQL. Read our guide on How to Install PostgreSQL on Ubuntu 24.04.
Install Python.

Create a Sample Database

To log in to the PostgreSQL server and create a sample database, follow these steps:

Create a Sample Database.

SQL

postgres=# CREATE DATABASE sample_db;
postgres=# \c sample_db

Create a Database User with Full Privileges. Replace password with a strong value.

SQL

postgres=# CREATE USER sample_user WITH PASSWORD 'password';
postgres=# GRANT ALL PRIVILEGES ON DATABASE sample_db TO sample_user;

The above commands:

Log you in to the PostgreSQL server as the postgres user.
Create a new sample_db database.
Create a new sample_user user with full privileges on the sample_db database.
Connect to the new database.

Install Required Libraries

To interact with PostgreSQL using Python, you need to install the psycopg2-binary library, which is a popular PostgreSQL adapter for Python.

Install psycopg2-binary using pip:

CONSOLE

$ pip install psycopg2-binary

Connect to PostgreSQL Database

To connect to a PostgreSQL database, use the psycopg2-binary library's connect() function and provide the necessary connection parameters such as host, database, user, and password.

Create a new connect-to-db.py file using nano text editor:
CONSOLE
```
$ nano connect-to-db.py
```

Add the following content to connect-to-db.py:

Python

import psycopg2

try:
    connection = psycopg2.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    print("Connection to PostgreSQL established successfully.")
except psycopg2.Error as e:
    print(f"Error connecting to PostgreSQL: {e}")

Save and close the file.
Run the file using Python.
CONSOLE
```
$ python3 connect-to-db.py
```

Create a Sample PostgreSQL Table with Python

Create a new create-table.py file.
CONSOLE
```
$ nano create-table.py
```

Add the following content to create-table.py:

Python

import psycopg2

try:
    connection = psycopg2.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    cursor = connection.cursor()
    create_table_query = """
    CREATE TABLE employees (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        position VARCHAR(100),
        salary NUMERIC
    )
    """
    cursor.execute(create_table_query)
    connection.commit()
    print("Table created successfully.")
except psycopg2.Error as e:
    print(f"Error creating table: {e}")
finally:
    cursor.close()
    connection.close()

Save and close the file.
Run the file using Python.
CONSOLE
```
$ python3 create-table.py
```

Insert Data into the Table

Create a new insert-data.py file.
CONSOLE
```
$ nano insert-data.py
```

Add the following content to insert-data.py:

Python

import psycopg2

try:
    connection = psycopg2.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    cursor = connection.cursor()
    insert_query = """
    INSERT INTO employees (name, position, salary)
    VALUES (%s, %s, %s)
    """
    data = ("John Doe", "Software Engineer", 75000)
    cursor.execute(insert_query, data)
    connection.commit()
    print("Data inserted successfully.")
except psycopg2.Error as e:
    print(f"Error inserting data: {e}")
finally:
    cursor.close()
    connection.close()

Save and close the file.
Run the file.
CONSOLE
```
$ python3 insert-data.py
```

Query Data from the Table

Create a new query-data.py file.
CONSOLE
```
$ nano query-data.py
```

Add the following content to query-data.py:

Python

import psycopg2

try:
    connection = psycopg2.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    cursor = connection.cursor()
    select_query = "SELECT * FROM employees"
    cursor.execute(select_query)
    rows = cursor.fetchall()
    for row in rows:
        print(row)
except psycopg2.Error as e:
    print(f"Error querying data: {e}")
finally:
    cursor.close()
    connection.close()

Save and close the file.
Run the file.
CONSOLE
```
$ python3 query-data.py
```

Update Data in the Table

Create a new update-data.py file.
CONSOLE
```
$ nano update-data.py
```

Add the following content to update-data.py:

Python

import psycopg2

try:
    connection = psycopg2.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    cursor = connection.cursor()
    update_query = """
    UPDATE employees
    SET salary = %s
    WHERE name = %s
    """
    data = (80000, "John Doe")
    cursor.execute(update_query, data)
    connection.commit()
    print("Data updated successfully.")
except psycopg2.Error as e:
    print(f"Error updating data: {e}")
finally:
    cursor.close()
    connection.close()

Save and close the file.
Run the file.
CONSOLE
```
$ python3 update-data.py
```

Delete Data from the Table

Create a new delete-data.py file.
CONSOLE
```
$ nano delete-data.py
```

Add the following content to delete-data.py:

Python

import psycopg2

try:
    connection = psycopg2.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    cursor = connection.cursor()
    delete_query = """
    DELETE FROM employees
    WHERE name = %s
    """
    data = ("John Doe",)
    cursor.execute(delete_query, data)
    connection.commit()
    print("Data deleted successfully.")
except psycopg2.Error as e:
    print(f"Error deleting data: {e}")
finally:
    cursor.close()
    connection.close()

Save and close the file
Run the file.
CONSOLE
```
$ python3 delete-data.py
```

Implement Best Practices for Using PostgreSQL with Python

Use parameterized queries: Prevent SQL injection by using parameterized queries.
Handle exceptions: Use try-except blocks to manage database-related errors.
Close connections: Ensure cursors and connections are closed after operations.
Use context managers: Utilize the with statement to manage database connections.
Commit transactions: Ensure changes are committed to the database after write operations.

Conclusion

Using PostgreSQL with Python is essential for building robust and scalable database applications. In this guide, you've learned how to log in to the PostgreSQL server, create a sample database, create a database user with full privileges, connect to the PostgreSQL database, create tables, insert, query, update, and delete data, and implement best practices. By mastering these techniques, you can efficiently manage and interact with PostgreSQL databases in your Python programs.