Building Python Applications Using PostgreSQL

PostgreSQL is an open-source object-relational database management system. It is widely used in enterprise-level applications for its stability, reliability, and scalability. Python is also one of the most popular programming languages used for developing various types of applications. In this post, we will learn how to build Python applications using PostgreSQL.

History of PostgreSQL

PostgreSQL, also known as Postgres, was first released in 1989 by the University of California, Berkeley. It was initially called Postgres and later renamed to PostgreSQL to avoid trademark issues. It was designed to be a powerful and reliable database system that can handle large amounts of data and provide features like transactions, SQL support, and concurrency control. PostgreSQL is an open-source project and is maintained by a global community of developers.

PostgreSQL vs Other Databases

PostgreSQL is known for its reliability, robustness, and stability. It is one of the most advanced open-source databases available today, and its features are comparable to those of commercial databases like Oracle and Microsoft SQL Server. Some of the main differences between PostgreSQL and other databases are:

• PostgreSQL is open source, while many commercial databases are not.
• PostgreSQL supports more advanced features like full-text search, arrays, and JSON.
• PostgreSQL has a more complex architecture that allows for better concurrency and scalability.
• PostgreSQL has a more liberal license compared to other databases.

Installing PostgreSQL and psycopg2

To build Python applications using PostgreSQL, we need to install PostgreSQL and a Python library called psycopg2, which is a PostgreSQL adapter for Python. Here are the steps to install PostgreSQL and psycopg2:

Installing PostgreSQL

On Linux:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

On macOS:

brew install postgresql

Installing psycopg2

pip install psycopg2

Connecting to PostgreSQL

To connect to a PostgreSQL database, we need to create a connection object using the psycopg2 library. Here’s how to create a connection object:

import psycopg2

conn = psycopg2.connect(
    database="mydb",
    user="myusername",
    password="mypassword",
    host="localhost",
    port="5432"
)

Here, we create a connection object and pass the database name, username, password, host, and port as arguments.

Creating a Table

To create a table in PostgreSQL, we need to execute a SQL query using the execute() method of the connection object. Here’s an example:

cur = conn.cursor()

cur.execute("CREATE TABLE users (id serial PRIMARY KEY, name VARCHAR(50), age INTEGER);")

conn.commit()

cur.close()
conn.close()

In this example, we create a table called users with three columns: id, name, and age. The id column is set to be the primary key, and it uses the serial data type, which is an auto-incrementing integer.

Inserting Data

To insert data into a PostgreSQL table, we need to execute an INSERT SQL query using the execute() method of the cursor object. Here’s an example:

# define the query
query = "INSERT INTO employees (first_name, last_name, email) VALUES (%s, %s, %s)"

# define the values
values = ("John", "Doe", "john.doe@email.com")

# execute the query with values
cursor.execute(query, values)

# commit the transaction
conn.commit()

# print success message
print(cursor.rowcount, "record inserted.")

In this example, we define the SQL query to insert a record into the employees table with three columns: first_name, last_name, and email. We also define the values for these columns in a tuple.

Then, we execute the query with the execute() method of the cursor object and pass the query and the values as arguments. We also commit the transaction using the commit() method of the connection object.

Finally, we print a success message with the number of rows that were inserted.

Selecting Data

To select data from a PostgreSQL table, we need to execute a SELECT SQL query using the execute() method of the cursor object. Here’s an example:

# define the query
query = "SELECT * FROM employees"

# execute the query
cursor.execute(query)

# fetch all rows
rows = cursor.fetchall()

# print the rows
for row in rows:
    print(row)

In this example, we define the SQL query to select all records from the employees table. Then, we execute the query with the execute() method of the cursor object.

We fetch all rows using the fetchall() method of the cursor object and store them in the rows variable. Finally, we loop through the rows variable and print each row.

Updating Data

To update data in a PostgreSQL table, we need to execute an UPDATE SQL query using the execute() method of the cursor object. Here’s an example:

import psycopg2

#establishing the connection
conn = psycopg2.connect(database="testdb", user='postgres', password='password', host='localhost', port='5432')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Preparing query to update a record
update_query = """UPDATE student SET age = 20 WHERE id = 1"""

try:
    #Executing the query
    cursor.execute(update_query)
    #Commit your changes in the database
    conn.commit()
    print("Record updated successfully")
except:
    #Rollback in case there is any error
    conn.rollback()
    print("Error occurred while updating the record")

#Closing the cursor and connection
cursor.close()
conn.close()

In this example, we are updating the age of a student whose ID is 1 in the student table. We first prepare the SQL query to update the record, then we execute the query using the execute() method of the cursor object. If the update is successful, we commit the changes to the database. Otherwise, we rollback the changes and print an error message.

Deleting Data

To delete data from a PostgreSQL table, we need to execute a DELETE SQL query using the execute() method of the cursor object. Here’s an example:

# define the query
query = "DELETE FROM employees WHERE last_name = %s"

# define the value
value = ("Doe", )

# execute the query with value
cursor.execute(query, value)

# commit the transaction
conn.commit()

# print success message
print(cursor.rowcount, "record(s) deleted.")

Closing the Connection

Once we have finished performing CRUD operations, we need to close the cursor and connection objects using the close() method. Here’s an example:

import psycopg2

#establishing the connection
conn = psycopg2.connect(database="testdb", user='postgres', password='password', host='localhost', port='5432')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Closing the cursor and connection
cursor.close()
conn.close()

In this example, we are closing the cursor and connection objects that we opened earlier.

Conclusion

In this tutorial, we learned how to build Python applications using the PostgreSQL database. We covered the basics of PostgreSQL, including how to create a database, create tables, and perform CRUD operations. We also learned how to use the psycopg2 module to connect to a PostgreSQL database from a Python application and execute SQL queries. With this knowledge, you should be able to build Python applications that interact with PostgreSQL databases.