Best practice for database connection
Jason Friedman
jsf80238 at gmail.com
Wed May 31 14:10:48 EDT 2023
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.
More information about the Python-list
mailing list