SQLAlchemy - create table template

I am just starting with SQLAlchemy and I have been wondering... I am going to have a lot of tables in my model. I would like to have own file for each table I will have in my model.

I am currently using following code:

from sqlalchemy import MetaData
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects import postgresql
import sqlalchemy as sa

__all__ = ['Session', 'engine', 'metadata']

# SQLAlchemy database engine. Updated by model.init_model()
engine = None

# SQLAlchemy session manager. Updated by model.init_model()
Session = scoped_session(sessionmaker())

# Global metadata. If you have multiple databases with overlapping table
# names, you'll need a metadata for each database
metadata = MetaData()

# declarative table definitions
Base = declarative_base()
Base.metadata = metadata

schema = 'srbam_dev'

in meta.py

Following in _init_.py

"""The application's model objects"""
import sqlalchemy as sa
from sqlalchemy import orm

from models import meta
from models.filers import Filer
from models.vfilers import Vfiler
from models.filer_options import FilerOption

def init_models(engine):
    """Call me before using any of the tables or classes in the model"""
    ## Reflected tables must be defined and mapped here
    #global reflected_table
    #reflected_table = sa.Table("Reflected", 开发者_JS百科meta.metadata, autoload=True,
    #                           autoload_with=engine)
    #orm.mapper(Reflected, reflected_table)
    #
    meta.engine = sa.create_engine(engine)
    meta.Session.configure(bind=meta.engine)

    class Basic_Table(object):
        id = sa.Column(
            postgresql.UUID(),
            nullable=False,
            primary_key=True
            )
        created = sa.Column(
            sa.types.DateTime(True),
            nullable=False
            )
        modified = sa.Column(
            sa.types.DateTime(True),
            nullable=False
            )

And then following in all of my models

from models.meta import Base
from models.meta import Basic_Table
from models.meta import schema
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

class Filer(Base,Basic_Table):

This works just fine, as long as I do not start to use some Foreign Keys on tables, once I use Foreign Key says

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 't_vfilers.filer_id' could not find table 't_filers' with which to generate a foreign key to target column 'id'

I tried to define id key directly in Filer class (and remove Basic_Table from declaration), however this does not solve the issue.

My code for creating the database looks like this #!/usr/bin/python import ConfigParser from sqlalchemy.engine.url import URL from models import *

config = ConfigParser.RawConfigParser()
config.read('conf/core.conf')
db_url = URL(
    'postgresql+psycopg2',
    config.get('database','username'),
    config.get('database','password'),
    config.get('database','host'),
    config.get('database','port'),
    config.get('database','dbname')
    )

init_models(db_url)
meta.Base.metadata.drop_all(bind=meta.engine)
meta.Base.metadata.create_all(bind=meta.engine)

Does anyone have an idea how to fix this issue?

Marek,do one thing try defining the foreign key along with the schema name i.e 'test.t_vfilers.filer_id'(here 'test' is the schema name),this will solve the problem.

Have you remembered to import the different modules containing the models. In my init.py I have at the bottom a ton of:

from comparty3.model.users import User, UserGroup, Permission
from comparty3.model.pages import PageGroup, Page
etc...

If that's not the issue then I'm not sure; however have you tried to change:

metadata = MetaData()

# declarative table definitions
Base = declarative_base()
Base.metadata = metadata

to:

# declarative table definitions
Base = declarative_base()
metadata = Base.metadata

I'm guessing here, but it may be that declarative_base() creates a special metadata object. This is how it is defined in my pylons projects (were I'm guessing your code is from too).