python mysql 错误处理_Python-MySQL中的错误处理

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.

解决方案

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

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

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

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,])

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.