How to catch exceptions from PyMSSQL

Question

I am using PyMSSQL to connect to a database. However, if I enter the incorrect details or there is some other problem, PyMSSQL throws an exception. However, I cannot work out how to catch the exception. I've used every variation I can think of but I can't seem to catch the exception in a graceful way.

My connection code is:

import pymssql
import getpass

tempServer = input("Enter host: ")
tempUser = input("Enter user: ")
tempPwd = getpass.getpass("Enter password: ")

try:
    phjTempConnection = pymssql.connect(server = tempServer,
                                        user = tempUser,
                                        password = tempPwd,
                                        port = '1433')
except pymssql.MSSQLDatabaseException:
    print("ERROR")

If I enter nonsense into the input fields, I get the following output:

---------------------------------------------------------------------------
MSSQLDatabaseException                    Traceback (most recent call last)
pymssql.pyx in pymssql.connect (pymssql.c:10734)()

_mssql.pyx in _mssql.connect (_mssql.c:21821)()

_mssql.pyx in _mssql.MSSQLConnection.__init__ (_mssql.c:6581)()

_mssql.pyx in _mssql.maybe_raise_MSSQLDatabaseException (_mssql.c:17524)()

MSSQLDatabaseException: (18456, b'Unknown error')

During handling of the above exception, another exception occurred:

OperationalError                          Traceback (most recent call last)
<ipython-input-21-22d7fd0e3d05> in <module>()
     11                                         password = tempPwd,
---> 12                                         port = '1433')
     13 except pymssql.MSSQLDatabaseException:

pymssql.pyx in pymssql.connect (pymssql.c:10824)()

OperationalError: (18456, b'Unknown error')

During handling of the above exception, another exception occurred:

AttributeError                            Traceback (most recent call last)
<ipython-input-21-22d7fd0e3d05> in <module>()
     11                                         password = tempPwd,
     12                                         port = '1433')
---> 13 except pymssql.MSSQLDatabaseException:
     14     print("ERROR")

AttributeError: 'module' object has no attribute 'MSSQLDatabaseException'

I would have thought the output would provide enough information to be able to work out how to catch the MSSQLDatabaseException exception but I've tried all sorts of variations with no success.

How can I use the output to work out how the catch the exception that's been raised?

Answer 1

Using the original code, you can find the type of error as shown below.

try:
    phjTempConnection = pymssql.connect(server = tempServer,
                                        user = tempUser,
                                        password = tempPwd,
                                        port = '1433')
except Exception as e:
   print(type(e))

Answer 2

With Python3.7 and recent version of pymssql, you cannot use import _mssql as you will get No module named '_mssql'. To reference, instead use pymssql._mssql....

Also, I don't see any advantage of calling _mssql.connect, so I've illustrated the proper pymssql method below.

For Example:

import pymssql
import getpass

tempServer = input("Enter host: ")
tempUser = input("Enter user: ")
tempPwd = getpass.getpass("Enter password: ")

try:
    phjTempConnection = pymssql.connect(server = tempServer,
                                       user = tempUser,
                                       password = tempPwd,
                                       port = '1433')

except pymssql._mssql.MssqlDriverException:
    print("A MSSQLDriverException has been caught.")

except pymssql._mssql.MssqlDatabaseException as e:
    print("A MSSQLDatabaseException has been caught.")
    print('Number = ',e.number)
    print('Severity = ',e.severity)
    print('State = ',e.state)
    print('Message = ',e.message)

Answer 3

try:
phjTempConnection = pymssql.connect(server = tempServer, user = tempUser, password = tempPwd, port = '1433')
except Exception as e:
var_err = str(e)
print("MSSQLDriverException or MSSQLDriverError \n", var_err )

Note that 'Exception' is the base error handling. You can look for the error in the end of var_err

Answer 4

After a bit more exploring, I've found a reasonable solution. With the code set out in the original question (using pymssql.connect() ), there were two potential Exceptions that were raised. If a correct server address was entered but incorrect username or password, a MSSQLDatabaseException was raised. If the server address was incorrect, however, a MSSQLDriverException was raised (but see note at end). To exit gracefully from these errors, it seems to be necessary to catch pymssql.InterfaceError (catches the MSSQLDriverException) and pymssql.DatabaseError (catches the MSSQLDatabaseError).

import pymssql
import getpass

tempServer = input("Enter host: ")
tempUser = input("Enter user: ")
tempPwd = getpass.getpass("Enter password: ")

try:
    phjTempConnection = pymssql.connect(server = tempServer,
                                        user = tempUser,
                                        password = tempPwd,
                                        port = '1433')
except pymssql.InterfaceError:
    print("A MSSQLDriverException has been caught.")

except pymssql.DatabaseError:
    print("A MSSQLDatabaseException has been caught.")

Alternatively, can catch the exceptions generated using _mysql as hinted at by match in comments. (But, in this case, there were problems with subsequently importing data into a Pandas dataframe because _mssql does not have a Cursor attribute.)

import pymssql
import _mssql
import getpass

tempServer = input("Enter host: ")
tempUser = input("Enter user: ")
tempPwd = getpass.getpass("Enter password: ")

try:
    phjTempConnection = _mssql.connect(server = tempServer,
                                       user = tempUser,
                                       password = tempPwd,
                                       port = '1433')

except _mssql.MssqlDriverException:
    print("A MSSQLDriverException has been caught.")

except _mssql.MssqlDatabaseException as e:
    print("A MSSQLDatabaseException has been caught.")
    print('Number = ',e.number)
    print('Severity = ',e.severity)
    print('State = ',e.state)
    print('Message = ',e.message)

Hope that will be useful for others.

(N.B. If the address of the SQL SERVER is entered incorrectly the first time that either of these functions is run, a MSSQLDriverException is raised. If a correct server address is entered (but incorrect user and password) then a MSSQLDatabaseException is raised. However, if an incorrect server is subsequently entered, a MSSQLDatabaseException will continue to be raised rather than a MSSQLDriverException as in the first case.)

Answer 5

The documentation implies that the exception is part of the _mssql module, so you may need to catch _mssql.MSSQLDatabaseException here.

http://pymssql.org/en/stable/_mssql_examples.html