Best practice for database connection
Thomas Passin
list1 at tompassin.net
Wed May 31 14:45:16 EDT 2023
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.
More information about the Python-list
mailing list