TechBeamersTechBeamers
  • Learn ProgrammingLearn Programming
    • Python Programming
      • Python Basic
      • Python OOP
      • Python Pandas
      • Python PIP
      • Python Advanced
      • Python Selenium
    • Python Examples
    • Selenium Tutorials
      • Selenium with Java
      • Selenium with Python
    • Software Testing Tutorials
    • Java Programming
      • Java Basic
      • Java Flow Control
      • Java OOP
    • C Programming
    • Linux Commands
    • MySQL Commands
    • Agile in Software
    • AngularJS Guides
    • Android Tutorials
  • Interview PrepInterview Prep
    • SQL Interview Questions
    • Testing Interview Q&A
    • Python Interview Q&A
    • Selenium Interview Q&A
    • C Sharp Interview Q&A
    • PHP Interview Questions
    • Java Interview Questions
    • Web Development Q&A
  • Self AssessmentSelf Assessment
    • Python Test
    • Java Online Test
    • Selenium Quiz
    • Testing Quiz
    • HTML CSS Quiz
    • Shell Script Test
    • C/C++ Coding Test
Search
  • Python Multiline String
  • Python Multiline Comment
  • Python Iterate String
  • Python Dictionary
  • Python Lists
  • Python List Contains
  • Page Object Model
  • TestNG Annotations
  • Python Function Quiz
  • Python String Quiz
  • Python OOP Test
  • Java Spring Test
  • Java Collection Quiz
  • JavaScript Skill Test
  • Selenium Skill Test
  • Selenium Python Quiz
  • Shell Scripting Test
  • Latest Python Q&A
  • CSharp Coding Q&A
  • SQL Query Question
  • Top Selenium Q&A
  • Top QA Questions
  • Latest Testing Q&A
  • REST API Questions
  • Linux Interview Q&A
  • Shell Script Questions
© 2024 TechBeamers. All Rights Reserved.
Reading: How to Connect to PostgreSQL in Python
Font ResizerAa
TechBeamersTechBeamers
Font ResizerAa
  • Python
  • SQL
  • C
  • Java
  • Testing
  • Selenium
  • Agile Concepts Simplified
  • Linux
  • MySQL
  • Python Quizzes
  • Java Quiz
  • Testing Quiz
  • Shell Script Quiz
  • WebDev Interview
  • Python Basic
  • Python Examples
  • Python Advanced
  • Python OOP
  • Python Selenium
  • General Tech
Search
  • Programming Tutorials
    • Python Tutorial
    • Python Examples
    • Java Tutorial
    • C Tutorial
    • MySQL Tutorial
    • Selenium Tutorial
    • Testing Tutorial
  • Top Interview Q&A
    • SQL Interview
    • Web Dev Interview
  • Best Coding Quiz
    • Python Quizzes
    • Java Quiz
    • Testing Quiz
    • ShellScript Quiz
Follow US
© 2024 TechBeamers. All Rights Reserved.
Python AdvancedPython Tutorials

How to Connect to PostgreSQL in Python

Last updated: Apr 13, 2024 12:57 pm
By Meenakshi Agarwal
Share
7 Min Read
How to Connect to PostgreSQL in Python
SHARE

PostgreSQL is a powerful open-source relational database management system. In this tutorial, we’ll explore all the steps you need to connect PostgreSQL from Python code. From setting up a PostgreSQL database to executing queries using Python, we’ll cover it all. By the end, you’ll have a solid foundation for seamlessly interacting with PostgreSQL databases in your Python projects.

Contents
1. Installing Required Libraries2. Set up a PostgreSQL Database3. Connect to DB in Python4. Connection Pooling5. Executing SQL Queries6. Handle Exceptions in Python7. Transactions8. Using Context Managers9. Parameterized Queries for Security10. Fetching Specific RecordsConclusion – Python to Connect with PostgreSQL

Also Read:
How to Connect MySQL DB from Python
How to Connect Mongo DB from Python

Steps to Connect with PostgreSQL from Python

How to Connect to PostgreSQL in Python

1. Installing Required Libraries

Before diving into PostgreSQL-Python connectivity, ensure you have the necessary libraries installed. Use the following commands:

