0

I have a bot which uses sqlalchemy to interface with the database. The bot is created using Flask and Twilio and it is connected to an AzureSQL database. When saving a response, I use the code:

db.save(dbTable(content=incoming_msg,
                             question=question,
                             user=User.query.filter(User.number == num).first()))

where db.save is defined as

def save_and_commit(item):
    db.session.add(item)
    db.session.commit()


db.save = save_and_commit

The db tables are set up as follows:

class dbTable(db.Model):
    __tablename__ = 'db_table'

    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.String, nullable=False)
    question_id = db.Column(db.Integer, db.ForeignKey('dbTable_questions.id'))
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))

    def __init__(self, content, question, user):
        self.content = content
        self.question = question
        self.user = user

This works fine for some db tables but not for others. For certain tables, I get the response:

sqlalchemy.exc.IntegrityError: (pyodbc.IntegrityError) ('23000', "[23000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot insert the value NULL into column 'user_id', table 'test.dbo.baseline_answers'; column does not allow nulls. INSERT fails. (515) (SQLExecDirectW)")

I cannot figure out why this is the case. I have tried creating a user object with User.query.filter(User.number == num).first() and passing that as as argument to db.save. Despite the server confirming that the user is not none, as soon as I try to save something in the db I get the above response.

Would really appreciate any help on this problem.

Ben
  • 41
  • 5

1 Answers1

0

The problem was where the user object was defined: the relationship between the user and the db table hadn't been defined.

Ben
  • 41
  • 5