MayTheSchwartzBeWithYou MayTheSchwartzBeWithYou - 1 year ago 92
Python Question

Flask, MySQL and SQLAlchemy querying ID error

I get a weird error when I try to query an item by id. I have tried all suggestion I have found and only when doing raw query I get a proper result.

Part of the Traceback:

File "C:\Users\pgsid\Envs\xo\lib\site-packages\flask\", line 1988, in wsgi_app
response = self.full_dispatch_request()
File "C:\Users\pgsid\Envs\xo\lib\site-packages\flask\", line 1641, in full_dispatch_requ`est
rv = self.handle_user_exception(e)
File "C:\Users\pgsid\Envs\xo\lib\site-packages\flask\", line 1544, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\pgsid\Envs\xo\lib\site-packages\flask\", line 1639, in full_dispatch_request
rv = self.dispatch_request()
File "C:\Users\pgsid\Envs\xo\lib\site-packages\flask\", line 1625, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "", line 24, in get_user
a = User.get_item(user_id)
File "C:\Users\pgsid\xo\xow\models\", line 57, in get_item
result = User.query.filter_by(id=idd).first()
File "C:\Users\pgsid\Envs\xo\lib\site-packages\sqlalchemy\orm\", line 2659, in first
ret = list(self[0:1])
File "C:\Users\pgsid\Envs\xo\lib\site-packages\sqlalchemy\orm\", line 2457, in __getitem__
return list(res)
File "C:\Users\pgsid\Envs\xo\lib\site-packages\sqlalchemy\orm\", line 86, in instances
File "C:\Users\pgsid\Envs\xo\lib\site-packages\sqlalchemy\util\", line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "C:\Users\pgsid\Envs\xo\lib\site-packages\sqlalchemy\orm\", line 71, in instances
rows = [proc(row) for row in fetch]
File "C:\Users\pgsid\Envs\xo\lib\site-packages\sqlalchemy\orm\", line 428, in _instance
loaded_instance, populate_existing, populators)
File "C:\Users\pgsid\Envs\xo\lib\site-packages\sqlalchemy\orm\", line 486, in _populate_full
dict_[key] = getter(row)
TypeError: an integer is required

The query is
result = User.query.filter_by(id=idd).first()
with idd of type

The type of the ID field in MySQL db is

and the model is like this

class User(db.Model):
__tablename__ = 'user'
id = db.Column('id', db.INT, primary_key=True)
name = db.Column(db.VARCHAR, index=True)
post = db.Column(db.VARCHAR, nullable=True)
type = db.Column(db.VARCHAR)
url = db.Column(db.VARCHAR, nullable=True)
subtype = db.Column(db.VARCHAR, nullable=True)
tel = db.Column(db.VARCHAR, nullable=True)
address = db.Column(db.VARCHAR, nullable=True)
latitude = db.Column(db.FLOAT)
longitude = db.Column(db.FLOAT)
deleted = db.Column(db.Boolean, default=False, index=True)
children = db.relationship("Children")

The database is initialized as such:

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:test@localhost/mydb'
db = SQLAlchemy(app)

What could be wrong? Any suggestions?

Answer Source

Turns out it was the fact that in the MySQL schema for some reason deleted was defined as a BIT type.

All type combinations in SQLAlchemy failed. I had to change the schema to make it into a TINYINT(1)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download