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 elemental app that sends SMS notifications for upcoming birthdays. 🎉 We group up a PostgreSQL database connected DigitalOcean to shop relationship details.

Today, we’ll nexus to that database utilizing Python and group up our task to support delicate credentials safe.

By nan extremity of this post, you’ll personification a Python book that securely fetches accusation from your database and is caller to modular erstwhile we large it connected DigitalOcean.

✨ Why This Step?

Setting up nan database was conscionable nan beginning. To build immoderate meaningful app, we petition a measurement to interact pinch that accusation programmatically. Python makes it easy to nexus to PostgreSQL databases, and pinch nan thief of a room for illustration pg8000, we tin activity pinch PostgreSQL databases successful conscionable a less lines of code.

To support things secure, we’ll shop our database credentials successful a .env grounds and load them into our book utilizing python-dotenv. This ensures your credentials aren’t hard-coded into nan script, making it safer to banal and deploy your app.

🚀 What You’ll Learn

Here’s nan strategy for today:

1. Store delicate credentials successful a .env file.
2. Use python-dotenv to load those credentials into your script.
3. Write a Python book to securely nexus to your PostgreSQL database utilizing pg8000
4. Fetch and show accusation from nan contacts table

By nan extremity of this, you’ll personification a coagulated instauration for building nan app’s logic.

🛠 What You’ll Need

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

• The database we group up connected Day 1. TODO: Link to clip 1 URL
• Python installed connected your conception instrumentality (we impulse Python 3.8+).

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

Step 1: Install nan Required Libraries 📦

To nexus Python to PostgreSQL and support our credentials secure, we’ll usage 2 cardinal libraries:

• pg8000: A axenic Python room that allows you to nexus to and interact pinch PostgreSQL databases.
• python-dotenv: A inferior to load delicate credentials (like database usernames and passwords) from a .env file, truthful you don’t personification to hardcode them successful your script.

Let’s instal them now. Run this bid successful your terminal:

pip instal pg8000 python-dotenv

Pro Tip: If you’re utilizing a virtual business (always a bully idea!), make judge to activate it earlier moving nan supra bid to support your limitations organized.

Step 2: Create a .env File 📂

In your task directory, create a grounds named .env. This is wherever we’ll shop our database credentials. Add nan following:

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

Replace nan placeholder values pinch nan credentials from Day 1.

Pro Tip: Add .env to your .gitignore grounds to guarantee your credentials aren’t accidentally pushed to type control.

Step 3: Create a Python Script 🐍

Create a caller grounds called connect_to_db.py, and group up nan book to load credentials from .env utilizing python-dotenv, and nexus to our database.

Here’s nan codification to get started:

import pg8000 from dotenv import load_dotenv import os load_dotenv() 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: narration = 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 correction occurred while connecting to nan database:", e) finally: if connection: connection.close()

This book does a less important things:

• Loads credentials securely from your .env file.
• Establishes a narration to your database utilizing pg8000.connect().
• Prints a occurrence aliases correction relationship depending connected nan outcome.

Step 4: Test nan Connection ✅

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

python connect_to_db.py

If everything is group up correctly, you should see:

Connection successful!

If there’s an error:

• Double-check nan values successful your .env file.
• Make judge your IP reside is added to nan database’s trusted sources (see Step 6 from Day 1).

Step 5: Fetch Data from nan Database 🔍

Now, let’s widen nan book to fetch data. Update your connect_to_db.py book to spot nan following:

import pg8000 from dotenv import load_dotenv import os load_dotenv() 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: narration = pg8000.connect( host=DB_HOST, database=DB_NAME, user=DB_USER, password=DB_PASSWORD, port=DB_PORT ) print("Connection successful!") cursor = connection.cursor() query = "SELECT * FROM contacts;" cursor.execute(query) records = cursor.fetchall() print("Contacts:") for grounds in records: print(record) cursor.close() connection.close() except Exception as e: print("An correction occurred:", e)

This book now:

• Executes a query to fetch each records from nan contacts table.
• Prints each grounds to nan console.

Note: If nan array is empty, nary worries! You tin still cheque that nan book runs without errors. For testing, you tin quickly adhd a sample relationship by opening your database utilizing psql (or your preferred tool) and moving this SQL command:

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

If you petition a refresher connected really to usage psql aliases want to investigation UI devices for illustration pgAdmin aliases TablePlus, cheque retired nan instructions successful Day 1.

🎁 Wrap-Up

Here’s what you accomplished today:

✅ Installed nan required Python libraries.
✅ Connected to your DigitalOcean PostgreSQL database utilizing Python.
✅ Fetched accusation from nan contacts array pinch a elemental query.

Up next: Tomorrow, we’ll commencement adding logic to find upcoming birthdays and nonstop SMS notifications utilizing Twilio. This is wherever nan app starts to recreation alive—stay tuned! 🚀