I find myself each week learning one or more new things, at least from the perspective of my own personal experience. So, I thought sharing it here would be a good way to solidify some of that knowledge, keep a record of it, and to potentially help someone else learn something they didn't previously know.

This week I learned that it's possible to write a python script that can take a list of database backup names, import a database into MySQL, then start up a Flask context, and run a report on the database as it stands, and then rinse and repeat.

Maybe this sounds like a simple concept but trying to achieve this using a mostly standard approach proved to be deceptively challenging. I started off by simply writing a script that imported the backup, then ran the report on the imported data, and put that into a loop. But sadly, I was met with a terminal that hung on the 2nd loop iteration, and so I started to break down what might be happening.

The first thought that came to mind was that the imports that I was making from the Flask app were causing the import to be blocked since the script hasn't terminated yet, so the lock holding the database connection with Flask was still active, but there was no error message to inform me that this was happening, and I assume this is by design, so I used my experience with Flask to rationalize that this was most likely the case.

So, I went and searched for how to simply 'un-import' the modules I had imported from Flask and came across the del keyword in python to get the job done and tried again... Only to be met with the same hanging terminal I had sat with previously. So, it was back to the drawing board.

The next thought I had was that it might be that the modules have been removed, but the Flask app context is still running somewhere. To force the Flask app context to run and stop where I wanted it to, I tried an approach that I had used in a previous task some 2 years ago where I needed to use the Flask app context with send out emails asynchronously.

I then tried importing the app from within the for loop, then using the with app.app_context(): command to force Flask to start and stop only within this specified context. I then ran the script and watched the terminal with bated breath as I waited the minute or so that it took for the database to import in the first loop, then it started with the 2nd as it had before, and then, success! The 2nd import worked, and I got the expected next step output indicating that the report had been completed, which I verified against the output file.

I was pleased with my results as this was not something I had done before, so I was happy I could figure it out and get it working as expected.

You might be wondering why I was trying to do this in the first place, and well, that's a good question. I was tasked with pulling a historical monthly report that required data that has not been saved anywhere other than in the database backups, and that couldn't be gathered from the current live database, so I only had backups to work with to pull this data. Which is what led me to learning about how to manage Flask context alongside database imports.

For those that enjoy a bit of code reading, this is how I structured my script:

import os # for running the database import command

backup_name_list = [
    "backup1",
    "backup2"
]

for backup_name in backup_name_list:
    # do all necessary changes or checks on the db name here
    command = f"mysql -u db_user_name --password='SomePassword' db_name < '/path/to/{backup_name}.sql'"
    os.command(command)

    # import the app variable to allow for the context to be created
    from app_module import app_variable

    with app_variable.app_context():
        # do all Flask related imports here
        from app_module import relevant_app_class_or_function

        # run all relevant code here to build up your data from the loaded backup

        # remove the imported modules
        del relevant_app_class_or_function

    # remove the app variable after the context expires just to be safe
    del app_variable

And that's it, that's how I was able to pull historical data from only database backups.

This is highly specific, and I realize that, but this is also not a tutorial, it's purely an outlet for my personal learning experience, that I would like to share here in the hopes that it becomes either an interesting read or a helpful tip to someone, somewhere out there.

If you've made it this far, I would like to say thank you for reading, and I hope you'll join me on my next post.