sqlalchemy外键/查询连接

Question

嗨，我在sqlalchemy中遇到外键问题，主键ID不能自动递增

即时消息使用：Python2.7、Pyramid1.3和sqlalchemy 0.7

这是我的模型

class Page(Base):
    __tablename__ = 'page'
    id = Column(Integer, ForeignKey('mapper.object_id'), autoincrement=True, primary_key=True)
    title = Column(String(30), unique=True)
    title_slug = Column(String(75), unique=True)
    text = Column(Text)
    date_added = Column(DateTime)

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(100), unique=True)
    email = Column(String(100), unique=True)
    password = Column(String(100))

class Group(Base):
    __tablename__ = 'groups'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), unique=True)

class Member(Base):
    __tablename__ = 'members'

    user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    group_id = Column(Integer, ForeignKey('groups.id'), primary_key=True)

class Resource(Base):
    __tablename__ = 'resource'

    id = Column(Integer, primary_key=True)
    tablename = Column(Text)
    action = Column(Text)

class Mapper(Base):
    __tablename__ = 'mapper'

    resource_id = Column(Integer, ForeignKey('resource.id'), primary_key=True)
    group_id = Column(Integer, ForeignKey('groups.id'), primary_key=True)
    object_id = Column(Integer, primary_key=True)

这是我用SQLAlchemys ORM编写的原始SQL查询

'''
SELECT g.name, r.action
FROM groups AS g
INNER JOIN resource AS r
ON m.resource_id = r.id
INNER JOIN page AS p
ON p.id = m.object_id
INNER JOIN mapper AS m
ON m.group_id = g.id
WHERE p.id = ? AND
r.tablename = ?;
'''
obj = Page
query = DBSession().query(Group.name, Resource.action)\
        .join(Mapper)\
        .join(obj)\
        .join(Resource)\
        .filter(obj.id == obj_id, Resource.tablename == obj.__tablename__).all()

原始SQL查询工作正常，页面和映射器之间没有任何关系，但是SQLAlchemys ORM似乎需要一个ForeignKey链接才能连接它们。所以我决定把ForeignKey放在Page.id，因为Mapper.object_id将链接到几个不同的表。

这使得带有连接的SQL ORM查询按预期工作，但向页表添加新数据会导致异常。

FlushError: Instance <Page at 0x3377c90> has a NULL identity key.  
If this is an auto-  generated value, check that the database 
table allows generation of new primary key values, and that the mapped 
Column object is configured to expect these generated values.  
Ensure also that this flush() is not occurring at an inappropriate time, 
such as within a load() event.

这是我的视图代码：

try:
    session = DBSession()
    with transaction.manager:
        page = Page(title, text)
        session.add(page)
        return HTTPFound(location=request.route_url('home'))
except Exception as e:
    print e
    pass
finally:
    session.close()

我真的不知道为什么，但是我宁愿用SQLalchemy来解决问题，也不要使用原始的SQL，因为我制作这个项目是为了学习：）

Answer 1

我不认为autoincrement=True和ForeignKey(...)在一起玩得很好。

在任何情况下，要使join在没有任何ForeignKey的情况下工作，只需在^{}的第二个参数中指定join条件：

obj = Page
query = DBSession().query(Group.name, Resource.action)\
    .join(Mapper)\
    .join(Resource)\
    .join(obj, Resource.tablename == obj.__tablename__)\
    .filter(obj.id == obj_id)\
    .all()