sqlalchemy foreign keys / query joins

Question

Hi im having some trouble with foreign key in sqlalchemy not auto incrementing on a primary key ID

Im using: python 2.7, pyramid 1.3 and sqlalchemy 0.7

Here is my models

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)

and here is my RAW SQL query which i've written in SQLAlchemys ORM

'''
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()

the raw SQL Query works fine without any relations between Page and Mapper, but SQLAlchemys ORM seem to require a ForeignKey link to be able to join them. So i decided to put the ForeignKey at Page.id since Mapper.object_id will link to several different tables.

This makes the SQL ORM query with the joins work as expected but adding new data to the Page table results in a exception.

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.

here is my view code:

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()

I really don't know why, but i'd rather have the solution in SQLalchemy than doing the RAW SQL since im making this project for learning purposes :)

Answer 1

I do not think autoincrement=True and ForeignKey(...) play together well.

In any case, for join to work without any ForeignKey, you can just specify the join condition in the second parameter of the 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()