Build: Insert Data into Postgres Using Python

Build: Insert Data into Postgres Using Python

Use psycopg2 to add new records on your Raspberry Pi

Now that you’ve connected to your Postgres database and successfully queried existing data, it’s time to go the other direction—inserting new data using Python. This post will walk you through the script_two.py file included in the Gist, and show you how to insert a user into the demo_users table with a simple call.

You’re building out real CRUD capability now, and that means you’re no longer just testing—you’re actively shaping your database from code.

Step 1: Review the Insert Script

Here’s the full script_two.py script from the Github Gist. It takes a username and email, inserts a new row into the demo_users table, and commits the change:

Python

import psycopg2

def insert_user(username, email):
    try:
        conn = psycopg2.connect(
            dbname="test_env",
            user="postgres",
            password="",  # add password if needed
            host="localhost"
        )
        cur = conn.cursor()
        cur.execute(
            "INSERT INTO demo_users (username, email) VALUES (%s, %s);",
            (username, email)
        )
        conn.commit()
        print("User added successfully.")
        cur.close()
        conn.close()
    except Exception as e:
        print("Error:", e)

if __name__ == "__main__":
    insert_user("new_user", "new_user@example.com")  

This is safe, parameterized SQL—no string interpolation or injection risk here.

Step 2: Run the Insert

You can run this from the command line like any other Python file:

Bash

python3 script_two.py  

If everything’s working correctly, you’ll see:

Bash

User added successfully.

Step 3: Confirm the Insert

To verify the new record is actually in the database, run script_one.py again:

Bash

python3 script_one.py  

Now you should see your original record and the new one you just inserted:

Bash

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

This confirms your Python code has full insert capability. You’re now ready to build more advanced tools, automation scripts, or RESTful APIs on top of this.

What’s Next: Beyond the Basics

This concludes the core Raspberry Pi + Postgres mini-series. From here, you can:

• Expand your table structure
• Add more scripts (for updating or deleting)
• Wrap this logic into a web service or CLI tool

We’ll be doing just that in our next series—starting with a Python utility that maps recursive view dependencies in Postgres. If you’re ready to dive into system-level introspection and CLI tools, the Solve series is where you’ll want to go next.

Stay tuned. The tools are coming. 

Need Postgres Expertise?

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

Email: info@pacificw.com

Image: Gemini