Sql Alchemy cannot run inside a transaction block

Question

I'm trying to run a query in redshift from a python script, but I'm getting error:

sqlalchemy.exc.InternalError: (psycopg2.InternalError) ALTER EXTERNAL TABLE cannot run inside a transaction block

This is my code:

engine = create_engine(SQL_ENGINE % urlquote(REDSHIFT_PASS))
partition_date = (date.today() - timedelta(day)).strftime("%Y%m%d")
query = """alter table  {table_name} add partition (dt={date_partition}) location 's3://dft-dwh-files/raw_data/google_analytics/revenue_per_channel/{date_partition}/';""".format(date_partition=partition_date,table_name=table_name)
conn = engine.connect()
conn.execute(query).execution_options(autocommit=True)

How can I fix this?

score 31 · Accepted Answer · edited Apr 19 '18 at 19:08

31

For PostgreSQL, you need to set the isolation level to AUTOCOMMIT, not the SQLAlchemy autocommit:

conn.execution_options(isolation_level="AUTOCOMMIT").execute(query)

edited Apr 19 '18 at 19:08

Leo Bouloc

302
1
3
16

answered Jul 07 '17 at 17:50

univerio

19,548
3
66
68

1

If you are using sqlalchemy :session instead of :conn, see this answer below https://stackoverflow.com/a/53327098/248616 – Nam G VU Nov 15 '18 at 20:01
This has been renamed to `conn.set_isolation_level("AUTOCOMMIT")` - See http://initd.org/psycopg/docs/connection.html#connection.set_isolation_level – gies0r Sep 17 '19 at 22:02
@gies0r That's if you're using psycopg directly. This question is about SQLAlchemy. See https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-transaction-isolation-level – univerio Sep 18 '19 at 03:06
@univerio Oh yeah.. Missed that the specific question was SQLAlchemy. So my answer contains how to do it in `psycopg` (which looks working also) – gies0r Sep 18 '19 at 09:38

score 6 · Answer 2 · answered Nov 15 '18 at 20:00

This solution works for me as using sqlalchemy :session not :conn as @univerio answer

I quote their answer here

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('postgresql+psycopg2://USER:PASSWORD@127.0.0.1:5432/DB_OR_TEMPLATE')
session = sessionmaker(bind=engine)()
session.connection().connection.set_isolation_level(0)
session.execute('CREATE DATABASE test')
session.connection().connection.set_isolation_level(1)

If you don't have any databases, you should use template1

"""Isolation level values."""
ISOLATION_LEVEL_AUTOCOMMIT     = 0
ISOLATION_LEVEL_READ_COMMITTED = 1
ISOLATION_LEVEL_SERIALIZABLE   = 2

looks like `set_isolation_level()` is no longer in use in 2022 — Evgene, Dec 13 '22 at 17:06

tread · Answer 3 · 2023-03-16T14:21:34.157

1

Another option is to run COMMIT manually before removing or adding the index:

from sqlalchemy.orm import Session

with Session(get_engine()) as session:
    session.execute("COMMIT")
    session.execute("DROP INDEX CONCURRENTLY IF EXISTS <my_index>;")

edited Mar 16 '23 at 14:21

answered Mar 16 '23 at 13:59

tread

10,133
17
95
170

Somehow `session.commit()` didn´t work but your answer worked for me. Thanks – CIRCLE May 30 '23 at 15:34

Sql Alchemy cannot run inside a transaction block

3 Answers3

Linked