How to Connect to PostgreSQL in Python: Simple Guide

To connect to PostgreSQL in Python, use the psycopg2 library by importing it and calling psycopg2.connect() with your database credentials. This creates a connection object that you can use to run SQL commands.

Syntax

The basic syntax to connect to PostgreSQL in Python uses the psycopg2.connect() function with parameters like dbname, user, password, host, and port. This returns a connection object.

Then, you create a cursor from the connection to execute SQL queries.

python

import psycopg2

conn = psycopg2.connect(
    dbname="your_db_name",
    user="your_username",
    password="your_password",
    host="localhost",
    port=5432
)
cursor = conn.cursor()

Example

This example shows how to connect to a PostgreSQL database, create a table, insert data, and fetch it back.

python

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="testdb",
        user="testuser",
        password="testpass",
        host="localhost",
        port=5432
    )
    cursor = conn.cursor()

    cursor.execute("CREATE TABLE IF NOT EXISTS people (id SERIAL PRIMARY KEY, name VARCHAR(50));")
    cursor.execute("INSERT INTO people (name) VALUES (%s);", ("Alice",))
    conn.commit()

    cursor.execute("SELECT * FROM people;")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

except Exception as e:
    print("Error connecting to PostgreSQL", e)

finally:
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()

Output

(1, 'Alice')

Common Pitfalls

• Not installing psycopg2 before running code. Install it with pip install psycopg2-binary.
• Forgetting to commit changes after insert/update queries.
• Not closing the cursor and connection, which can cause resource leaks.
• Using wrong connection parameters like database name or password.

python

import psycopg2

# Wrong: Missing commit after insert
conn = psycopg2.connect(dbname="testdb", user="user", password="pass", host="localhost")
cursor = conn.cursor()
cursor.execute("INSERT INTO people (name) VALUES ('Bob');")
# Missing conn.commit() here

# Right: Commit after insert
conn.commit()
cursor.close()
conn.close()

Quick Reference

Remember these key points when connecting to PostgreSQL in Python:

• Use psycopg2.connect() with correct parameters.
• Always commit after data changes.
• Close cursor and connection to free resources.
• Handle exceptions to catch connection errors.

Key Takeaways

Install psycopg2 library before connecting to PostgreSQL in Python.

Use psycopg2.connect() with correct database credentials to create a connection.

Always commit your changes after insert or update queries.

Close your cursor and connection to avoid resource leaks.

Handle exceptions to manage connection errors gracefully.