代码:
1 2 3 4 5 6 | 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)) |
在调试期间,看到输出是:
1 2 3 | 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,
以便于调试,去看看处理此处特定的错误
写成:
1 2 3 4 5 6 7 8 9 | 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" ) |
结果出错:
1 2 3 | 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
1 2 3 4 5 6 7 8 9 | 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
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 | 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
”
except pymysql.ProgrammingError:
except pymysql.Error:
“
1 2 | errType = type (err) print ( "errType=%s" % errType) #<class 'pymysql.err.ProgrammingError'> |
然后就可以用多种类型错误,去判断了
但是还是没有code之类的字段
而且PyCharm中普通变量看不到对应的值
显示在Special Variables中了:
此处打印出的:
1 | (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:
1 2 3 4 | 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)) |
结果:
1 2 3 | 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映射过来的:
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 31 32 33 | 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的语法错误,就需要定位到,并想办法解决
代码:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 |
效果: