UTSE PostgreSQL Server

# in your terminal or conda environment
pip install psycopg2-binary

# add to ~/.bashrc, ~/.zshrc, or a .env file
export PG_HOST=vcloud229.it.ox.ac.uk
export PG_PORT=5432
export PG_DB=your_database
export PG_USER=your_username
export PG_PASS=your_password

import psycopg2, os

conn = psycopg2.connect(
    host=os.environ["PG_HOST"],
    port=os.environ["PG_PORT"],
    dbname=os.environ["PG_DB"],
    user=os.environ["PG_USER"],
    password=os.environ["PG_PASS"],
    sslmode="require"  # always encrypt in transit
)

with conn.cursor() as cur:
    cur.execute("SELECT * FROM my_table LIMIT 5;")
    for row in cur.fetchall():
        print(row)

conn.close()

import pandas as pd, os
from sqlalchemy import create_engine

# pip install sqlalchemy psycopg2-binary
url = (
    f"postgresql+psycopg2://{os.environ['PG_USER']}:{os.environ['PG_PASS']}"
    f"@{os.environ['PG_HOST']}:{os.environ['PG_PORT']}/{os.environ['PG_DB']}"
    "?sslmode=require"
)
engine = create_engine(url)

df = pd.read_sql("SELECT * FROM my_table LIMIT 100", engine)