代码:
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
”
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
效果: