Build: Fetch Data from Postgres Using Python

Build: Fetch Data from Postgres Using Python


Use psycopg2 to query your Raspberry Pi database

Now that you’ve installed PostgreSQL on your Raspberry Pi and created a simple test database (test_env) with a sample table (demo_users), it’s time to actually talk to it using Python. This post will walk you through connecting to your database using the psycopg2 library and printing the contents of your table.

This is your first taste of Python-Postgres interactivity, and it’s a great test to confirm that your environment is wired up correctly.

Step 1: Install psycopg2

If you haven’t already, install the psycopg2 library. It’s the most widely used adapter for Python and Postgres:

Bash

pip install psycopg2   

If you’re using a virtual environment or Python 3-specific setup, make sure you’re calling the right pip—pip3 on some systems.


Step 2: Connect and Fetch Records

We’ve included a Python script in the GitHub Gist linked below called script_one.py. It connects to the test_env database and queries all rows from the demo_users table:

Python

import psycopg2

def fetch_users():
    try:
        conn = psycopg2.connect(
            dbname="test_env",
            user="postgres",
            password="",  # add password if you've set one
            host="localhost"
        )
        cur = conn.cursor()
        cur.execute("SELECT * FROM demo_users;")
        users = cur.fetchall()
        for user in users:
            print(user)
        cur.close()
        conn.close()
    except Exception as e:
        print("Error:", e)

if __name__ == "__main__":
    fetch_users()

This is a basic read operation—nothing fancy, just a quick confirmation that your table exists, and that data retrieval is working properly.


Step 3: Run the Script

Save the file as script_one.py, or just download it directly from the Github Gist. Then run:

Python

python3 script_one.py  

If your database is set up correctly and running, you’ll see:

Python

(1, 'prasad_tester', 'prasad@example.com')  

Success! That means Python is connected to Postgres, and your demo table is working as expected.


Next: Insert New Records with Python

Now that you can read data from the database, let’s flip it around. In the next post, you’ll use Python to insert new records into the same table—confirming you have full CRUD capabilities on your Raspberry Pi setup.


👉 Build: Insert Data into Postgres Using Python coming next.


You’re on your way to building fully interactive Raspberry Pi projects with a real database backend. Let’s keep going. 🥧📡

Need Postgres Expertise?

We're happy to help you with your Postgres projects!  Feel free to contact us.

Email: info@pacificw.com

Image: Gemini