CODEWITHSUNDEEP
sqlitesqlalchemyflask-sqlalchemy
Egzon Korenica
Egzon Korenica

Reputation: 61

Unable to assing IDs automatically in SQLite

I have the following database model:

class Survey(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    question_ts = db.relationship('Questions')

class Questions(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    survey_id = db.Column(db.Integer, db.ForeignKey('survey.id'), nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    lan_code = db.Column(db.String(3), nullable=False)
    q1 = db.Column(db.String(100), nullable=False)
    q2 = db.Column(db.String(100), nullable=False)
    q3 = db.Column(db.String(100), nullable=False)

When I insert q1, q2, q3, it fails with a NOT Null constraint failed

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: questions.survey_id [SQL: INSERT INTO questions (survey_id, date_posted, lan_code, q1, q2, q3) VALUES (?, ?, ?, ?, ?, ?)] [parameters: (None, '2021-10-06 19:36:08.192194', 'en', 'how are you?', 'Did you get vaccinated?', 'When is your birthday?')]

It works perfectly if I assign IDs manually for the survey. However, is there a way to make the database handle this?

Upvotes: 0

Views: 34

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55640

If you are creating a new Survey, you would do it like this:

qs = Questions(q1='Why?', q2='What?', q3='When?')
survey = Survey(question_ts=[qs])
db.session.add(survey)
db.session.commit()

SQLAlchemy will recognise that the objects are related and set up the keys and relationship.

If you are adding a Questions to an existing Survey you append to the relationship:

survey = session.query(Survey).first()
qs = Questions(q1='Why?', q2='What?', q3='When?')
survey.question_ts.append(qs)
db.session.commit()

If you need to obtain ids before committing, add the object(s) to the session and then call the session's flush method.

Upvotes: 1

Related Questions