Context manager for database connection

[dn]

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