insert to Db2 using Flask and sqlalchemy

Question

I have a Flask application with sqlalchemy in it. Running manage db init, manage db migrate and manage db upgrade worked perfectly. So the tables are created on db2 without any problems. When i try to do an insert, i get all kinds of errors.

Traceback (most recent call last):
  File "*path*/lib/python3.6/site-packages/ibm_db_dbi.py", line 1258, in _execute_helper
    return_value = ibm_db.execute(self.stmt_handler, parameters)
Exception: Binding Error:

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/ibm_db_sa/ibm_db.py", line 107, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/ibm_db_dbi.py", line 1356, in execute
    self._execute_helper(parameters)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/ibm_db_dbi.py", line 1268, in _execute_helper
    raise self.messages[len(self.messages) - 1]
ibm_db_dbi.DatabaseError: ibm_db_dbi::DatabaseError: Binding Error: 

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

Traceback (most recent call last):
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/flask_restplus/api.py", line 319, in wrapper
    resp = resource(*args, **kwargs)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/flask/views.py", line 88, in view
    return self.dispatch_request(*args, **kwargs)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/flask_restplus/resource.py", line 44, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api/app/api.py", line 37, in get
    snp.sensor_info()
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api/app/network.py", line 123, in sensor_info
    db_session.commit()
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/scoping.py", line 153, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 943, in commit
    self.transaction.commit()
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
    flush_context.execute()
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 830, in _emit_insert_statements
    execute(statement, multiparams)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/ibm_db_sa/ibm_db.py", line 107, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/ibm_db_dbi.py", line 1356, in execute
    self._execute_helper(parameters)
  File "/Users/sebastianlindblom/Documents/GrowSmarter/metrolive-api-env/lib/python3.6/site-packages/ibm_db_dbi.py", line 1268, in _execute_helper
    raise self.messages[len(self.messages) - 1]
sqlalchemy.exc.DatabaseError: (ibm_db_dbi.DatabaseError) ibm_db_dbi::DatabaseError: Binding Error:  [SQL: 'INSERT INTO "schema"."table" (sensor_id, code, lat, lon, x, y) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: (2855, 30, 59.291658, 18.077241, 411.59730406100783, 769.5522643844229)] (Background on this error at: http://sqlalche.me/e/4xp6)

The model looks like:

class Sensor(Base):
    __tablename__ = 'table'
    __table_args__ = {'schema':'schema'}

    sensor_id = db.Column(db.Integer, primary_key=True)
    code = db.Column(db.Integer, unique=True, nullable=False)
    lat = db.Column(db.Float, unique=True, nullable=False)
    lon = db.Column(db.Float, unique=True, nullable=False)
    x = db.Column(db.Float, unique=True, nullable=False)
    y = db.Column(db.Float, unique=True, nullable=False)

    def __repr__(self):
        return '<Sensor %r, x=%r, y=%r>' % self.code  % self.x %  self.y

The insert code looks like:

    from .models import Sensor
    from .database import db_session
    ins = Sensor(sensor_id = sensor_coords['sensor_code'][0],
                                    code = sensor_coords['sensor'][0],
                                    lat = sensor_coords['lat'][0],
                                    lon = sensor_coords['long'][0],
                                    x = sensor_coords['x'][0],
                                    y = sensor_coords['y'][0])
    db_session.add(ins)
    db_session.commit()

Looking at the error message, this is how it's trying to bind the sql statement: [SQL: 'INSERT INTO "schema"."table" (sensor_id, code, lat, lon, x, y) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: (2855, 30, 59.291658, 18.077241, 411.59730406100783, 769.5522643844229)] which looks correct? (schema and table names are replaced).

My database.py looks like:

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import ibm_db_sa

from app.api import app

engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'], convert_unicode=True)


db_session = scoped_session(sessionmaker(autocommit=False,
                                     autoflush=False,
                                     bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
   Base.metadata.create_all(bind=engine)

I can't figure out whats wrong.. The insert format looks correct?

Appreciate all the help I can get. Thanks!

I always initialize(__init__) the variables in the db model class. For instance, in the Sensor class, define def __init__(self, sensor_id, code, lat, lon, x,y): self.sensor_id = sensor_id, ... and so on. The DatabaseError is raised by the DBAPI and not SQLAlchemy. — amanb
– amanb, Commented Jun 29, 2018 at 10:19
Yes, I realize the error is raised by the DBAPI, but I don't understand why. INSERT INTO "schema"."table" (sensor_id, code, lat, lon, x, y) VALUES (?, ?, ?, ?, ?, ?) is the correct way of inserting things to db2. Right? Maybe it's thrown because of the bindning error thrown first? Mayeb the sql-statement isn't binded in the correct way? — t3ng1l
– t3ng1l, Commented Jun 29, 2018 at 10:26
The "schema"."table" should be without the inverted commas. It should be schema.table. The query should be INSERT INTO schema.table (sensor_id, code, lat, lon, x, y) VALUES (?, ?, ?, ?, ?, ?) — amanb
– amanb, Commented Jun 29, 2018 at 10:43
Ah, correct.. Thansk! I didn't notice that. Any idea how to fix it? Since sqlalchemy it self defines the sql statement. — t3ng1l
– t3ng1l, Commented Jun 29, 2018 at 10:47

t3ng1l · Accepted Answer · 2018-06-29 11:34:53Z

1

Got it to work!

Had to cast the types in the insert code as:

ins = Sensor(sensor_id = int(sensor_coords['sensor_code'][0]),
                                    code = int(sensor_coords['sensor'][0]),
                                    lat = float(sensor_coords['lat'][0]),
                                    lon = float(sensor_coords['long'][0]),
                                    x = float(sensor_coords['x'][0]),
                                    y = float(sensor_coords['y'][0]))

Didn't realize numpy used its own int and float types...

answered Jun 29, 2018 at 11:34

t3ng1l

1,09510 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

amanb Over a year ago

You may accept your answer. Although, there was no mention of numpy in the question. That part of the code was not shared.

t3ng1l Over a year ago

Can't accept for another two days... But I'll accept as soon as I can! Yes, sorry for that. I didn't realize numpy would be a problem.

Tony Over a year ago

Hi, which format for the SQLALCHEMY_DATABASE_URI did you use in order to work with flask?

t3ng1l Over a year ago

I don't remember to be honest @Tony. Sorry..

Collectives™ on Stack Overflow

insert to Db2 using Flask and sqlalchemy

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related