Best practice for database connection

[Thomas Passin]

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.