Python PostgreSQL Tutorial

Introduction

PostgreSQL is a powerful, open-source relational database management system. Python, with its extensive libraries, can interact with PostgreSQL databases to perform various database operations like creating, reading, updating, and deleting records. This tutorial covers the basics of connecting Python to a PostgreSQL database and performing common database operations.

Table of Contents

1. Prerequisites
2. Installing Psycopg2
3. Connecting to PostgreSQL Database
4. Creating a Database
5. Creating a Table
6. Inserting Data
7. Querying Data
8. Updating Data
9. Deleting Data
10. Using Transactions
11. Handling Exceptions
12. Conclusion

1. Prerequisites

Before you start, make sure you have:

• Python installed on your system.
• PostgreSQL installed and running on your system.
• Basic knowledge of SQL and Python.

2. Installing Psycopg2

To interact with PostgreSQL, you need to install the Psycopg2 library. You can install it using pip.

pip install psycopg2-binary

3. Connecting to PostgreSQL Database

To connect to a PostgreSQL database, you need to import the psycopg2 module and use the connect() function.

Example

import psycopg2

# Establishing the connection
conn = psycopg2.connect(
    host="localhost",
    database="yourdatabase",
    user="yourusername",
    password="yourpassword"
)

# Creating a cursor object
cursor = conn.cursor()

# Checking if the connection was successful
if conn:
    print("Connected to PostgreSQL database")

4. Creating a Database

To create a new database, you need to connect to the PostgreSQL server and use the CREATE DATABASE SQL statement. Note that typically, databases are created outside of Python scripts, as this often requires higher-level administrative privileges.

Example

# Connecting to the default database
conn = psycopg2.connect(
    host="localhost",
    database="postgres",
    user="yourusername",
    password="yourpassword"
)

# Creating a cursor object
cursor = conn.cursor()

# Creating a new database
cursor.execute("CREATE DATABASE mydatabase")
print("Database created successfully")

# Closing the connection to the default database
cursor.close()
conn.close()

5. Creating a Table

To create a table, you need to connect to your newly created database and then use the CREATE TABLE SQL statement.

Example

# Connecting to the new database
conn = psycopg2.connect(
    host="localhost",
    database="mydatabase",
    user="yourusername",
    password="yourpassword"
)

# Creating a cursor object
cursor = conn.cursor()

# Creating a table
create_table_query = """
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
)
"""
cursor.execute(create_table_query)
conn.commit()
print("Table created successfully")

6. Inserting Data

You can insert data into a table using the INSERT INTO SQL statement.

Example

# Inserting data
insert_query = "INSERT INTO employees (first_name, last_name, email) VALUES (%s, %s, %s)"
values = ("Ravi", "Kumar", "ravi.kumar@example.com")

cursor.execute(insert_query, values)
conn.commit()
print("Data inserted successfully")

7. Querying Data

You can query data from a table using the SELECT SQL statement.

Example

# Querying data
select_query = "SELECT * FROM employees"
cursor.execute(select_query)

# Fetching all rows
rows = cursor.fetchall()

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

8. Updating Data

You can update data in a table using the UPDATE SQL statement.

Example

# Updating data
update_query = "UPDATE employees SET email = %s WHERE first_name = %s AND last_name = %s"
values = ("ravi.kumar@newemail.com", "Ravi", "Kumar")

cursor.execute(update_query, values)
conn.commit()
print("Data updated successfully")

9. Deleting Data

You can delete data from a table using the DELETE FROM SQL statement.

Example

# Deleting data
delete_query = "DELETE FROM employees WHERE first_name = %s AND last_name = %s"
values = ("Ravi", "Kumar")

cursor.execute(delete_query, values)
conn.commit()
print("Data deleted successfully")

10. Using Transactions

Transactions ensure that a sequence of operations is executed as a single unit of work. You can use BEGIN, COMMIT, and ROLLBACK statements to manage transactions.

Example

try:
    # Starting a transaction
    conn.autocommit = False

    # Performing some operations
    cursor.execute("INSERT INTO employees (first_name, last_name, email) VALUES (%s, %s, %s)", ("Anjali", "Sharma", "anjali.sharma@example.com"))
    cursor.execute("UPDATE employees SET email = %s WHERE first_name = %s AND last_name = %s", ("anjali.sharma@newemail.com", "Anjali", "Sharma"))

    # Committing the transaction
    conn.commit()
    print("Transaction committed successfully")
except Exception as e:
    # Rolling back the transaction in case of an error
    conn.rollback()
    print("Transaction failed and rolled back", e)
finally:
    # Resetting autocommit mode
    conn.autocommit = True

11. Handling Exceptions

Use try-except blocks to handle exceptions that may occur during database operations.

Example

try:
    # Trying to connect to PostgreSQL
    conn = psycopg2.connect(
        host="localhost",
        database="mydatabase",
        user="yourusername",
        password="yourpassword"
    )
    cursor = conn.cursor()
    print("Connected to PostgreSQL and accessed the database")
except psycopg2.DatabaseError as e:
    print("Failed to connect to PostgreSQL", e)
finally:
    if conn:
        cursor.close()
        conn.close()
        print("PostgreSQL connection closed")

12. Conclusion

Connecting Python to a PostgreSQL database allows you to perform various database operations from your Python applications. This tutorial covered the basics of connecting to a PostgreSQL database, creating databases and tables, inserting, querying, updating, and deleting data, using transactions, and handling exceptions. By mastering these concepts, you can efficiently manage and interact with PostgreSQL databases in your Python projects.