2

I have the code below which fails with the message

from sqlalchemy_utils.functions import database_exists, create_database, drop_database

url = f'mssql+pymssql://user:secret_password@db_host/my_database?charset=utf8'
if database_exists(url):
    drop_database(url)
create_database(url)

Error:

sqlalchemy.exc.OperationalError: (pymssql.OperationalError) (574, b'DROP DATABASE statement cannot be used inside a user transaction.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n') E [SQL: DROP DATABASE my_database] E

(Background on this error at: http://sqlalche.me/e/e3q8)

Not sure what I'm doing wrong - examples online show this as valid code. There is no state - I've executed the above in a fresh ipython session.

s5s
  • 11,159
  • 21
  • 74
  • 121

1 Answers1

0

I couldn't get this to work with sqlalchemy-utils, but I could get it to work with just sqlalchemy and executing sql.

The key seems to be telling the engine to use autocommit.

For example, the following will generate the same error that you are experiencing:

db_master = sqlalchemy.create_engine('mssql+pyodbc://server/master?driver=SQL+Server+Native+Client+11.0')

db_master.execute('DROP DATABASE IF EXISTS ' + destination_db)
db_master.execute('CREATE DATABASE ' + destination_db)

of:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]DROP DATABASE statement cannot be used inside a user transaction. (574) (SQLExecDirectW)')

However, adding the autocommit option to the engine creation as per below removes the error and DROPs and CREATEs the database 'destination_db' as expected:

db2_master = sqlalchemy.create_engine('mssql+pyodbc://server/master?driver=SQL+Server+Native+Client+11.0', connect_args = {'autocommit':True})

db2_master.execute('DROP DATABASE IF EXISTS ' + destination_db)
db2_master.execute('CREATE DATABASE ' + destination_db)
t_warsop
  • 1,170
  • 2
  • 24
  • 38
  • Interesting, thanks. I believe I created the engine like so and still got the error but I had so many permutations that perhaps I did something different: engine = create_engine(self.config.SQLALCHEMY_DATABASE_URI, connect_args={'autocommit': True}, isolation_level='AUTOCOMMIT') – s5s Jan 13 '20 at 16:17
  • @t_warsop, why did that work for you? If I add `engine.execute('COMMIT')` after each statement, it fails, but `autocommit: True` works. – Shuzheng Sep 29 '20 at 09:04