12 Days of DigitalOcean (Day 2) - Connecting to Your PostgreSQL Database with Python

Welcome to Day 2 of 12 Days of DigitalOcean! Yesterday, we started building our Birthday Reminder Service—a simple app that sends SMS notifications for upcoming birthdays. 🎉 We set up a PostgreSQL database on DigitalOcean to store contact details.

Today, we’ll connect to that database using Python and set up our project to keep sensitive credentials safe.

By the end of this post, you’ll have a Python script that securely fetches data from your database and is ready to scale when we host it on DigitalOcean.

✨ Why This Step?

Setting up the database was just the beginning. To build any meaningful app, we need a way to interact with that data programmatically. Python makes it easy to connect to PostgreSQL databases, and with the help of a library like pg8000, we can work with PostgreSQL databases in just a few lines of code.

To keep things secure, we’ll store our database credentials in a .env file and load them into our script using python-dotenv. This ensures your credentials aren’t hard-coded into the script, making it safer to share and deploy your app.

🚀 What You’ll Learn

Here’s the plan for today:

1. Store sensitive credentials in a .env file.
2. Use python-dotenv to load those credentials into your script.
3. Write a Python script to securely connect to your PostgreSQL database using pg8000
4. Fetch and display data from the contacts table

By the end of this, you’ll have a solid foundation for building the app’s logic.

🛠 What You’ll Need

Before we dive in, here’s what you’ll need:

• The database we set up on Day 1. TODO: Link to day 1 URL
• Python installed on your local machine (we recommend Python 3.8+).

🧑‍🍳 Recipe for Day 2: Connecting to PostgreSQL with Python

Step 1: Install the Required Libraries 📦

To connect Python to PostgreSQL and keep our credentials secure, we’ll use two key libraries:

• pg8000: A pure Python library that allows you to connect to and interact with PostgreSQL databases.
• python-dotenv: A utility to load sensitive credentials (like database usernames and passwords) from a .env file, so you don’t have to hardcode them in your script.

Let’s install them now. Run this command in your terminal:

pip install pg8000 python-dotenv

Pro Tip: If you’re using a virtual environment (always a good idea!), make sure to activate it before running the above command to keep your dependencies organized.

Step 2: Create a .env File 📂

In your project directory, create a file named .env. This is where we’ll store our database credentials. Add the following:

DB_HOST=<your-hostname>
DB_NAME=<your-database-name>
DB_USER=<your-username>
DB_PASSWORD=<your-password>

Replace the placeholder values with the credentials from Day 1.

Pro Tip: Add .env to your .gitignore file to ensure your credentials aren’t accidentally pushed to version control.

Step 3: Create a Python Script 🐍

Create a new file called connect_to_db.py, and set up the script to load credentials from .env using python-dotenv, and connect to our database.

Here’s the code to get started:

# connect_to_db.py

import pg8000
from dotenv import load_dotenv
import os

# Load environment variables from .env file
load_dotenv()

# Database connection details
DB_HOST = os.getenv("DB_HOST")
DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_PORT = int(os.getenv("DB_PORT"))

try:
    # Connect to the database
    connection = pg8000.connect(
        host=DB_HOST,
        database=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD,
        port=DB_PORT
    )
    print("Connection successful!")
except Exception as e:
    print("An error occurred while connecting to the database:", e)
finally:
    if connection:
        connection.close()

This script does a few important things:

• Loads credentials securely from your .env file.
• Establishes a connection to your database using pg8000.connect().
• Prints a success or error message depending on the outcome.

Step 4: Test the Connection ✅

Now, let’s make sure everything works. Run your script:

python connect_to_db.py

If everything is set up correctly, you should see:

Connection successful!

If there’s an error:

• Double-check the values in your .env file.
• Make sure your IP address is added to the database’s trusted sources (see Step 6 from Day 1).

Step 5: Fetch Data from the Database 🔍

Now, let’s extend the script to fetch data. Update your connect_to_db.py script to include the following:

# connect_to_db.py

import pg8000
from dotenv import load_dotenv
import os

# Load environment variables from .env file
load_dotenv()

# Database connection details
DB_HOST = os.getenv("DB_HOST")
DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_PORT = int(os.getenv("DB_PORT"))

try:
    # Connect to the database
    connection = pg8000.connect(
        host=DB_HOST,
        database=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD,
        port=DB_PORT
    )
    print("Connection successful!")

    # Query the database
    cursor = connection.cursor()
    query = "SELECT * FROM contacts;"
    cursor.execute(query)
    records = cursor.fetchall()

    # Print the results
    print("Contacts:")
    for record in records:
        print(record)

    # Close the cursor and connection
    cursor.close()
    connection.close()

except Exception as e:
    print("An error occurred:", e)

This script now:

• Executes a query to fetch all records from the contacts table.
• Prints each record to the console.

Note: If the table is empty, no worries! You can still check that the script runs without errors. For testing, you can quickly add a sample contact by opening your database using psql (or your preferred tool) and running this SQL command:

INSERT INTO contacts (first_name, last_name, birthday) 
VALUES ('Test', 'User', '1990-01-01');

If you need a refresher on how to use psql or want to explore UI tools like pgAdmin or TablePlus, check out the instructions in Day 1.

🎁 Wrap-Up

Here’s what you accomplished today:

✅ Installed the required Python libraries.
✅ Connected to your DigitalOcean PostgreSQL database using Python.
✅ Fetched data from the contacts table with a simple query.

Up next: Tomorrow, we’ll start adding logic to find upcoming birthdays and send SMS notifications using Twilio. This is where the app starts to come alive—stay tuned! 🚀