What exceptions could be returned from Pandas read_sql()

Question

I have a user-defined function that uses pymysql to connect to a mysql database and then it interrogates the database and reads the results into a Pandas dataframe.

import pandas as pd
import pymysql
import getpass

def myGetData(myQuery):

    myServer = 'xxx.xxx.xxx.xxx'
    myUser = input("Enter MySQL database username: ")
    myPwd = getpass.getpass("Enter password: ")

    myConnection = pymysql.connect(host=myServer,user=myUser,password=myPwd)

    myTempDF = pd.io.sql.read_sql(myQuery, con=myConnection)

    myConnection.close()

    return myTempDF

myDF = myGetData("SELECT * FROM `myDB`.`myTable`")

I have written code to catch exceptions arising from pymysql.connect() although I've not shown it here for clarity. I also want to be able to catch any exceptions that might arise from read_sql(). Where can I find a list of exceptions that might be raised? It's not in the Pandas documentation (http://pandas.pydata.org/pandas-docs/version/0.19.2/generated/pandas.read_sql.html) and I can't find any hints online. I could just catch all exceptions but that seems to be generally frowned upon by the Python community. How should I catch exceptions raised by read_sql()?

EDIT

I've done some more work on this and it seems that even when I know what error is being generated, it's not straight-forward to catch the exception. So, for example, in the code given above, if I enter the username and/or password incorrectly, an operational error is generated. The final line or the error report reads something like:

OperationalError: (1045, "Access denied for user 'yyy'@'xxx.xxx.xxx.xxx' (using password: YES)")

I've been able to catch this error using:

try:
    phjConnection = pymysql.connect(host=phjServer, user=phjUser, password=phjPwd)

except pymysql.OperationalError as e:
            print("\nAn OperationalError occurred. Error number {0}: {1}.".format(e.args[0],e.args[1]))

That works fine (although discovering that the OperationalError needed to be caught using pymysql.OperationalError was by chance).

Now, in the next part of the function, Pandas function real_sql() uses the connection created above to run a SQL query. If I include a purposely incorrect query that has an incorrect table name, then another OperationalError occurs followed by a DatabaseError:

OperationalError: (1142, "SELECT command denied to user 'yyy'@'xxx.xxx.xxx.xxx' for table 'table'")

During handling of the above exception, another exception occurred:

DatabaseError: Execution failed on sql 'SELECT * FROM `db`.`table`': (1142, "SELECT command denied to user 'yyy'@'xxx.xxx.xxx.xxx' for table 'table'")

But I am now completely mystified as to how I catch this second OperationalError. The pymysql.OperationalError used previously doesn't work. I've tried almost everything I can think of and still can't catch the error. Shouldn't the error message be a little more informative about how the error was generated and how it can be caught? Clearly, I'm missing something obvious but I just can't find the solution. Any suggestions would be appreciated.

EDIT 2

In response to the comment, I am now catching exceptions as follows:

import pandas as pd
import pymysql
import getpass

def myGetData(myQuery):

    myServer = 'xxx.xxx.xxx.xxx'
    myUser = input("Enter MySQL database username: ")
    myPwd = getpass.getpass("Enter password: ")

    try:
        myConnection = pymysql.connect(host=myServer,user=myUser,password=myPwd)

    except pymysql.OperationalError as e:
        # Catching this exception works fine if, for example,
        # I enter the wrong username and password
        print("\nAn OperationalError occurred. Error number {0}: {1}.".format(e.args[0],e.args[1]))

    try:
        myTempDF = pd.io.sql.read_sql(myQuery, con=myConnection)

    except pymysql.OperationalError as e:
        # However, this error isn't picked up following an incorrect
        # SQL query despite the error message saying that an
        # OperationalError has occurred.
        # Many variations on this theme have been tried but failed.
        print("\nAn error occurred. Error number {0}: {1}.".format(e.args[0],e.args[1]))

    myConnection.close()

    return myTempDF

myDF = myGetData("SELECT * FROM `myDB`.`myTable`")

Solution 1

Good question, note, read_sql is a wrapper around 'read_sql_table and read_sql_query. Reading through the source, a ValueError is consistently thrown inside the parent and the helper functions. So you can safely catch a ValueError and handle appropriately. (Do have a look at the source)

Solution 2

I just stumbled on this in a similar problem and found the answer to seek the exception from SQLalchemy..

try:
        df = pd.read_sql_query(QUERY, engine)
    except sqlalchemy.exc.OperationalError as e:
        logger.Info('Error occured while executing a query {}'.format(e.args))

more information can be found here. SQL Alchemy Docs

Comments

[user1718097]

Thanks for the suggestion...I'll take a look.

[MaxU - stop genocide of UA]

It's a good hint (++). @user1718097, you might also want to check SQLAlchemy exceptions (as Pandas uses this module in the to_sql()) in the corresponding source files...

[user1718097]

I've done a bit more work on this problem and added an EDIT to the original question.

[Timothy L.J. Stewart]

just the info I needed, I switch Operational to Programming for SQL syntax checks. Note: you may also string the except statements as such execpt (sqlaclchemy.exc.ProgrammingError, sqlalchemy.exc.OperationalError) as e: to catch them all