Adventures in Machine Learning

Efficiently Manipulating PostgreSQL Tables with Python’s Psycopg2 Library

PostgreSQL is an open-source, object-relational database management system that has gained immense popularity in the industry due to its powerful features. One of the essential aspects of working with a database system is performing Data Manipulation Language (DML) operations, which includes INSERT, UPDATE, and DELETE queries.

In this article, we will explore how Python can interact with PostgreSQL to perform these operations seamlessly.

Performing INSERT, UPDATE, and DELETE operations

One of the primary requirements of any database management system is inserting new records into a table.

Fortunately, Python provides us with a powerful PostgreSQL library, Psycopg2, which allows us to execute SQL queries from our Python code.

Consider the following example where we want to insert a new record into our database:

import psycopg2
conn = psycopg2.connect(host="localhost",database="example_db", user="postgres", password="password")
cursor = conn.cursor()
# Insert Query
insert_query = "INSERT INTO employee (name, age, salary) VALUES ('John', 25, 50000);"
cursor.execute(insert_query)
conn.commit()
# Close the cursor and connection
cursor.close()
conn.close()

In the above code, we first establish a connection to our PostgreSQL database using the Psycopg2 library. Then, we create a cursor object that allows us to execute SQL queries.

In the next line, we define our insert query that inserts a new employee record with name, age, and salary into our employee table. After executing our query, we commit the changes made to the database using the commit() method.

Finally, we close the cursor and connection objects to release resources. Similarly, we can use UPDATE and DELETE queries to modify or remove existing records from our tables.

Passing parameters to SQL queries

In the previous example, we hard-coded the values for our new employee record in our SQL query. However, this approach is not always feasible or safe, especially when dealing with user input data.

To solve this problem, we can use parameterized queries that allow us to use Python variables as parameter values. The Psycopg2 library supports using placeholders %s in our SQL query that gets replaced with the actual parameter values during execution.

Consider the following code example:

import psycopg2
conn = psycopg2.connect(host="localhost",database="example_db", user="postgres", password="password")
cursor = conn.cursor()
# Insert Query with parameters
insert_query = "INSERT INTO employee (name, age, salary) VALUES (%s, %s, %s);"
employee_data = ('Peter', 28, 70000)
cursor.execute(insert_query, employee_data)
conn.commit()
# Close the cursor and connection
cursor.close()
conn.close()

In the above example, we define our insert query with placeholders %s instead of hard-coding the values. We also define a tuple employee_data that contains values for our three parameters – name, age, and salary.

During query execution, we pass our query and tuple of parameters to the execute() method of the cursor object, which replaces the placeholders with actual values. Thus, we can safely insert our record with user input values, preventing SQL injection attacks.

Python PostgreSQL INSERT

Lastly, let’s discuss the steps required to execute an INSERT query from Python, along with some key points to remember.

  1. Import the Psycopg2 library and establish a connection to the PostgreSQL database using the connect() method.
  2. Create a cursor object using the cursor() method.
  3. Define your INSERT query with placeholders %s for parameter values. Make sure to provide the table’s column details correctly.
  4. Define Python variables containing the values for your new record.
  5. Call the execute() method of the cursor object, passing your query and parameter values as arguments.
  6. Call the commit() method to finalize the changes made to the database.
  7. Close the cursor and connection objects using the close() method.

Remember to follow these steps while executing any DML operation and use parameterized queries wherever possible to ensure safe and secure database operations.

Conclusion

In this article, we explored how we can use Python’s Psycopg2 library to interact with a PostgreSQL database and perform various DML operations such as INSERT, UPDATE, and DELETE queries. We also learned how we can use parameterized queries to sanitize user input and prevent SQL injection attacks.

By following the simple steps outlined, you can easily write Python programs to interact with PostgreSQL databases and automate tedious database management tasks, making your life as a developer easier.

Python PostgreSQL UPDATE

The UPDATE query is used to modify existing records in a PostgreSQL table, with changes to one or more columns. We can update a single row or multiple rows simultaneously using the UPDATE statement.

Consider an example where we want to update the salary of an employee with ID 101:

