Best practice for database connection

[dn]

On 01/06/2023 06.45, Thomas Passin wrote:
> On 5/31/2023 2:10 PM, Jason Friedman wrote:
>> I'm trying to reconcile two best practices which seem to conflict.
>>
>> 1) Use a _with_ clause when connecting to a database so the connection is
>> closed in case of premature exit.
>>
>> class_name = 'oracle.jdbc.OracleDriver'
>> url = f"jdbc:oracle:thin:@//{host_name}:{port_number}/{database_name}"
>> with jdbc.connect(class_name, url, [user_name, password],
>> jdbc_jar_file.as_posix()) as connection:
>>      logger.info(f"Connected.")
>>
>> 2) Use self-made functions to streamline code. For example, there are
>> several places I need to know if the database object is a particular 
>> type,
>> so I create a function like this:
>>
>> foobar_set = set()
>> ...
>> def is_foobar(connection: jdbc.Connection, name: str) -> bool:
>>      """
>>      :param connection: connection object
>>      :param name: owner.object
>>      :return: True if this object is of type foobar
>>      """
>>      global foobar_set
>>      if not foobar_set:
>>          query = f"""select stuff from stuff"""
>>          cursor = connection.cursor()
>>          cursor.execute(query)
>>          for owner, object_name in cursor.fetchall():
>>              foobar_set.add(f"{owner}.{object_name}")
>>          cursor.close()
>>      return name.upper() in foobar_set
>>
>>
>> But that requires that I call is_foobar() with a connection object.
>>
>> Essentially I'd like a function that leverages the one connection I 
>> create
>> at the beginning using a with clause.
> 
> If you need to have a connection object that persists outside of the 
> with block, then
> 
> 1. you can just not use a "with" block:
> 
> connection = jdbc.connect(class_name, url, [user_name, password],
>     jdbc_jar_file.as_posix())
> 
> You will want to close the connection yourself when you are done with it.
> 
> 2. do all the subsequent work within the "with" block.

As with many such questions, the answer is "it depends". Sadly that's no 
help!


Please consider: is the database critical to this application? In other 
words, if the script is unable to proceed without access, eg RDBMS is 
down or credentials are not accepted, then must the logic simply stop?

Most of mine fit into that category. Accordingly, script-execution 
starts with setting the environment, eg gathering credentials; followed 
by establishing a connection to the RDBMS. An operational RDBMS is part 
of the environment! Now (wait for many of our colleagues to hurriedly 
suck in their breath) we can see that the connection is a global-value - 
something which resides in a base "frame" of the Python stack, and is 
accordingly available (R-O) 'everywhere'.

NB when I say "connection", it is part of a wider RDBMS-interface object.

If you wish to use a Context Manager, then its only content may be a 
call to main() - or preferably a function which better describes what 
the application will do. (per @Thomas' contribution)

PS I don't like using the global keyword/command, but prefer to pass the 
connection as a parameter. A matter of style? A "contract": 
no-connection, no-query? YMMV...


Another approach might be to enact the Dependency Inversion Principle 
(one of 'Uncle Bob' Martin's SOLID Principles. In this case, proceed 
with the application, and when it comes time to issue a query against 
the database, ask the question: "does a connection exist?". In the 
negative case, then call a routine which establishes the connector, 
passes that to the query-routine, and proceeds.

Assuming multiple query-routines, the problem with this is that it takes 
some sleight-of-hand to create a shared connection. Alternately, maybe 
the application is prepared to assume the 'cost' of creating multiple 
connections (over time) because the RDBMS-interactions are few-and-far 
between, or only a small part of the total.


I don't use one, but others enthuse about ORMs, eg SQLAlchemy. This 
suits those who combine RDBMS and OOP, and has its own persistence 
methodology.

-- 
Regards,
=dn