• Context manager for database connection

    From Jason Friedman@21:1/5 to All on Wed Aug 23 09:41:07 2023
    I want to be able to write code like this:

    with Database() as mydb:
    conn = mydb.get_connection()
    cursor = conn.get_cursor()
    cursor.execute("update table1 set x = 1 where y = 2")
    cursor.close()
    cursor = conn.get_cursor()
    cursor.execute("update table2 set a = 1 where b = 2")
    cursor.close()

    I'd like for both statements to succeed and commit, or if either fails to
    stop and for all to rollback.

    Is what I have below correct?


    import jaydebeapi as jdbc
    class Database:
    database_connection = None

    def __init__(self, auto_commit: bool = False):
    self.database_connection = jdbc.connect(...)
    self.database_connection.jconn.setAutoCommit(auto_commit)

    def __enter__(self) -> jdbc.Connection:
    return self

    def __exit__(self, exception_type: Optional[Type[BaseException]],
    exception_value: Optional[BaseException],
    traceback: Optional[types.TracebackType]) -> bool:
    if exception_type:
    self.database_connection.rollback()
    else:
    self.database_connection.commit()
    self.database_connection.close()

    def get_connection(self) -> jdbc.Connection:
    return self.database_connection

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From dn@21:1/5 to Jason Friedman via Python-list on Thu Aug 24 06:11:02 2023
    On 24/08/2023 03.41, Jason Friedman via Python-list wrote:
    I want to be able to write code like this:

    with Database() as mydb:
    conn = mydb.get_connection()
    cursor = conn.get_cursor()
    cursor.execute("update table1 set x = 1 where y = 2")
    cursor.close()
    cursor = conn.get_cursor()
    cursor.execute("update table2 set a = 1 where b = 2")
    cursor.close()

    I'd like for both statements to succeed and commit, or if either fails to stop and for all to rollback.

    Is what I have below correct?


    import jaydebeapi as jdbc
    class Database:
    database_connection = None

    def __init__(self, auto_commit: bool = False):
    self.database_connection = jdbc.connect(...)
    self.database_connection.jconn.setAutoCommit(auto_commit)

    def __enter__(self) -> jdbc.Connection:
    return self

    def __exit__(self, exception_type: Optional[Type[BaseException]],
    exception_value: Optional[BaseException],
    traceback: Optional[types.TracebackType]) -> bool:
    if exception_type:
    self.database_connection.rollback()
    else:
    self.database_connection.commit()
    self.database_connection.close()

    def get_connection(self) -> jdbc.Connection:
    return self.database_connection


    Looking good!


    Assuming this is the only DB-interaction, a context-manager seems
    appropriate. If the real use-case calls for more interactions, the cost
    of establishing and breaking DB-connections becomes a consideration. Alternately, the 'length'?'life' of the context-manager *might*
    complicate things.

    Intriguing that given such a start, the code doesn't feature a
    context-manager for a query.


    That two cursors are established is also a 'cost'. Could both queries
    utilise the same cursor?
    (in which case, could consider adding to __init__() or __enter__(), and
    close in __exit__() )


    Because the context-manager has been implemented as a class, there is no
    reason why one can't add more methods to that class (it doesn't need to
    be limited to the functional __enter__() and __exit__() methods!

    Indeed there is already get_connection(). Why not also a query( self,
    sql-code ) method?


    These might reduce the mainline-code to something like:

    if __name__ == "__main__":
    with Database() as mydb:
    mydb.query( "update table1 set x = 1 where y = 2" )
    mydb.query( "update table2 set a = 1 where b = 2" )
    --
    Regards,
    =dn

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From dn@21:1/5 to dn via Python-list on Thu Aug 24 11:28:48 2023
    On 24/08/2023 06.11, dn via Python-list wrote:
    On 24/08/2023 03.41, Jason Friedman via Python-list wrote:
    with Database() as mydb:
    conn = mydb.get_connection()
    cursor = conn.get_cursor()
    cursor.execute("update table1 set x = 1 where y = 2")
    cursor.close()
    cursor = conn.get_cursor()
    cursor.execute("update table2 set a = 1 where b = 2")
    cursor.close()


    import jaydebeapi as jdbc
    class Database:
         database_connection = None

         def __init__(self, auto_commit: bool = False):
             self.database_connection = jdbc.connect(...)
             self.database_connection.jconn.setAutoCommit(auto_commit) >>
         def __enter__(self) -> jdbc.Connection:
             return self

         def __exit__(self, exception_type: Optional[Type[BaseException]], >>                   exception_value: Optional[BaseException], >>                   traceback: Optional[types.TracebackType]) -> bool:
             if exception_type:
                 self.database_connection.rollback()
             else:
                 self.database_connection.commit()
             self.database_connection.close()

         def get_connection(self) -> jdbc.Connection:
             return self.database_connection

    Using a context-manager is a good idea: it ensures clean-up with/without
    an exception occurring. Accordingly, I (and may I presume, most) like
    the idea when working with life-cycle type resources, eg I/O. Absolutely nothing wrong with the idea!


    However, the scope of a c-m is the with-block. If there are a number of
    'nested operations' to be performed (which could conceivably involve
    other c-ms, loops, or similar code-structures) the code could become
    harder to read and the length of the scope unwieldy.

    An ease of management tactic is being able to see the start and end of a construct on the same page/screen. Such would 'limit' the length of a
    c-m's scope.

    Perhaps drawing an inappropriate parallel, but like a try-except block,
    there seems virtue in keeping a c-m's scope short, eg releasing
    resources such as a file opened for output, and some basic DBMS-es which
    don't offer multi-access.


    Accordingly, why I stopped using a c-m for database work. NB YMMV!

    There were two other reasons:
    1 multiple databases
    2 principles (and my shining virtue (?) )


    1 came across a (statistics) situation where the client was using two
    DBMS-es. They'd used one for some time, but then preferred another. For
    their own reasons, they didn't migrate old data to the new DBMS. Thus,
    when performing certain analyses, the data-collection part of the script
    might have to utilise different DB 'sources'. In at least one case,
    comparing data through time, the routine needed to access both DBMS-es.
    (oh what a tangled web we weave...)

    2 another situation where the script may or may not actually have needed
    to access the DB. Odd? In which case, the 'expense' of the 'entry' and
    'exit' phases would never be rewarded.

    Thus, 'inspired' to realise that had (long) been contravening SOLID's
    DSP advice?rule (Dependency Inversion Principle).


    Accordingly, these days adopt something like the following (explaining
    it 'backwards' to aid comprehension in case you (gentle reader) have not come-across such thinking before - remember that word, "inversion"!)

    - in the mainline, prior to processing, instantiate a database object

    database = Database( credentials )

    - assume the mainline calls a function which is the substance of the script:

    def just_do_it( database_instance, other_args, ):
    while "there's work to be done":
    database.query( query, data, etc, )
    # could be SELECT or UPDATE in and amongst the 'work'

    - a basic outline of query() might be:

    def query( self, sql, data, etc, ):
    cursor = self.get_cursor()
    cursor.execute( sql, data, ) # according to DB/connector, etc
    # return query results

    - a query can't happen without a "cursor", so either use an existing
    cursor, or create a fresh one:

    def get_cursor( self ):
    if not self._cursor:
    connection = self.get_connection()
    self._cursor = connection.cursor()
    return self._cursor

    NB assuming the DBMS has restrictions on cursors, I may have multiple connections with one cursor each, but in some situations it may be
    applicable to run multiple cursors through a single connection.

    - a cursor can't exist without a "connection", so either ... :

    def get_connection( self ):
    if not self._connection:
    self._connection = # connect to the DB
    return self._connection

    - to instantiate a DB-object in the first place, the class definition:

    class Database:
    def __init__(self):
    self._connection = None
    self._cursor = None

    - and the one part of the exposition that's not 'backwards':

    def close(self):
    if self._connection:
    self._connection.close()


    It might be a plan to have several query() methods, according to
    application. If each is a dedicated query, such avoids the need to pass
    SQL around.

    Alternately, and because having "SELECT ..." sprinkled throughout one's
    code is a 'code smell' ("magic constants"), it's a good idea to have all
    such collected into a separate module. This would also facilitate the
    corporate situation where a DBA will provide services to the
    applications team.
    (don't EVER let a DBA into your Python code - you have been warned!)


    Just as with all toy-examples, much detail has been omitted.
    Specifically the OP's concern for error-checking. The above enables more thorough checking and more precise error-reporting; because the steps
    are discrete (very SRP = Single Responsibility Principle). That said,
    from an application point-of-view, all the DB stuff has been centralised
    and it either works or the whole thing should probably be drawn to a grinding-halt.

    The one glaring disadvantage is in the situation where a lot of
    (expensive) processing is carried-out, and only at the end is the DB
    accessed (presumably to persist the results). It could be frustrating to
    do 'all the work' and only thereafter find out that the DBMS is asleep-at-the-wheel, eg that Docker container has been started. Doh!


    The OP had a linked-query 'commit or rollback' situation. The structure
    of a separate method for that query-pair (perhaps calling individual
    methods for each query) with attendant controls (as described
    previously) will work nicely.


    Should you be foolish-enough to be required to (and capable of) cope
    with more than one DBMS, you can likely see that turning the Database
    class into an ABC, will enable consistency between multiple concrete and specific database class implementations. Thereafter a 'factory' to
    choose which DB-class to use, and all will be roses...
    (yeah right!)


    --
    Regards,
    =dn

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)