Python psycopg2: How to Perform Insert Operations With psycopg2 in Python

psycopg2 is the most widely used Python adapter for PostgreSQL databases. It provides an efficient, secure way to execute SQL commands and manage data from Python applications. Insert operations - adding new records to database tables - are among the most fundamental tasks in database management.

In this guide, you will learn how to set up psycopg2, perform single and batch inserts, handle transactions safely, import data from CSV files, and implement proper error handling.

Installation

psycopg2 comes in two versions:

# Standard version (requires C compiler)
pip install psycopg2

# Pre-compiled binary (easier installation, recommended for most users)
pip install psycopg2-binary

tip

Use psycopg2-binary for development and quick setups. For production environments, the standard psycopg2 package is recommended for better performance and security.

Connecting to PostgreSQL

Establish a connection by providing your database credentials:

import psycopg2

connection_params = {
    'dbname': 'postgres',
    'user': 'postgres',
    'password': 'your_password',
    'host': 'localhost',
    'port': '5432'
}

connection = psycopg2.connect(**connection_params)
cursor = connection.cursor()

print("Connection established!")

cursor.close()
connection.close()

Output:

Connection established!

Parameter	Description
dbname	Name of the PostgreSQL database
user	Username for authentication
password	Password for the user
host	Server address (localhost for local databases)
port	PostgreSQL port (default: 5432)

Basic Insert Operation

Use parameterized queries with %s placeholders to insert data safely:

import psycopg2

connection = psycopg2.connect(
    dbname='postgres', user='postgres',
    password='your_password', host='localhost', port='5432'
)
cursor = connection.cursor()

# Create table if it doesn't exist
cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        age INTEGER,
        course VARCHAR(100)
    );
