V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
推荐学习书目
Learn Python the Hard Way
Python Sites
PyPI - Python Package Index
http://diveintopython.org/toc/index.html
Pocoo
值得关注的项目
PyPy
Celery
Jinja2
Read the Docs
gevent
pyenv
virtualenv
Stackless Python
Beautiful Soup
结巴中文分词
Green Unicorn
Sentry
Shovel
Pyflakes
pytest
Python 编程
pep8 Checker
Styles
PEP 8
Google Python Style Guide
Code Style from The Hitchhiker's Guide
Mephisto233
V2EX  ›  Python

在 flask 中使用 pymysql 报错,望大神解答!感激不尽!

  •  
  •   Mephisto233 · 2019-01-18 10:08:20 +08:00 · 2716 次点击
    这是一个创建于 1896 天前的主题,其中的信息可能已经有所发展或是发生改变。

    本人主做 iOS 开发,自学 flask, 前段时间做了这个小应用,使用 flask_restful 写了后台,flask_admin 做后台管理,项目稳定运行了好几个月,但是在一个新版本更新后,点击 flask_admin 后台管理界面的时偶尔触发一个 pymysql 错误,非必现,后台查看错误日志如下:

    Python main interpreter initialized at 0x14a1ed0
    uWSGI running as root, you can use --uid/--gid/--chroot options
    *** WARNING: you are running uWSGI as root !!! (use the --uid flag) ***
    python threads support enabled
    your server socket listen backlog is limited to 100 connections
    your mercy for graceful operations on workers is 60 seconds
    "acgpicture.log" [readonly] 26068L, 5473340C                                                               3,1           Top
        conn.query(q)
      File "/home/ubuntu/ACGPictureFlask/ACGPicture_Flask/venv/lib/python3.6/site-packages/pymysql/connections.py", line 893, in query
        self._affected_rows = self._read_query_result(unbuffered=unbuffered)
      File "/home/ubuntu/ACGPictureFlask/ACGPicture_Flask/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1103, in _read_query_result
        result.read()
      File "/home/ubuntu/ACGPictureFlask/ACGPicture_Flask/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1396, in read
        first_packet = self.connection._read_packet()
      File "/home/ubuntu/ACGPictureFlask/ACGPicture_Flask/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1059, in _read_packet
        packet.check_error()
      File "/home/ubuntu/ACGPictureFlask/ACGPicture_Flask/venv/lib/python3.6/site-packages/pymysql/connections.py", line 384, in check_error
        err.raise_mysql_exception(self._data)
      File "/home/ubuntu/ACGPictureFlask/ACGPicture_Flask/venv/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
        raise errorclass(errno, errval)
    sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1054, "Unknown column 'FALSE' in 'order clause'") [SQL: 'SELECT user.id AS user_id, user.email AS user_email, user.password AS user_password, user.telephone AS user_telephone, user.username AS user_username, user.`headImageUrl` AS `user_headImageUrl`, user.is_vip AS user_is_vip, user.vip_grade AS user_vip_grade, user.pic_package AS user_pic_package, user.`deadLineTime` AS `user_deadLineTime`, user.`adsID` AS `user_adsID`, user.uuid AS user_uuid, user.creat_time AS user_creat_time, user.login_time AS user_login_time, user.last_login_time AS user_last_login_time, user.vip_id AS user_vip_id \nFROM user ORDER BY false DESC \n LIMIT %(param_1)s'] [parameters: {'param_1': 20}] (Background on this error at: http://sqlalche.me/e/2j85)
    [pid: 25290|app: 0|req: 19246/21506] 222.211.233.18 () {46 vars in 1022 bytes} [Fri Jan 18 09:38:39 2019] GET /admin/user/ => generated 291 bytes in 10 msecs (HTTP/1.1 500) 2 headers in 84 bytes (1 switches on core 1)
    

    该界面实现代码如下:

    class UserView(ModelView):
        def is_accessible(self):
            return login.current_user.is_authenticated
    
        column_list = ('id', 'email', 'pic_package', 'is_vip', 'creat_time', 'last_login_time')
    
        def date(v, c, m, p): 
            return time.strftime( "%Y-%m-%d %H:%M:%S", time.localtime(m.last_login_time))
    
        column_formatters = dict(last_login_time=date)
    
        column_searchable_list = ('email', 'username', 'uuid')
        column_default_sort = ('is_vip', True)
    
        def __init__(self, session, **kwargs):
            super(UserView, self).__init__(User, session, **kwargs)
    

    用户的模型代码如下:

    class User(db.Model):
        __tablename__ = 'user'
        id = db.Column(db.Integer, primary_key=True, autoincrement=True)
        email = db.Column(db.String(50), nullable=True)
        password = db.Column(db.String(100), nullable=False)
        telephone = db.Column(db.String(11), nullable=True)
        username = db.Column(db.String(50), nullable=True)
        headImageUrl = db.Column(db.String(200), nullable=True, comment="头像")
        pictures = db.relationship('Picture',
                                   secondary=user_pictures,
                                   backref=db.backref('users', lazy='dynamic'),
                                   lazy='dynamic')
        is_vip = db.Column(db.Boolean, nullable=False,
                           default=False, comment="是否 Vip")
        vip_grade = db.Column(db.Integer, nullable=False,
                              default=VipType.NoVipType.value, comment="会员等级")
        pic_package = db.Column(db.Text, nullable=True)
        deadLineTime = db.Column(db.String(200), default=None, comment="过期时间")
        adsID = db.Column(db.String(100), nullable=True, default=ADSID)
        uuid = db.Column(db.String(50), nullable=False, comment="uuid")
        creat_time = db.Column(db.DateTime, default=datetime.now)
        login_time = db.Column(db.Integer)
        last_login_time = db.Column(db.Integer)
        vip_id = db.Column(db.Integer, db.ForeignKey('vip.id'))
    
        def check_password_email(password, checkPassword):
            return (password == md5(checkPassword))
    
        def get(self, id):
            return self.query.filter_by(id=id).first()
    
        def add(user):
            db.session.add(user)
            return session_commit()
    
        def update(self):
            return session_commit()
    
        def delete(self, id):
            self.query.filter_by(id=id).delete()
            return session_commit()
    
        @property
        def is_authenticated(self):
            return True
    
        @property
        def is_active(self):
            return True
    
        @property
        def is_anonymous(self):
            return False
    
        def get_id(self):
            return self.id
    
    

    谷歌好久之后都没有结果,望大神不吝赐教,感激不尽!

    python 版本: 3.6.5
    Flask==1.0.2
    Flask-Admin==1.5.1
    Flask-RESTful==0.3.6
    Flask-Script==2.0.6
    Flask-SQLAlchemy==2.3.2
    PyMySQL==0.8.1
    SQLAlchemy==1.2.7
    
    9 条回复    2019-01-19 12:57:16 +08:00
    Horla
        1
    Horla  
       2019-01-18 10:25:38 +08:00
    ```sql
    SELECT user.id AS user_id, user.email AS user_email, user.password AS user_password, user.telephone AS user_telephone, user.username AS user_username, user.`headImageUrl` AS `user_headImageUrl`, user.is_vip AS user_is_vip, user.vip_grade AS user_vip_grade, user.pic_package AS user_pic_package, user.`deadLineTime` AS `user_deadLineTime`, user.`adsID` AS `user_adsID`, user.uuid AS user_uuid, user.creat_time AS user_creat_time, user.login_time AS user_login_time, user.last_login_time AS user_last_login_time, user.vip_id AS user_vip_id \nFROM user ORDER BY false DESC \n LIMIT %(param_1)s'
    ```

    报错信息里有的啊,你的 SQL 里有一段`ORDER BY false DECS`,查询写的不对。
    myyou
        2
    myyou  
       2019-01-18 10:30:48 +08:00
    column_default_sort = ('is_vip', True) 这个不应该写 True 吧
    yangsi
        3
    yangsi  
       2019-01-18 10:43:50 +08:00 via iPhone
    column_default_sort ( is_vip,True ) 是不是这个问题。is_vip 里面有的是 false 就报错。


    改一个排序字段试下。
    yangsi
        4
    yangsi  
       2019-01-18 10:46:17 +08:00 via iPhone
    (字段,True )
    默认正序,加上 True 是倒序
    Mephisto233
        5
    Mephisto233  
    OP
       2019-01-18 14:06:45 +08:00
    @myyou @yangsi 改完之后好像确实没有报错了,我在观察看看,非常感谢!
    iamobj
        6
    iamobj  
       2019-01-18 14:27:46 +08:00
    老哥,我想问下你是怎么自学的 flask,有没有资源,我也一直又计划学习,无赖看官方文档,看不太明白,有没有其它什么资源
    iamobj
        7
    iamobj  
       2019-01-18 15:18:31 +08:00
    @Mephisto233 老哥,我想问下你是怎么自学的 flask,有没有资源,我也一直又计划学习,无赖看官方文档,看不太明白,有没有其它什么资源
    Mephisto233
        8
    Mephisto233  
    OP
       2019-01-18 23:44:17 +08:00
    @iamobj 我的话一开始找了份免费的 flask 基础教学视频看了,大概了解了下 flask 的基本使用,因为自己是做 iOS 开发的,一开始明确目标就是给前端写 Api,所以锁定了 Flask-RESTful 框架,然后开始构思做项目,从简单的功能开始做起来,边学边做,看官方文档,搜博客解决实际问题,有一个明确的目标就感觉学的很快,现在我那个小应用也已经上线好几个月了,虽然还是很菜,但应该算是入门了吧😁,免费的资源慕课网,网易云课堂,腾讯视频啥的都有,甚至 bilibili 上都有...这个你可以搜搜看,还有就是 Flask-RESTful 的官方文档感觉很不错,有中文翻译的,仔细看看感觉能收获很多,
    iamobj
        9
    iamobj  
       2019-01-19 12:57:16 +08:00 via iPhone
    @Mephisto233 多谢指路,我情况也和你差不多,也就是写 api 用😁
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5324 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 09:26 · PVG 17:26 · LAX 02:26 · JFK 05:26
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.