Introduction

Connecting a PostgreSQL database with Python is a powerful way to interact with data securely. In this article, we’ll show you how to use the python-dotenv library to store sensitive credentials in a .env file, and leverage the pg8000 library to establish a secure connection with your PostgreSQL database. This approach ensures your credentials stay safe while allowing you to easily fetch data from the database. By the end, you’ll have a fully functional Python script ready for deployment, using best practices for security and scalability.

Step 1: Install the Required Libraries

So, here’s the thing—if you want to link Python with a PostgreSQL database and keep your credentials safe, you’ll need two super useful libraries:

• pg8000: This is a simple Python library that lets you talk to PostgreSQL databases. You can think of it like a bridge between your Python code and the database.
• python-dotenv: This handy tool lets you load your sensitive credentials, like your database username and password, from a .env file. This keeps your credentials hidden and safe from prying eyes, rather than hardcoding them directly into your script.

To get these libraries installed, just open your terminal and type this:

$ pip install pg8000 python-dotenv

Pro Tip: If you’re using a virtual environment (which is definitely a good idea to keep things neat), don’t forget to activate it first. That way, everything stays nice and organized.

Step 2: Create a .env File

Next up, we need to create a .env file. This file will be the secret vault for your database credentials. So, go ahead and create a file called .env in your project folder. Then, add these details:

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

Make sure to replace the placeholder values with your real credentials.

Pro Tip: It’s easy to forget, but don’t forget to add .env to your .gitignore file. That way, you won’t accidentally push your sensitive credentials to version control and risk exposing them to the world.

Step 3: Create a Python Script

Alright, let’s get to the fun part! Now it’s time to create a Python script that’ll securely connect to your PostgreSQL database. Create a new file called connect_to_db.py in your project folder. In this script, we’ll load the credentials from the .env file and make the connection to the database. Here’s how to kick things off:

# 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()

Here’s what this script does:

• It loads the database credentials securely from the .env file.
• It connects to your database using the pg8000.connect() method.
• Finally, it prints a success message if everything works or an error message if something goes wrong.

Step 4: Test the Connection

Now that your script is set up, let’s test it. Open your terminal and run the script with this command:

$ python connect_to_db.py

If everything is good, you should see:

Connection successful!

If something goes wrong, don’t worry! Here’s what you can do:

• Double-check the values in your .env file to make sure everything is correct.
• Also, make sure your IP address is on the trusted sources list for your database (we covered this earlier).

Step 5: Fetch Data from the Database

Now that we’ve got the connection working, let’s get to something more fun—fetching data from the database! We’ll update the script so that it pulls records from the database. Here’s the new version of your connect_to_db.py script:

# 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)

Here’s what this updated script does:

• It runs a query to get all records from the contacts table.
• It prints out each record so you can see them in your console.
• It makes sure to safely close the cursor and the connection once it’s all done.

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

And that’s it! Now you have a fully functional Python script that securely connects to your PostgreSQL database, fetches data, and keeps your credentials safe.

PostgreSQL SELECT Command Documentation

Conclusion

In conclusion, connecting a PostgreSQL database with Python using libraries like python-dotenv and pg8000 is an essential skill for developers looking to build secure, scalable applications. By following the steps outlined in this guide, you’ve learned how to manage sensitive credentials safely and interact with your database efficiently. Using python-dotenv to store credentials in a .env file ensures your data remains secure, while pg8000 enables seamless communication with PostgreSQL. As you move forward, integrating these best practices into your Python projects will help you build more robust, production-ready applications. Stay updated with new libraries and tools in the Python ecosystem to continue improving your database interactions and security practices.

Set Up PostgreSQL Database for Birthday Reminder Service