Flask、MySQL和SQLAlchemy查询ID

Question

当我试图按id查询一个项目时，我遇到了一个奇怪的错误。我尝试了所有我找到的建议，只有在执行原始查询时，我才能得到正确的结果。你知道吗

部分回溯：

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

查询是result = User.query.filter_by(id=idd).first()，idd类型为int。你知道吗

MySQL db中ID字段的类型是INT

模型是这样的

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

数据库初始化如下：

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

有什么问题吗？有什么建议吗？你知道吗

Answer 1

更改您的id类型：

id = db.Column('id', db.Integer(), primary_key=True)

Answer 2

事实证明，在MySQL模式中，由于某种原因deleted被定义为BIT类型。你知道吗

SQLAlchemy中的所有类型组合都失败。我不得不更改模式，使其成为TINYINT(1)