最新消息:20181230 VPS服务器已从Linode换到腾讯云香港,主题仍用朋友推荐的大前端D8

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

MySQL crifan 182浏览 0评论

代码:

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

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
61 queries in 0.102 seconds, using 10.01MB memory