python - 使用 Flask 和 sqlalchemy 插入到 Db2

Question

我有一个包含 sqlalchemy 的 Flask 应用程序。运行manage db init、manage db migrate和manage db Upgrade工作得很好。所以在db2上创建表没有任何问题。当我尝试插入时，出现各种错误。

<小时/>

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)

模型看起来像:

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

插入代码如下所示:

    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()

查看错误消息，这就是它尝试绑定(bind)sql语句的方式:[SQL: 'INSERT INTO "schema"."table"(sensor_id, code, lat, lon, x, y) VALUES (?, ?, ?, ?, ?, ?)'] [参数:(2855 , 30, 59.291658, 18.077241, 411.59730406100783, 769.5522643844229)]哪个看起来正确？ (模式和表名称被替换)。

我的database.py看起来像:

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)

我不知道出了什么问题。插入格式看起来正确吗？

感谢我能得到的所有帮助。谢谢!

Answer 1

开始工作了!

必须将插入代码中的类型转换为:

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]))

没有意识到 numpy 使用自己的 int 和 float 类型...