Обработка ошибок sql python

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.

1. Home
2. MySQL Connector/Python Tutorial
3. Exception Handling in Connector/Python

Last updated on July 27, 2020

Error happens all the time when programming, so its better to equip yourself how to deal with them.

Errors and Warnings #

There are two levels of error message severity in MySQL.

1. Error
2. Warning

Error #

An Error indicates a problem with query or command which prevented it from being executed.

mysql> select * from earth;
ERROR 1146 (42S02): Table 'world.earth' doesn't exist

Warning #

Warning tells you some thing unexpected has happened that could cause problem down the line, but it is not severe enough to stop the statement from being executed. We can display warnings using SHOW WARNINGS; command. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

mysql> 
mysql> CREATE database if not exists world;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 1007 | Can't create database 'world'; database exists |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> SELECT 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.12 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)
mysql>

Error Codes #

Errors and warnings in MySQL contains three pieces of information:

mysql> select * from earth;
ERROR 1146 (42S02): Table 'world.earth' doesn't exist

1. A unique MySQL specific error code (1146) that is not portable to other databases.

2. A 5 character code (42S02) called SQLSTATE which is used to indicate success or failure of the operation. The SQLSTATE is portable across other databases. It consists of two parts: the first two character represents SQL error class and the next three represents subclass. Each class can belong to one of the following four categories.

   1. Success (class 00)
   2. Warning (class 01)
   3. No Data (class 02)
   4. Exception (all the others 07-HZ)

3. A textual description of the error.

So why two error codes?

This is because SQLSTATE represents a group of errors. As a result, we can only use it to handle generic errors. If you want to handle some specific error use MySQL-specific error code.

There are hundreds of MySQL-specific error codes. Fortunately, you don’t need to memorize them as the mysql.connector.errorcode module contains all the MySQL-specific error codes.

>>> 
>>> from mysql.connector import errorcode
>>> errorcode.ER_BAD_TABLE_ERROR
1051
>>>

Exceptions Classes #

All the exception classes for dealing with error and warnings are defined in mysql.connector.errors module.

The mysql.connector.errors.Error is the base class for all the other exceptions. We can use it to catch any kind of exception.

Here is an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """
CREATE TABLE `city` (
  `ID` int(11) AUTO_INCREMENT PRIMARY KEY,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT ''  
)
"""

try:
    cursor.execute(sql)
    db.commit()

except mysql.connector.Error as err:
    print(err)
    print("Error Code:", err.errno)
    print("SQLSTATE", err.sqlstate)
    print("Message", err.msg)

cursor.close()
db.close()

Expected Output:

1050 (42S01): Table 'city' already exists
Error Code: 1050
SQLSTATE 42S01
Message Table 'city' already exists

When an exception is thrown you have access to error code, SQLSTATE and error message in the form of errno, sqlstate and msg attributes of the exception object.

The mysql.connector.errors.Error base class is further subclassed into the following three class:

1. DatabaseError
2. InterfaceError
3. PoolError

Let’s discuss them one by one.

1. DatabaseError: This exception is raised for errors related to the database. It can catch a variety of errors like problem in data processing, error in SQL syntax, MySQL internal problems etc. If a connection is made and a problem arises then DatabaseError will catch it. There are 6 types of DatabaseError:

   1. DataError
   2. InternalError
   3. IntegrityError
   4. OperationalError
   5. NotSupportedError
   6. ProgrammingError

   ---

   1. DataError: This error indicates a problem with the data processing, like division by zero, numeric value out of range, invalid type etc.
   2. InternalError: This exception is raised when the database encounters an internal error. For e.g invalid cursor, transaction out of sync etc.
   3. IntegrityError: This exception is raised when the foreign key constraint fails.
   4. OperationalError: This exception is raised for things that are not in control of the programmer. For e.g unexpected disconnect, error in memory allocation etc, transaction failure, selected database not exists.
   5. NotSupportedError: This exception is raised when you invoke method or api that is not supported. For example, calling rollback() on a connection that doesn’t support the transaction.
   6. ProgrammingError: This exception is raised of programming errors. For e.g table not found or already exists, error in MySQL syntax, wrong number of parameters specified, wrong connection credentials etc.

2. InterfaceError: This exception is raised for errors related to the interface (in our case interface is MySQL Connector/Python) rather than the database itself.

3. PoolError: It is raised for errors related to connection pooling. We will learn about the connection pooling in the upcoming chapter.

The following figure shows the hierarchy of the exception classes:

StandardError
|__ Error
    |__ InterfaceError
    |__ PoolError
    |__ DatabaseError
        |__ DataError
        |__ InternalError 
        |__ IntegrityError
        |__ OperationalError        
        |__ NotSupportedError
        |__ ProgrammingError

All the exception classes are mapped to one or more SQLSTATE class, you can print this mapping by typing the following command.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

