How to insert the 'answer' model?

Question

The title may be not exactly, but I don't know how to express it.

I have 3 class: User, Question, Answer. The simple code is:

Session = scoped_session(sessionmaker())
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)

    questions = relationship('Question', backref="user")
    answers = relationship('Answer', backref="user")

class Question(Base):
     __tablename__ = 'questions'
     id = Column(Integer, primary_key=True)
     user_id = Column(Integer, ForeignKey('users.id'))

     answers = relationship('Answer', backref="user")

class Answer(Base):
     __tablename__ = 'answers'
     user_id = Column(Integer, ForeignKey('users.id'))
     question_id = Column(Integer, ForeignKey('questions.id'))

     id = Column(Integer, primary_key=True)

Now, A user asked a question, so there will be an answer created:

user = get_user_from_session()
question = get_question(question_id)

# create answer
answer = Answer()
answer.user = user
answer.question = question

Session.add(answer)  # !!!
Session.commit()

I hope the answer will be inserted to database, but unfortunately, there is an error reported:

 AttributeError: 'module' object has no attribute '_sa_instance_state'

Is there something I've missed? How to fix it?

---

UPDATE

Thanks for @dhaffey, I've fixed the typos. I recreate a test file to test this, found no error happened again, but answer.user_id and answer.question_id are null in database after commit.

This is my code, you can run it directly.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import *

engine = create_engine('sqlite:///test.sqlite', echo=True)
Session = scoped_session(sessionmaker())
Base = declarative_base()

Base.metadata.bind=engine

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    questions = relationship('Question')
    answers = relationship('Answer')

class Question(Base):
    __tablename__ = 'questions'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    title = Column(String)
    answers = relationship('Answer')

class Answer(Base):
    __tablename__ = 'answers'
    user_id = Column(Integer, ForeignKey('users.id'))
    question_id = Column(Integer, ForeignKey('questions.id'))

    id = Column(Integer, primary_key=True)

Base.metadata.create_all()

user = User()
user.name = 'aaa'
Session.add(user)
Session.flush()

question = Question()
question.title = 'ttt'
question.user = user
Session.add(question)
Session.flush()

answer = Answer()
answer.user = user
answer.question = question

Session.add(answer)
Session.commit()

print answer.id # not None

found = Session.query(Answer).filter_by(id=answer.id).one()

print found.user.name # not None
print found.question.title # not None

# !!! It seems all models are saved correctly,
# but please open the test.sqlite database, (not querying by sqlahchemy)
# the question.user_id and answer.user_id and answer.question_id are null

Answer 1

Your class declarations don't "compile" for me, so I'm wondering if you've run this code, and which SQLAlchemy version you're using if so. The line

user_id = Column(Integer, ForeignKey='users.id')

raises

sqlalchemy.exc.ArgumentError: Unknown arguments passed to Column: ['ForeignKey']

with SQLAlchemy 0.6.4. You're trying to declare the foreign key with a keyword argument, but the correct usage is to construct a ForeignKey object and pass it positionally, like this:

user_id = Column(Integer, ForeignKey('users.id'))

With the foreign keys fixed, your example works as expected for me.

Note that you don't need to explicitly provide the primaryjoin argument on these relationships when the corresponding foreign keys are appropriately declared - SQLAlchemy infers the correct join.