【已解决】python中mysql异常时获取Exception的code

代码：

try:
    cur.execute(executeSql)
db.commit()
    print("[%5d](%s) (%s)" % (curRowNum, dialogA, dialogB))
except Exception as err:
    print("Error %s for execute sql: %s" % (err, executeSql))

在调试期间，看到输出是：

Error (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'Let It Be?"","2018-05-31 12:04:52.240980","2018-05-31 12:04:52.240980",0)\' at line 2') for execute sql:
                      INSERT INTO qa(`id`,`question`,`answer`,`createTime`,`modifyTime`,`source`)
                      VALUES(54,"What a great song.","How about "Let It Be?"","2018-05-31 12:04:52.240980","2018-05-31 12:04:52.240980",0)

想要获取到此处的Exception的code，

以便于调试，去看看处理此处特定的错误

写成：

try:
    cur.execute(executeSql)
db.commit()
    print("[%5d](%s) (%s)" % (curRowNum, dialogA, dialogB))
except Exception as err:
    print("Error %s for execute sql: %s" % (err, executeSql))
    errCode = err[0]
    if errCode == 1064:
        print("debug: syntax error")

结果出错：

    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'Let It Be?"","2018-05-31 13:24:43.949193","2018-05-31 13:24:43.949193",0)\' at line 2')
During handling of the above exception, another exception occurred:

PyCharm中去看看Exception的定义：

也还是没有找到想要的code

python Exception code

8. Errors and Exceptions — Python 2.7.15 documentation

5. Built-in Exceptions — Python 3.6.5 documentation

Python get an error code from exception – Stack Overflow

python: How do I know what type of exception occurred? – Stack Overflow

好像只是打印出exception的name

如果可以except多种异常，那么也可以去找到此处的

mysql的语法错误的exception

然后实现调试特定的错误的目的

官网示例：

https://docs.python.org/2/tutorial/errors.html

import sys
try:f = open('myfile.txt')s =f.readline()i = int(s.strip())
except IOError ase:
    print "I/O error({0}): {1}".format(e.errno,e.strerror)
except ValueError:
    print "Could not convert data to an integer."
except:
    print "Unexpected error:",sys.exc_info()[0]
    raise

是可以有多种异常写法的。

python Exception error code

Custom Python Exceptions with Error Codes and Error Messages – Stack Overflow

这里面是自定义error的code

Python Exception Handling: Getting the error message | Treehouse Community

此处看来先要去搞清楚，此处pymysql抛出的是哪种异常

pymysql exception

python – Handling PyMySql exceptions – Best Practices – Stack Overflow

python – How to get the MySQL type of error with PyMySQL? – Stack Overflow

PyMySQL/err.py at master · PyMySQL/PyMySQL

class MySQLError(Exception):
    """Exception related to operation with MySQL.”""
class Error(MySQLError):
    """Exception that is the base class of all other error exceptions
    (not Warning)."""
class DatabaseError(Error):
    """Exception raised for errors that are related to the
    database."""
class ProgrammingError(DatabaseError):
    """Exception raised for programming errors, e.g. table not found
or already exists, syntax error in the SQL statement, wrong number
of parameters specified, etc."""
_map_error(ProgrammingError, ER.DB_CREATE_EXISTS, ER.SYNTAX_ERROR,
           ER.PARSE_ERROR, ER.NO_SUCH_TABLE, ER.WRONG_DB_NAME,
           ER.WRONG_TABLE_NAME, ER.FIELD_SPECIFIED_TWICE,
           ER.INVALID_GROUP_FUNC_USE, ER.UNSUPPORTED_EXTENSION,
           ER.TABLE_MUST_HAVE_COLUMNS, ER.CANT_DO_THIS_DURING_AN_TRANSACTION,
           ER.WRONG_DB_NAME, ER.WRONG_COLUMN_NAME,
           )
_map_error(DataError, ER.WARN_DATA_TRUNCATED, ER.WARN_NULL_TO_NOTNULL,
           ER.WARN_DATA_OUT_OF_RANGE, ER.NO_DEFAULT, ER.PRIMARY_CANT_HAVE_NULL,
           ER.DATA_TOO_LONG, ER.DATETIME_FUNCTION_OVERFLOW)
_map_error(IntegrityError, ER.DUP_ENTRY, ER.NO_REFERENCED_ROW,
           ER.NO_REFERENCED_ROW_2, ER.ROW_IS_REFERENCED, ER.ROW_IS_REFERENCED_2,
           ER.CANNOT_ADD_FOREIGN, ER.BAD_NULL_ERROR)
_map_error(NotSupportedError, ER.WARNING_NOT_COMPLETE_ROLLBACK,
           ER.NOT_SUPPORTED_YET, ER.FEATURE_DISABLED, ER.UNKNOWN_STORAGE_ENGINE)
_map_error(OperationalError, ER.DBACCESS_DENIED_ERROR, ER.ACCESS_DENIED_ERROR,
           ER.CON_COUNT_ERROR, ER.TABLEACCESS_DENIED_ERROR,
           ER.COLUMNACCESS_DENIED_ERROR, ER.CONSTRAINT_FAILED)

看起来是的：

pymysql把mysql的一些异常，都映射到ProgrammingError

而mysql的很多异常，比如ER.SYNTAX_ERROR，ER.DB_CREATE_EXISTS等等，都映射为了ProgrammingError

而

Exception-》MySQLError-〉Error-》DatabaseError-〉ProgrammingError

所以，此处也再去先通过代码去确定此处exception是否的确是ProgrammingError

pymysql.Error Python Example

”

except pymysql.ProgrammingError:

except pymysql.Error:

“

errType = type(err)
print("errType=%s" % errType) #<class 'pymysql.err.ProgrammingError'>

然后就可以用多种类型错误，去判断了

但是还是没有code之类的字段

而且PyCharm中普通变量看不到对应的值

显示在Special Variables中了：

此处打印出的：

(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'Let It Be?"","2018-06-01 11:41:02.123188","2018-06-01 11:41:02.123188",0)\' at line 2')

难道是tuple：(code, message) ？

去试试

以及找找

<attribute ‘args’ of ‘BaseException’ objects>

中的BaseException

python BaseException

5. Built-in Exceptions — Python 3.6.5 documentation

https://docs.python.org/3/library/exceptions.html#BaseException

还是看不太懂，好像没有要的error的code

突然感到：貌似python中的exception本身就没有code这一说

上面错误信息中的1064数字，是mysql中输出来的信息

所以还是去看看是否是tuple：

except pymysql.ProgrammingError as progErr:
    print("ProgrammingError %s for run sql %s" % (progErr, executeSql))
    (errCode, errMsg) = str(progErr)
    print("errCode=%s, errMsg=%s" % (errCode, errMsg))

结果：

  File "/Users/crifan/dev/dev_root/company/naturling/projects/NLP/sourcecode/naturling/processData/mysqlQa/MongodbToMysql.py", line 197, in insertDialog
    (errCode, errMsg) = str(progErr)
ValueError: too many values to unpack (expected 2)

不是tuple

【总结】

此处的pymysql中调用execute去执行sql语句，当发生异常时，此处只能获得几个有限的异常的类型：

• ProgrammingError
• DataError
• IntegrityError
• NotSupportedError
• OperationalError

对应着是pymysql中的代码

PyMySQL/err.py at master · PyMySQL/PyMySQL

通过map映射过来的：

class MySQLError(Exception):
    """Exception related to operation with MySQL.”""