pip install psycopg2

This installs the psycopg2 library, a PostgreSQL adapter for Python. Now, let’s delve into the details.

2. Set up a PostgreSQL Database

Assuming you have PostgreSQL installed on your machine, let’s create a database and a table for demonstration purposes. Open your PostgreSQL shell and run the following SQL commands:

CREATE DATABASE tutorialdb;

Now, connect to the newly created database:

\c tutorialdb;

You can run the above command in the command-line interface (CLI) to connect to a specific DB like tutorialdb. Let’s now create a sample table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR NOT NULL,
    email VARCHAR NOT NULL
);

This sets up a basic database structure that we’ll interact with using Python.

3. Connect to DB in Python

Now, let’s jump into Python and create a connection to our database. Use the following script as a starting point:

import psycopg2

# Start the connection
conn = psycopg2.connect(
    database="tutorialdb",
    user="srv_username",
    password="srv_password",
    host="srv_host",
    port="srv_port"
)

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

# Run operations...

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

Replace placeholders with your PostgreSQL credentials. This script initiates a connection and creates a cursor, essential for running the SQL commands

4. Connection Pooling

To allow multiple users or connections, use connection pooling to manage and reuse DB connections.

from psycopg2 import pool

# Create a conn pool
conn_pool = pool.SimpleConnectionPool(
    1,  # Min no of conn
    5,  # Max no of conn
    database="tutorialdb",
    user="srv_username",
    password="srv_password",
    host="srv_host",
    port="srv_port"
)

# Make a conn from the pool
conn = conn_pool.getconn()

# ... Run operations ...

# Free the conn back to the pool
conn_pool.putconn(conn)

5. Executing SQL Queries

With the connection established, let’s execute some basic queries. For instance, inserting data into the ‘users’ table:

# Example: Inserting data
cursor.execute("INSERT INTO users (username, email) VALUES (%s, %s)", ("akbar_malik", "akbar@example.com"))

# Committing the changes
conn.commit()

This inserts a new user into the ‘users’ table. Always remember to commit changes to persist them in the database.

Now, let’s fetch data:

# Example: Fetching data
cursor.execute("SELECT * FROM users")

# Fetch all rows
rows = cursor.fetchall()

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

This retrieves all rows from the ‘users’ table and prints them.

6. Handle Exceptions in Python

Python Exception handling is crucial when dealing with databases. Wrap your database operations in try-except blocks to gracefully handle errors:

try:
    # Database operations here...

except psycopg2.Error as e:
    print(f"Error: {e}")

finally:
    # Cleanup operations (closing cursor and connection)
    cursor.close()
    conn.close()

This ensures that even if an error occurs, the connection and cursor are properly closed, preventing potential issues.

7. Transactions

psycopg2 supports transactions. It allows you to group multiple SQL statements into a single atomic operation. Use the commit() method to apply changes or the rollback() method to discard them.

try:
    # Start a transaction
    conn = psycopg2.connect(database="tutorialdb", user="srv_username", password="srv_password", host="srv_host", port="srv_port")
    cursor = conn.cursor()

    # Run SQL commands within the transaction
    cursor.execute("INSERT INTO users (username, email) VALUES (%s, %s)", ("rihana_julfi", "rihana@example.com"))

    # Commit the transaction
    conn.commit()

except Exception as e:
    # Rollback the transaction in case of an error
    conn.rollback()
    print(f"Error: {e}")

finally:
    # Close the cursor and conn
    cursor.close()
    conn.close()

8. Using Context Managers

Python’s context managers, facilitated by the with statement, simplify resource management. Let’s modify our connection script to use a context manager:

import psycopg2

# Establishing the connection using a context manager
with psycopg2.connect(
        database="tutorialdb",
        user="srv_username",
        password="srv_password",
        host="srv_host",
        port="srv_port"
) as conn:
    # Creating a cursor within the context manager
    with conn.cursor() as cursor:
        # Performing operations...

# No need to explicitly close the connection and cursor; the context manager handles it

This ensures proper resource cleanup without the need for explicit close() calls.

