Once you've created a PostgreSQL table using Python, the next step is to insert data into it. In this tutorial, you'll learn how to use Python and psycopg2 to insert single and multiple records into a PostgreSQL table with best practices and error handling.

Table of Contents

1. What You’ll Learn

2. Prerequisites

3. Installing psycopg2

4. Connecting to PostgreSQL

5. Inserting a Single Record

6. Inserting Multiple Records

7. Using execute() vs executemany()

8. Full Working Example

9. Tips and Common Pitfalls

1. What You’ll Learn

• How to insert data into a PostgreSQL table using Python

• How to insert multiple rows efficiently

• How to use parameterized queries to prevent SQL injection

• How to handle database errors

✅ 2. Prerequisites

Ensure you have the following:

• Python installed

• PostgreSQL installed and running

• A PostgreSQL database and table (e.g., students)

• The psycopg2 library installed

3. Installing psycopg2

If you haven’t installed it yet:

pip install psycopg2-binary

4. Connecting to PostgreSQL

Use the following code to connect to your database:

import psycopg2

conn = psycopg2.connect(
    dbname="school",
    user="postgres",
    password="your_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

5. Inserting a Single Record

Let’s assume we have a students table like this:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

Inserting a Record (Python Code):

cur.execute("""
    INSERT INTO students (name, age, grade)
    VALUES (%s, %s, %s)
""", ("Alice", 14, "8th"))

conn.commit()

✅ The %s placeholders ensure the query is parameterized, helping to prevent SQL injection.

6. Inserting Multiple Records

Using executemany():

students_data = [
    ("Bob", 13, "7th"),
    ("Carol", 15, "9th"),
    ("Dave", 12, "6th")
]

cur.executemany("""
    INSERT INTO students (name, age, grade)
    VALUES (%s, %s, %s)
""", students_data)

conn.commit()

⚖️ 7. execute() vs executemany()

8. Full Working Example

import psycopg2

try:
    # Connect to the database
    conn = psycopg2.connect(
        dbname="school",
        user="postgres",
        password="your_password",
        host="localhost",
        port="5432"
    )
    cur = conn.cursor()

    # Insert single student
    cur.execute("""
        INSERT INTO students (name, age, grade)
        VALUES (%s, %s, %s)
    """, ("Alice", 14, "8th"))

    # Insert multiple students
    students_data = [
        ("Bob", 13, "7th"),
        ("Carol", 15, "9th"),
        ("Dave", 12, "6th")
    ]

    cur.executemany("""
        INSERT INTO students (name, age, grade)
        VALUES (%s, %s, %s)
    """, students_data)

    conn.commit()
    print("Data inserted successfully.")

    cur.close()
    conn.close()

except psycopg2.Error as e:
    print("Database error:", e)

9. Tips and Common Pitfalls

Conclusion

Inserting data into PostgreSQL using Python is straightforward with the psycopg2 library. Whether you’re inserting a single row or handling a bulk operation, using parameterized queries and best practices ensures secure and reliable database operations.