title: How to correctly structure SQLAlchemy (declarative style) python project and it`s unittests
body: <p>I am developing large backend for some web apps. This is my first python and SQLAlchemy project, so I am confused with some things. And was kind of spoiled by Java programming tools and IDE`s, compared to python`s (I use pydev in eclipse anyway). I need help with how to structure the project and write tests. I`ll describe situation first.</p>
  
  <p>In PyDev i named my project for example "ProjectName", and below I have shown my current folder/package and files structure. </p>
  
  <ul>
  <li>ProjectName
  <ul>
  <li>projectname
  <ul>
  <li>__init__.py</li>
  <li>some_package
  <ul>
  <li>__init__.py</li>
  <li>Foo.py</li>
  <li>Bar.py</li>
  </ul></li>
  </ul></li>
  <li>tests
  <ul>
  <li>unit_tests
  <ul>
  <li>__init__.py</li>
  <li>some_package
  <ul>
  <li>__init__.py</li>
  <li>TestFoo.py</li>
  <li>TestBar.py</li>
  </ul></li>
  </ul></li>
  <li>load_tests</li>
  <li>integration_tests</li>
  <li>__init__.py</li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>I use declarative style in SQLAlchemy. Foo and Bar are some classes, such that Foo extends SQLAlchemy declarative Base and Bar extends Foo. Under 'projectname.some_package' in it`s __init__.py I have this code :</p>
  
  <pre><code>engine = create_engine('mysql+mysqldb://user:pass@localhost:3306/SomeDataBase', pool_recycle=3600)
  Session = sessionmaker(bind=engine)
  Base = declarative_base()
  </code></pre>
  
  <p>So, Foo imports this Base and extends it, and Bar imports Foo and extends it. My first question is, should I store Base in that __init__.py and use it like I started with this 2 classes? This create_engine is just temporary there, I would like to have config file and load it`s settings from there, how to do that? Where should I call Base.metadata.create_all(), so it can create all database tables at once?</p>
  
  <p>Next, in testing classes, for example in TestFoo I have this code :</p>
  
  <pre><code>def setUp(self):
      #create database tables and session object
      self.engine = create_engine('mysql+mysqldb://user:pass@localhost:3306/SomeDatabase', pool_recycle=3600)
      Session = sessionmaker(bind=self.engine)
      Foo.metadata.create_all(bind=self.engine)
      self.session = Session()
  
  def tearDown(self):
      #drop all tables and close session object
      self.session.close()
      meta = MetaData(self.engine)
      meta.reflect()
      meta.drop_all()
  </code></pre>
  
  <p>End then I have some test methods in that test class and it runs fine. In TestBar class difference is that </p>
  
  <pre><code>Foo.metadata.create_all(bind=self.engine)
  </code></pre>
  
  <p>is : </p>
  
  <pre><code>Bar.metadata.create_all(bind=self.engine)
  </code></pre>
  
  <p>When I run TestBar, it also runs fine. But, when I select both test classes and run them, I get errors : </p>
  
  <pre><code>/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/declarative.py:1336: SAWarning: The classname 'Foo' is already in the registry of this declarative base, mapped to &lt;class 'projectname.some_package.Foo.Foo'&gt;
    _as_declarative(cls, classname, cls.__dict__)
  /usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py:330: Warning: Field 'id' doesn't have a default value
   cursor.execute(statement, parameters)
  </code></pre>
  
  <p>What is the problem here? I tried to run tests with nose and pydev runners and get same errors. Then I tried to move database tables creation in __init__.py in some_package under unit_tests but I could not get it working. Also, I`m confused about how python import works. For example if I add Foo import in TestBar class I also get errors similar to that that I have shown already. How can I run many unit tests that test SQLAlchemy classes, all at once?</p>
  
  <p>So to extract most important questions again : </p>
  
  <ol>
  <li>How to structure python project that uses SQLAlchemy declarative style and unittests correctly. BTW I have many class methods in Foo and Bar that interact with database, in context of their respective classes, I hope that is OK? </li>
  <li>Where to store Base declarative class and how to properly use it in whole project, and how to extract all database schema (that I defined declaratively in my classes) anywhere in project and use it?</li>
  <li>How to best use unit tests with SQLAlchemy and run multiple unittests at once?</li>
  <li>If u have any other suggestions feel free to add it?</li>
  </ol>
  
  <p>Thank you very much for the help.</p>