如何使用psycopg2/psycopg3连接Supabase非public模式并查询

首先必须确保你的Supabase角色拥有目标模式的访问权限，先在Supabase SQL编辑器执行以下授权语句（替换your_schema为你的目标模式名，authenticated可根据实际角色替换，比如服务角色用service_role）：

-- 授权使用目标模式
GRANT USAGE ON SCHEMA your_schema TO authenticated;
-- 授权查询模式下所有表
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO authenticated;
-- 如需写入权限，添加对应语句
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA your_schema TO authenticated;
-- 让后续新建表自动继承权限
ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema GRANT SELECT ON TABLES TO authenticated;

用psycopg2实现的三种方法 #

方法1：连接时指定search_path #

直接在连接参数中设置搜索路径，让PostgreSQL优先查找目标模式：

import psycopg2

# 替换为你的Supabase连接字符串
conn = psycopg2.connect(
    "postgresql://your_user:your_password@db.supabase.co:5432/postgres",
    options="-c search_path=your_schema,public"
)

cur = conn.cursor()
cur.execute("SELECT * FROM your_table;")  # 无需手动指定模式前缀
rows = cur.fetchall()

# 记得关闭连接
cur.close()
conn.close()

方法2：连接后设置search_path #

连接成功后执行SQL语句修改搜索路径：

import psycopg2

conn = psycopg2.connect("postgresql://your_user:your_password@db.supabase.co:5432/postgres")
cur = conn.cursor()

# 设置搜索路径，包含目标模式和public
cur.execute("SET search_path TO your_schema, public;")
conn.commit()

cur.execute("SELECT * FROM your_table;")
rows = cur.fetchall()

cur.close()
conn.close()

方法3：查询时显式指定模式 #

在SQL语句中直接写模式名.表名的形式：

import psycopg2

conn = psycopg2.connect("postgresql://your_user:your_password@db.supabase.co:5432/postgres")
cur = conn.cursor()

# 直接指定模式和表
cur.execute("SELECT * FROM your_schema.your_table;")
rows = cur.fetchall()

cur.close()
conn.close()

用psycopg3实现的三种方法 #

psycopg3的语法更简洁，支持上下文管理器，以下是对应实现：

方法1：连接时指定search_path #

import psycopg

# 替换为你的Supabase连接字符串
with psycopg.connect(
    "postgresql://your_user:your_password@db.supabase.co:5432/postgres",
    options="-c search_path=your_schema,public"
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM your_table;")
        rows = cur.fetchall()

方法2：连接后设置search_path #

import psycopg

with psycopg.connect("postgresql://your_user:your_password@db.supabase.co:5432/postgres") as conn:
    with conn.cursor() as cur:
        cur.execute("SET search_path TO your_schema, public;")
        conn.commit()
        
        cur.execute("SELECT * FROM your_table;")
        rows = cur.fetchall()

方法3：查询时显式指定模式 #

import psycopg

with psycopg.connect("postgresql://your_user:your_password@db.supabase.co:5432/postgres") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM your_schema.your_table;")
        rows = cur.fetchall()

内容的提问来源于stack exchange，提问作者dejoma