Support for context managers with Connection object

[jaeger-2601]

Is your feature request related to a problem? Please describe. When working with IO, It is common to acquire some resource i.e open('file.txt') or ibm_db_dbi.connect(connection_str). These resources also have to manually closed i.e fp.close() or connection.close(). Developers may forget to clean up this resources and this could lead to resource leaks. Python solves this problem with context managers which automatically solve the problem of resource management and ensure proper cleanup. They provide a convenient and reliable way to allocate and release resources, such as connecting and disconnecting from a database.

Context managers could provide a concise and intuitive way to handle database connections and transactions, ensuring proper cleanup and reducing the risk of resource leaks. With context managers, developers can write cleaner and more readable code by encapsulating the database-related operations within a well-defined scope. Additionally, context managers can also enable automatic rollback of transactions in case of exceptions, ensuring data consistency and integrity.

Describe the solution you'd like Context managers to be added to connection objects such that they can be used easily like:

with ibm_db_dbi.connect(connection_str) as connection:
    cursor = connection.cursor()
    cursor.execute('SELECT * from table')
    result = cursor.fetchall()

This removes the need for manual cleanup of resources

Describe alternatives you've considered

Additional context

[jaeger-2601]

@bimalkjha @amukherjee28 Can I get any comments on if this feature is possible and will be added or not? Thanks

[bimalkjha]

@Earammak is working on it and she should share update here. Thanks.

[kuwv]

class DB2Connect:
    """Context manager to handle connections to DB2."""

    __cxn: Optional['IBM_DBConnection']

    def __init__(self, dsn: str) -> None:
        """Instantiate a DB2 connection."""
        self.__dsn = dsn
        self.__cxn = None

    def __enter__(self) -> 'IBM_DBConnection':
        """Connect to DB2."""
        self.__cxn = ibm_db.connect(self.__dsn, '', '')
        return self.__cxn

    def __exit__(self, exc_type, exc_val, exc_tb) -> None:
        """Disconnect from DB2."""
        ibm_db.close(self.__cxn)

[bimalkjha]

@jaeger-2601 Please review PR #941 . Thanks.

[jaeger-2601]

@bimalkjha Left some comments on the PR

[Earammak]

@jaeger-2601, Can you please check for https://github.com/ibmdb/python-ibmdb/pull/947 PR.

[jaeger-2601]

@bimalkjha @Earammak left comments. None of original comments seem to be resolved?

[Earammak]

@jaeger-2601, On top of your comment, i have replied also. The one you commented was closed. Please refer to new PR and review the same. Thanks

[Earammak]

Fix for this given as part of https://github.com/ibmdb/python-ibmdb/pull/947. Closing the issue now. Thanks