Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

If it wasn't for Newton, we wouldn't have to eat bruised apples.

devel / comp.lang.python / Re: Best practice for database connection

SubjectAuthor
o Re: Best practice for database connectiondn

1
Re: Best practice for database connection

<mailman.48.1685585532.23016.python-list@python.org>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=23489&group=comp.lang.python#23489

  copy link   Newsgroups: comp.lang.python
Path: i2pn2.org!i2pn.org!news.swapon.de!fu-berlin.de!uni-berlin.de!not-for-mail
From: PythonL...@DancesWithMice.info (dn)
Newsgroups: comp.lang.python
Subject: Re: Best practice for database connection
Date: Thu, 1 Jun 2023 14:11:47 +1200
Organization: DWM
Lines: 103
Message-ID: <mailman.48.1685585532.23016.python-list@python.org>
References: <CANy1k1iB7rY6vfHGwkh11M5qxXVyfoDbnmPaKo_TpHvUYdTVmg@mail.gmail.com>
<3cbabd14-f6c7-46b1-dbcc-6a8200e725f0@tompassin.net>
<f559a8b7-bda3-1828-bae9-d4e1941ec396@DancesWithMice.info>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Trace: news.uni-berlin.de YeVYsh0IXGLbydv1omQFngx583rJWV5iBkF7p4mJtW/Q==
Return-Path: <PythonList@DancesWithMice.info>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
Authentication-Results: mail.python.org; dkim=pass
reason="2048-bit key; unprotected key"
header.d=danceswithmice.info header.i=@danceswithmice.info
header.b=lfCXh1G7; dkim-adsp=pass; dkim-atps=neutral
X-Spam-Status: OK 0.013
X-Spam-Evidence: '*H*': 0.97; '*S*': 0.00; 'this:': 0.03; 'def': 0.04;
'"it': 0.05; '"""': 0.09; '=dn': 0.09; 'access,': 0.09;
'environment,': 0.09; 'from:addr:danceswithmice.info': 0.09;
'from:addr:pythonlist': 0.09; 'question:': 0.09; 'routine': 0.09;
'skip:\xc2 20': 0.09; '(over': 0.16; 'accordingly': 0.16;
'assuming': 0.16; 'category.': 0.16; 'connection.': 0.16;
'database,': 0.16; 'help!': 0.16; 'main()': 0.16; 'message-
id:@DancesWithMice.info': 0.16; 'oop,': 0.16; 'persistence': 0.16;
'preferably': 0.16; 'received:cloud': 0.16;
'received:rangi.cloud': 0.16; 'resides': 0.16; 'time)': 0.16;
'wider': 0.16; 'wrote:': 0.16; 'problem': 0.16; 'python': 0.16;
'code.': 0.17; 'pm,': 0.19; 'url': 0.19; 'to:addr:python-list':
0.20; 'issue': 0.21; 'unable': 0.21; 'closed': 0.22; 'maybe':
0.22; "i'd": 0.24; 'stuff': 0.25; 'questions,': 0.26; 'manager,':
0.26; 'object': 0.26; 'practices': 0.26; 'creating': 0.27;
'function': 0.27; 'done': 0.28; 'example,': 0.28; 'header:User-
Agent:1': 0.30; 'seem': 0.31; 'takes': 0.31; 'approach': 0.31;
'header:Organization:1': 0.31; 'assume': 0.32; 'context': 0.32;
'credentials': 0.32; 'do.': 0.32; 'gathering': 0.32; 'negative':
0.32; 'passes': 0.32; 'words,': 0.32; 'received:192.168.1': 0.32;
'but': 0.32; "i'm": 0.33; 'subject:for': 0.33; 'there': 0.33;
'particular': 0.33; 'script': 0.33; 'requires': 0.34; 'header:In-
Reply-To:1': 0.34; 'trying': 0.35; 'close': 0.35; 'functions':
0.36; 'those': 0.36; 'name:': 0.37; "skip:' 10": 0.37; 'using':
0.37; 'others': 0.37; 'received:192.168': 0.37; 'two': 0.39;
'setting': 0.39; 'use': 0.39; 'shared': 0.39; 'base': 0.40;
'beginning': 0.40; 'something': 0.40; 'want': 0.40; 'best': 0.61;
'connection': 0.61; 'skip:\xc2 10': 0.62; 'skip:i 20': 0.62;
'critical': 0.62; 'true': 0.63; 'simply': 0.63; 'pass': 0.64;
'skip:k 10': 0.64; 'our': 0.64; 'down': 0.64; 'wish': 0.66;
'outside': 0.67; 'matter': 0.68; 'establishing': 0.69; 'within':
0.69; 'content': 0.72; 'global': 0.73; 'yourself': 0.75;
'8bit%:100': 0.76; 'subsequent': 0.76; 'database': 0.80; 'clause':
0.84; 'enact': 0.84; 'establishes': 0.84; 'exit.': 0.84; 'skip:j
20': 0.84; 'stack,': 0.84; 'streamline': 0.84;
'subject:connection': 0.84; 'type,': 0.84;
'\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0': 0.84; 'proceed': 0.88;
'owner,': 0.91; 'colleagues': 0.97
DKIM-Filter: OpenDKIM Filter v2.11.0 vps.rangi.cloud 9138F42BC
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=danceswithmice.info;
s=staff; t=1685585518;
bh=crqjO6ukzZ7aBRS3sdnMXsKaqb1J0NUJnNAYkoG+b34=;
h=Date:Subject:To:References:From:In-Reply-To:From;
b=lfCXh1G7v2QbUdfm9vh0SN6cFDd0qi9AVZtDvVOBHSQzYO+EAPZmFTNX/UPhXUsrU
xEyh2QBnO/ODxgtIBijYHwXDHK6yzbCFABKaVKy+9gc0+wk9uUoTUL5uxfVOduQceI
iSJakuP4fPJcHosXu6RHCjLw+VMQSmPWepTNzCNSjjOVAhI1NltAVXAq2QZlOU1CjB
spmpfZYT0ygMjE3Oh+8PZEKFtVanBq0XbNyCtR2jmw/Mlkmvo2ztcZXK/53eibYK+5
+lnbUi06IWb54UFcHKfn9wa/HKPMwsL3ltTH3H5eQ7WK7cbDZFRp+DNjn9AMsj8Ywh
NGyJ0T8Vbl/XA==
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
Thunderbird/102.11.0
Content-Language: en-GB
In-Reply-To: <3cbabd14-f6c7-46b1-dbcc-6a8200e725f0@tompassin.net>
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.39
Precedence: list
List-Id: General discussion list for the Python programming language
<python-list.python.org>
List-Unsubscribe: <https://mail.python.org/mailman/options/python-list>,
<mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <https://mail.python.org/pipermail/python-list/>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <https://mail.python.org/mailman/listinfo/python-list>,
<mailto:python-list-request@python.org?subject=subscribe>
X-Mailman-Original-Message-ID: <f559a8b7-bda3-1828-bae9-d4e1941ec396@DancesWithMice.info>
X-Mailman-Original-References: <CANy1k1iB7rY6vfHGwkh11M5qxXVyfoDbnmPaKo_TpHvUYdTVmg@mail.gmail.com>
<3cbabd14-f6c7-46b1-dbcc-6a8200e725f0@tompassin.net>
 by: dn - Thu, 1 Jun 2023 02:11 UTC

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

1
server_pubkey.txt

rocksolid light 0.9.81
clearnet tor