What exceptions could be returned from Pandas read_sql()

13,893

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

Share:
13,893

Related videos on Youtube

user1718097
Author by

user1718097

Updated on October 01, 2022

Comments

  • user1718097
    user1718097 2 months

    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`")
    
  • user1718097
    user1718097 almost 6 years
    Thanks for the suggestion...I'll take a look.
  • MaxU - stop genocide of UA
    MaxU - stop genocide of UA almost 6 years
    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
    user1718097 over 5 years
    I've done a bit more work on this problem and added an EDIT to the original question.
  • Timothy L.J. Stewart
    Timothy L.J. Stewart about 1 year
    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