Connecting to Apex DB via Python

Using duckdb

import duckdb

conn = duckdb.connect()
conn_str = dedent("""
    INSTALL postgres;
    LOAD postgres;
    ATTACH '
        dbname=apex
        user=apex
        password=password
        host=apex.tailcdd45f.ts.net
        port=5432
        ' AS apex (TYPE postgres);
    """)
conn.execute(conn_str)

df = duckdb_conn.execute("SELECT * FROM apex.TABLE;").df()

Using sqlmodel

import pandas as pd
from sqlalchemy import URL
from sqlmodel import Field, Session, SQLModel, create_engine

url = URL.create(
    drivername="postgresql+psycopg2",
    username="apex",
    password="password",  # noqa: S106
    host="apex.tailcdd45f.ts.net",
    database="apex",
    port=5432,
)
engine = create_engine(url)

class TABLE(SQLModel, table=True):
    id: int = Field(primary_key=True)
    field1: str

with Session(engine) as session:
    query = session.query(TABLE)
    results = query.all()
    df = pd.DataFrame([result.dict() for result in results])

Using psycopg2

import pandas as pd
import psycopg2

conn = psycopg2.connect(
    dbname="apex",
    user="apex",
    password="password",
    host="apex.tailcdd45f.ts.net",
    port=5432,
)
cur = conn.cursor()
cur.execute("SELECT * FROM TABLE;")

df1 = pd.DataFrame(cur.fetchall(), columns=[desc[0] for desc in cur.description])
df2 = psql.read_sql("SELECT * FROM TABLE", conn)