Connect Database from Google Colab(Source Code)

1!pip install psycopg

1from google.colab import userdata
2import psycopg
3from psycopg.conninfo import conninfo_to_dict
4# --------------------------
5# Securely fetch database connection URL
6# --------------------------
7# This retrieves the DATABASE_URL stored securely in Colab via `userdata.set("DATABASE_URL", "...")`
8DATABASE_URL = userdata.get("DATABASE_URL")
9
10# Establish a secure connection to a PostgreSQL database using psycopg3.
11# Input:
12#     url (str): A PostgreSQL connection string in URI format.
13#         Example: "postgresql://username:password@host:port/dbname"
14# Output:
15#     psycopg.Connection object if successful, otherwise None.
16# Key Notes:
17# - Automatically enforces SSL mode to ensure encrypted connection.
18# - Useful for connecting to remote DBs like Neon, Supabase, etc.
19def connect_to_db(url: str):
20    try:
21        # Convert connection URI to dictionary format expected by psycopg
22        conn_params = conninfo_to_dict(url)
23
24        # Force SSL encryption regardless of original URL
25        conn_params["sslmode"] = "require"
26
27        # Establish connection with parsed parameters
28        conn = psycopg.connect(**conn_params)
29        print("✅ Connected using psycopg3.")
30        return conn
31    except Exception as e:
32        print("❌ Connection failed:", e)
33        return None

1#  We will use pandas later to organize and analyze our results.
2import pandas as pd  
3
4#  Step 1: Reuse our trusty connection function.
5conn = connect_to_db(DATABASE_URL)  
6
7try:
8    if conn:
9       # Use a cursor to interact with the DB.
10        with conn.cursor() as cur: 
11           #  Run a simple query — grab 5 rows from the 'seek_jobs' table.
12            cur.execute("SELECT * FROM seek_jobs LIMIT 5;") 
13            #  Fetch all those rows and store them in a list.
14            rows = cur.fetchall() 
15             #  Extract column names from the query result metadata. 
16            colnames = [desc.name for desc in cur.description] 
17            # Preview the data
18            print(rows, colnames)  
19except Exception as e:
20    print(f"Error: {e}")  # Catch and display any errors if things go south.
21    conn.rollback()  #  Undo any changes just in case.