import psycopg2
conn = psycopg2.connect(host="localhost",database="example_db", user="postgres", password="password")
cursor = conn.cursor()
# Update Query
update_query = "UPDATE employee SET salary = 60000 WHERE emp_id = 101;"
cursor.execute(update_query)
conn.commit()
# Close the cursor and connection
cursor.close()
conn.close()

In the above example, we create a connection to our PostgreSQL database and define our UPDATE query that changes the salary of an employee with ID 101 to 60000. After executing our query and committing the changes, we close the cursor and connection objects.

Suppose we want to update multiple employees’ salary simultaneously, we can use the IN operator to specify a list of ID values in our WHERE clause of the UPDATE query. For example:

update_query = "UPDATE employee SET salary = 70000 WHERE emp_id IN (101, 102, 105);"

Similarly, we can update a single column or multiple columns of our table using the SET clause of the UPDATE query.

Let’s see an example below:

update_query = "UPDATE employee SET age = 30, salary = 75000 WHERE emp_id = 101;"

Here, we update the age and salary columns of the employee with ID 101 to 30 and 75000, respectively.

Steps to execute UPDATE statement from Python

  1. Import the Psycopg2 library and establish a connection to the PostgreSQL database using the connect() method.
  2. Create a cursor object using the cursor() method.
  3. Define your UPDATE query with the appropriate column and table names.
  4. Call the execute() method of the cursor object, passing your query as an argument.
  5. Call the commit() method to finalize the changes made to the database.
  6. Close the cursor and connection objects using the close() method.

Python PostgreSQL DELETE

The DELETE query is used to remove existing records from a PostgreSQL table. We can delete a single row or multiple rows simultaneously using the DELETE statement.

Consider an example where we want to delete an employee record with ID 102:

import psycopg2
conn = psycopg2.connect(host="localhost",database="example_db", user="postgres", password="password")
cursor = conn.cursor()
# Delete Query
delete_query = "DELETE FROM employee WHERE emp_id = 102;"
cursor.execute(delete_query)
conn.commit()
# Close the cursor and connection
cursor.close()
conn.close()

In the above example, we define our DELETE query that removes an employee record with ID 102 from our employee table. After executing our query and committing the changes, we close the cursor and connection objects.

Suppose we want to delete multiple employees’ records simultaneously, we can use the IN operator to specify a list of ID values in our WHERE clause of the DELETE query. For example:

delete_query = "DELETE FROM employee WHERE emp_id IN (102, 103, 106);"

Steps to execute DELETE statement from Python

  1. Import the Psycopg2 library and establish a connection to the PostgreSQL database using the connect() method.
  2. Create a cursor object using the cursor() method.
  3. Define your DELETE query with the appropriate table and column names.
  4. Call the execute() method of the cursor object, passing your query as an argument.
  5. Call the commit() method to finalize the changes made to the database.
  6. Close the cursor and connection objects using the close() method.

Conclusion

In this article, we explored the different ways we can use Python’s Psycopg2 library to perform UPDATE and DELETE operations on PostgreSQL tables. We learned how to update a single and multiple rows, single and multiple columns of a table using UPDATE queries.

We also learned how to delete a single row or multiple rows from a PostgreSQL table using DELETE queries. By following these simple steps, we can perform these crucial DML operations effortlessly and automate tedious database management tasks.

Sometimes we need to insert, update, or delete multiple rows into our PostgreSQL table using a single query. Suppose we have a large dataset to work with.

In that case, performing DML operations on each record individually can be quite time-consuming and inefficient. Fortunately, the Psycopg2 library provides us with a cursor method, executemany(), that allows us to execute the same SQL statement multiple times, with different parameter values each time.

This feature speeds up the process of performing DML operations on multiple records.

Using cursor.executemany() method to insert, update, and delete multiple rows using a single query

The following example shows how we can use the cursor.executemany() method to insert multiple records in a single query:

import psycopg2
conn = psycopg2.connect(host="localhost",database="example_db", user="postgres", password="password")
cursor = conn.cursor()
# Insert multiple records
insert_query = "INSERT INTO employee (name, age, salary) VALUES (%s, %s, %s);"
employee_data = [('Tom', 30, 60000), ('Jane', 28, 65000), ('Harry', 35, 75000)]
cursor.executemany(insert_query, employee_data)
conn.commit()
# Close the cursor and connection
cursor.close()
conn.close()

