How to properly close db connection, best practice

[t4keheart]

Hi everyone,
I have a module that connects to a db to check for new entries, and if new, trigger more functions etc.

My question is regarding the connection itself. As it currently stands, I create and close the connection in each function, each time a function needs to interact with the db. I'm wondering how this affects performance and would think it better to connect and close just once when the loops is starting up.

My issue is that the module that connects runs a loop, and I don't want to create incremental numbers of connections... at the same time, I don't want to make the connection once and just have it time out or something.

What's the best practice for this?

Here's what some of my code looks like:

def beginSending():
    global last_sent
    cnxn = pyodbc.connect('UID='+dbUser+';PWD='+dbPassword+';DSN='+dbHost)
    cursor = cnxn.cursor()
    cursor.execute(pullId)
    results = cursor.fetchone()
    last_sent = results
    cnxn.close()
    Logger.writeAndPrintLine('Send listener started.', 0)
    sendLoop()

def sendLoop():
    while 0 == 0:
        sendListener()
        time.sleep(2.0)

def sendListener():
    global last_sent
    cnxn = pyodbc.connect('UID='+dbUser+';PWD='+dbPassword+';DSN='+dbHost)
    cursor = cnxn.cursor()
    cursor.execute(pullId)
    results = cursor.fetchone()
    if results != last_sent:
        sendSMS()
    else:
        cnxn.close()

[t4keheart]

I've decided to create another loop for the db connection that refreshes the connection and stops all other functions every 10 mins. My next question is how to run multiple functions in parallel so that I can do a sleep() or time.wait() while other stuff runs.

[Marbelous]

You'll need the threading or multiprocessing modules. I'm just learning them myself for some Tkinter GUI apps. This is a good, pretty extensive intro to threading.
https://realpython.com/intro-to-python-threading/

[t4keheart]

You'll need the threading or multiprocessing modules. I'm just learning them myself for some Tkinter GUI apps. This is a good, pretty extensive intro to threading.
https://realpython.com/intro-to-python-threading/

Haha thanks for the reply. I realized this after doing some digging online... looking into multiprocessing, as that seems more efficient for what I'm trying to do.

[woooee]

I'm just learning them myself for some Tkinter GUI apps

With a good design you should not have to use threading in a Tkinter GUI.

[ndc85430]

You should also avoid using global variables. Also, line 13: you do realise that Python (like most languages) has a True constant (and a False one, obviously)?

[Marbelous]

I'm just learning them myself for some Tkinter GUI apps

With a good design you should not have to use threading in a Tkinter GUI.

I'm actually finishing the code without bothering with it for now but it's a data logging app that reads several pieces of hardware for each of 16 test sockets through GPIB communications and it takes about 3 seconds for all the data to be collected. It doesn't really matter that the GUI goes non-responsive and of course I could break the task into slices that would allow Tkinter to update the UI more frequently but I want to learn threading, multi-processing and how python's GIL works anyway so I figured now is as good a time as any...