title: How to setup a SQLAlchemy database for testing and development?
body: <p>I had the following file structure in my project:</p>
  
  <pre><code>model/
    models.py
  controller/
    controller.py
  persistence/
    persistence.py
  test/
    test_controller.py
  </code></pre>
  
  <p>In persistence.py I have a piece of code: </p>
  
  <pre><code>from sqlalchemy import *
  from sqlalchemy.ext.declarative.api import declarative_base
  from sqlalchemy.orm.session import sessionmaker
  
  engine = create_engine("sqlite:///database.db", echo=True)
  BASE = declarative_base(engine)
  
  def get_session():
       engine = create_engine("sqlite:///database.db", echo=False)
       Session = sessionmaker(bind=engine)
       session = Session()
       return session
  </code></pre>
  
  <p>Base and and engine are both used in models.py to create the database using the declarative approach.</p>
  
  <p>In test_controller I import both models and persistence. When I run the tests a database is created under tests directory. The operations like update, insert, delete are performed well, but before finising the unit test SQLAlchemy raise the following exception:</p>
  
  <pre><code>DetachedInstanceError: Instance &lt;Account at 0x945378c&gt; is not bound to a Session;   
  attribute refresh operation cannot proceed
  </code></pre>
  
  <p>All the methods that access de database closes the session, always. So, it seems that I have more than session opened. </p>
  
  <p>Do you know how can I setup a sqlalchemy database for testing and other for developing just like Rails does using the same model module? Any idea?</p>
  
  <p>here the test code:</p>
  
  <pre><code>from model import Account
  
  engine = create_engine("sqlite://database-test.db", echo=True)
  BASE = declarative_base(engine)
  BASE.metadata.create_all(engine)
  Session = sessionmaker(bind=engine)
  session = Session()
  
  def delete_account(account):
    if account:
      session.delete(account)
      session.commit()
      session.flush()
      session.close()
  
  class TestBankController(unittest.TestCase):
  
  
    def test_insert_bank_twice(self):
      controller = Account()
      bank = Account('TestAcct')
      controller.create_account(bank)
      try:
          print "tentando inserir repetido"
          bank2 = BankAccount("TestAcct")
          controller.create_account(bank2)
          assert False == True
      except ApplicationException:
          print "ok"
  
  
  if __name__ == '__main__':
     unittest.main()
  </code></pre>
  
  <p>and, in the model.py I have the following:</p>
  
  <pre><code> engine = create_engine("sqlite:///database.db", echo=True)
   BASE = declarative_base(engine)
  
   class Account(BASE):
     __tablename__ = "accounts_tb"
     id = Column(Integer, Sequence('account_iq_seq'), primary_key=True)
     name = Column(String(250))
     agency = Column(String(40))
     number = Column(String(40))
     initial_balance = Column(Integer)
  
  BASE.metadata.create_all(engine)
  </code></pre>
  
  <p>Thanks in advance.</p>