python-3.x flask sqlalchemy flask-sqlalchemy psycopg2

Error while trying to save a nested list in a Postgresql DB using SQLAlchemy

I am developing a Flask app using flask-restx to save a nested list to a PostgreSQL DB hosted on RDS using SQLAlchemy. Each nested list will be one row in the table. Assume the following nested list

csv_json_response = [
        [
            1577837700,
            63.2
        ],
        [
            1577838600,
            61.0
        ],
        [
            1577839500,
            58.9
        ],
        [
            1577840400,
            57.0
        ],
        [
            1577841300,
            55.3
        ]
    ]

I am trying to insert this into a database as follows:

import sqlalchemy as db    
if (type(csv_json_response) is list and len(csv_json_response) > 0):
    db.session.bulk_insert_mappings(Upload, csv_json_response)
elif (type(csv_json_response) is dict):
    item = Upload(**csv_json_response)
    print(item)
    db.session.add(item)
print('Data ready')
db.session.commit()
print('Data dumped into the dummy DB')

where Upload is

class Upload(db.Model):
    __tablename__ = "user_uploaded_CSV"
    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.Integer, nullable=False)
    value = db.Column(db.Float(precision=2), nullable=False)

When I try to run this, it throws the following error:

Error on request:
Traceback (most recent call last):
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1257, in _execute_context
    cursor, statement, parameters, context
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 873, in do_executemany
    cursor.executemany(statement, parameters)
psycopg2.errors.NotNullViolation: null value in column "timestamp" violates not-null constraint
DETAIL:  Failing row contains (6, null, null).

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/werkzeug/serving.py", line 323, in run_wsgi
    execute(self.server.app)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/werkzeug/serving.py", line 312, in execute
    application_iter = app(environ, start_response)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/app.py", line 2464, in __call__
    return self.wsgi_app(environ, start_response)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/app.py", line 2450, in wsgi_app
    response = self.handle_exception(e)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask_restx/api.py", line 638, in error_router
    return original_handler(f)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/app.py", line 1867, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask_restx/api.py", line 636, in error_router
    return self.handle_error(e)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask_restx/api.py", line 638, in error_router
    return original_handler(f)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask_restx/api.py", line 636, in error_router
    return self.handle_error(e)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask_restx/api.py", line 375, in wrapper
    resp = resource(*args, **kwargs)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask/views.py", line 89, in view
    return self.dispatch_request(*args, **kwargs)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/flask_restx/resource.py", line 44, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/home/cortex/Energycortex/upload-service/app/upload/controller.py", line 37, in post
    return Uploader.csv_uploader(**args)
  File "/home/cortex/Energycortex/upload-service/app/upload/services/uploader.py", line 19, in csv_uploader
    return CsvReader.csv_check(csv_file)
  File "/home/cortex/Energycortex/upload-service/app/upload/services/processor.py", line 40, in csv_check
    CsvReader.__csv_uploader(csv_json)
  File "/home/cortex/Energycortex/upload-service/app/upload/services/processor.py", line 94, in __csv_uploader
    db.session.bulk_insert_mappings(Upload, csv_json_response)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 163, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2867, in bulk_insert_mappings
    render_nulls,
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2958, in _bulk_save_mappings
    transaction.rollback(_capture_exception=True)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
    exc_value, with_traceback=exc_tb,
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2952, in _bulk_save_mappings
    render_nulls,
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 102, in _bulk_insert
    bookkeeping=return_defaults,
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1083, in _emit_insert_statements
    c = cached_connections[connection].execute(statement, multiparams)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1130, in _execute_clauseelement
    distilled_params,
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1257, in _execute_context
    cursor, statement, parameters, context
  File "/home/cortex/.local/share/virtualenvs/upload-service-3zz-Lxl9/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 873, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "timestamp" violates not-null constraint
DETAIL:  Failing row contains (6, null, null).

[SQL: INSERT INTO "user_uploaded_CSV" DEFAULT VALUES]
[parameters: ({}, {}, {}, {}, {}, {}, {}, {})]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

What is the mistake I am doing?

Solution

bulk_insert_mappings(mapper , mappings) mappings is a sequence of dictionaries. your mappings(csv_json_response) is a sequence of list. so you should change csv_json_response. https://docs.sqlalchemy.org/en/13/orm/session_api.html#sqlalchemy.orm.session.Session.bulk_insert_mappings

csv_json_response = [
        {
            "timestamp": 1577837700,
            "value": 63.2
        },
        {
            "timestamp": 1577838600,
            "value": 61.0
        }, ...
    ]