[FIXED] SQLAlchemy changes value after commit

Issue

Seemingly out of nowhere, my Flask app is now inserting the same value for one column in a table in my database no matter what when it had been previously working.

I have a model here:

class rma_test_record(db.Model):
    primary_key = db.Column(db.Integer, primary_key=True)
    test_key = db.Column(db.Integer)
    test_result = db.Column(db.Boolean, default=False)
    rma_number = db.Column(db.Integer)
    test_date = db.Column(db.DateTime, default=dt.utcnow)

    def __init__(self, rma_number, test_key, test_result):
        self.test_key = test_key
        self.test_result = test_result
        self.rma_number = rma_number

    def __repr__(self):
        return str(self.rma_number) + ' - ' + str(self.primary_key) + ' - ' + str(self.test_key) + ' - ' + str(self.test_result)

    def save(self):
        db.session.add( self )
        db.session.commit()

and code in my application:

new_test = rma_test_record(rma_number=rma_number, test_key=test_key, test_result=test_result)
print(new_test)
new_test.save()
print(new_test)

The result of the prints is

2203193857 - None - 41 - True
2147483647 - 7484 - 41 - True

So it looks like after the record is committed, the number changes and it is always to this same number. I've never seen a problem like this and can't figure out what is happening.

Solution

The problem is with the range of your value and the type of the column to which the value is being saved in.

The rma_number column has a SQLAlchemy column type of Integer but the value you are passing in (which is 2203193857 in this example) is greater than the maximum value of an Integer. As a result, it default to the maximum value it can store 2147483647 (i.e.: 2^31 - 1)

Consider using the BigInteger Column type instead, which allows for larger numbers. For more SQLAlchemy column types see https://docs.sqlalchemy.org/en/14/core/type_basics.html

Answered By - tintins7a6164