Is this the right way to structure database initialization calls in mysql/python?

Question

I'm new to mysql, python and the mysql Python connector. Wanting to learn best-practice. I have an initialization method to create the db, create the tables and/or populate it with data (depending upon what's missing).

When cursor gets overwritten does it cleanup any resources anyway? Could something be done better?

def initialize(config):
    connection = None
    cursor = None

    # open the database connection
    try:
        connection = mysql.connector.connect(**config["DATABASE"]["CONNECTION"])
        cursor = connection.cursor()
    except mysql.connector.Error as err:
        print(err)
        exit(1)

    # connection successful, check if the db exists already
    name = config["DATABASE"]["NAME"]
    try:
        cursor.execute("USE {}".format(name))
    except mysql.connector.Error as err:
        print("Database {} does not exist".format(name))
        if err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Creating {}...".format(name))
            try:
                cursor.execute(
                    "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8mb4'".format(name)
                )
            except mysql.connector.Error as err:
                print("Failed creating database: {}".format(err))
                exit(1)

            print("Database {} created successfully".format(name))
            # set the active database
            connection.database = name
        else:
            print(err)
            exit(1)

    # also set connection to use the new database
    config["DATABASE"]["CONNECTION"]["database"] = config["DATABASE"]["NAME"]

    # load and run schema
    # safe to do as it doesn't create tables if they exist
    with open(config["DATABASE"]["SCHEMA"], "rb") as f:
        tables = f.read().decode("utf-8").split(";")
        for table in tables:
            table = re.sub("[\n\r]", "", table)
            if len(table) > 0:
                try:
                    cursor.execute(table)
                    connection.commit()
                except mysql.connector.Error as err:
                    print(err)
                    exit(1)

    # if there is test data
    if config["DATABASE"]["DATA"] != "":
        # if the db is empty
        cursor.execute("SELECT * FROM account LIMIT 1")
        # must fetch the data for the cursor
        cursor.fetchone()
        if cursor.rowcount == 0:
            # populate
            with open(config["DATABASE"]["DATA"], "rb") as f:
                tables = f.read().decode("utf-8").split(";")
                for table in tables:
                    table = re.sub("[\n\r]", "", table)
                    if len(table) > 0:
                        try:
                            cursor.execute(table)
                            connection.commit()
                        except mysql.connector.Error as err:
                            print(err)
                            exit(1)

    # cleanup
    cursor.close()
    connection.close()

Kyle Davis · Accepted Answer · 2023-11-01 15:47:16Z

1

When working with resources such as file handles, network connections, or database connections in Python, we want to close them after use. We use a context manager in Python.

A context manager in Python is an object that defines the methods __enter__() and __exit__(). It is used with the with keyword to ensure that resources are acquired and released properly.

We can use a decorator from context lib to make a simple context manager.

Import Required Modules:

import mysql.connector
from contextlib import contextmanager

Define the Context Manager:

@contextmanager
def mysql_connection(config):
    connection = mysql.connector.connect(**config["DATABASE"]["CONNECTION"])
    cursor = connection.cursor()
    try:
        yield cursor
    finally:
        cursor.close()
        connection.close()

We establish a connection to the MySQL database and create a cursor.
The yield keyword yields control back to the block of code that follows the with keyword.
The finally block ensures that the cursor and connection are closed, regardless of whether an exception occurred.

Using Using the Context Manager

with mysql_connection(config) as cursor:
    cursor.execute('SELECT * FROM your_table')
    result = cursor.fetchall()
    print(result)

Since transactions in MySQL are managed at the connection level, you might want to handle them within the context manager as well.

@contextmanager
def mysql_connection(config):
    connection = mysql.connector.connect(**config["DATABASE"]["CONNECTION"])
    cursor = connection.cursor()
    try:
        yield cursor
        connection.commit()
    except:
        connection.rollback()
        raise
    finally:
        cursor.close()
        connection.close()

We commit the transaction if everything goes well, and roll back if an exception occurs.

References:

edited Nov 1, 2023 at 15:47

answered Oct 25, 2023 at 22:15

Kyle Davis

1061 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mr.b Over a year ago

I didn't know about contextmanager previously, this looks very useful indeed. Thanks for the heads-up and example.

Kyle Davis Over a year ago

To add further clarification, the with statement is used to work with context managers. For example, with open("example.txt", "r") as f; in this example, open is the context manager for files. As a file needs to be opened, and closed. The open context manager, opens the file, and returns the handler, we can assign the file handler, using the as keyword, to a variable. In this case, the cursor is our database handler. It lets us work with the database.

mr.b Over a year ago

It seems I still need to manage the cursor(s) though because I can only commit with the connection, not a cursor?

Kyle Davis Over a year ago

You're on the right track @mr.b In MySQL, transactions are managed at the connection level, not the cursor level. This means that you need to commit or rollback a transaction using the connection object, not the cursor object. This is why you're not able to commit with the cursor. However, this doesn't mean that you need to manage cursors manually. Please see edit to my anwser.

Collectives™ on Stack Overflow

Is this the right way to structure database initialization calls in mysql/python?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related