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:
sudo apt-get install python-MySQLdb
Once you have that, make sure it all worked by typing:
That should open a python instance in your server, so then do:
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
Import the module.
Create a connection function to run our code. Here we specify where we're connecting to, the user, the user's password, and then the database that we want to connect to.
Referencing the table will be done in the code that actually works with the table.
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))
We allow for GET and POST, but aren't handling it just yet.
We're going to just try to run the imported connection function, which returns c and conn (cursor and connection objects).
If the connection is successful, we just have the page say okay, otherwise it will output the error.
Next up, let's build our register page.