9. Parameterized Queries for Security

To prevent SQL injection attacks, always use parameterized queries. Here’s an example:

# Example: Parameterized query
username = "salma_bano"
email = "salmab@example.com"

cursor.execute("INSERT INTO users (username, email) VALUES (%s, %s)", (username, email))

This approach ensures that user inputs are treated as data and not executable code.

10. Fetching Specific Records

Let’s refine our data fetching example to retrieve specific records based on a condition:

# Example: Fetching specific records
cursor.execute("SELECT * FROM users WHERE username = %s", ("abu_asim",))

# Fetch the first matching row
row = cursor.fetchone()

# Displaying the result
print(row)

This fetches the first row where the username is “abu_asim.”

Conclusion – Python to Connect with PostgreSQL

In this tutorial, we covered the essential steps to connect PostgreSQL with Python. From installation to executing queries and handling exceptions, you now have a solid understanding of integrating these two powerful tools. As you continue to explore this synergy, consider exploring advanced topics such as ORM (Object-Relational Mapping) libraries to further enhance your Python-PostgreSQL experience.

Happy coding!

You Might Also Like

Generate Random IP Address (IPv4/IPv6) in Python

Python Remove Elements from a List

How to Use Extent Report in Python

10 Python Tricky Coding Exercises

Difference Between 3 Python SQL Libraries

Meenakshi Agarwal Avatar
By Meenakshi Agarwal
Follow:
Hi, I'm Meenakshi Agarwal. I have a Bachelor's degree in Computer Science and a Master's degree in Computer Applications. After spending over a decade in large MNCs, I gained extensive experience in programming, coding, software development, testing, and automation. Now, I share my knowledge through tutorials, quizzes, and interview questions on Python, Java, Selenium, SQL, and C# on my blog, TechBeamers.com.
Previous Article Struct in C Programming Language Explained with Examples C Programming Language “Struct”
Next Article MySQL vs MongoDB Comparison - Know the Key Differences MySQL vs MongoDB Comparison

Popular Tutorials

SQL Interview Questions List
50 SQL Practice Questions for Good Results in Interview
SQL Interview Nov 01, 2016
Demo Websites You Need to Practice Selenium
7 Sites to Practice Selenium for Free in 2024
Selenium Tutorial Feb 08, 2016
SQL Exercises with Sample Table and Demo Data
SQL Exercises – Complex Queries
SQL Interview May 10, 2020
Java Coding Questions for Software Testers
15 Java Coding Questions for Testers
Selenium Tutorial Jun 17, 2016
30 Quick Python Programming Questions On List, Tuple & Dictionary
30 Python Programming Questions On List, Tuple, and Dictionary
Python Basic Python Tutorials Oct 07, 2016
//
Our tutorials are written by real people who’ve put in the time to research and test thoroughly. Whether you’re a beginner or a pro, our tutorials will guide you through everything you need to learn a programming language.

Top Coding Tips

  • PYTHON TIPS
  • PANDAS TIPSNew
  • DATA ANALYSIS TIPS
  • SELENIUM TIPS
  • C CODING TIPS
  • GDB DEBUG TIPS
  • SQL TIPS & TRICKS

Top Tutorials

  • PYTHON TUTORIAL FOR BEGINNERS
  • SELENIUM WEBDRIVER TUTORIAL
  • SELENIUM PYTHON TUTORIAL
  • SELENIUM DEMO WEBSITESHot
  • TESTNG TUTORIALS FOR BEGINNERS
  • PYTHON MULTITHREADING TUTORIAL
  • JAVA MULTITHREADING TUTORIAL

Sign Up for Our Newsletter

Subscribe to our newsletter to get our newest articles instantly!

Loading
TechBeamersTechBeamers
Follow US
© 2024 TechBeamers. All Rights Reserved.
  • About
  • Contact
  • Disclaimer
  • Privacy Policy
  • Terms of Use
TechBeamers Newsletter - Subscribe for Latest Updates
Join Us!

Subscribe to our newsletter and never miss the latest tech tutorials, quizzes, and tips.

Loading
Zero spam, Unsubscribe at any time.
x