最新消息:20210917 已从crifan.com换到crifan.org

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

MySQL crifan 8882浏览 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,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

网友最新评论 (3)

  1. 我要说点啥呢,你还是帮到了我的,但是亲 你这文章写的有点太通俗易懂了哈哈哈, 最重要的ProgrammingError让你硬生生的给人家强转成了str,还傻傻的拆包可不就报错了吧,直接不需要转str方法,ProgrammingError.args拆包就得到了status和massage信息了
    xiaobaiya4年前 (2019-11-13)回复
  2. 代码排版敢不敢改下
    aaad5年前 (2019-09-19)回复
    • 已更新代码排版。 注:之前不是不想优化,而是几千个帖子,没时间一个个优化 😂
      crifan5年前 (2019-09-23)回复
97 queries in 0.195 seconds, using 23.42MB memory