Python-MySQL 中的错误处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30996401/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error handling in Python-MySQL
提问by BoHyman Horseman
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
我正在运行一个基于 python flask 的小网络服务，我想在其中执行一个小的 MySQL 查询。当我获得 SQL 查询的有效输入时，一切都按预期工作，并且我得到了正确的值。但是，如果该值未存储在数据库中，我会收到一个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?
基本上我只想返回一个值，当一切正常时，如果我的服务器上没有错误消息，我不想返回任何内容。如何以正确的方式使用错误处理？
EDITUpdated current code + error message.
编辑更新当前代码 + 错误消息。
采纳答案by bruno desthuilliers
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/except 块中有太多代码。当您有两个可能引发不同错误的语句（或两组语句）时，最好使用不同的 try/except 块：
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()
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():
现在您真的必须在这里捕获 TypeError 吗？如果你在阅读回溯，你会发现，你的错误来自调用__getitem__()上None（注意：__getitem__()是实施下标运算符[]），这意味着，如果你有没有匹配行cursor.fetchone()的回报None，这样你就可以测试的回报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()
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 loggingpackage 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):
现在您真的需要在这里捕获 MySQL 错误吗？您的查询应该经过良好测试，并且它只是一个读取操作，因此它不应该崩溃 - 所以如果您在这里出现问题，那么您显然有一个更大的问题，并且您不想将其隐藏在地毯下。IOW：要么记录异常（使用标准logging包 and logger.exception()）并重新引发它们，要么更简单地让它们传播（并最终让更高级别的组件负责记录未处理的异常）：
try:
curs.execute(sql)
row = curs.fetchone()
if row:
return row[0]
return None
finally:
conn.close()
And finally: the way you build your sql query is utterly unsafe. Use sql placeholders instead:
最后：您构建 sql 查询的方式是完全不安全的。改用 sql 占位符：
q = "%s%%" % data["email"].strip()
sql = "select userid from oc_preferences where configkey='email' and configvalue like %s"
cursor.execute(sql, [q,])
Oh and yes: wrt/ the "View function did not return a response" ValueError, it's because, well, your view returns Nonein many places. A flask view is supposed to return something that can be used as a HTTP response, and Noneis not a valid option here.
哦，是的：wrt/“视图函数没有返回响应”ValueError，这是因为，你的视图None在很多地方都返回了。Flask 视图应该返回一些可以用作 HTTP 响应的东西，None在这里不是一个有效的选项。
