Perform Insert Operations with psycopg2 in Python
Last Updated :
07 Aug, 2024
psycopg2 is a widely used Python library designed to facilitate communication with PostgreSQL databases, offering a robust and efficient way to perform various database operations. It is a powerful and flexible connector, which allows Python applications to execute SQL commands and handle data seamlessly. Insert operation is one of the core operations in database management. These are used for adding new records to a database, making them crucial for data manipulation, storage, and retrieval. In this article, we will learn how psycopg2 can be utilized to perform insert operations in PostgreSQL databases.
Setting Up psycopg2 to Perform Insert Operations
Installation
To start using psycopg2, you need to install it in your Python environment. You can easily install psycopg2 using pip, which is the package installer for Python. There are two versions of psycopg2 you can choose from:
psycopg2: The standard version, which requires compilation of C extensions.
pip install psycopg2
psycopg2-binary: A pre-compiled version that is easier to install and doesn't require a compiler. It’s recommended for most users who do not need to build the library from a source.
pip install psycopg2-binary
Connecting to PostgreSQL
Once psycopg2 is installed, you can use it to connect to your PostgreSQL database. Below is a basic example of how to establish a connection.
Explanation:
- dbname: The name of your PostgreSQL database.
- user: The username used to authenticate with the database.
- password: The password associated with the username.
- host: The address of the PostgreSQL server (e.g., localhost for local servers).
- port: The port number on which the PostgreSQL server is listening (default is 5432).
Python
import psycopg2
# Define connection parameters
connection_params = {
'dbname': 'postgres',
'user': 'postgres',
'password': '12345678',
'host': 'localhost',
'port': '5432'
}
# Establishing the connection
connection = psycopg2.connect(**connection_params)
# Creating a cursor object
cursor = connection.cursor()
# Print the connection status
print("Connection established!")
# Close the cursor and connection
cursor.close()
connection.close()
Output:
If the connection is established successfully, then output will be:
Connection established!
Insert Operations with psycopg2
1. Basic Insert Operation
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
Example Code
Python
import psycopg2
# Connection parameters
connection_params = {
'dbname': 'postgres',
'user': 'postgres',
'password': '12345678',
'host': 'localhost',
'port': '5432'
}
# Establishing the connection
connection = psycopg2.connect(**connection_params)
cursor = connection.cursor()
# Create table if it doesn't exist
create_table_query = """
CREATE TABLE IF NOT EXISTS students (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INTEGER,
course VARCHAR(100)
);
"""
cursor.execute(create_table_query)
# SQL INSERT statement
insert_query = """
INSERT INTO students (name, age, course)
VALUES (%s, %s, %s);
"""
# Data to be inserted
data = ('Shalini', 21 , 'Python')
# Executing the INSERT statement
cursor.execute(insert_query, data)
# Committing the transaction
connection.commit()
# Print success message
print("Data inserted successfully!")
# Output
print(f"Inserted data: {data}")
# Closing the cursor and connection
cursor.close()
connection.close()
Output:
Data inserted successfully!
Inserted data: ('Shalini', 21 , 'Python')
Note: The query used above is called parameterized query because we have used placeholders (%s) and passed the parameters separately. This is important for security reasons and it also makes the code cleaner.
2. Batch Insert Operations
Batch inserts involve inserting multiple records in a single transaction, which is more efficient than inserting records one by one. Here’s how you can perform batch inserts using psycopg2:
Python
import psycopg2
# Connection parameters
connection_params = {
'dbname': 'postgres',
'user': 'postgres',
'password': '12345678',
'host': 'localhost',
'port': '5432'
}
# Establishing the connection
connection = psycopg2.connect(**connection_params)
cursor = connection.cursor()
# SQL INSERT statement
insert_query = """
INSERT INTO students (name, age, course)
VALUES (%s, %s, %s);
"""
# Data to be inserted (batch)
data = [
('Shalini', 21, 'Python'),
('Arun', 22 , 'Java'),
('Anvay', 22, 'C++')
]
# Executing the batch INSERT statement
cursor.executemany(insert_query, data)
# Committing the transaction
connection.commit()
# Print success message
print("Batch data inserted successfully!")
# Output
for record in data:
print(f"Inserted data: {record}")
# Closing the cursor and connection
cursor.close()
connection.close()
Output:
Batch data inserted successfully!
Inserted data: ('Shalini', 21 , 'Python')
Inserted data: ('Arun', 22 , 'Java')
Inserted data: ('Anvay', 22 , 'C++')
Advanced Techniques
1. Handling Transactions
Using Transactions for Insert Operations:
- Transactions are essential for ensuring that a series of operations either complete successfully or leave the database in a consistent state if something goes wrong. Using transactions allows you to group multiple insert operations into a single unit of work.
Committing and Rolling Back Transactions:
- Committing a Transaction: Saves all changes made during the transaction to the database.
- Rolling Back a Transaction: Undoes all changes made during the transaction if an error occurs.
Code Explanation:
- Connection Setup: Establish a connection to the database and create a cursor object.
- Try Block: Perform the insert operations and commit the transaction if successful.
- Exception Handling: If an error occurs, roll back the transaction to undo all changes.
- Finally Block: Ensure that resources (cursor and connection) are closed properly.
Python
import psycopg2
# Connection parameters
connection_params = {
'dbname': 'postgres',
'user': 'postgres',
'password': '12345678',
'host': 'localhost',
'port': '5432'
}
# Establishing the connection
connection = psycopg2.connect(**connection_params)
cursor = connection.cursor()
try:
# SQL INSERT statement
insert_query = """
INSERT INTO students (name, age, course)
VALUES (%s, %s, %s);
"""
# Data to be inserted
data = [
('Shalini', 21, 'Python'),
('Arun', 22, 'Java')
]
# Executing insert statements
for record in data:
cursor.execute(insert_query, record)
# Committing the transaction
connection.commit()
print("Transaction committed successfully!")
except Exception as e:
# Rolling back the transaction in case of error
connection.rollback()
print(f"Transaction rolled back due to error: {e}")
finally:
# Closing the cursor and connection
cursor.close()
connection.close()
Output:
If the operation is successful:
Transaction committed successfully!
If an error occurs (e.g., incorrect SQL syntax, connection issue):
Transaction rolled back due to error: <error_message>
Using executemany for Batch Inserts
- Connection Setup: Establish a connection to the database and create a cursor object.
- SQL Query: Define the SQL INSERT statement with placeholders (%s).
- Batch Data: Prepare a list of tuples where each tuple represents a row to be inserted.
- executemany Method: Execute the SQL command with multiple sets of parameters efficiently.
- Commit Transaction: Save all changes to the database.
- Resource Cleanup: Close the cursor and connection.
Python
import psycopg2
# Connection parameters
connection_params = {
'dbname': 'postgres',
'user': 'postgres',
'password': '12345678',
'host': 'localhost',
'port': '5432'
}
# Establishing the connection
connection = psycopg2.connect(**connection_params)
cursor = connection.cursor()
# SQL INSERT statement
insert_query = """
INSERT INTO students (name, age, course)
VALUES (%s, %s, %s);
"""
# Data to be inserted (batch)
data = [
('Shalini', 21, 'Python'),
('Arun', 22, 'Java'),
('Anvay', 22, 'C++')
]
# Executing the batch INSERT statement
cursor.executemany(insert_query, data)
# Committing the transaction
connection.commit()
print("Batch data inserted successfully!")
# Closing the cursor and connection
cursor.close()
connection.close()
Output:
Batch data inserted successfully!
2. Inserting Data from CSV Files
Inserting data from CSV files involves reading the file contents and inserting the records into the database. This is useful for bulk data import.
Code Explanation:
- Connection Setup: Establish a connection to the database and create a cursor object.
- Open CSV File: Use Python’s csv module to read data from the CSV file.
- Skip Header: Skip the header row if your CSV file includes one.
- Prepare Data: Convert CSV rows into a list of tuples for batch insertion.
- executemany Method: Insert all records from the CSV file in one batch.
- Commit Transaction: Save all changes to the database.
- Resource Cleanup: Close the cursor and connection.
Python
import psycopg2
import csv
# Connection parameters
connection_params = {
'dbname': 'postgres',
'user': 'postgres',
'password': '12345678',
'host': 'localhost',
'port': '5432'
}
# Establishing the connection
connection = psycopg2.connect(**connection_params)
cursor = connection.cursor()
# SQL INSERT statement
insert_query = """
INSERT INTO students (name, age, course)
VALUES (%s, %s, %s);
"""
# Open the CSV file
with open('students.csv', 'r') as csv_file:
csv_reader = csv.reader(csv_file)
# Skip the header row
next(csv_reader)
# Prepare data for insertion
data = [row for row in csv_reader]
# Executing the batch INSERT statement
cursor.executemany(insert_query, data)
# Committing the transaction
connection.commit()
print("CSV data inserted successfully!")
# Closing the cursor and connection
cursor.close()
connection.close()
Output:
CSV data inserted successfully!
Error Handling with psycopg2
How to Handle and Debug Errors Effectively:
- Use Try-Except Blocks: Wrap database operations in try-except blocks to catch and handle exceptions.
- Log Errors: Print or log detailed error messages to understand what went wrong.
- Check Error Messages: Analyze the error messages to identify issues related to SQL syntax, data types, or constraints.
- Verify Input Data: Ensure that the data being inserted matches the expected format and data types of the database schema.
- Review SQL Statements: Double-check SQL queries for syntax errors or incorrect table/column names.
Sample Code for Catching and Logging Exceptions
- Logging Setup: Configures logging to capture errors in a file (db_errors.log).
- Try Block: Attempts to execute the insert operation.
- Exception Handling: Catches psycopg2.Error exceptions, logs detailed error messages, and prints them.
- Finally Block: Ensures that resources are closed properly regardless of success or failure.
Python
import psycopg2
import logging
# Setup logging
logging.basicConfig(filename='db_errors.log', level=logging.ERROR)
# Connection parameters
connection_params = {
'dbname': 'Geeks',
'user': 'username',
'password': 'password',
'host': 'localhost',
'port': '5432'
}
try:
# Establishing the connection
connection = psycopg2.connect(**connection_params)
cursor = connection.cursor()
# SQL INSERT statement
insert_query = """
INSERT INTO geeksforgeeks (name, course, age)
VALUES (%s, %s, %s);
"""
# Data to be inserted
data = [
('Shalini', 'Python', 'not_a_number') # Deliberate error
]
# Executing the INSERT statement
cursor.executemany(insert_query, data)
# Committing the transaction
connection.commit()
except psycopg2.Error as e:
# Log error message
logging.error(f"Database error: {e.pgcode} - {e.pgerror}")
print(f"An error occurred: {e.pgcode} - {e.pgerror}")
finally:
# Closing the cursor and connection
if cursor is not None:
cursor.close()
if connection is not None:
connection.close()
Outputs
Connection Errors:
An error occurred: 28000 - password authentication failed for user "username"
SQL Syntax Errors:
An error occurred: 42601 - syntax error at or near "VALUES"
Data Type Mismatches:
An error occurred: 22P02 - invalid input syntax for integer: "not_a_number"
Conclusion
Performing insert operations using psycopg2 involves connecting to a PostgreSQL database, executing basic and batch inserts, and managing transactions to ensure data integrity. Understanding how to handle these operations effectively is crucial for robust database management.
Similar Reads
How to Fix 'psycopg2 OperationalError' in Python
The psycopg2 is a popular PostgreSQL adapter for the Python programming language. It's widely used in web applications for database interactions. However, while working with psycopg2, you may encounter the OperationalError error. This error typically occurs due to issues related to the database conn
3 min read
Introduction to Psycopg2 module in Python
Psycopg is the most popular PostgreSQL adapter used in Python. Its works on the principle of the whole implementation of Python DB API 2.0 along with the thread safety (the same connection is shared by multiple threads). It is designed to perform heavily multi-threaded applications that usually crea
4 min read
Python - Performing operations on the stock data
This article demonstrates basic operations that can be done using Python to analyze and construct algorithmic trading strategies on stock data. We run through some simple operations that can be performed using Python on stock data, and we begin by reading stock data from a CSV file. Python has emerg
3 min read
Perform PostgreSQL CRUD operations from Python
The DDL is comprised of the Create, Read, Update, Delete (CRUD) operations which form the backbone of any SQL database system. Let us discuss how to perform CRUD operations on a PostgreSQL database using python. Pyscopg2 is the most preferred mode and is widely used to connect the PostgreSQL databas
7 min read
Python Psycopg2 - Insert multiple rows with one query
This article is about inserting multiple rows in our table of a specified database with one query. There are multiple ways of executing this task, let's see how we can do it from the below approaches. Method 1: Inserting Values through Naive method In this method, we import the psycopg2 package and
4 min read
Python Psycopg2 - Getting ID of row just inserted
In this article, we are going to see how to get the ID of the row just inserted using pyscopg2 in Python Establishing a connection to the PostgreSQL serverIn order to establish a connection to the PostgreSQL server, we will make use of the pscopg2 library in python. You can install psycopg2 using th
3 min read
Python Psycopg2 - Inserting array of strings
In this article, we will be looking at the multiple approaches to inserting an array of strings in pyscopg2/Postgres in the python programming language., Method 1: Naive method In this example, we form a connection to the classroom database using psycopg2.connect() method, we then create a cursor us
2 min read
Insert Python Dictionary in PostgreSQL using Psycopg2
In this article, we are going to see how to insert a Python dictionary in PostgreSQL using Psycopg2. We can insert a python dictionary by taking the dictionary keys as column names and values as dictionary values. We import the Psycopg2 package and form a connection to the PostgreSQL database using
2 min read
Format SQL in Python with Psycopg's Mogrify
Psycopg, the Python PostgreSQL driver, includes a very useful mechanism for formatting SQL in python, which is mogrify. After parameters binding, returns a query string. The string returned is the same as what SQLwas sent to the database if you used the execute() function or anything similar. One ma
2 min read
How to install pyscopg2 package in Python?
psycopg2 is the most popular PostgreSQL database adapter for the Python programming language. It is a DB API 2.0 compliant PostgreSQL driver is actively developed. It is designed for heavily multi-threaded applications that create and destroy lots of cursors and create a large number of "INSERTs" or
1 min read