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
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)