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 1

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.

Answer 2

Following by zzzeek's comment, a file "__init__.py" of the schema/model package can be used, instead of regular Python file. It works on SQLAlchemy 1.4.32.