7

I have a bit of a problem with my current flask + SQLAlchemy app. I create a database/engine instance that connects to database 'master' which always exists in SQL server and after this, I create my test db and change the database of the existing engine from 'master' to this new database. My problem is that I don't seem to be doing the 'repointing' of the databases for this engine correctly.

def create_db(self):
    url = copy(self.db.engine.url)  # current url points to database 'master'
    engine = create_engine(url, connect_args={'autocommit': True}, isolation_level='AUTOCOMMIT')
    res = engine.execute(f"exec dbo.create_my_test_temp_db")
    tempdbname = res.fetchone()[0]  # this is the new DB the stored proc created for us
    res.close()
    engine.dispose()
    self.db.engine.url.database = tempdbname  # repoint to the new DB (doesn't work)
    return tempdbname

I have to use the stored procedure dbo.create_my_test_temp_db which is the server-side because it does some magic when creating the database. I change the database in the URL and the connection string for the engine does show the new database but all operations are executed against master.

How can I change the database for an existing engine to another database?

A.Najafi
  • 691
  • 1
  • 9
  • 20
s5s
  • 11,159
  • 21
  • 74
  • 121
  • Switch the engine, not the URL of the engine. Or if SQL Server allows, use some T-SQL command to switch the database of an open connection. – Ilja Everilä Oct 31 '19 at 12:13
  • I thought about this - just wasn't sure if I can just throw the engine out completely and swap it with a new one. There wouldn't be any state left or coupling with any other flask-sqlalchemy objects (e.g. some sort of a connection pool left which keeps the old engine active)? – s5s Oct 31 '19 at 12:14
  • That is a possibility, and come to think of it I'm not a 100% sure if **Flask**-SQLA has good support for switching the "default" bind. – Ilja Everilä Oct 31 '19 at 12:16
  • @IljaEverilä If we both aren't sure it's probably not a good idea - I've flipped my code around to create the test DB before any SQLAlchemy or flask is initialised but I thought it's an interesting answer - I can see use cases where swapping the engine or changing it's database is useful – s5s Oct 31 '19 at 12:17
  • It'd depend on your other code. The engine has very little state in addition to the connection pool. If you dispose of the engine, any checked out connections will be closed as soon as what ever is using them closes them. – Ilja Everilä Oct 31 '19 at 12:20
  • I'm not sure - see this https://stackoverflow.com/questions/58559883/sqlalchemy-engine-execute-leaves-a-connection-to-the-database-in-sleeping-stat – s5s Oct 31 '19 at 12:21
  • In that example though `res.close()` *should* also release the connection, since you're using "connectionless execution", I'd double check that it's really returned to the pool. If not, the following dispose call simply cannot close it, as it can only operate on checked in connections. It'd be interesting if you'd add `del res` either before or after `engine.dispose()`. – Ilja Everilä Oct 31 '19 at 12:27

0 Answers0