class Error(MySQLError):
    """Exception that is the base class of all other error exceptions
    (not Warning)."""

class DatabaseError(Error):
    """Exception raised for errors that are related to the
    database."""

class ProgrammingError(DatabaseError):
    """Exception raised for programming errors, e.g. table not found
or already exists, syntax error in the SQL statement, wrong number
of parameters specified, etc."""
_map_error(ProgrammingError, ER.DB_CREATE_EXISTS, ER.SYNTAX_ERROR,
           ER.PARSE_ERROR, ER.NO_SUCH_TABLE, ER.WRONG_DB_NAME,
           ER.WRONG_TABLE_NAME, ER.FIELD_SPECIFIED_TWICE,
           ER.INVALID_GROUP_FUNC_USE, ER.UNSUPPORTED_EXTENSION,
           ER.TABLE_MUST_HAVE_COLUMNS, ER.CANT_DO_THIS_DURING_AN_TRANSACTION,
           ER.WRONG_DB_NAME, ER.WRONG_COLUMN_NAME,
           )
_map_error(DataError, ER.WARN_DATA_TRUNCATED, ER.WARN_NULL_TO_NOTNULL,
           ER.WARN_DATA_OUT_OF_RANGE, ER.NO_DEFAULT, ER.PRIMARY_CANT_HAVE_NULL,
           ER.DATA_TOO_LONG, ER.DATETIME_FUNCTION_OVERFLOW)
_map_error(IntegrityError, ER.DUP_ENTRY, ER.NO_REFERENCED_ROW,
           ER.NO_REFERENCED_ROW_2, ER.ROW_IS_REFERENCED, ER.ROW_IS_REFERENCED_2,
           ER.CANNOT_ADD_FOREIGN, ER.BAD_NULL_ERROR)
_map_error(NotSupportedError, ER.WARNING_NOT_COMPLETE_ROLLBACK,
           ER.NOT_SUPPORTED_YET, ER.FEATURE_DISABLED, ER.UNKNOWN_STORAGE_ENGINE)
_map_error(OperationalError, ER.DBACCESS_DENIED_ERROR, ER.ACCESS_DENIED_ERROR,
           ER.CON_COUNT_ERROR, ER.TABLEACCESS_DENIED_ERROR,
           ER.COLUMNACCESS_DENIED_ERROR, ER.CONSTRAINT_FAILED)

并且：

这些异常的信息中，是没有想要的error的code的。

不过由于错误信息字符串中，有mysql的code，比如1064，所以，最终是：

通过pyhton的re正则，去找到了此处pymysql返回的exception中mysql的error的code，从而实现：此处特定的调试方面的目的，当是mysql的语法错误，就需要定位到，并想办法解决

代码：

executeSql = insertMediaSql % (qaid, dialogA, dialogB, now, now, 0)
try:
    cur.execute(executeSql)
db.commit()
    print("[%5d](%s) (%s)" % (curRowNum, dialogA, dialogB))
except pymysql.ProgrammingError as progErr:
    print("ProgrammingError %s for run sql %s" % (progErr, executeSql))
    progErrStr = str(progErr)
    # (1064, 'You have an error in your SQL syntax; ...
    mysqlErrorCodeMatch = re.search("^\((?P<mysqlErrorCode>\d+),", progErrStr)
    if mysqlErrorCodeMatch:
        mysqlErrorCode = mysqlErrorCodeMatch.group("mysqlErrorCode")
        mysqlErrorCodeInt = int(mysqlErrorCode)
        if mysqlErrorCodeInt == 1064:
            print("Debug: should pay attention for syntax error of mysql: %s" % executeSql)
except Exception as err:
    # errType = type(err)
    # print("errType=%s" % errType) #<class 'pymysql.err.ProgrammingError'>
    print("Error %s for execute sql: %s" % (err, executeSql))
    db.rollback()
    curId += 1
    continue

效果：

转载请注明：在路上 » 【已解决】python中mysql异常时获取Exception的code