postgresql 在 psycopg2 中为连接的所有查询设置架构：设置 search_path 时获取竞争条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32812463/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting schema for all queries of a connection in psycopg2: Getting race condition when setting search_path
提问by André C. Andersen
Our system is running on Ubuntu, python 3.4, postgres 9.4.x and psycopg2.
我们的系统在 Ubuntu、python 3.4、postgres 9.4.x 和 psycopg2 上运行。
We (will in the furture) split between dev, testand prodenvironments using schemas. I've create a convenience method for creating connections to our database. It uses json connection configuration files in order create the connection string. I want to configure the connection to use a particular schema for all following queries using the returned connection. I don't want my queries to have hardcoded schemas, because we should be able to easily switch between them depending on if we are in development, testing or production phase/environment.
我们（将在未来）使用模式在dev、test和prod环境之间进行划分。我已经创建了一个方便的方法来创建到我们的数据库的连接。它使用 json 连接配置文件来创建连接字符串。我想将连接配置为使用返回的连接对所有后续查询使用特定模式。我不希望我的查询具有硬编码模式，因为我们应该能够根据我们是处于开发、测试还是生产阶段/环境轻松地在它们之间切换。
Currently the convenience method looks like the following:
目前，便捷方法如下所示：
def connect(conn_config_file = 'Commons/config/conn_commons.json'):
with open(conn_config_file) as config_file:
conn_config = json.load(config_file)
conn = psycopg2.connect(
"dbname='" + conn_config['dbname'] + "' " +
"user='" + conn_config['user'] + "' " +
"host='" + conn_config['host'] + "' " +
"password='" + conn_config['password'] + "' " +
"port=" + conn_config['port'] + " "
)
cur = conn.cursor()
cur.execute("SET search_path TO " + conn_config['schema'])
return conn
It works fine as long as you give it time to execute the set search_pathquery. Unfortunately, if I'm too fast with executing a following query a race condition happens where the search_pathisn't set. I've tried to force the execution with doing a conn.commit()before the return conn, however, this resets the search_pathto the default schema postgresso that it doesn't use, say, prod. Suggestions at the database or application layer is preferable, however, I know we probably could solve this at the OS level too, any suggestions in that direction are also welcomed.
只要您给它时间执行设置的search_path查询，它就可以正常工作。不幸的是，如果我执行以下查询的速度太快，则会在search_path未设置的情况下发生竞争条件。我试图强制执行与做conn.commit()之前return conn，但是，这将重置search_path为默认模式postgres，以便它不使用，也就是说，prod。最好在数据库或应用程序层提出建议，但是，我知道我们可能也可以在操作系统级别解决这个问题，也欢迎在该方向上提出任何建议。
An example json configuration file looks like the following:
示例 json 配置文件如下所示：
{
"dbname": "thedatabase",
"user": "theuser",
"host": "localhost",
"password": "theusers_secret_password",
"port": "6432",
"schema": "prod"
}
Any suggestion is very appreciated.
任何建议都非常感谢。
回答by butla
I think a more elegant solution would be to set the search_pathin optionsparameter of connect(), like so:
我认为更优雅的解决方案是设置search_pathinoptions参数connect()，如下所示：
def connect(conn_config_file = 'Commons/config/conn_commons.json'):
with open(conn_config_file) as config_file:
conn_config = json.load(config_file)
schema = conn_config['schema']
conn = psycopg2.connect(
dbname=conn_config['dbname'],
user=conn_config['user'],
host=conn_config['host'],
password=conn_config['password'],
port=conn_config['port'],
options=f'-c search_path={schema}',
)
return conn
Of course, you can use "options" as part of the connection string. But using keyword arguments prevents all the hassle with string concatenations.
当然，您可以使用“选项”作为连接字符串的一部分。但是使用关键字参数可以避免字符串连接的所有麻烦。
I found this solution in this psycopg2 feature request. As for the "options" parameter itself, it's mentioned here.
我在这个psycopg2 功能请求中找到了这个解决方案。至于“options”参数本身，这里有提到。
回答by bersen
I think a better idea is to have something like DatabaseCursor returning cursor you use to execute queries with "SET search_path..." instead of connection. Well I mean something like this:
我认为更好的主意是使用诸如 DatabaseCursor 之类的返回游标的东西，您可以使用“SET search_path ...”而不是连接来执行查询。嗯，我的意思是这样的：
class DatabaseCursor(object):
def __init__(self, conn_config_file):
with open(conn_config_file) as config_file:
self.conn_config = json.load(config_file)
def __enter__(self):
self.conn = psycopg2.connect(
"dbname='" + self.conn_config['dbname'] + "' " +
"user='" + self.conn_config['user'] + "' " +
"host='" + self.conn_config['host'] + "' " +
"password='" + self.conn_config['password'] + "' " +
"port=" + self.conn_config['port'] + " "
)
self.cur = self.conn.cursor()
self.cur.execute("SET search_path TO " + self.conn_config['schema'])
return self.cur
def __exit__(self, exc_type, exc_val, exc_tb):
# some logic to commit/rollback
self.conn.close()
and
和
with DatabaseCursor('Commons/config/conn_commons.json') as cur:
cur.execute("...")
