cx_Oracle和异常处理-好的做法? [英] cx_Oracle and Exception Handling - Good practices?

查看：68 发布时间：2020/5/22 0:01:38 python oracle cx-oracle

本文介绍了cx_Oracle和异常处理-好的做法?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试使用cx_Oracle连接到Oracle实例并执行一些DDL语句:

db = None
try:
    db = cx_Oracle.connect('username', 'password', 'hostname:port/SERVICENAME')
#print(db.version)
except cx_Oracle.DatabaseError as e:
    error, = e.args
    if error.code == 1017:
        print('Please check your credentials.')
        # sys.exit()?
    else:
        print('Database connection error: %s'.format(e))
cursor = db.cursor()
try:
    cursor.execute(ddl_statements)
except cx_Oracle.DatabaseError as e:
    error, = e.args
    if error.code == 955:
        print('Table already exists')
    if error.code == 1031:
        print("Insufficient privileges - are you sure you're using the owner account?")
    print(error.code)
    print(error.message)
    print(error.context)
cursor.close()
db.commit()
db.close()

但是，我不太确定在这里进行异常处理的最佳设计是什么.

首先，我在try块内创建db对象，以捕获任何连接错误.

但是，如果无法连接，则db将不再存在-这就是为什么我在上面设置了db = None的原因.但是，这是个好习惯吗?

理想情况下，我需要在连接时捕获错误，然后在运行DDL语句时捕获错误，等等.

嵌套异常是个好主意吗?还是有更好的方法来处理像这样的依存/级联异常?

此外，在某些部分(例如，连接失败)中，我希望脚本仅终止-因此注释为sys.exit()的调用.但是，我听说对这样的流控制使用异常处理是不好的做法.有想法吗?

解决方案

但是，如果无法连接，则db将不再存在- 这就是为什么我在上面设置db = None的原因.但是，这是个好习惯吗?

否，设置db = None不是最佳做法.有两种可能性，要么连接到数据库将起作用，要么将不起作用.

连接到数据库无效:

由于引发的异常已被捕获且未重新引发，因此请继续直到到达cursor = db.Cursor().

db == None，因此将引发类似于TypeError: 'NoneType' object has no attribute 'Cursor'的异常.由于已经捕获了数据库连接失败时产生的异常，因此掩盖了失败的原因.

就我个人而言，除非您打算稍后再次尝试，否则我总是会提出连接异常.如何捕捉取决于您自己；如果错误仍然存在，我将通过电子邮件发送去检查数据库"的电子邮件.
连接数据库确实有效:

在您的try:... except块中分配了变量db.如果connect方法确实起作用，则db将替换为连接对象.

无论哪种方式，都不会使用db的初始值.

但是，我听说使用异常处理进行流控制这样是不好的做法.

与其他语言不同，Python 确实使用异常处理进行流控制.在回答的最后，我已链接到有关堆栈溢出和程序员的几个问题，这些问题都提出了类似的问题.在每个示例中，您都会看到单词"but in Python".

这并不是说您应该过度，但Python通常使用 EAFP 的口头禅，宽恕要比许可容易." BaseException .

此外，我还需要一些部分(例如，连接失败) 脚本刚刚终止-因此注释掉了sys.exit()调用.

我添加了一个简单的类以及如何调用它，这大致就是我将要做的事情.如果要在后台运行，那么打印错误是不值得的-人们不会坐在那里手动寻找错误.无论采用哪种标准方式，都应记录它们，并通知适当的人员.由于这个原因，我已经删除了打印件，并替换为要提醒的记录.

当connect方法失败并且引发异常时，我将类拆分为多个函数，在尝试断开连接后，将不会运行execute调用，并且脚本将完成.

 import cx_Oracle

class Oracle(object):

    def connect(self, username, password, hostname, port, servicename):
        """ Connect to the database. """

        try:
            self.db = cx_Oracle.connect(username, password
                                , hostname + ':' + port + '/' + servicename)
        except cx_Oracle.DatabaseError as e:
            # Log error as appropriate
            raise

        # If the database connection succeeded create the cursor
        # we-re going to use.
        self.cursor = self.db.cursor()

    def disconnect(self):
        """
        Disconnect from the database. If this fails, for instance
        if the connection instance doesn't exist, ignore the exception.
        """

        try:
            self.cursor.close()
            self.db.close()
        except cx_Oracle.DatabaseError:
            pass

    def execute(self, sql, bindvars=None, commit=False):
        """
        Execute whatever SQL statements are passed to the method;
        commit if specified. Do not specify fetchall() in here as
        the SQL statement may not be a select.
        bindvars is a dictionary of variables you pass to execute.
        """

        try:
            self.cursor.execute(sql, bindvars)
        except cx_Oracle.DatabaseError as e:
            # Log error as appropriate
            raise

        # Only commit if it-s necessary.
        if commit:
            self.db.commit()

然后称呼它:

 if __name__ == "__main__":

    oracle = Oracle.connect('username', 'password', 'hostname'
                           , 'port', 'servicename')

    try:
        # No commit as you don-t need to commit DDL.
        oracle.execute('ddl_statements')

    # Ensure that we always disconnect from the database to avoid
    # ORA-00018: Maximum number of sessions exceeded. 
    finally:
        oracle.disconnect()

进一步阅读:

cx_Oracle文档

为什么不将异常用作常规控制流?
Python异常处理比PHP更有效和/或其他语言?
支持或反对将try catch用作逻辑运算符的参数

I'm trying to use cx_Oracle to connect to an Oracle instance and execute some DDL statements:

