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:
“`python
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:
“`python
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.In the previous sections of 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 queries. Now, let’s dive into how we can use Python to perform UPDATE and DELETE operations on PostgreSQL tables.
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:
“`python
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:
“`python
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:
“`python
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
To execute an UPDATE statement from Python, we follow these steps:
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:
“`python
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:
“`python
delete_query = “DELETE FROM employee WHERE emp_id IN (102, 103, 106);”
“`
Steps to execute DELETE statement from Python
To execute a DELETE statement from Python, we follow these steps:
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.In the earlier sections, we have looked at how to use Python’s Psycopg2 library to interact with a PostgreSQL database and perform various DML operations such as INSERT, UPDATE, and DELETE queries.
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:
“`python
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:
“`python
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:
“`python
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 the previous sections, we learned how to use the `cursor.executemany()` method to insert multiple rows into a PostgreSQL database table.
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:
“`python
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:
“`python
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