In the above example, we use the cursor.executemany() method to insert multiple employee records in a single query. The insert_query contains placeholders %s for the parameter values, and the employee_data argument is a list of tuples containing the column values for each new record.

Similarly, we can use the cursor.executemany() method to update and delete multiple records in a single query. The syntax remains the same with parameter values for each query being different.

Syntax of executemany() and its usage in PostgreSQL

The syntax of the cursor.executemany() method is as follows:

cursor.executemany(sql_query, vars_list)

where sql_query is the SQL query statement containing placeholders (%s), and vars_list is a list of tuples, each containing the parameter values for each query.

Using the cursor.executemany() method is efficient as it allows a single query to perform multiple updates, inserts, or deletes.

This feature reduces the impact on both the client and server systems, making code more optimized for DML operations.

Python PostgreSQL Insert multiple rows

We can also use the cursor.executemany() method to insert multiple rows using a parameterized query. In this case, we define a parameterized query with placeholders for parameter values, and we pass multiple rows of data as tuples in a list.

Consider the following example:

import psycopg2
conn = psycopg2.connect(host="localhost",database="example_db", user="postgres", password="password")
cursor = conn.cursor()
# Insert multiple records using executemany()
insert_query = "INSERT INTO employee (name, age, salary) VALUES (%s, %s, %s);"
rows_to_insert = [
    ('Bob', 24, 50000),
    ('Maggie', 27, 68000),
    ('Alex', 23, 45000)
]
cursor.executemany(insert_query, rows_to_insert)
conn.commit()
# Close the cursor and connection
cursor.close()
conn.close()

In the above example, we define our parameterized query with placeholders %s for parameter values, and we pass multiple rows of data as tuples in a list. We then use the cursor.executemany() method to insert multiple rows of data in a single query.

Conclusion

In this article, we looked at how we can use Python’s Psycopg2 library to perform DML operations such as insert, update, and delete on multiple rows of a PostgreSQL table using a single query. We learned the syntax of the cursor.executemany() method and its usage in PostgreSQL.

Additionally, we looked at how to insert multiple rows using a parameterized query and the cursor.executemany() method. Using these techniques, we can perform large volume DML operations on a PostgreSQL table quickly and efficiently without overburdening the client or server systems.

In this section, we will continue exploring the cursor.executemany() method to perform UPDATE and DELETE operations on multiple rows.

Updating multiple rows using the cursor.executemany() method and parameterized query

We can use the cursor.executemany() method and parameterized query to update multiple rows in a PostgreSQL database table.

Let’s consider the following code example:

import psycopg2
conn = psycopg2.connect(host="localhost",database="example_db", user="postgres", password="password")
cursor = conn.cursor()
# Update multiple rows
update_query = "UPDATE employee SET salary = %s WHERE age > %s"
new_salary_and_age_data = [(120000, 35), (100000, 32), (80000, 30)]
cursor.executemany(update_query, new_salary_and_age_data)
conn.commit()
# Close the cursor and connection
cursor.close()
conn.close()

In the above example, we define an UPDATE query that modifies the salary of employees having an age greater than the given value. We then define a list of tuples called new_salary_and_age_data that contains the new salary and age data for each employee.

The cursor.executemany() method is used to execute the UPDATE query for each employee record in the list. This method can be used to update multiple rows of data efficiently, without having to execute multiple UPDATE statements.

Parameterized queries also ensure that our queries are safe from SQL injection attacks, making it a secure choice.

Deleting multiple rows using a SQL Delete query and a list of records to delete

We can use a SQL DELETE query along with placeholders and prepare a list of records to delete multiple rows of data in a PostgreSQL database table. Let’s consider the following code example:

import psycopg2
conn = psycopg2.connect(host="localhost",database="example_db", user="postgres", password="password")
cursor = conn.cursor()
# Delete multiple rows
delete_query = "DELETE FROM employee WHERE emp_id IN (%s, %s, %s)"
employee_ids = (101, 102, 105)
cursor.execute(delete_query, employee_ids)
conn.commit()
# Close the cursor and connection
cursor.close()
conn.close()

In the above example, we define a DELETE query that deletes employees’ records whose emp_id belongs to the given list. We then prepare a tuple called employee_ids that contains the

Popular Posts