0

I am working on a Flask app using PostgreSQL and SQLAlchemy with psycopg2. I develop on Debian bullseye/sid and have a test website running on Ubuntu 18.04.4 LTS. Recently (and I regret that I cannot pin down the update), I have been unable to commit anything to the database in the flask shell on my local machine; every attempt fails with a NotNullViolation on the primary key table id. This has always previously been handled. The following is an example of the error:

>>> db.session.commit()
Traceback (most recent call last):
  File "[...]DandyLion/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1283, in _execute_context [...]
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 1, Resolve database id problem, null_id_problem, null, null, 2020-05-28 08:01:00.184953, null, 2020-05-30 17:00:00, [...]).
[SQL: INSERT INTO tasks (user_id, title, slug, [...]) RETURNING tasks.id]

The database is setup with the following:

app.config.from_object(Config)
db = SQLAlchemy(app)

I will confess ignorance as to the implementation details but notice that the SQL statement in the traceback indicates that tasks.id ought to be returned. This has always previously been the case.

The following are the versions on the (working) server and the local setup:

python:      3.8.3,  3.6.8
psycopg2:    2.8.4,  2.8.4
sqlalchemy:  1.3.12, 1.3.17
PostgresSQL: 10.12,  12.3

I initially investigated psycopg2, changing the version in my Pipfile on the local machine. To investigate further, I would appreciate any insights into the most likely point of failure. Can anybody help with this?

krozruch
  • 33
  • 3

1 Answers1

-1

As explained in this SO post you are trying to post a NULL value as your id.

You can solve it either by providing a value for the id or change the id column type to serial.

above_c_level
  • 3,579
  • 3
  • 22
  • 37
  • Thank you... I am still investigating, but I had not provided sufficient information here. I had commented out a couple of imports which set up the app and database and initialise flask-SQLAlchemy. My database was partially initialised and was returning posts etc., without the database connection being properly configured. I will carry on investigating and post an answer below. – krozruch Jun 03 '20 at 12:17