Python Flask SQLAlchemy not commiting changes to the database

Question

I have a class for users which then has a method to save data to the database.

class User(db.Model, ExtraMixin):
    first_name = db.Column(db.String(50), nullable=False)
    last_name = db.Column(db.String(50), nullable=False)
    other_names = db.Column(db.String(100), nullable=True)
    phone_number = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), index=True, unique=True, nullable=False)
    password = db.Column(db.String(200), nullable=False)
    user_type_id = db.Column(db.Integer, db.ForeignKey('user_type.id'))
    vendor_id = db.Column(db.Integer, db.ForeignKey('vendor.id'), nullable=True)
    
    vendor = db.relationship('Vendor', primaryjoin="and_(User.vendor_id==Vendor.id)", lazy=True) 
    

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

For some reason, this does not work. I don't know if I am doing anything wrong.

This is where the save_to_db method gets called

def post(self):
        data = self.parser.parse_args()
        new_user = User(
            first_name =data['first_name'],
            last_name =data['last_name'],
            other_names =data['other_names'],
            phone_number =data['phone_number'],
            email =data['email'],
            password =User.generate_hash(data['password']),
            user_type_id =data['user_type_id'],
            vendor_id =data['vendor_id'],
            created_by =get_jwt_claims()['id']
            )
        try:
            new_user.save_to_db()
            return {'message': 'New User {} added'.format(data['first_name'])}
        except:
            return {'message': 'Internal sever error. Please contact the person responsible'}, 500

Answer 1

Giving your database model:

class User(db.Model, ExtraMixin):
    first_name = db.Column(db.String(50), nullable=False)
    last_name = db.Column(db.String(50), nullable=False)
    other_names = db.Column(db.String(100), nullable=True)
    phone_number = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), index=True, unique=True, nullable=False)
    password = db.Column(db.String(200), nullable=False)
    user_type_id = db.Column(db.Integer, db.ForeignKey('user_type.id'))
    vendor_id = db.Column(db.Integer, db.ForeignKey('vendor.id'), nullable=True)
    
    vendor = db.relationship('Vendor', primaryjoin="and_(User.vendor_id==Vendor.id)", lazy=True) 

You have two paths to write the data in the database, based on how you get the information for the database.

If you are building API and make a POST request from a dedicated frontend to the register endpoint, you will get the data from that request written in the database:

class SignUp(Resource):
    def post(self):
            data = request.get_json()
            user = User(**data)
            db.session.add(user)
            db.session.commit()
            return {'Success': 'Success '}
        return 400, {'error': 'Error creating user'}

api.add_resource(SignUp, "/register")

If you are building Flask SPA without the need for the data to be sent to a frontend framework you can do it like this and have a register page with a RegistrationForm:

@app.route('/register', methods=['GET', 'POST'])
def register():
    if current_user.is_authenticated:
        return redirect(url_for('home'))
    form = RegistrationForm()
    if form.validate_on_submit():
        user = User(first_name=form.first_name.data,
                    last_name=form.last_name.data, 
                    other_names=form.other_names.data
                    # the rest of the fields go here
                    )
        db.session.add(user)
        db.session.commit()
        flash(f'Created user {form.username.data}.')
        return redirect(url_for('login'))
    return render_template('register.html', title='Register', form=form)

By doing one of these suggestions you will not need the method in the model class because it will execute when you reach the route or make a POST request.

Your POST request should be like this:

def post(self):
        data = self.parser.parse_args()
        new_user = User(
            first_name =data['first_name'],
            last_name =data['last_name'],
            other_names =data['other_names'],
            phone_number =data['phone_number'],
            email =data['email'],
            password =User.generate_hash(data['password']),
            user_type_id =data['user_type_id'],
            vendor_id =data['vendor_id'],
            created_by =get_jwt_claims()['id']
            )
        try:
            db.session.add(new_user)
            db.session.commit()
            return {'message': 'New User {} added'.format(data['first_name'])}
        except:
            return {'message': 'Internal sever error. Please contact the person responsible'}, 500

Or if you really want to use the method replace new_user.save_to_db() with user.save_to_db(new_user)