Python Can't Catch pymssql Exception

Question

The try/except is not capturing the specified pymssql exception in the following snippet:

import pandas as pd
from fps.databaseconnections import DatabaseConnections
from pymssql import ProgrammingError

DatabaseConnections_instance = DatabaseConnections()
db_cnxn = DatabaseConnections_instance.get_connection("gtaemndprod")

print "\ndb_cnxn is this class: {}\n".format(db_cnxn.__class__)

try:
    pd.read_sql("SELECT blah",db_cnxn)
except ProgrammingError:
    print "A"

Traceback:

db_cnxn is this class: <class 'sqlalchemy.engine.base.Engine'>

Traceback (most recent call last):

  File "<ipython-input-20-9e8944849cfd>", line 11, in <module>
pd.read_sql("SELECT blah",db_cnxn)

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\pandas\io\sql.py", line 515, in read_sql
chunksize=chunksize)

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\pandas\io\sql.py", line 1190, in read_query
result = self.execute(*args)

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\pandas\io\sql.py", line 1081, in execute
return self.connectable.execute(*args, **kwargs)

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py", line 1991, in execute
return connection.execute(statement, *multiparams, **params)

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py", line 906, in execute
return self._execute_text(object, multiparams, params)

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py", line 1054, in _execute_text
statement, parameters

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py", line 1146, in _execute_context
context)

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py", line 1341, in _handle_dbapi_exception
exc_info

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\sqlalchemy\util\compat.py", line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py", line 1139, in _execute_context
context)

  File "C:\Users\212476924\AppData\Local\Continuum\Anaconda2\lib\site-packages\sqlalchemy\engine\default.py", line 450, in do_execute
cursor.execute(statement, parameters)

  File "pymssql.pyx", line 464, in pymssql.Cursor.execute (pymssql.c:7491)

ProgrammingError: (pymssql.ProgrammingError) (207, "Invalid column name 'blah'.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: 'SELECT blah']

The DatabaseConnections class is just an easy way to access some commonly used databases that I need to work with. It returns the database connection engine, db_cnxn. As you can see in the traceback, this is a sqlalchemy.engine.base.Engine object.

Importing the entire pymssql package as shown below produces the same traceback:

import pandas as pd
from fps.databaseconnections import DatabaseConnections
import pymssql

DatabaseConnections_instance = DatabaseConnections()
db_cnxn = DatabaseConnections_instance.get_connection("gtaemndprod")

print "\ndb_cnxn is this class: {}\n".format(db_cnxn.__class__)

try:
    pd.read_sql("SELECT blah",db_cnxn)
except pymssql.ProgrammingError:
    print "A"

Please help! Thank you.

Answer 1

Since you are using sqlalchemy, this is the one you are interested in, not the pymssql one:

from sqlalchemy.exc import ProgrammingError