如何在 psycopg2 连接方法中指定 Schema?

问题描述

使用 psycopg2 模块通过 python 连接到 PostgreSQL 数据库.我可以使用以下连接方法执行所有查询.现在我想指定一个不同的模式而不是 public 来执行我的 SQL 语句.有没有办法在连接方法中指定schema名称?

Using the psycopg2 module to connect to the PostgreSQL database using python. I'm able to execute all of my queries using the below connection method. Now I want to specify a different schema other than the public to execute my SQL statements. Is there any way to specify the schema name in the connection method?

conn = psycopg2.connect(host="localhost",
                            port="5432",
                            user="postgres",
                            password="password",
                            database="database",
                            )

我尝试直接在方法内部指定架构.schema="schema2"但我收到以下编程错误.

I tried to specify schema directly inside the method.schema="schema2"But I am getting the following programming error.

ProgrammingError: invalid dsn: invalid connection option "schema"

推荐答案

当我们在 psycopg2 中的 ThreadConnectionPool 上工作并创建连接池时，我们是这样的做到了.

When we were working on ThreadConnectionPool which is in psycopg2 and creating connection pool, this is how we did it.

from psycopg2.pool import ThreadedConnectionPool

db_conn = ThreadedConnectionPool(
    minconn=1, maxconn=5,
    user="postgres", password="password", database="dbname", host="localhost", port=5432,
    options="-c search_path=dbo,public"
)

您会看到 options 在 params 中的关键.我们就是这样做的.

You see that options key there in params. That's how we did it.

当您使用来自该连接的游标执行查询时，它将从左到右依次搜索options 中提到的那些模式，即dbo,public.

When you execute a query using the cursor from that connection, it will search across those schemas mentioned in options i.e., dbo,public in sequence from left to right.

你可以尝试这样的事情:

You may try something like this:

psycopg2.connect(host="localhost",
                 port="5432",
                 user="postgres",
                 password="password",
                 database="database",
                 options="-c search_path=dbo,public")

希望对您有所帮助.

这篇关于如何在 psycopg2 连接方法中指定 Schema?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！