How to get the MySQL type of error with PyMySQL?

Question

I'm doing a Python application with MySQL and PyMySQL and I'd like to be able to know the number of the MySQL error when I get one so that I can do something different depending on it.

Is there a way to do that with a try-except statement or another way?

Answer 1

Any exception in Python has an args member that shows you how it was constructed. For example:

>>> e = Exception(1, 2, 3, 4)
>>> e.args
(1, 2, 3, 4)

For pymysql, they're always constructed with (errno, errorvalue). So:

try:
    do_stuff()
except MySQLError as e:
    print('Got error {!r}, errno is {}'.format(e, e.args[0]))

I'm not sure this is guaranteed by the documentation, but you can see how it works pretty easily from the source.

Answer 2

pymysql maps mysql errors to python errors according to the following table:

_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)
_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)

if you want to catch the errors then you will need to catch ProgrammingError, DataError, IntegrityError, NotSupportedError, and OperationalError, individually. You can see specifically which mysql error was caught by coercing the exception to a string using str.

try:
    #interact with pymysql
except ProgrammingError as e:
    print "Caught a Programming Error:",
    print e

Answer 3

for name, ddl in TABLES.iteritems():
    try:
        print("Creating table {}: ".format(name))
        db.execute(ddl)
    except pymysql.InternalError as error:
        code, message = error.args
        print ">>>>>>>>>>>>>", code, message

That's a start but loads of other errors exist eg. OperationalError