'How to correctly structure SQLAlchemy (declarative style) python project and it`s unittests

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.



Solution 1:[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.

Solution 2:[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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Wichert Akkerman
Solution 2 Nikolai Varankine