SQLAlchemy classes for files

Question

I'm trying to figure out how to distribute SQLAlchemy classes across multiple files, and in my life I may not understand how to do this. I'm new to SQLAlchemy, so forgive me if this question is trivial.

Consider these 3 classes in each of our files:

A.py:

from sqlalchemy import * from main import Base class A(Base): __tablename__ = "A" id = Column(Integer, primary_key=True) Bs = relationship("B", backref="A.id") Cs = relationship("C", backref="A.id") 

B.py:

 from sqlalchemy import * from main import Base class B(Base): __tablename__ = "B" id = Column(Integer, primary_key=True) A_id = Column(Integer, ForeignKey("A.id")) 

C.py:

 from sqlalchemy import * from main import Base class C(Base): __tablename__ = "C" id = Column(Integer, primary_key=True) A_id = Column(Integer, ForeignKey("A.id")) 

And then let's say that we have main.py something like this:

 from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship, backref, sessionmaker Base = declarative_base() import A import B import C engine = create_engine("sqlite:///test.db") Base.metadata.create_all(engine, checkfirst=True) Session = sessionmaker(bind=engine) session = Session() a = AA() b1 = BB() b2 = BB() c1 = CC() c2 = CC() a.Bs.append(b1) a.Bs.append(b2) a.Cs.append(c1) a.Cs.append(c2) session.add(a) session.commit() 

The above error:

 sqlalchemy.exc.NoReferencedTableError: Foreign key assocated with column 'C.A_id' could not find table 'A' with which to generate a foreign key to target column 'id' 

How can I provide a declarative base in these files?

What is the “right” way to do this, given that I could add something like Pylons or Turbogears on top of this?

change 10-03-2011

I found this description from the Pyramids framework, which describes the problem and, more importantly, checks that this is an actual problem, and not (only) just my confused self that the problem is. I hope this helps others who dare to take this dangerous road :)

Answer 1

The simplest solution to your problem would be to infer Base from a module that imports A , B and C ; Break cyclic import.

base.py

 from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() 

a.py

 from sqlalchemy import * from base import Base from sqlalchemy.orm import relationship class A(Base): __tablename__ = "A" id = Column(Integer, primary_key=True) Bs = relationship("B", backref="A.id") Cs = relationship("C", backref="A.id") 

b.py

 from sqlalchemy import * from base import Base class B(Base): __tablename__ = "B" id = Column(Integer, primary_key=True) A_id = Column(Integer, ForeignKey("A.id")) 

c.py

 from sqlalchemy import * from base import Base class C(Base): __tablename__ = "C" id = Column(Integer, primary_key=True) A_id = Column(Integer, ForeignKey("A.id")) 

main.py

 from sqlalchemy import create_engine from sqlalchemy.orm import relationship, backref, sessionmaker import base import a import b import c engine = create_engine("sqlite:///:memory:") base.Base.metadata.create_all(engine, checkfirst=True) Session = sessionmaker(bind=engine) session = Session() a1 = aA() b1 = bB() b2 = bB() c1 = cC() c2 = cC() a1.Bs.append(b1) a1.Bs.append(b2) a1.Cs.append(c1) a1.Cs.append(c2) session.add(a1) session.commit() 

Works on my machine:

 $ python main.py ; echo $? 0 

Answer 2

I am using Python 2.7 + Flask 0.10 + SQLAlchemy 1.0.8 + Postgres 9.4.4.1

This template template is configured for the User and UserDetail models stored in the same “models.py” file in the “user” module. These classes inherit from the SQLAlchemy base class.

All the additional classes that I added to my project were also obtained from this base class, and as the models.py file increased, I decided to split the models.py file into one file for each class and ran into the problem described here.

The solution that I found on the same lines as the @computermacgyver message of October 23, 2013 was to include all my classes in the init .py file of the new module that I created to host all the new class files created. Looks like that:

 /project/models/ __init__.py contains from project.models.a import A from project.models.b import B etc... 

Answer 3

If I can add my meaning, since I had the same problem. You need to import the classes into a file where you create Base = declarative_base() AFTER you created Base and Tables . A brief description of how my project is configured:

model /user.py

 from sqlalchemy import * from sqlalchemy.orm import relationship from model import Base class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) budgets = relationship('Budget') 

model /budget.py

 from sqlalchemy import * from model import Base class Budget(Base): __tablename__ = 'budget' id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey('user.id')) 

model / __ __ INIT. RU

 from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker _DB_URI = 'sqlite:///:memory:' engine = create_engine(_DB_URI) Base = declarative_base() Base.metadata.create_all(engine) DBSession = sessionmaker(bind=engine) session = DBSession() from .user import User from .budget import Budget