CRUD functions in a PostgreSQL database in Python

by Alex
CRUD functions in a PostgreSQL database in Python

In this tutorial, let’s look at how to perform Insert, Update, and Delete operations on a PostgreSQL database from Python scripts. They are also called DML operations. We will also learn how to pass parameters to SQL queries. Finally, let’s break down how to use cursor.executemany() to perform insert, update, or delete multiple rows in a single query.

Insert operation

In this section, we’ll look at how to perform the Insert command to insert one or more records into a PostgreSQL table from Python using Psycopg2. To execute the query, you need to do the following:

  • Install psycopg2 using pip.
  • Establish a connection to the database from Python.
  • Create an Insert query. This requires knowing the name of the table and its columns.
  • Execute the query with cursor.execute(). In response, you will get the number of rows affected.
  • After executing the query, you must commit the changes to the database.
  • Close the cursor object and the database connection.
  • It’s also important to catch any exceptions that may occur during the process.
  • Finally, you can check the results by querying the data from the table.

Now let’s look at a real-world example.


import psycopg2
from psycopg2 import Error
try:
# Connect to an existing database
connection = psycopg2.connect(user="postgres",
# password that you used when you installed PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO mobile (ID, MODEL, PRICE)
VALUES (%s,%s,%s)"""
record_to_insert = (5, 'One Plus 6', 950)
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print (count, "Record was successfully added to the mobile table")
except (Exception, Error) as error:
print("PostgreSQL error", error)
finally:
if connection:
cursor.close()
connection.close()
print("Connection to PostgreSQL closed")

Conclusion:

1 Record was successfully added to mobile table
Connection with PostgreSQL closed
  • This example used a query with parameters to pass values while the program was running. And at the end, the changes were saved with cursor.commit.
  • You can use a query with parameters to pass python variables as parameters in place of %s.

Операция Insert

The Update operation

In this section, you learn how to update a value in one or more columns for one or more table rows. To do this, you must modify a database query.

# Set the new value of price in the id line for the mobile table
Update mobile set price = %s where id = %s

Let’s look at the example of updating a single row in a table:


import psycopg2
from psycopg2 import
def update_table(mobile_id, price):
try:
# Connect to existing database
connection = psycopg2.connect(user="postgres",
# password that you specified when you installed PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
print("Table before the record update")
sql_select_query = """select * from mobile where id = %s""
cursor.execute(sql_select_query, (mobile_id,))
record = cursor.fetchone()
print(record)
# Update individual record
sql_update_query = """Update mobile set price = %s where id = %s""
cursor.execute(sql_update_query, (price, mobile_id))
connection.commit()
count = cursor.rowcount
print(count, "Record was successfully updated")
print("Table after update")
sql_select_query = """select * from mobile where id = %s""
cursor.execute(sql_select_query, (mobile_id,))
record = cursor.fetchone()
print(record)
except (Exception, Error) as error:
print("PostgreSQL error", error)
finally:
if connection:
cursor.close()
connection.close()
print("Connection to PostgreSQL closed")
update_table(3, 970)

Let’s make sure that the update worked. Conclusion:

Table before the record update
(3, 'Samsung Galaxy S21', 900.0)
1 Record was successfully updated
Table after record updating
(3, 'Samsung Galaxy S21', 970.0)
Connection with PostgreSQL closed

Deleting rows and columns

In this section we will look at how to perform the operation of deleting data from a table using a program in Python and Psycopg2.

# Remove from the table ... in the row with id ...
Delete from mobile where id = %s

You can go straight to the example. It looks like this:


import psycopg2
from psycopg2 import
def delete_data(mobile_id):
try:
# Connect to existing database
connection = psycopg2.connect(user="postgres",
# password which you used to install PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
# Deleting a record
sql_delete_query = """Delete from mobile where id = %s""
cursor.execute(sql_delete_query, (mobile_id,))
connection.commit()
count = cursor.rowcount
print(count, "Record was successfully deleted")
except (Exception, Error) as error:
print("PostgreSQL error", error)
finally:
if connection:
cursor.close()
connection.close()
print("Connection to PostgreSQL closed")
delete_data(4)
delete_data(5)

Let’s make sure that the entry has disappeared from the table.

1 Record was successfully deleted
Connection with PostgreSQL is closed
1 Record was successfully deleted
Connection with PostgreSQL is closed

Cursor.executemany() queries multiple rows

The cursor.executemany() method queries the database with all parameters. Very often you need to run the same query with different data. For example, to update student attendance information. The data will most likely be different, but the SQL will remain the same. Use cursor.executemany() to insert, update, and delete multiple rows in the same query. The executemany() syntax is:

executemany(query, vars_list)
  • In this case query can be any DML operation (insert, update, delete).
  • thevars_list is just a list of tuples that are passed to the query.
  • Each tuple contains one string to insert or delete.

Now let’s see how to use this method.

Inserting multiple rows into a PostgreSQL table

You can insert multiple rows using an SQL query. This uses a query with parameters and the executemany() method.


import psycopg2
from psycopg2 import Error
def bulk_insert(records):
try:
# Connect to an existing database
connection = psycopg2.connect(user="postgres",
# password that you specified when you installed PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
sql_insert_query = """ INSERT INTO mobile (id, model, price)
VALUES (%s,%s,%s) """
# executemany() to insert multiple lines
result = cursor.executemany(sql_insert_query, records)
connection.commit()
print(cursor.rowcount, "Record(s) successfully inserted into table mobile")
except (Exception, Error) as error:
print("PostgreSQL error", error)
finally:
if connection:
cursor.close()
connection.close()
print("Connection to PostgreSQL closed")
records_to_insert = [ (4,'LG', 800) , (5,'One Plus 6', 950)]
bulk_insert(records_to_insert)

Let’s check the result by returning data from the table.

2 Record(s) successfully inserted into mobile table
Connection with PostgreSQL closed
Note: For this query, a list of records was created with two tuples. We also used surrogates. They allow you to pass values into the query while the program is running.

Updating multiple rows in one query

Most often you need to run the same query, but with different data. For example, you want to update the salaries of your employees. The amount will be different, but not the query. You can update multiple columns in a table using cursor.executemany() and a query with parameters(%). Let’s look at an example.


import psycopg2
from psycopg2 import
def update_in_bulk(records):
try:
# Connect to existing database
connection = psycopg2.connect(user="postgres",
# password which you specified during PostgreSQL installation
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
# Refresh multiple records
sql_update_query = """Update mobile set price = %s where id = %s""
cursor.executemany(sql_update_query, records)
connection.commit()
row_count = cursor.rowcount
print(row_count, "Records updated")
except (Exception, Error) as error:
print("Error when working with PostgreSQL", error)
finally:
if connection:
cursor.close()
connection.close()
print("Connection to PostgreSQL closed")
update_in_bulk([(750, 4), (950, 5)])

Output:

2 Entries updated
Connection with PostgreSQL closed
Let's check the result. Обновление нескольких строк в одном запросе Use cursor.rowcount to get the total number of rows changed by the executemany() method.

Deleting multiple rows from a table

In this example, we use the Delete query with substitutes that substitute the IDs of the records to be deleted. There is also a list of records to delete. The list has tuples for each row. In the example there are two, which means two rows will be deleted.


import psycopg2
from psycopg2 import Error
def delete_in_bulk(records):
try:
# Connect to existing database
connection = psycopg2.connect(user="postgres",
# password which you specified during PostgreSQL installation
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
delete_query = """Delete from mobile where id = %s""
cursor.executemany(delete_query, records)
connection.commit()
row_count = cursor.rowcount
print(row_count, "The records were deleted")
except (Exception, Error) as error:
print("PostgreSQL error", error)
finally:
if connection:
cursor.close()
connection.close()
print("Connection to PostgreSQL closed")
delete_in_bulk([(5,), (4,), (3,)])

Let’s make sure that the query was successful.

3 Entries deleted
Connection with PostgreSQL is closed

Related Posts

LEAVE A COMMENT