insert to Db2 using Flask and sqlalchemy


insert to Db2 using Flask and sqlalchemy



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.


manage db init


manage db migrate


manage db upgrade


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


[SQL: 'INSERT INTO "schema"."table" (sensor_id, code, lat, lon, x, y) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: (2855, 30, 59.291658, 18.077241, 411.59730406100783, 769.5522643844229)]



My database.py looks like:


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)



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.
– user8212173
Jun 29 at 10:19


__init__


Sensor


def __init__(self, sensor_id, code, lat, lon, x,y): self.sensor_id = sensor_id, ...


DatabaseError





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
Jun 29 at 10:26


INSERT INTO "schema"."table" (sensor_id, code, lat, lon, x, y) VALUES (?, ?, ?, ?, ?, ?)





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 (?, ?, ?, ?, ?, ?)
– user8212173
Jun 29 at 10:43



schema.table


INSERT INTO schema.table (sensor_id, code, lat, lon, x, y) VALUES (?, ?, ?, ?, ?, ?)





Ah, correct.. Thansk! I didn't notice that. Any idea how to fix it? Since sqlalchemy it self defines the sql statement.
– t3ng1l
Jun 29 at 10:47




1 Answer
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...





You may accept your answer. Although, there was no mention of numpy in the question. That part of the code was not shared.
– user8212173
Jun 29 at 16:51





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.
– t3ng1l
2 days ago






By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Comments

Post a Comment

Popular posts from this blog

paramiko-expect timeout is happening after executing the command

paramiko-expect timeout is happening after executing the command Can someone help me why timeout is happening after executing the command .I am trying to SSH to a machine and execute a command.I want to store the result of the executed command . I have referred paramiko-timeout but i didn't get my expected result. import traceback try: import paramiko except ImportError: raise Exception("Please install paramiko , pip install paramiko") import pexpect from paramiko_expect import SSHClientInteraction def main(): HOSTNAME = "minion5.net" USERNAME = "root" PASSWORD = "help@432" PROMPT = "root@*:~$s+" try: client = paramiko.SSHClient() client.load_system_host_keys() client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) client.connect(hostname=HOSTNAME, username=USERNAME, password=PASSWORD) with SSHClientInteraction(client, timeout=20, display=True) as interact: ...
Read more

Export result set on Dbeaver to CSV

Export result set on Dbeaver to CSV Normally I use Dbeaver for windows and always export my result set like this: run my query --> select the result --> export the result set --> select export to clipboard --> done This step by step puts my result set in my clipboard and I can paste it wherever I want to work with it. The problem is that now I am using dbeaver for mac and this guide is not working. I can go on until the moment that I select my result set like in the image below: exporting data set But once I go further in the process, in the last step I get: no query Note that in "source" it was suppose to show the query that originated the result set, but instead it says just "select. As a result it does't select my result or anything (besides being "successful"). Normally my query would show up there automatically and I couldn't find any option that corrects this problem in the menus. Please help me! Thanks. Aft...
Read more

Opening a url is failing in Swift

Opening a url is failing in Swift breakpointing inside the callback and logging success shows false. I added itms-apps to my plist: LSApplicationQueriesSchemes and put this in my app delegate: func application(_ app: UIApplication, open url: URL, options: [UIApplicationOpenURLOptionsKey : Any] = [:]) -> Bool { return true } func moreTapped() { let url = NSURL(string: "itms-apps://itunes.com/developer/quantum-productions/id979315877") if #available(iOS 10.0, *) { UIApplication.shared.open(url! as URL, options: [:], completionHandler: {(success: Bool) in }) } else { UIApplication.shared.openURL(url! as URL) } } Are you testing on a real device? – rmaddy Jun 21 at 23:57 I try on a device, get "Cannot Connect to App Store". Device is online ...
Read more