Pandas read_sql() 可以返回哪些异常

What exceptions could be returned from Pandas read_sql()

提问by user1718097

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.

我有一个用户定义的函数，它使用 pymysql 连接到 mysql 数据库，然后查询数据库并将结果读入 Pandas 数据帧。

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()?

我已经编写了代码来捕获由 pymysql.connect() 引起的异常，尽管为了清楚起见我没有在这里展示它。我还希望能够捕获任何可能由 read_sql() 引起的异常。我在哪里可以找到可能引发的异常列表？它不在 Pandas 文档（http://pandas.pydata.org/pandas-docs/version/0.19.2/generated/pandas.read_sql.html）中，我在网上找不到任何提示。我可以捕获所有异常，但 Python 社区似乎普遍反对。我应该如何捕获 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).

这工作正常（尽管偶然发现需要使用 pymysql.OperationalError 捕获 OperationalError ）。

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:

现在，在函数的下一部分中，Pandas 函数 real_sql() 使用上面创建的连接来运行 SQL 查询。如果我故意包含一个具有不正确表名的错误查询，则会发生另一个 OperationalError，然后是 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.

但是我现在完全不知道我是如何捕捉到第二个 OperationalError 的。之前使用的 pymysql.OperationalError 不起作用。我已经尝试了几乎所有我能想到的方法，但仍然无法捕捉到错误。错误消息不应该更详细地说明错误是如何生成的以及如何捕获它的吗？显然，我遗漏了一些明显的东西，但我找不到解决方案。任何建议，将不胜感激。

EDIT 2

编辑 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`")

回答by parsethis

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

请注意，好问题read_sql是围绕“read_sql_table 和 read_sql_query”的包装。通读源代码， aValueError始终被抛出到父函数和辅助函数中。所以你可以安全地抓住 aValueError并适当地处理。（一定要看源码）

回答by Mohammed Gaber

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

我只是在一个类似的问题中偶然发现了这个问题，并找到了从 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

更多信息可以在这里找到。 SQL Alchemy 文档