I'm working in a proyect to read and update data from a mysql database. I need several functions to perform different actions. I show them to the user in a menu. I did my research about how to structure the code but I still have some doubts.
I understand using context managers for db connection and cursor is the best practice. Also, I understand using only one db connection and one cursor objects is the best option to optimize code and to avoid server connections overload. I'm right? Following this ideas, I structured my code as follows:
# Database connection base class
class Database:
# Setup connection
def __init__(self):
# data (host, pass, port, etc)
# Start connection
def __enter__(self):
# create and return connection object
# Disconnect
def __exit__(self, exc_type, exc_value, exc_tb):
# close() connection
# Functions
def function_1(self, cursor):
cursor.execute(query1)
def function_2(self, cursor):
cursor.execute(query2)
def function_3(self, cursor):
cursor.execute(query3)
# Cursor base class
class Cursor:
def __enter__(self):
# create and return cursor object
def __exit__(self, exc_type, exc_value, exc_tb):
# close() cursor
# main.py
with Database() as db:
with Cursor() as cursor:
while True:
print("MAIN MENU")
option = input("1. function 1\n2. function 2\n3.function 3\n4. Exit")
if option == '1':
db.function_1(cursor)
if option == '2':
db.function_2(cursor)
if option == '3':
db.function_3(cursor)
if option == '4':
sys.exit()
MY QUESTIONS:
- Is it okay to have the main menu (infinite loop) in the
withblock? - Is it okay to use only one connection and one cursor, for several functions? If yes, it's correct an implementation like the previous code? If not, should I start a new connection and cursor for each function?.
posts used as reference: Proper management of database resources: cursor and connection
self.connection.commit()before closing the connection.sys.exit()will call the context manager exit functions?withblock. Or it's considered wrong?withblock inside the loop, you'll keep opening and closing connections. Which is the opposite of what you said you want to do.