CODEWITHSUNDEEP
pythonpostgresqlsqlalchemytemp-tables
Wayne Werner
Wayne Werner

Reputation: 51817

Why do multiple connections from the same sqlalchemy engine produce different info?

I'm using PostgreSQL 9.3, and SQLAlchemy 1.0.11

I have code that looks like this:

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'))

Now this works just fine - cursor.rowcount reports the expected number of rows inserted. I can even run cursor.execute('SELECT count(*) FROM my_temp_table'); print(cursor.fetchone()) and it will display the same #. The problem is when I try to run a query from SQLAlchemy's connection, e.g

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

It doesn't matter where I put that. I've tried several places:

Nothing seems to work - no matter where I run the query from, it barfs with:

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

The funny thing is that if I wrap that code in a try/except then I can do cursor.execute(...) in the except block successfully.

Actually, now that I'm writing this out, it appears that using the sqlalchemy connection anywhere fails to see that those tables exists.

So what gives? Why doesn't my SQLAlchemy connection see these tables, but the postgres (engine.raw_connection()) does?

Edit:

To further the mystery - if I create the the connection after the metadata.create_all(engine), it works! Well, sort of.

I can select from the tables, but then when I get the engine.raw_connection() it fails on .copy_expert because it can't find the table.

Upvotes: 3

Views: 6402

Answers (2)

Nick Barnes
Nick Barnes

Reputation: 21346

The first thing to note is that temporary tables are only visible to the connection which created them.

The second is that an Engine doesn't encapsulate a single connection; it manages a connection pool.

Finally, the documentation points out that operations performed directly on an Engine (engine.execute("select ...") in their example) will internally acquire and release their own connections.

With all of this in mind, it's clear what's going on in your example:

  • conn = engine.connect() acquires Connection #1 from the pool.
  • metadata.create_all(engine) implicitly acquires Connection #2 (as #1 is still "in use" from the engine's perspective), uses it to create the table, and releases it back to the pool.
  • pg_conn = engine.raw_connection() acquires #2 again, so the COPY executed via this object can still see the table.
  • conn is still using #1, and nothing you do via this object will be able to see your temp table.

In your second case:

  • metadata.create_all(engine) implicitly acquires/uses/releases Connection #1.
  • conn = engine.connect() acquires #1 and holds it.
  • pg_conn = engine.raw_connection() acquires #2, and the COPY fails to find the temp table.

The moral of the story: if you're doing something which relies on the connection state, you'd better be sure which connection you're using. Running commands directly on the engine is fine for standalone operations, but for anything involving temp tables, you should acquire one connection and stick with it through every step (including the table creation, which I suggest you change to metadata.create_all(conn)).

Upvotes: 4

Wayne Werner
Wayne Werner

Reputation: 51817

Well, this doesn't answer the why but it it is how to accomplish what I want.

Rather than:

pg_conn = engine.raw_connection()

with pg_conn.cursor() as cursor:

Just replace it with:

with conn.connection.cursor() as cursor:

The SQLAlchemy connection object exposes its underlying DBAPI connection via the .connection property. And whatever magic involved there does the right thing.

Upvotes: 0

Related Questions