How to correctly structure SQLAlchemy (declarative style) python project and it`s unittests
Question: 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.

In PyDev i named my project for example "ProjectName", and below I have shown
my current folder/package and files structure.

  * ProjectName 
    * projectname 
      * __init__.py
      * some_package 
        * __init__.py
        * Foo.py
        * Bar.py
    * tests 
      * unit_tests 
        * __init__.py
        * some_package 
          * __init__.py
          * TestFoo.py
          * TestBar.py
      * load_tests
      * integration_tests
      * __init__.py

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 :

    
    
    engine = create_engine('mysql+mysqldb://user:pass@localhost:3306/SomeDataBase', pool_recycle=3600)
    Session = sessionmaker(bind=engine)
    Base = declarative_base()
    

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?

Next, in testing classes, for example in TestFoo I have this 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()
    

End then I have some test methods in that test class and it runs fine. In
TestBar class difference is that

    
    
    Foo.metadata.create_all(bind=self.engine)
    

is :

    
    
    Bar.metadata.create_all(bind=self.engine)
    

When I run TestBar, it also runs fine. But, when I select both test classes
and run them, I get errors :

    
    
    /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 <class 'projectname.some_package.Foo.Foo'>
      _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)
    

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?

So to extract most important questions again :

  1. 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? 
  2. 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?
  3. How to best use unit tests with SQLAlchemy and run multiple unittests at once?
  4. If u have any other suggestions feel free to add it?

Thank you very much for the help.


Answer: Quick answer (lack of time, sorry): use a single MetaData instance instead of
having one for both Foo and Bar. In general multiple MetaData instances is an
advanced trick that you almost never need.

