python - 为什么来自同一个 sqlalchemy 引擎的多个连接会产生不同的信息？

Question

我正在使用 PostgreSQL 9.3 和 SQLAlchemy 1.0.11

我有这样的代码:

import sqlalchemy as sa

engine = sa.create_engine('postgresql+psycopg2://me@myhost/mydb')
conn = engine.connect()

metadata = sa.MetaData()

# Real table has more columns
mytable = sa.Table(
    'my_temp_table', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('something', sa.String(200)),
    prefixes=['TEMPORARY'],
)

metadata.create_all(engine)

pg_conn = engine.raw_connection()
with pg_conn.cursor() as cursor:
    cursor.copy_expert('''COPY my_temp_table (id, something)
                          FROM STDIN WITH CSV''',
                       open('somecsvfile', 'r'))

现在一切正常 - cursor.rowcount 报告预期插入的行数。我什至可以运行 cursor.execute('SELECT count(*) FROM my_temp_table'); print(cursor.fetchone()) 并且它会显示相同的#。问题是当我尝试从 SQLAlchemy 的连接运行查询时，例如

    result = conn.execute(sa.text('SELECT count(*) FROM my_temp_table'))

我把它放在哪里并不重要。我试过几个地方:

• 在 with block 内
• 在 with block 之外
• 在 cursor.close() 之后
• 在 pg_conn.close() 之后

似乎没有任何效果 - 无论我从哪里运行查询，它都会吐出:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "my_temp_table" does not exist

有趣的是，如果我将该代码包装在 try/except 中，那么我可以在 except block 中成功执行 cursor.execute(...) 。

实际上，既然我正在写这篇文章，似乎使用 sqlalchemy 连接任何地方都无法看到这些表的存在。

那么是什么给了？为什么我的 SQLAlchemy 连接看不到这些表，但 postgres (engine.raw_connection()) 可以？

编辑:

更神秘的是 - 如果我在 metadata.create_all(engine) 之后 创建连接，它就可以工作!好吧，有点。

我可以从表中选择，但是当我得到 engine.raw_connection() 时，它在 .copy_expert 上失败，因为它找不到表。

Answer 1

首先要注意的是，临时表仅对创建它们的连接可见。

第二个是 Engine 不封装单个连接；它管理一个 connection pool .

最后，documentation指出直接在 Engine 上执行的操作(在他们的示例中为 engine.execute("select ..."))将在内部获取和释放它们自己的连接。

考虑到所有这些，您的示例中发生的事情就很清楚了:

• conn = engine.connect() 从池中获取连接 #1。
• metadata.create_all(engine) 隐式获取连接 #2(因为从引擎的角度来看，#1 仍在“使用中”)，使用它来创建表，并将其释放回游泳池。
• pg_conn = engine.raw_connection() 再次获取#2，因此通过此对象执行的COPY 仍然可以看到该表。
• conn 仍在使用 #1，您通过此对象执行的任何操作都无法看到您的临时表。

在你的第二种情况下:

• metadata.create_all(engine) 隐式获取/使用/释放连接 #1。
• conn = engine.connect() 获取 #1 并保持它。
• pg_conn = engine.raw_connection() 获取#2，COPY 找不到临时表。

故事的寓意:如果您正在做依赖于连接状态的事情，您最好确定您使用的是哪个连接。直接在 engine 上运行命令对于独立操作来说很好，但是对于涉及临时表的任何事情，您应该获得一个连接并坚持执行每一步(包括表创建，我建议您将其更改为metadata.create_all(conn)).