Perform PostgreSQL CRUD operations from Python

Perform PostgreSQL CRUD operations from Python

Performing CRUD (Create, Read, Update, Delete) operations in a PostgreSQL database from Python can be done using the psycopg2 library, which is a PostgreSQL adapter for Python. Below is a step-by-step guide on how to perform these operations.

Step 1: Install psycopg2

First, you need to install the psycopg2 library. You can do this using pip:

pip install psycopg2-binary

Step 2: Establish a Connection

To perform any operation, you must first establish a connection to your PostgreSQL database.

import psycopg2 from psycopg2 import OperationalError def create_connection(db_name, db_user, db_password, db_host, db_port): connection = None try: connection = psycopg2.connect( database=db_name, user=db_user, password=db_password, host=db_host, port=db_port, ) print("Connection to PostgreSQL DB successful") except OperationalError as e: print(f"The error '{e}' occurred") return connection # Replace with your details connection = create_connection( "your_dbname", "your_dbuser", "your_dbpassword", "your_dbhost", "your_dbport" )

Step 3: Create Operations (CRUD)

Let's define functions for each of the CRUD operations.

CREATE

To create a new table:

def create_table(connection, query): cursor = connection.cursor() try: cursor.execute(query) connection.commit() print("Query executed successfully") except OperationalError as e: print(f"The error '{e}' occurred") create_table_query = """ CREATE TABLE IF NOT EXISTS users ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER, gender TEXT, nationality TEXT ) """ create_table(connection, create_table_query)

READ

To read data from the table:

def execute_read_query(connection, query): cursor = connection.cursor() result = None try: cursor.execute(query) result = cursor.fetchall() return result except OperationalError as e: print(f"The error '{e}' occurred") select_users = "SELECT * from users" users = execute_read_query(connection, select_users) for user in users: print(user)

UPDATE

To update data in the table:

def execute_query(connection, query): cursor = connection.cursor() try: cursor.execute(query) connection.commit() print("Query executed successfully") except OperationalError as e: print(f"The error '{e}' occurred") update_user_age = """ UPDATE users SET age = 32 WHERE id = 1 """ execute_query(connection, update_user_age)

DELETE

To delete data from the table:

delete_comment = "DELETE FROM users WHERE id = 2" execute_query(connection, delete_comment)

Step 4: Close the Connection

After performing your operations, close the connection to the database.

connection.close()

Notes

  • Replace "your_dbname", "your_dbuser", etc., with your PostgreSQL database details.
  • Be sure to handle exceptions and errors appropriately, especially in a production environment.
  • The structure of your SQL queries will depend on your specific database schema.
  • Using psycopg2, SQL queries are executed in a transactional manner, so don't forget to call commit() after a transaction.
  • For complex applications, consider using an ORM (Object-Relational Mapping) library like SQLAlchemy, which can simplify database interactions and reduce the need to write raw SQL.

More Tags

iteration final javax.activation android-radiogroup code-coverage imagedecoder large-title getattribute translate3d vpn

More Programming Guides

Other Guides

More Programming Examples