title: how to reconnect with MS SQL after hibernation (S4) (or dropped connection)?
body: <p>I'm running some hibernation tests using python + microsoft's <a href="http://msdn.microsoft.com/en-us/library/windows/hardware/ff550682%28v=vs.85%29.aspx" rel="noreferrer">pwrtest utility</a></p>
  
  <p>Also I'm using <strong>sqlalchemy</strong> (orm) to work with database (ms sql server 2008 r2).</p>
  
  <p>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). </p>
  
  <p>When my pc gets back for hibernation and continues with the script I get the error "<strong>DBAPIError: (Error) ('08S01', '[08S01] [Microsoft][ODBC SQL Server Driver]Communication link failure (0) (SQLExecDirectW)')</strong>"</p>
  
  <p>I've tried to use the <code>pool_recycle</code></p>
  
  <pre><code>engine = create_engine(settings.sql_engine, echo=True, pool_recycle=1)
  </code></pre>
  
  <p>However, as far as I understand <code>sqlalchemy</code> does not realize that the connection does not exist anymore.</p>
  
  <p>I've also tried to use <code>engine.dispose()</code> and according to the <a href="http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.Engine.dispose" rel="noreferrer">documentation</a> it should drop the current pool: </p>
  
  <blockquote>
    <p>Dispose of the connection pool used by this Engine.</p>
    
    <p>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.</p>
  </blockquote>
  
  <p>But that also didn't work</p>
  
  <p>How to reconnect to the Database?</p>
  
  <p>Thanks!</p>
  
  <p>The code:</p>
  
  <pre><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)
  </code></pre>
  
  <p>actual call:</p>
  
  <pre><code>#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()
  </code></pre>