Why SQLAlchemy send null value for id primary column?

Question

I'm trying to build an user registration page and facing this:
For some reason sqlalchemy forms this row for postgresql database - (null, roman, 2021-05-04), but I've passed in User model that id column is primary key (so NOT NULL)
I created tables in database via pgAdmin4
I have no idea why this is happening, because I used the same code but for MySQL and for SQLite and it works

models.py

class User(UserMixin, db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    registration_date = db.Column(db.DateTime, nullable=False)

    def __init__(self, username, registration_date):
        self.username = username
        self.registration_date = registration_date

routes.py

@app.route('/register', methods=['POST', 'GET'])
def register():
    form = RegistrationForm()
    if form.validate_on_submit():
        user = User(username=form.username.data,
                    registration_date=datetime.now(),
                    )
        db.session.add(user)
        db.session.commit()
        flash('User was created!', 'success')
        return redirect(url_for('login'))
    return render_template('register.html', form=form)

score 0 · Answer 1 · answered May 04 '21 at 08:39

The column is type INTEGER and PRIMARY_KEY but doesn't have any default value, so when trying to insert a row there with null value you'll get the error.

How is the table defined on postgreSQL? I suggest to use the identity as written here

create table t2 (id integer primary key generated always as identity);

score 0 · Answer 2 · answered May 04 '21 at 16:58

The structure of the table is not what SQLAlchemy expects for your model. If we let SQLAlchemy create the table it will be

CREATE TABLE users (
    id SERIAL NOT NULL, 
    username VARCHAR(20) NOT NULL, 
    registration_date TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
    PRIMARY KEY (id), 
    UNIQUE (username)
)

so SQLAlchemy does not try to insert the "id" column

INSERT INTO users (username, registration_date) VALUES (%(username)s, %(registration_date)s) RETURNING users.id
[generated in 0.00079s] {'username': 'gord', 'registration_date': datetime.datetime(2021, 5, 4, 10, 53, 9, 930021)}

which is apparently not valid for the table that you created yourself.

Why SQLAlchemy send null value for id primary column?

2 Answers2