SQLAlchemy - create a table template

Question

I am just starting out with SQLAlchemy and I was wondering ... I will have many tables in my model. I would like to have my own file for each table that I will have in my model.

I am currently using the 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

Next in _init_.py

 """The application 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", 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 further in all 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 fine if I don't start using some foreign keys for tables as soon as I use Foreign Key,

 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 determine the key key directly in the Filer class (and remove Basic_Table from the declaration), however this does not solve the problem.

My code for creating the database is as follows: #! / USR / bin / python import ConfigParser from the url sqlalchemy.engine.url from importing models *

 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 solve this problem?

Answer 1

Marek, try to determine the foreign key with the schema name ie 'test.t_vfilers.filer_id' (here "test" is the name of the schema), this will solve the problem.

Answer 2

Remember that you have imported various modules containing models. In my init .py I have at the bottom:

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

If this is not a problem, then I am not sure; however you tried to change:

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

in

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

I assume here, but it may be that declarative_base () creates a special metadata object. Here is how it is defined in my pylons projects (I assume your code too).