0

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:

  1. Is it okay to have the main menu (infinite loop) in the with block?
  2. 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

https://dev.to/c_v_ya/sql-cursor-via-context-manager-2gc7

Marcos Tarnoski
  • 1
  • 1
  • Make sure you do `self.connection.commit()` before closing the connection. – Barmar Aug 25 '21 at 23:52
  • Are you asking whether `sys.exit()` will call the context manager exit functions? – Barmar Aug 25 '21 at 23:58
  • I would like to know if it is a good practice to have a main menu inside a `with` block. Or it's considered wrong? – Marcos Tarnoski Aug 26 '21 at 00:01
  • If you put the `with` block inside the loop, you'll keep opening and closing connections. Which is the opposite of what you said you want to do. – Barmar Aug 26 '21 at 00:02
  • okey. So, it's okey to have the menu as in the post to achieve what I want? And it's not considered a bad practice – Marcos Tarnoski Aug 26 '21 at 00:04
  • It's not a good idea if you will have hundreds of users running scripts like this at the same time. You may reach your database connection limit and many of the connections will be idle. – Barmar Aug 26 '21 at 00:05
  • Suppose that case, with hundreds of users. Should I start a new connection and cursor for each function? Or it would be even worse – Marcos Tarnoski Aug 26 '21 at 00:17
  • The general idea is to avoid having lots of idle connections. If you have an automated application that's doing lots of database operations, it should just open one connection. But interactive applications shouldn't keep connections open while waiting for the user to tell it what to do. – Barmar Aug 26 '21 at 00:19
  • One connection per instance of a program. – Rick James Aug 27 '21 at 17:10

1 Answers1

0

not sure I completely understand the issue you are having. Is this question more about correct/accepted practices? Or is there some sort of issue with the way you have implemented your code?

Normally when I connect to SQL Server I am defining it as follows:

cnxn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};"
                              "Server=xxx;"
                              "Database=xxx;"
                              "Trusted_connection=xx;"
                              "UID=xxx;"
                              "PWD=xxx;")
# Connect to database
cursor = cnxn.cursor()

If I understand correctly, you are trying to trigger a query based on user input/selection. Presumably there is a finite about of these queries?

Anyway, onto the infinite loop question. In short yes, you can do this. Typically depending on the situation you will probably find an alternative however. Either way, you may want to alter your implementation depending on the OS you are using, like in these answers:

Windows: here

Unix: here

max_settings
  • 59
  • 9
  • thanks for your infinite loop answer. About the post, I was asking if it's okay the code structure I thought, in my scenario where I have a finite amount of options to offer a user. Is it okey to have only one connection/cursor started in the beginning of the program and finished when te program is closed? Or there is a better alternative in this scenario – Marcos Tarnoski Aug 26 '21 at 00:24
  • @MarcosTarnoski - Oh yeah, I see. I don't think there is an issue with doing it that way, assuming you are connecting to the same resource the whole time. Depending on how long the program takes to execute, you may need to put checks in to make sure the resource (server) is available. Backups etc can get in your way. That said, in full production, I think a more elegant solution would be preferable. But just to get it functioning, I don't see an issue. I would go with what works for you, and then refactor it as you make changes and learn more best practices. – max_settings Aug 27 '21 at 07:25
  • Thanks for your knowledge ! If you have any resource for learning more about best practices would be nice! Thanks again for your time – Marcos Tarnoski Aug 27 '21 at 22:52
  • To be honest I would speak to people in either your department at work or college or whatever, as best practices do change depending on what you are working on and with who. This has been my experience in the short time I have worked in the industry. – max_settings Sep 03 '21 at 02:13