Connecting to MySQL database with MySQLdb Flask Tutorial

In this tutorial, we're going to introduce how we interact with a MySQL database using Python. The module that we use to do this is called MySQLdb. To get this, run the following on your server:

Once you have that, make sure it all worked by typing:

That should open a python instance in your server, so then do:

import MySQLdb

So long as that works, do a quick control+d to exit the python instance.

Next, we want to make a Python file that can connect to the database. Generally you will have a separate "connect" file, outside of any main files you may have. This is usually true across languages, and here's why. Initially, you may have just a simple __init__.py, or app.py, or whatever, and that file does all of your operations. What can happen in time, however, is that your website does other things. For example, with one of my websites, Sentdex.com, I perform a lot of analysis, store that analysis to a database, and I also operate a website for users to use. Generally, for tasks, you will use what is called a "cron." A cron is a scheduled task that runs when you program it to run. Generally this runs another file, almost certain to not be your website's file. So then, to connect to a database, you'd have to write the database connecting code again in the file being run by your cron.

As time goes on, these sorts of needs stack up where you have some files modifying the database, but you still want the website to be able to access it, and maybe modify it too. Then, consider what might happen if you change your database password. You'd then need to go to every single file that connects to the database and change that too. So, usually, you will find the smartest thing to do is to just create one file, which houses the connection code. That's what we're going to build today.

import MySQLdb

def connection():
    conn = MySQLdb.connect(host="localhost",
                           user = "root",
                           passwd = "cookies!",
                           db = "pythonprogramming")
    c = conn.cursor()

    return c, conn
		

As a note, we use "localhost" as our host. This just means we'll use the same server that this code is running on. You can connect to databases remotely as well, which can be pretty neat. To do that, you would connect to a host by their IP, or their domain. To connect to a database remotely, you will need to first allow it from the remote database that will be accessed/modified.

Next, let's go ahead and edit our __init__.py file, adding a register function. For now we'll keep it simple, mostly just to test our connection functionality.

from dbconnect import connection
		
...		
		
@app.route('/register/', methods=["GET","POST"])
def register_page():
    try:
        c, conn = connection()
        return("okay")
    except Exception as e:
        return(str(e))
		

Next up, let's build our register page.

The next tutorial: User Registration Form Flask Tutorial