0

I am using an old version of sqlalchemy (0.8) and I need to execute "REINDEX DATABASE <dbname>" on PostgreSQLql 9.4 by using sqlalchemy api.

Initially I tried with:

conn = pg_db.connect()  
conn.execute('REINDEX DATABASE sg2')
conn.close()

but I got error "REINDEX DATABASE cannot run inside a transaction block".

I read in internet and tried other changes:

engine.execute(text("REINDEX DATABASE sg2").execution_options(autocommit=True))

(I tried also with autocommit=False).

and

conn = engine.raw_connection()  
cursor = conn.cursor()
cursor.execute('REINDEX DATABASE sg2')
cursor.close()

I always have the same error.

I tried also following:

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

but I got error Invalid value 'AUTOCOMMIT' for isolation_level. Valid isolation levels for postgresql are REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED, SERIALIZABLE

What am I missing here ? Thanks for any help.

Ernest Poldrige
  • 399
  • 1
  • 6
  • 17
  • autocommit=on did you try this? in your psql like command SET AUTOCOMMIT = ON – Avi Aug 17 '18 at 07:21
  • Possible duplicate of [Sql Alchemy cannot run inside a transaction block](https://stackoverflow.com/questions/44959599/sql-alchemy-cannot-run-inside-a-transaction-block) – Łukasz Kamiński Aug 17 '18 at 07:35
  • @ŁukaszKamiński I tried suggestion provided in that link but using that I got error "Invalid value 'AUTOCOMMIT' for isolation_level. Valid isolation levels for postgresql are REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED, SERIALIZABLE" – Ernest Poldrige Aug 17 '18 at 07:42
  • @Avi I tried using cursor.execute('SET AUTOCOMMIT = ON; REINDEX DATABASE sg2') but I got the same error. – Ernest Poldrige Aug 17 '18 at 07:45
  • Maybe try from here: https://stackoverflow.com/questions/5402805/error-when-creating-a-postgresql-database-using-python-sqlalchemy-and-psycopg2/5654427 It does seem to be doing the same thing, just using numerical values instead, so might not work with your SQL Alchemy version. – Łukasz Kamiński Aug 17 '18 at 07:45
  • No `SET AUTOCOMMIT = ON` this should be run in psql cmd.... not inside code – Avi Aug 17 '18 at 07:47
  • 1
    @avi There has been no `autocommit` parameter in PostgreSQL since 2003. – Laurenz Albe Aug 17 '18 at 07:55
  • @ŁukaszKamiński its there... read this https://dzone.com/articles/autocommit-in-postgresqls-psql – Avi Aug 17 '18 at 07:57
  • @ŁukaszKamiński by using numerical values it works thanks. – Ernest Poldrige Aug 17 '18 at 08:31

0 Answers0