How To Connect and run SQL Queries to a PostgreSQL Database from Python

In this PostgreSQL Python tutorial, we will explain how to connect to a PostgreSQL database using Python and execute SQL queries. Using the powerful psycopg2 library, we can seamlessly interact with our PostgreSQL database from Python, making it easy to perform tasks like inserting, updating, and retrieving data.

Whether we're new to Python or a seasoned developer, mastering these techniques will enhance our ability to manage and manipulate data directly within our PostgreSQL database. In this article, we will walk us through the process of connecting to a PostgreSQL database using Python, running SQL queries, and handling results effectively.

Introduction to PostgreSQL and Python Integration

PostgreSQL is a widely-used open-source relational database management system known for its scalability, robustness, and advanced features. Python, with its flexibility, makes database connectivity seamless through libraries like psycopg2, enabling developers to interact with PostgreSQL databases for executing SQL queries.

Step 1: Install psycopg2 Library

The psycopg2 library is the most popular and widely used PostgreSQL adapter for Python.

• Install PostgreSQL, If you haven't installed it.
  
• We need to install the psycopg2 library to connect to a PostgreSQL database. Open the command prompt and run the below command to install psycopg2

pip3 install psycopg2

Step 2: Create a PostgreSQL Database

To interact with a database, we need a PostgreSQL database. We can create a Database in 2 Ways:

1. Using pgAdmin 4 UI
2. Using SQL query

1. Using pgAdmin 4 UI

Go to pgAdmin and Follow these Steps.

• Open pgAdmin and navigate to your server.
  
• Right-click on the server, select Create -> Database.
  
• Fill out the form (e.g., database name: Workspace) and click Save.

2. Create Database Using SQL query

Go to pgAdmin and follow these Steps. Run the below Command in the Query tab

CREATE DATABASE WorkSpace;

Step 3: Connect to PostgreSQL Database Using psycopg2

We need to connect to a PostgreSQL database using psycopg2.connect() function.

Where the attributes of connect() function are:

• host: Hostname (e.g., localhost)
  
• dbname: Database name
  
• user: Username
  
• password: Password
  
• port: Port number (default is 5432)

In case we don't know any of these connect() function attributes, we can follow the below steps:

Python Example: Establishing Connection

Now You Know All the properties of this Database. To connect to the database, we need to pass the attributes as arguments to the connect() function.

Syntax

conn = psycopg2.connect(
    host = 'localhost',
    dbname = 'For_Practice',
    user = 'postgres',
    password = '[Password]',
    port = 5432
)

Step 4: Create a Cursor

• Create a cursor(i.e., curr) object and call its execute() method to execute queries.
  
• Where execute() method is used to run a query that is passed as a string.

Syntax

cur = conn.cursor()
cur.execute('[SQL queries]')

Step 5: Close the Connection

In the end, We need to save the changes using commit() method and finally close the opened connection using close() method.

Syntax

conn.commit()
cur.close()

Complete Python Script Example

import psycopg2

conn = None
try:
    # connect to the PostgreSQL server
    print('Connecting to the PostgreSQL database...')
    conn = psycopg2.connect(
        host = 'localhost',
        dbname = 'For_Practice',
        user = 'postgres',
        password = '321654',
        port = 5432
    )
    
    # Creating a cursor with name cur.
    cur = conn.cursor()
    print('Connected to the PostgreSQL database')
    
    # Execute a query:
    # To display the PostgreSQL 
    # database server version
    cur.execute('SELECT version()')
    print(cur.fetchone())
    
    # Close the connection
    cur.close()
    
except(Exception, psycopg2.DatabaseError) as error:
    print(error)
finally:
    if conn is not None:
        conn.close()
        print('Database connection closed.')

Output

Conclusion

In conclusion, the psycopg2 library in Python is an essential tool for establishing a seamless Python PostgreSQL database connection and running SQL queries directly from our Python code. By mastering these techniques, we can efficiently manage and manipulate data in PostgreSQL databases, allowing for better integration of database operations into our Python applications. Whether for development or data analysis, this powerful combination enhances our productivity and workflow.