Perform PostgreSQL CRUD operations from Python
Last Updated :
23 Mar, 2023
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 database using python. Pyscopg2 is a Database API which is a PostgreSQL compatible driver that manages connection pools.
In this article, we will learn how to connect to the PostgreSQL database using SQLAlchemy in python and also understood the procedure on how to perform CRUD operations against the PostgreSQL database.
Establishing a connection with the PostgreSQL database
As the first steps establish a connection with your existing database, using the connect() function of Psycopg2.
Python3
from psycopg2 import connect
# declare the connection string specifying
# the host name database name
# use name and password
conn_string = "host='host_name' \
dbname='database_name' user='user_name'\
password='your_password'"
# use connect function to establish the connection
conn = connect(conn_string)
Explanation:
host - name of the host in which the database is hosted
Username - Name of the admin
Password - Password of the admin
dbname- database name
Performing Create operation in PostgreSQL database using Python
- The syntax for creating a table is similar to that of the syntax used in conventionally SQL statements. Create different tables with required columns and column constraints as shown. Establish a connection with the PostgreSQL using connect() as discussed above
- Now, instantiate a new cursor() object. A cursor is a read-only pointer that allows a program, to access the result set of a query.
- Drop any tables in the same name if already exists. Then, pass the query for creating a table to the execute() function of psycopg2.
- Finally call the commit() method, indicating the connector object which was created to establish a connection with the database to commit all changes to the database. This will ensure that the tables are created.
Python3
import psycopg2
# Establishing the connection
conn = psycopg2.connect(
database="databasename",
user='username',
password='password',
host='hostname',
port='5432'
)
# Creating a cursor object using the
# cursor() method
cursor = conn.cursor()
# Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS publisher")
# Creating table as per requirement
sql = '''CREATE TABLE PUBLISHER(
publisher_id SERIAL PRIMARY KEY,
publisher_name VARCHAR(255) NOT NULL,
publisher_estd INT,
publsiher_location VARCHAR(255),
publsiher_type VARCHAR(255)
)'''
cursor.execute(sql)
print("Table created successfully........")
conn.commit()
# Closing the connection
conn.close()
Output:
Table created successfully........
Creating a table in PostgreSQL using PythonPerforming Insert operation in PostgreSQL database using Python
- The syntax for inserting records in a table is similar to that of the syntax used in conventionally SQL statements. Establish a connection with the PostgreSQL database as discussed above.
- Now, instantiate a new cursor() object. A cursor is a read-only pointer that allows a program, to access the result set of a query.
- Then, pass the query for inserting table records to the execute() function of psycopg2.
- Finally call the commit() method, indicating the connector object which was created to establish a connection with the database to commit all changes to the database. This will ensure that the records are inserted.
Python3
import psycopg2
try:
connection = psycopg2.connect(user="username",
password="password",
host="127.0.0.1",
port="5432",
database="databasename")
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO publisher(publisher_id,
publisher_name, publisher_estd, publsiher_location, publsiher_type)
VALUES (%s,%s,%s,%s,%s)"""
record_to_insert = [(1, 'Packt', 1950,
'chennai', 'books'),
(2, 'Springer', 1950,
'chennai', 'books'),
(3, 'Springer', 1950,
'chennai', 'articles'),
(4, 'Oxford', 1950,
'chennai', 'all'),
(5, 'MIT', 1950,
'chennai', 'books')]
for i in record_to_insert:
cursor.execute(postgres_insert_query, i)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully \
into publisher table")
except (Exception, psycopg2.Error) as error:
print("Failed to insert record into publisher table", error)
finally:
# closing database connection.
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
Output:
1 Record inserted successfully into publisher table
PostgreSQL connection is closed
Inserting records in a table in PostgreSQL using PythonPerforming Read operation in PostgreSQL database using Python
- Establish a connection with the PostgreSQL database as discussed above.
- Now, instantiate a new cursor() object. A cursor is a read-only pointer that allows a program, to access the result set of a query.
- Then, pass the query for creating a table to the execute() function of psycopg2.
- The query result will be stored in the cursor object named the engine. Use fetchall() method to get all the rows of the resulting query.
- Now, iterate through each row to see the query result as shown. In order to capture any errors while selecting the records in a database and close the connection smoothly after committing all changes, use try, expect, and finally block as shown in the below code.
Python3
import psycopg2
try:
connection = psycopg2.connect(user="username",
password="password",
host="hostname",
port="5432",
database="databasename")
cursor = connection.cursor()
postgreSQL_select_Query = "select * from publisher"
cursor.execute(postgreSQL_select_Query)
print("Selecting rows from publisher table using cursor.fetchall")
publisher_records = cursor.fetchall()
print("Print each row and it's columns values")
for row in publisher_records:
print("publisher_Id = ", row[0], )
print("publisher_name = ", row[1])
print("publisher_estd = ", row[2])
print("publisher_location = ", row[3])
print("publisher_type = ", row[4], "\n")
except (Exception, psycopg2.Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
# closing database connection.
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
Output:
Reading records in a table in PostgreSQL using PythonPerforming Update operation in PostgreSQL database using Python
The syntax for updating a table is similar to that of the syntax used in conventionally SQL statements. Here we write an update query that updates publisher name by publisher id as shown.
- Establish a connection with the PostgreSQL database as discussed above.
- Now, instantiate a new cursor() object. A cursor is a read-only pointer that allows a program, to access the result set of a query.
- Then, pass the query for updating a table to the execute() function of psycopg2.
- Use rowcount function to count the number of rows updated.
- Finally call the commit() method, indicating the connector object which was created to establish a connection with the database to commit all changes to the database. This will ensure that the tables are updated.
- In order to capture any errors while updating tables in a database and close the connection smoothly after committing all changes, use try, expect, and finally block as shown in the below code.
Test the update_publisher() function by calling the function and checking for the updates.
Python3
import psycopg2
def updateTable(publisherId, establishedYear):
try:
connection = psycopg2.connect(user="username",
password="password",
host="hostname",
port="5432",
database="databasename")
cursor = connection.cursor()
# Update single record now
sql_update_query = """Update publisher set \
publisher_estd = %s where publisher_id = %s"""
cursor.execute(sql_update_query,
(establishedYear,
publisherId))
connection.commit()
count = cursor.rowcount
print(count, "Record Updated successfully ")
except (Exception, psycopg2.Error) as error:
print("Error in update operation", error)
finally:
# closing database connection.
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
# call the update function
publisherId = 3
establishedYear = 2000
updateTable(publisherId, establishedYear)
Output:
1 Record Updated successfully
PostgreSQL connection is closed
Here, row corresponding to id = 3 is updated with a new value for publsher_estd.
Updating records in a table in PostgreSQL using PythonPerforming Delete operation in PostgreSQL database using Python
The syntax for deleting a table is similar to that of the syntax used in conventionally SQL statements. Here we will write a delete query that deletes the record by publisher id as shown.
- Establish a connection with the PostgreSQL database as discussed above.
- Now, instantiate a new cursor() object. A cursor is a read-only pointer that allows a program, to access the result set of a query.
- Then, pass the query for deleting a record in the table to the execute() function of psycopg2.
- Use rowcount function to count the number of rows deleted.
- Finally call the commit() method, indicating the connector object which was created to establish a connection with the database to commit all changes to the database. This will ensure that the records are deleted.
- In order to capture any errors while deleting records in a database and close the connection smoothly after committing all changes, use try, expect, and finally block as shown in the below code.
Test the delete_publisher() function by calling the function and check for the updates.
Python3
import psycopg2
def deleteData(publisherId):
try:
connection = psycopg2.connect(user="username",
password="password",
host="hostname",
port="5432",
database="databasename")
cursor = connection.cursor()
# Update single record now
sql_delete_query = """Delete from publisher\
where publisher_id = %s"""
cursor.execute(sql_delete_query, (publisherId,))
connection.commit()
count = cursor.rowcount
print(count, "Record deleted successfully ")
except (Exception, psycopg2.Error) as error:
print("Error in Delete operation", error)
finally:
# closing database connection.
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
publisherId = 4
deleteData(publisherId)
Output:
1 Record deleted successfully
PostgreSQL connection is closed
Here, the row with id = 4 has been deleted
Deleting records in a table in PostgreSQL using Python
Similar Reads
Python Tutorial - Learn Python Programming Language Python is one of the most popular programming languages. Itâs simple to use, packed with features and supported by a wide range of libraries and frameworks. Its clean syntax makes it beginner-friendly. It'sA high-level language, used in web development, data science, automation, AI and more.Known fo
10 min read
Python Interview Questions and Answers Python is the most used language in top companies such as Intel, IBM, NASA, Pixar, Netflix, Facebook, JP Morgan Chase, Spotify and many more because of its simplicity and powerful libraries. To crack their Online Assessment and Interview Rounds as a Python developer, we need to master important Pyth
15+ min read
Non-linear Components In electrical circuits, Non-linear Components are electronic devices that need an external power source to operate actively. Non-Linear Components are those that are changed with respect to the voltage and current. Elements that do not follow ohm's law are called Non-linear Components. Non-linear Co
11 min read
Python OOPs Concepts Object Oriented Programming is a fundamental concept in Python, empowering developers to build modular, maintainable, and scalable applications. By understanding the core OOP principles (classes, objects, inheritance, encapsulation, polymorphism, and abstraction), programmers can leverage the full p
11 min read
Python Projects - Beginner to Advanced Python is one of the most popular programming languages due to its simplicity, versatility, and supportive community. Whether youâre a beginner eager to learn the basics or an experienced programmer looking to challenge your skills, there are countless Python projects to help you grow.Hereâs a list
10 min read
Python Exercise with Practice Questions and Solutions Python Exercise for Beginner: Practice makes perfect in everything, and this is especially true when learning Python. If you're a beginner, regularly practicing Python exercises will build your confidence and sharpen your skills. To help you improve, try these Python exercises with solutions to test
9 min read
Python Programs Practice with Python program examples is always a good choice to scale up your logical understanding and programming skills and this article will provide you with the best sets of Python code examples.The below Python section contains a wide collection of Python programming examples. These Python co
11 min read
Spring Boot Tutorial Spring Boot is a Java framework that makes it easier to create and run Java applications. It simplifies the configuration and setup process, allowing developers to focus more on writing code for their applications. This Spring Boot Tutorial is a comprehensive guide that covers both basic and advance
10 min read
Python Introduction Python was created by Guido van Rossum in 1991 and further developed by the Python Software Foundation. It was designed with focus on code readability and its syntax allows us to express concepts in fewer lines of code.Key Features of PythonPythonâs simple and readable syntax makes it beginner-frien
3 min read
Python Data Types Python Data types are the classification or categorization of data items. It represents the kind of value that tells what operations can be performed on a particular data. Since everything is an object in Python programming, Python data types are classes and variables are instances (objects) of thes
9 min read