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

Learn how to execute single and batch inserts in PostgreSQL using psycopg2 in Python, including CSV imports, transaction handling, and security best practices.

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:

pip install psycopg2

pip install psycopg2-binary

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()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        age INTEGER,
        course VARCHAR(100)
    );
""")

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:

name = "Alice'; DROP TABLE students; --"
cursor.execute(f"INSERT INTO students (name, age, course) VALUES ('{name}', 21, 'Python')")

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)

    connection.commit()
    print("Transaction committed successfully!")

except psycopg2.Error as e:
    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')
        )

print("Insert completed with context manager!")

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.

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

Installation

Connecting to PostgreSQL

Basic Insert Operation

Batch Insert With executemany()

Handling Transactions Properly

Using Context Managers for Cleaner Code

Inserting Data From a CSV File

Faster CSV Import With copy_from()

Error Handling and Logging

Common psycopg2 Error Types

Quick Reference

Conclusion

Batch Insert With `executemany()`

Faster CSV Import With `copy_from()`