""")

# Insert a single record using parameterized query
insert_query = "INSERT INTO students (name, age, course) VALUES (%s, %s, %s);"
data = ('Shalini', 21, 'Python')

cursor.execute(insert_query, data)
connection.commit()

print(f"Inserted: {data}")

cursor.close()
connection.close()

Output:

Inserted: ('Shalini', 21, 'Python')

Always use parameterized queries

Never insert user-supplied values directly into SQL strings. This prevents SQL injection attacks:

# ❌ DANGEROUS: SQL injection vulnerability
name = "Alice'; DROP TABLE students; --"
cursor.execute(f"INSERT INTO students (name, age, course) VALUES ('{name}', 21, 'Python')")

# ✅ SAFE: parameterized query
cursor.execute("INSERT INTO students (name, age, course) VALUES (%s, %s, %s)", (name, 21, 'Python'))

Batch Insert With executemany()

Insert multiple records in a single transaction using executemany():

import psycopg2

connection = psycopg2.connect(
    dbname='postgres', user='postgres',
    password='your_password', host='localhost', port='5432'
)
cursor = connection.cursor()

insert_query = "INSERT INTO students (name, age, course) VALUES (%s, %s, %s);"

data = [
    ('Shalini', 21, 'Python'),
    ('Arun', 22, 'Java'),
    ('Anvay', 22, 'C++'),
    ('Priya', 20, 'Data Science'),
    ('Rahul', 23, 'Machine Learning')
]

cursor.executemany(insert_query, data)
connection.commit()

print(f"Inserted {len(data)} records successfully!")

cursor.close()
connection.close()

Output:

Inserted 5 records successfully!

executemany() is more efficient than calling execute() in a loop because it reduces the overhead of repeated function calls.

Handling Transactions Properly

Transactions ensure that a group of operations either all succeed or all fail - maintaining database consistency. Use try/except/finally to handle commits and rollbacks:

import psycopg2

connection = psycopg2.connect(
    dbname='postgres', user='postgres',
    password='your_password', host='localhost', port='5432'
)
cursor = connection.cursor()

try:
    insert_query = "INSERT INTO students (name, age, course) VALUES (%s, %s, %s);"

    data = [
        ('Meera', 21, 'Python'),
        ('Karan', 22, 'Java')
    ]

    for record in data:
        cursor.execute(insert_query, record)

    # All inserts succeeded: save changes
    connection.commit()
    print("Transaction committed successfully!")

except psycopg2.Error as e:
    # Something went wrong: undo all changes
    connection.rollback()
    print(f"Transaction rolled back due to error: {e}")

finally:
    cursor.close()
    connection.close()

Output (success):

Transaction committed successfully!

Output (failure):

Transaction rolled back due to error: <error details>

Using Context Managers for Cleaner Code

psycopg2 supports context managers (with statements) for automatic resource cleanup:

import psycopg2

connection_params = {
    'dbname': 'postgres', 'user': 'postgres',
    'password': 'your_password', 'host': 'localhost', 'port': '5432'
}

with psycopg2.connect(**connection_params) as connection:
    with connection.cursor() as cursor:
        cursor.execute(
            "INSERT INTO students (name, age, course) VALUES (%s, %s, %s)",
            ('Neha', 20, 'Web Development')
        )
    # connection.commit() is called automatically on exit
    # If an exception occurs, connection.rollback() is called instead

print("Insert completed with context manager!")

tip

Using with statements is the recommended pattern because:

• The cursor is automatically closed when the block exits.
• The transaction is automatically committed on success or rolled back on failure.
• The connection is returned to the pool (but not closed - close it explicitly if needed).

Inserting Data From a CSV File

For bulk data import, read a CSV file and insert the records into the database:

import psycopg2
import csv

connection = psycopg2.connect(
    dbname='postgres', user='postgres',
    password='your_password', host='localhost', port='5432'
)
cursor = connection.cursor()

insert_query = "INSERT INTO students (name, age, course) VALUES (%s, %s, %s);"

try:
    with open('students.csv', 'r') as csv_file:
        csv_reader = csv.reader(csv_file)
        next(csv_reader)  # Skip header row

        data = [tuple(row) for row in csv_reader]

    cursor.executemany(insert_query, data)
    connection.commit()

    print(f"Inserted {len(data)} records from CSV successfully!")

except FileNotFoundError:
    print("Error: CSV file not found.")
except psycopg2.Error as e:
    connection.rollback()
    print(f"Database error: {e}")
finally:
    cursor.close()
    connection.close()

Output:

Inserted 10 records from CSV successfully!

Faster CSV Import With copy_from()

For large CSV files, copy_from() is significantly faster than executemany() because it uses PostgreSQL's optimized COPY command:

import psycopg2

connection = psycopg2.connect(
    dbname='postgres', user='postgres',
    password='your_password', host='localhost', port='5432'
)
cursor = connection.cursor()

with open('students.csv', 'r') as f:
    next(f)  # Skip header
    cursor.copy_from(f, 'students', sep=',', columns=('name', 'age', 'course'))

connection.commit()
print("CSV data imported using COPY - much faster for large files!")

cursor.close()
connection.close()

Error Handling and Logging

Implement robust error handling with logging for production applications:

import psycopg2
import logging

logging.basicConfig(
    filename='db_errors.log',
    level=logging.ERROR,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

connection = None
cursor = None

try:
    connection = psycopg2.connect(
        dbname='postgres', user='postgres',
        password='your_password', host='localhost', port='5432'
    )
    cursor = connection.cursor()

    cursor.execute(
        "INSERT INTO students (name, age, course) VALUES (%s, %s, %s)",
        ('Test', 'not_a_number', 'Python')  # Deliberate type error
    )
    connection.commit()

except psycopg2.IntegrityError as e:
    logging.error(f"Integrity error: {e.pgerror}")
    print(f"Constraint violation: {e}")
    if connection:
        connection.rollback()

except psycopg2.DataError as e:
    logging.error(f"Data error: {e.pgerror}")
    print(f"Invalid data: {e}")
    if connection:
        connection.rollback()

except psycopg2.OperationalError as e:
    logging.error(f"Operational error: {e}")
    print(f"Connection/operation error: {e}")

except psycopg2.Error as e:
    logging.error(f"Database error: {e.pgcode} - {e.pgerror}")
    print(f"Database error: {e}")
    if connection:
        connection.rollback()

finally:
    if cursor:
        cursor.close()
    if connection:
        connection.close()

Common psycopg2 Error Types

Exception	Cause
OperationalError	Connection failures, authentication errors
IntegrityError	Unique constraint violations, foreign key violations
DataError	Invalid data types, numeric overflow
ProgrammingError	SQL syntax errors, referencing non-existent tables
InterfaceError	Cursor or connection already closed

Quick Reference

Operation	Method	Use Case
Single insert	cursor.execute(query, data)	Inserting one record
Batch insert	cursor.executemany(query, data_list)	Inserting multiple records
Bulk CSV import	cursor.copy_from(file, table)	Large file imports
Save changes	connection.commit()	After successful operations
Undo changes	connection.rollback()	After errors

Conclusion

Performing insert operations with psycopg2 involves establishing a connection, executing parameterized SQL queries, and managing transactions to ensure data integrity.

• Use execute() for single inserts
• Use executemany() for batch operations
• Use copy_from() for high-performance CSV imports
• Always use parameterized queries to prevent SQL injection, wrap operations in try/except blocks with proper commit/rollback handling, and use context managers for cleaner resource management.

These practices together produce secure, reliable, and maintainable database code.