Python-MySQL中的错误处理 [英] Error handling in Python-MySQL

问题描述

我正在运行一个基于python flask的小型Web服务，我想在其中执行一个小型MySQL查询。当我获得SQL查询的有效输入时，一切都按预期工作，并且我获得了正确的值。但是，如果该值未存储在数据库中，则会收到 TypeError

I am running a little webservice based on python flask, where I want to execute a small MySQL Query. When I get a valid input for my SQL query, everything is working as expected and I get the right value back. However, if the value is not stored in the database I receive a TypeError

    Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1478, in full_dispatch_request
    response = self.make_response(rv)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1566, in make_response
    raise ValueError('View function did not return a response')
ValueError: View function did not return a response

我试图利用错误处理自己的代码并将其用于我的项目，但似乎无法正常工作。

I tried to tap into error handling myself and use this code for my project, but it seems like this doesn't work properly.

#!/usr/bin/python

from flask import Flask, request
import MySQLdb

import json

app = Flask(__name__)


@app.route("/get_user", methods=["POST"])
def get_user():
    data = json.loads(request.data)
    email = data["email"]

    sql = "SELECT userid FROM oc_preferences WHERE configkey='email' AND configvalue LIKE '" + email + "%';";

    conn = MySQLdb.connect( host="localhost",
                            user="root",
                            passwd="ubuntu",
                            db="owncloud",
                            port=3306)
    curs = conn.cursor()

    try:
        curs.execute(sql)
        user = curs.fetchone()[0]
        return user
    except MySQLdb.Error, e:
        try:
            print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
            return None
        except IndexError:
            print "MySQL Error: %s" % str(e)
            return None
    except TypeError, e:
        print(e)
        return None
    except ValueError, e:
        print(e)
        return None
    finally:
        curs.close()
        conn.close()

if __name__ == "__main__":
    app.run(host="0.0.0.0", port=5000, debug=True)

基本上，我只是想返回一个值，当一切正常时，并且如果服务器上没有错误消息，我什么也不希望返回。如何正确使用错误处理？

Basically I just want to return a value, when everything is working properly and I want to return nothing if it isn't preferably with an error message on my server. How can I use error handling in a proper way?

编辑更新了当前代码+错误消息。

EDIT Updated current code + error message.

推荐答案

第一点：try / except块中的代码太多。当您有两个可能引起不同错误的语句（或两组语句）时，最好使用不同的try / except块：

First point: you have too much code in your try/except block. Better to use distinct try/except blocks when you have two statements (or two groups of statements) that may raise different errors:

try:
    try:
        curs.execute(sql)
        # NB : you won't get an IntegrityError when reading
    except (MySQLdb.Error, MySQLdb.Warning) as e:
        print(e)
        return None

    try: 
        user = curs.fetchone()[0]
        return user
    except TypeError as e:
        print(e)
        return None

finally:
    conn.close()

现在您真的必须在这里捕获TypeError吗？如果您在回溯中阅读，您会发现您的错误是由于在 None （nb）上调用 __ getitem __（） ： __ getitem __（）是下标运算符 [] ）的实现，这意味着如果没有匹配的行 cursor.fetchone（）返回 None ，因此您可以测试 currsor.fetchone的返回值（）：

Now do you really have to catch a TypeError here ? If you read at the traceback, you'll notice that your error comes from calling __getitem__() on None (nb : __getitem__() is implementation for the subscript operator []), which means that if you have no matching rows cursor.fetchone() returns None, so you can just test the return of currsor.fetchone():

try:
    try:
        curs.execute(sql)
        # NB : you won't get an IntegrityError when reading
    except (MySQLdb.Error, MySQLdb.Warning) as e:
        print(e)
        return None

    row = curs.fetchone()
    if row:
        return row[0]
    return None

finally:
    conn.close()

现在您真的需要在这里捕获MySQL错误吗？您的查询应该经过良好的测试，并且只是读取操作，因此不会崩溃-因此，如果您在此处遇到问题，那么显然会有更大的问题，并且不想将其隐藏在表层之下。 IOW：记录异常（使用标准的 logging 包和 logger.exception（））并重新引发它们或更简单地让它们传播（并最终让更高级别的组件负责记录未处理的异常）：

Now do you really need to catch MySQL errors here ? Your query is supposed to be well tested and it's only a read operation so it should not crash - so if you have something going wrong here then you obviously have a bigger problem, and you don't want to hide it under the carpet. IOW: either log the exceptions (using the standard logging package and logger.exception()) and re-raise them or more simply let them propagate (and eventually have an higher level component take care of logging unhandled exceptions):

try:
    curs.execute(sql)
    row = curs.fetchone()
    if row:
        return row[0]
    return None

finally:
    conn.close()

最后：构建SQL查询的方式是完全不安全。改用sql占位符：

And finally: the way you build your sql query is utterly unsafe. Use sql placeholders instead:

q = "%s%%" % data["email"].strip() 
sql = "select userid from oc_preferences where configkey='email' and configvalue like %s"
cursor.execute(sql, [q,])

哦，是的：wrt / View函数未返回响应 ValueError，这是因为，好，您的视图返回 None 在许多地方。烧瓶视图应该返回可以用作HTTP响应的内容，并且 None 在这里不是有效的选项。

Oh and yes: wrt/ the "View function did not return a response" ValueError, it's because, well, your view returns None in many places. A flask view is supposed to return something that can be used as a HTTP response, and None is not a valid option here.

这篇关于Python-MySQL中的错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！