Title: Use Python's with statement to make cleanup easier for MariaDB in Python

[Grouping author and book data while using the with statement to make resource cleanup easier in Python]

I mentioned in an earlier post that it's important to close MariaDB connection and cursor objects. Those objects use limited system resources so, if you don't close them, those resources may become scarce and impact system performance.

One way to make resource cleanup easier is to use Python's with statement. When a connection or cursor leaves the scope of the with block, that object is automatically cleaned up and its resources are released. You still need to use try except to tell the user about any errors, but you don't need to worry about closing connections and cursors.

Here's this example's main program with the key parts of the with statements highlighted in blue.

# Connect to MariaDB. try: with mariadb.connect( user='Rod', password=rods_password, host='localhost', port=3306 # Default MariaDB port ) as conn: conn.autocommit = True # Create a cursor. with conn.cursor() as cur: # Use the Library_Db database. cur.execute('USE Library_Db') # Recreate the data. drop_tables(cur) create_tables(cur) insert_records(cur) # Select some data. query = ''' SELECT CONCAT(FirstName, " ", LastName) AS Name, Title FROM Authors, Books WHERE Authors.AuthorId = Books.AuthorId ORDER BY Name, Title ''' cur.execute(query) for name, title in cur: print(f'{name:20}{title}') except mariadb.Error as e: print(f'MariaDB error: {e}')

The whole thing is wrapped in try except so the program can take action is something goes wrong. It actually did in my first version of the code because it tried to delete the Authors table before deleting the Books table and that violated the Books table's foreign key constraint. (There's more information about foreign key constraints in my post Create database tables and get table information from MariaDB in Python.) When that happened, I got the error MariaDB error: Cannot delete or update a parent row: a foreign key constraint fails. I reversed the order of the DROP TABLE statements and all was well with the world.

Anyway, inside the try block, the code uses a with statement to open the database connection. It then uses another with statement to open a cursor.

The code performs some database operations, dropping and recreating the tables, and then selects some data from them.

When the second (inner) with block ends, the cursor is automatically disposed of and its resources are released.

When the first (outer) with block ends, the connection is automatically disposed of and its resources are released.

Conclusion

It's not hard to use a try except block to free database connections and cursors, but it's even easier to use a with statement. This is a best practice so you should generally do it so the other programmers don't push you around and tease you at lunch.

Download the example to experiment with it and to see additional details.

For a lot more information about database design and implementation, see my book Beginning Database Design Solutions, Second Edition.