>>>
>>> from mysql.connector import errors
>>> from pprint import pprint
>>> 
>>> pprint(errors._SQLSTATE_CLASS_EXCEPTION)
{'02': <class 'mysql.connector.errors.DataError'>,
 '07': <class 'mysql.connector.errors.DatabaseError'>,
 '08': <class 'mysql.connector.errors.OperationalError'>,
 '0A': <class 'mysql.connector.errors.NotSupportedError'>,
 '0K': <class 'mysql.connector.errors.OperationalError'>,
...
 'HY': <class 'mysql.connector.errors.DatabaseError'>,
 'HZ': <class 'mysql.connector.errors.OperationalError'>,
 'XA': <class 'mysql.connector.errors.IntegrityError'>}
>>> 
>>>

Let’s look at some examples now.

Example 1: Handling the Generic Error

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """
select * from town limit 5
"""

try:
    cursor.execute(sql)

    for row in cursor:
        print(row)


    cursor.close()
    db.close()

except mysql.connector.ProgrammingError as err:
    print(err.errno)
    print(err.sqlstate)
    print(err.msg)

except mysql.connector.Error as err:
    print(err)

Remember that ProgrammingError can catch a variety of exceptions ranging from syntax error to table not found. If you want to catch some specific error use the errorcode module.

Example 2: Handling a specific error

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

import mysql.connector
from mysql.connector import errorcode

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """
select * from town limit 5
"""

try:
    cursor.execute(sql)

    for row in cursor:
        print(row)

    db.close()

except mysql.connector.ProgrammingError as err:
    if errorcode.ER_NO_SUCH_TABLE == err.errno:
        print("No table exists")
    else:
        print("Table exists")
        print(err)

except mysql.connector.Error as err:
    print("Some other error")
    print(err)

Example 3: Handling multiple errors in the same way

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """
insert into city(Name, CountryCode, District, Population)
VALUES ('Kabul', 'AFGANISTAN', 'Kabol', 1780000)
"""

try:
    cursor.execute(sql)

    db.commit()
    db.close()

except (mysql.connector.IntegrityError, mysql.connector.DataError) as err:
    print("DataError or IntegrityError")
    print(err)

except mysql.connector.ProgrammingError as err:
    print("Programming Error")
    print(err)

except mysql.connector.Error as err:
    print(err)

Handling Warnings #

By default, MySQL Connector/Python neither fetch warnings nor raise an exception on warnings. But, we can change that using the following arguments of the connect() function.

The above two arguments are also available as properties of the connection object, which can be used to set and retrieve the current setting.

import mysql.connector
from mysql.connector import errors

db = mysql.connector.connect(option_files='my.conf')

db.get_warnings = True  # this is not required if raise_on_warnings=True
db.raise_on_warnings = True

Once you have set get_warnings=True (or raise_on_warnings=True), to retrieve the actual warning use the fetchwarnings() method of the cursor object. The fetchwarnings() method returns a list of tuples containing message-level, error code and the message itself generated by the previously executed query.

The following example demonstrates how to fetch warnings produced by the queries:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

import mysql.connector

db = mysql.connector.connect(option_files='my.conf',
                             get_warnings=True)

# db.get_warnings = True  # we could have set get_warnings like this too

cursor = db.cursor()

cursor.execute("CREATE database if not exists world;")
print(cursor.fetchwarnings())

cursor.execute("select 1/0;")
cursor.fetchall() # fetch all the rows from the result set
print(cursor.fetchwarnings())

Expected Output:

[('Note', 1007, "Can't create database 'world'; database exists")]
[('Warning', 1365, 'Division by 0')]

Note that, if the query returns result set (like the SELECT statement) then it is necessary for you to fetch all the rows before calling the fetchwarnings() method, otherwise, the fetchwarnings() method would return None.

Here is an example which shows how to raise exceptions on warnings.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

import mysql.connector
from mysql.connector import errors

db = mysql.connector.connect(option_files='my.conf',
                             raise_on_warnings=True)

# db.raise_on_warnings = True  # we could have set raise_on_warnings like this

cursor = db.cursor()

try:

    cursor.execute("CREATE database if not exists world;")
    print(cursor.fetchwarnings())

    cursor.execute("select 1/0;")
    print(cursor.fetchall()) # fetch all the rows from the result set
    print(cursor.fetchwarnings())

except errors.DatabaseError as e:
    print(e)

except:
    print('some error occurred')

Expected Output:

1007: Can't create database 'world'; database exists

Note that setting raise_on_warnings=True implicitly sets get_warnings=True.

You should now have a pretty good idea of how to handle errors in the next few pages we will see some practical examples of SELECT, INSERT, UPDATE and DELTETE statements:

There are many sources of errors. A few examples are a syntax error in an executed SQL statement, a connection failure, or calling the fetch method for an already canceled or finished statement handle.

The DB API defines a number of errors that must exist in each database module. The following table lists these exceptions.

Your Python scripts should handle these errors, but before using any of the above exceptions, make sure your MySQLdb has support for that exception. You can get more information about them by reading the DB API 2.0 specification.