🐘 Python Tutorial — PostgreSQL Database Connectivity

Introduction 🌟

PostgreSQL is a powerful, open-source, enterprise-grade relational database known for stability, advanced features, and high performance. Python connects to PostgreSQL using the popular library psycopg2.

Note

💡 PostgreSQL must be installed on your system
💡 Use psycopg2 (classic driver) or psycopg (new version)
💡 Supports advanced SQL, JSON, arrays, transactions, and more

1. Installing psycopg2 📦

install_psycopg.sh

pip install psycopg2-binary

✔ -binary version is easiest for beginners
✔ Regular psycopg2 needs system compilers

2. Connecting to PostgreSQL 🔌

connect_pg.py

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    user="postgres",
    password="your_password",
    database="testdb"
)

print("Connected!")

✔ Returns a connection object

3. Creating a Cursor 🖱️

cursor.py

cursor = conn.cursor()

✔ Cursor executes SQL queries

4. Creating a Table 🏗️

create_table.py

cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
)
""")
conn.commit()

✔ SERIAL auto-increments ID (PostgreSQL-specific)

5. Inserting Data ➕

insert.py

cursor.execute(
    "INSERT INTO users (name, age) VALUES (%s, %s)",
    ("Sathish", 25)
)
conn.commit()

Note

✔ Always use %s placeholders
✔ Psycopg2 handles escaping safely

6. Insert Multiple Rows 📦

executemany.py

users = [
    ("Arun", 22),
    ("Kumar", 30),
    ("Priya", 27)
]

cursor.executemany("INSERT INTO users (name, age) VALUES (%s, %s)", users)
conn.commit()

7. Fetching Data (SELECT) 🔍

Fetch All

fetch_all.py

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
print(rows)

Fetch One

fetch_one.py

cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
print(cursor.fetchone())

Fetch Many

fetch_many.py

cursor.execute("SELECT * FROM users")
print(cursor.fetchmany(2))

8. Updating Data ✏️

update.py

cursor.execute(
    "UPDATE users SET age = %s WHERE name = %s",
    (26, "Sathish")
)
conn.commit()

9. Deleting Data ❌

delete.py

cursor.execute("DELETE FROM users WHERE id = %s", (1,))
conn.commit()

10. Using PostgreSQL Advanced Types 🧩

JSON Fields

json_type.py

cursor.execute("CREATE TABLE IF NOT EXISTS products (id SERIAL PRIMARY KEY, data JSON)")
cursor.execute("INSERT INTO products (data) VALUES (%s)", ('{"name": "Laptop", "price": 1500}',))
conn.commit()

Array Types

array_type.py

cursor.execute("CREATE TABLE IF NOT EXISTS tags (id SERIAL PRIMARY KEY, labels TEXT[])")
cursor.execute("INSERT INTO tags (labels) VALUES (%s)", (["python", "database"],))
conn.commit()

11. Dictionary Cursor (Named Columns) 🗂️

dict_cursor.py

import psycopg2.extras

cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursor.execute("SELECT * FROM users")

rows = cursor.fetchall()
for r in rows:
    print(r["name"], r["age"])

✔ Useful for APIs & JSON responses

12. Transactions — Commit & Rollback 🔄

transactions.py

try:
    cursor.execute("UPDATE users SET age = age + 1")
    conn.commit()
except:
    conn.rollback()

✔ Ensures safety in case of failure

13. Handling PostgreSQL Errors ⚠️

error_handling.py

import psycopg2

try:
    cursor.execute("SELECT * FROM unknown_table")
except psycopg2.Error as e:
    print("Database error:", e)

14. Connection Pooling 🚰

PostgreSQL supports connection pooling via psycopg2.pool.

pooling.py

from psycopg2 import pool

pg_pool = pool.SimpleConnectionPool(
    1, 5,
    user="postgres",
    password="password",
    host="localhost",
    database="testdb"
)

conn = pg_pool.getconn()
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM users")
print(cursor.fetchone())
pg_pool.putconn(conn)

✔ Essential for high-traffic applications

15. Dropping Tables ⚠️

drop_table.py

cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()

Note

⚠️ Irreversible — use with caution

16. Closing Connection 🔚

close.py

cursor.close()
conn.close()

17. Full CRUD Example 📝

pg_crud.py

import psycopg2

def connect():
    return psycopg2.connect(
        host="localhost",
        user="postgres",
        password="pass",
        database="crud_demo"
    )

def create_table():
    conn = connect()
    cur = conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS tasks(id SERIAL PRIMARY KEY, title VARCHAR(100))")
    conn.commit()
    conn.close()

def add_task(title):
    conn = connect()
    conn.cursor().execute("INSERT INTO tasks (title) VALUES (%s)", (title,))
    conn.commit()
    conn.close()

def get_tasks():
    conn = connect()
    cur = conn.cursor()
    cur.execute("SELECT * FROM tasks")
    data = cur.fetchall()
    conn.close()
    return data

def delete_task(id):
    conn = connect()
    conn.cursor().execute("DELETE FROM tasks WHERE id = %s", (id,))
    conn.commit()
    conn.close()

# Usage
create_table()
add_task("Learn PostgreSQL")
print(get_tasks())

PostgreSQL Cheat Sheet 📘

OperationSQL Command
Create TableCREATE TABLE ...
InsertINSERT INTO ...
SelectSELECT * FROM ...
UpdateUPDATE ...
DeleteDELETE FROM ...

Best Practices 💡

  • ✔ Always use parameterized queries
  • ✔ Use connection pooling in production
  • ✔ Close connections & cursors properly
  • ✔ Use JSONB fields for flexible structured data
  • ✔ Create indexes for faster queries

Conclusion 🎉

>>“PostgreSQL + Python gives you the power, flexibility, and reliability required for modern, scalable applications.” ✨

You now fully understand PostgreSQL Database Connectivity in Python! Want the next topic? Try SQLAlchemy ORM, Async PostgreSQL with asyncpg, or Database Migrations with Alembic. Just tell me! 😊