how to reconnect with MS SQL after hibernation (S4) (or dropped connection)?

Question

I'm running some hibernation tests using python + microsoft's pwrtest utility

Also I'm using sqlalchemy (orm) to work with database (ms sql server 2008 r2).

I'm connected to the remote sql server and everything works fines, however after computer goes into the hibernation mode (S4) sql server drops the connection (I see it as the "Activity monitor" at management studio).

When my pc gets back for hibernation and continues with the script I get the error "DBAPIError: (Error) ('08S01', '[08S01] [Microsoft][ODBC SQL Server Driver]Communication link failure (0) (SQLExecDirectW)')"

I've tried to use the pool_recycle

engine = create_engine(settings.sql_engine, echo=True, pool_recycle=1)

However, as far as I understand sqlalchemy does not realize that the connection does not exist anymore.

I've also tried to use engine.dispose() and according to the documentation it should drop the current pool:

Dispose of the connection pool used by this Engine.

A new connection pool is created immediately after the old one has been disposed. This new pool, like all SQLAlchemy connection pools, does not make any actual connections to the database until one is first requested.

But that also didn't work

How to reconnect to the Database?

Thanks!

The code:

#module db.py:
from sqlalchemy.ext.declarative import declarative_base , declared_attr
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.orm.exc import *

Base = declarative_base()

class Drive(Base):
    __tablename__ = "drives"

    id = Column(Integer, primary_key=True)
    isPhysical = Column(Boolean)
    devicePath = Column(String(100))
    name = Column(String(10))
    encrypted = Column(Boolean, default=False)
    size = Column(BigInteger)

    def __init__(self):
        pass        

sql_engine = 'mssql+pyodbc://Tester:Password@sql-server/Automation'
engine = create_engine(sql_engine, echo=True)
Session = sessionmaker(bind=engine)
Base.metadata.create_all(engine)

actual call:

#hibernation.py

from db import *
import subprocess

command = r"pwrtest /sleep /s:4 /h:n /c:1"

out = subprocess.check_output(command)
# hibernation occurs

session = Session()

session.query(Drive).all()

Answer 1

You can try this "Pessimistic Disconnect Handling":

http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html#disconnect-handling-pessimistic