db = None
try:
    db = cx_Oracle.connect('username', 'password', 'hostname:port/SERVICENAME')
#print(db.version)
except cx_Oracle.DatabaseError as e:
    error, = e.args
    if error.code == 1017:
        print('Please check your credentials.')
        # sys.exit()?
    else:
        print('Database connection error: %s'.format(e))
cursor = db.cursor()
try:
    cursor.execute(ddl_statements)
except cx_Oracle.DatabaseError as e:
    error, = e.args
    if error.code == 955:
        print('Table already exists')
    if error.code == 1031:
        print("Insufficient privileges - are you sure you're using the owner account?")
    print(error.code)
    print(error.message)
    print(error.context)
cursor.close()
db.commit()
db.close()

However, I'm not quite sure what's the best design for exception handling here.

Firstly, I create the db object inside a try block, to catch any connection errors.

However, if it can't connect, then db won't exist further down - which is why I set db = None above. However, is that good practice?

Ideally, I need to catch errors with connecting, then errors with running the DDL statements, and so on.

Is nesting exceptions a good idea? Or is there a better way of dealing with dependent/cascaded exceptions like this?

Also, there are some parts (e.g. connection failures) where I'd like the script to just terminate - hence the commented out sys.exit() call. However, I've heard that using exception handling for flow control like this is bad practice. Thoughts?

解决方案

However, if it can't connect, then db won't exist further down - which is why I set db = None above. However, is that good practice?

No, setting db = None is not best practice. There are two possibilities, either connecting to the database will work or it won't.

Connecting to the database doesn't work:

As the raised exception has been caught and not re-raised you continue until you reach cursor = db.Cursor().

db == None, so, an exception that resembles TypeError: 'NoneType' object has no attribute 'Cursor' will be raised. As the exception generated when the database connection failed has already been caught, the reason for the failure is disguised.

Personally, I'd always raise a connection exception unless you're going to try again shortly. How you catch it is up to you; if the error persists I e-mail to say "go and check the database".
Connecting to the database does work:

The variable db is assigned in your try:... except block. If the connect method does work then db is replaced with the connection object.

Either way the initial value of db is never used.

However, I've heard that using exception handling for flow control like this is bad practice.

Unlike other languages Python does use exception handling for flow control. At the end of my answer I've linked to several questions on Stack Overflow and Programmers that ask a similar question. In every example you'll see the words "but in Python".

That's not to say that you should go overboard but Python generally uses the mantra EAFP, "It's easier to ask forgiveness than permission." The top three voted examples in How do I check if a variable exists? are good examples of how you can both use flow control or not.

Is nesting exceptions a good idea? Or is there a better way of dealing with dependent/cascaded exceptions like this?

There's nothing wrong with nested exceptions, once again as long as you do it sanely. Consider your code. You could remove all exceptions and wrap the entire thing in a try:... except block. If an exception is raised then you know what it was, but it's a little harder to track down exactly what went wrong.

What then happens if you want to say e-mail yourself on the failure of cursor.execute? You should have an exception around cursor.execute in order to perform this one task. You then re-raise the exception so it's caught in your outer try:.... Not re-raising would result in your code continuing as if nothing had happened and whatever logic you had put in your outer try:... to deal with an exception would be ignored.

Ultimately all exceptions are inherited from BaseException.

Also, there are some parts (e.g. connection failures) where I'd like the script to just terminate - hence the commented out sys.exit() call.

I've added a simple class and how to call it, which is roughly how I would do what you're trying to do. If this is going to be run in the background then the printing of the errors isn't worthwhile - people won't be sitting there manually looking out for errors. They should be logged in whatever your standard way is and the appropriate people notified. I've removed the printing for this reason and replaced with a reminder to log.

As I've split the class out into multiple functions when the connect method fails and an exception is raised the execute call will not be run and the script will finish, after attempting to disconnect.

import cx_Oracle

class Oracle(object):

    def connect(self, username, password, hostname, port, servicename):
        """ Connect to the database. """

        try:
            self.db = cx_Oracle.connect(username, password
                                , hostname + ':' + port + '/' + servicename)
        except cx_Oracle.DatabaseError as e:
            # Log error as appropriate
            raise

        # If the database connection succeeded create the cursor
        # we-re going to use.
        self.cursor = self.db.cursor()

    def disconnect(self):
        """
        Disconnect from the database. If this fails, for instance
        if the connection instance doesn't exist, ignore the exception.
        """

        try:
            self.cursor.close()
            self.db.close()
        except cx_Oracle.DatabaseError:
            pass

    def execute(self, sql, bindvars=None, commit=False):
        """
        Execute whatever SQL statements are passed to the method;
        commit if specified. Do not specify fetchall() in here as
        the SQL statement may not be a select.
        bindvars is a dictionary of variables you pass to execute.
        """

        try:
            self.cursor.execute(sql, bindvars)
        except cx_Oracle.DatabaseError as e:
            # Log error as appropriate
            raise

        # Only commit if it-s necessary.
        if commit:
            self.db.commit()

Then call it:

if __name__ == "__main__":

    oracle = Oracle.connect('username', 'password', 'hostname'
                           , 'port', 'servicename')

    try:
        # No commit as you don-t need to commit DDL.
        oracle.execute('ddl_statements')

    # Ensure that we always disconnect from the database to avoid
    # ORA-00018: Maximum number of sessions exceeded. 
    finally:
        oracle.disconnect()

cx_Oracle和异常处理-好的做法? [英] cx_Oracle and Exception Handling - Good practices?

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

cx_Oracle和异常处理-好的做法? [英] cx_Oracle and Exception Handling - Good practices?

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