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
--
https://mail.python.org/mailman/listinfo/python-list
