我有一个用户定义的函数,它使用py
mysql连接到
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`")
我已经编写了代码来捕获pymysql.connect()引起的异常,尽管为了清楚起见我没有在这里显示它.我还希望能够捕获read_sql()可能产生的任何异常.我在哪里可以找到可能引发的异常列表?它不在Pandas文档(http://pandas.pydata.org/pandas-docs/version/0.19.2/generated/pandas.read_sql.html)中,我在网上找不到任何提示.我可以捕获所有异常,但这似乎通常被Python社区所厌恶.我应该如何捕获read_sql()引发的异常?
编辑
我已经做了更多的工作,似乎即使我知道正在生成什么错误,也不是直截了当地捕获异常.因此,例如,在上面给出的代码中,如果我错误地输入用户名和/或密码,则会生成操作错误.最后一行或错误报告的内容如下:
OperationalError: (1045, "Access denied for user 'yyy'@'xxx.xxx.xxx.xxx' (using password: YES)")
我已经能够使用以下方法捕获此错误:
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]))
这工作正常(虽然发现需要使用pymysql.OperationalError捕获的OperationalError是偶然的).
现在,在函数的下一部分中,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'")
但我现在对于如何捕获第二个OperationalError完全不知所措.之前使用的pymysql.OperationalError不起作用.我已经尝试了几乎所有我能想到的东西,但仍然无法捕捉错误.错误消息是否应该更多地提供有关如何生成错误以及如何捕获错误的信息?显然,我遗漏了一些明显的东西,但我找不到解决方案.任何建议,将不胜感激.
编辑2
在回应评论时,我现在正在捕捉例外如下:
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`")