title: SQLAlchemy and PyTest: how change database during tests?
body: <p>I'm writing a "batch" process in Python (without use of any framework).
  Project configuration is in a <code>config.ini</code> file</p>
  
  <pre class="lang-sh prettyprint-override"><code>[db]
  db_uri = mysql+pymysql://root:password@localhost:3306/manage
  </code></pre>
  
  <p>I have another file <code>config.test</code> to swap during test</p>
  
  <pre class="lang-sh prettyprint-override"><code>[db]
  db_uri = sqlite://
  </code></pre>
  
  <p>I have a simple <code>test_sample.py</code></p>
  
  <pre class="lang-py prettyprint-override"><code># tests/test_sample.py
  
  import pytest
  import shutil
  import os
  
  import batch
  import batch_manage.utils.getconfig as getconfig_class
  
  class TestClass():
      def setup_method(self, method):
          """ Rename the config """
          shutil.copyfile("config.ini", "config.bak")
          os.remove('config.ini')
          shutil.copyfile("config.test", "config.ini")
  
      def teardown_method(self, method):
          """ Replace the config """
          shutil.copyfile("config.bak", "config.ini")
          os.remove('config.bak')
  
  
      def test_can_get_all_data_from_table(self):
          conf = getconfig_class.get_config('db')
          db_uri = conf.get('db_uri')
          assert db_uri == "sqlite://"
          # This pass! ok!
          people = batch.get_all_people()
          assert len(people) == 0
          # This fails, because counts the records in production database
  </code></pre>
  
  <p><code>db_uri</code> assert is ok (at test time is sqlite and not the mysql) but len is not 0, but 42 (the number of records inside MySql database.</p>
  
  <p>I suspect an issue with the session of SqlAlchemy ORM. I made several try without possibility to overwrite / delete it.</p>
  
  <p>The rest of code is really simple:</p>
  
  <pre class="lang-py prettyprint-override"><code># batch_manage/models/base.py
  from sqlalchemy import create_engine
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker
  
  import batch_manage.utils.getconfig as getconfig_class
  
  conf = getconfig_class.get_config('db')
  db_uri = conf.get('db_uri')
  
  engine = create_engine(db_uri)
  Session = sessionmaker(bind=engine)
  
  Base = declarative_base()
  </code></pre>
  
  <pre class="lang-py prettyprint-override"><code># batch_manage/models/persone.py
  from sqlalchemy import Column, String, Integer, Date
  
  from batch_manage.models.base import Base
  
  
  class Persone(Base):
      __tablename__ = "persone"
  
      idpersona = Column(Integer, primary_key=True)
      nome = Column(String)
      created_at = Column(Date)
  
      def __init__(self, nome, created_at):
          self.nome = nome
          self.created_at = created_at
  
  </code></pre>
  
  <p>And the <code>batch.py</code> itself</p>
  
  <pre class="lang-py prettyprint-override"><code># batch.py
  import click
  
  from batch_manage.models.base import Session
  from batch_manage.models.persone import Persone
  
  def get_all_people():
      """ Get all people from database """
      session = Session()
  
      people = session.query(Persone).all()
  
      return people
  
  
  @click.command()
  def batch():
      click.echo("------------------------------")
      click.echo("Running Batch")
      click.echo("------------------------------")
  
      people = get_all_people()
  
      for item in people:
          print(f"Persona con ID {item.idpersona} creata il {item.created_at}")
  
  
  
  if __name__ == '__main__':
      batch()
  
  </code></pre>
  
  <h2>First "solution" (not elegant, I would refactor this)</h2>
  
  <p>I managed for the moment changing test with:</p>
  
  <pre class="lang-py prettyprint-override"><code>def test_can_get_all_data_from_table(self):
          conf = getconfig_class.get_config('db')
          db_uri = conf.get('db_uri')
          assert db_uri == "sqlite://"
  
          from sqlalchemy.orm import sessionmaker
          from sqlalchemy import create_engine
          engine = create_engine(db_uri)
          Session = sessionmaker(bind=engine)
          session = Session()
  
          people = batch.get_all_people(session)
          assert len(people) == 0
  </code></pre>
  
  <p>And <code>get_all_people</code> method with</p>
  
  <pre class="lang-py prettyprint-override"><code>def get_all_people(session = None):
      """ Get all people from database """
      if session is None:
        session = Session()
  
      people = session.query(Persone).all()
  
      return people
  </code></pre>
  
  <p>But this solution is not elegant and decrease also code coverage, for a if path not followed.</p>