<h1>12 Days of DigitalOcean: Setting Up and Connecting a PostgreSQL Database for Birthday Reminders with Python</h1>
Welcome to Day 2 of DigitalOcean’s 12-day series! Today, we’ll dive into setting up a PostgreSQL database designed for storing birthday reminders and securely connecting to it using Python.
<h2>Setting Up the PostgreSQL Database</h2>
First, you’ll want to set up your PostgreSQL database on DigitalOcean. Follow these steps:
<ol>
<li>
Create the Database:
Use the <code>psql</code> command-line tool to create your database:
<pre><code class="language-sh">createdb birthday_reminders
</code></pre>
</li>
<li>
Set Up the Contacts Table:
Next, create a ‘contacts’ table to store essential information:
<pre><code class="language-sql">CREATE TABLE contacts (
 id SERIAL PRIMARY KEY,
 name VARCHAR(100),
 birthdate DATE
);
</code></pre>
</li>
<li>
Insert Sample Data:
Populate the table with some initial data:
<pre><code class="language-sql">INSERT INTO contacts (name, birthdate) VALUES (&#x27;John Doe&#x27;, &#x27;1990-01-15&#x27;);
INSERT INTO contacts (name, birthdate) VALUES (&#x27;Jane Smith&#x27;, &#x27;1985-05-23&#x27;);
</code></pre>
</li>
<li>
Secure Your Database:
Ensure your database is secure by configuring access from trusted sources only.
</li>
</ol>
<h2>Connecting to the PostgreSQL Database with Python</h2>
Now, let’s write a Python script to connect to your PostgreSQL database.
<h3>Prerequisites</h3>
<ul>
<li>Python installed on your machine</li>
<li>Libraries: <code>pg8000</code> and <code>python-dotenv</code></li>
</ul>
<h3>Steps</h3>
<ol>
<li>
Install Required Libraries:
<pre><code class="language-sh">pip install pg8000 python-dotenv
</code></pre>
</li>
<li>
Set Up the <code>.env</code> File:
Create a <code>.env</code> file in your project directory to store your database credentials securely:
<pre><code class="language-env">DB_HOST=your_database_host
DB_NAME=birthday_reminders
DB_USER=your_database_user
DB_PASS=your_database_password
</code></pre>
</li>
<li>
Write the Python Script:
Create a Python script to connect and query the database, ensuring your credentials are not hard-coded:
<pre><code class="language-python">import os
import pg8000
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

DB_HOST = os.getenv(&#x27;DB_HOST&#x27;)
DB_NAME = os.getenv(&#x27;DB_NAME&#x27;)
DB_USER = os.getenv(&#x27;DB_USER&#x27;)
DB_PASS = os.getenv(&#x27;DB_PASS&#x27;)

# Connect to the database
conn = pg8000.connect(user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME)
cursor = conn.cursor()

# Query the contacts table
cursor.execute(&quot;SELECT * FROM contacts;&quot;)
for row in cursor.fetchall():
 print(row)

cursor.close()
conn.close()
</code></pre>
</li>
</ol>
By following these steps, you ensure that your Python script connects securely to the PostgreSQL database. This makes your code ready for deployment without exposing sensitive credentials. Stay tuned for the next part of the series where we’ll extend this setup to send SMS reminders for birthdays.

Collections

Learn how to set up a PostgreSQL database on DigitalOcean for storing birthday reminders and how to securely connect to it using Python. This guide covers database creation, table setup, inserting sample data, securing the database, and writing a Python script using `pg8000` and `python-dotenv` for secure connection and data querying.

12 Days of DigitalOcean - Setting Up a PostgreSQL Database for Birthday Reminders