CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
Jack Parkinson
Jack Parkinson

Reputation: 711

Object changes after it is committed with SQL Alchemy

Perhaps I am missing something very simple here, but I have a Flask site in which I have a test for people to take. My database has a TestAttempt table which is defined as follows:

class TestAttempt(db.Model, UserMixin):
    user_id = db.Column(db.Integer,db.ForeignKey(User.id, ondelete='CASCADE'), primary_key=True)
    attempt_number = db.Column(db.Integer)
    attempt_data = db.Column(db.String(1000))
    timestamps = db.Column(db.String(1000))
    score = db.Column(db.Integer)
    begin_time = db.Column(db.String(20))

The site is set up for a pre and post test, so the attempt_number field should be either 1 or 2. I have a function called get_or_create_test_attempt() which does what it says on the tin. If a user already has an open attempt, it is returned; otherwise a new one is created. It was originally defined as follows:

def get_or_create_test_attempt(user_id, attempt_id):
    attempt = TestAttempt.query.filter_by(user_id=user_id, attempt_number=attempt_id).first()
    if attempt == None:
        new_attempt = TestAttempt(user_id=user_id, attempt_number=attempt_id, begin_time=str(datetime.now().strftime("%m%d%Y%H%M%S")))
        db.session.add(new_attempt)
        db.session.commit()
        return new_attempt
    else:
        return attempt

This wasn't working: it got the first attemt with no issues, but whenever I supplied 2 as the attempt_id it kept returning the first one. I added some print statements to try and determine what was going on and to tell a bit of a story:

def get_or_create_test_attempt(user_id, attempt_id):
    print("Asked for:", user_id, attempt_id)
    attempt = TestAttempt.query.filter_by(user_id=user_id, attempt_number=attempt_id).first()
    if attempt == None:
        new_attempt = TestAttempt(user_id=user_id, attempt_number=attempt_id, begin_time=str(datetime.now().strftime("%m%d%Y%H%M%S")))
        print("Created:", new_attempt.user_id, new_attempt.attempt_number)
        db.session.add(new_attempt)
        db.session.commit()
        print("After commit:", new_attempt.user_id, new_attempt.attempt_number)
        return new_attempt
    else:
        return attempt

I got the following output for when I requested with the attempt_id of 2 and the user_id of 1160:

Asked for: 1160 2
Created: 1160 2
After commit: 1160 1
Recieved: 1160 1

NB: I also added the "Recieved" part after the function call, like so:

attempt = get_or_create_test_attempt(current_user.id, attempt_id)
print("Recieved:", attempt.user_id, attempt.attempt_number)

In desperation, I tried to run a new query for the object I had just created, but it showed the same behavior:

def get_or_create_test_attempt(user_id, attempt_id):
    print("Asked for:", user_id, attempt_id)
    attempt = TestAttempt.query.filter_by(user_id=user_id, attempt_number=attempt_id).first()
    if attempt == None:
        new_attempt = TestAttempt(user_id=user_id, attempt_number=attempt_id, begin_time=str(datetime.now().strftime("%m%d%Y%H%M%S")))
        print("Created:", new_attempt.user_id, new_attempt.attempt_number)
        db.session.add(new_attempt)
        db.session.commit()
        print("After commit:", new_attempt.user_id, new_attempt.attempt_number)
        new_attempt = TestAttempt.query.filter_by(user_id=user_id, attempt_number=attempt_id).first()
        print("After new query:", new_attempt.user_id, new_attempt.attempt_number, "\tand query was fed:", user_id, attempt_id)
        return new_attempt
    else:
        return attempt

And the output, bafflingly:

Asked for: 1160 2
Created: 1160 2
After commit: 1160 1
After new query: 1160 1         and query was fed: 1160 2
Recieved: 1160 1

Note that if I begin the post-test first, then that is the "prevailing" test - if I try and access/create an attempt with attempt_number of 1, it always gives the one with attempt_number of 2. Also note that, even if the function fails to return the corect object, it is created because I can see it, with the correct parameters, in the database.

I have no idea what could be causing this behaviour. Any input at all is welcome.

Upvotes: 0

Views: 229

Answers (1)

PGHE
PGHE

Reputation: 1962

Your user_id column is a primary_key, you'd only be able to have 1 user entry in your table, i.e. you wouldn't be able to commit a second attempt with the same user_id. You could try make a composite primary key out of your user_id and attempt_number columns.

Upvotes: 1

Related Questions