How To Use MySQL Database With Flask - Flask Fridays #9

Introduction: From SQLite to MySQL

What's going on guys? John Elder here from codingme.com and in this video, I'm going to show you how to use the MySQL database with Flask and Python.

All right guys, like I said, in this video we're going to look at using the MySQL database. But before we get started, if you like this video and see more like it, be sure to smash the like button below, subscribe to the channel, give me a thumbs up for the YouTube algorithm, and check out codingme.com where I have dozens of courses with hundreds of videos that teach you to code. Use coupon code YouTube 1 and get $30 off membership. That's all my courses, videos, and books. One time fee is just $49, which is insanely cheap.

Okay, in the last video we set up SQL Alchemy and we set up a database table using SQLite, the database SQLite. SQLite 3 comes with Python. It's already installed. We don't really have to install anything and it's super easy. The problem is it's not really a production level database, right? You need something with a little more power. And in this video we're going to look at MySQL. Now in the next video we'll probably look at Postgres, but in this video I think showing you how to go from SQL to MySQL, you'll see just how incredibly easy it is to swap out databases using SQL Alchemy. You may be able to figure out the Postgres on your own, but we'll get to that part later.

Reviewing the Existing Application

So we've got our user list here. We've got a few things listed here. We want to switch this from the SQLite database to the MySQL database. Now, I'm just going to throw away this data. So stuff in here, I'm just going to toss it out. We're not going to try and migrate it over in this video. That's not what this video is. In this video I just want to show you how to set up all of this with the MySQL database. So first thing we need is the MySQL database.

Downloading and Installing MySQL

So let's head over to mysql.com and this is the website and click on downloads. Now there are paid versions, free versions, all kinds of different licenses. So this could be confusing. What you want is the MySQL Community GPL download. So that's the free version.

Now I'm on Windows, so I want the MySQL installer for Windows, so I'll go ahead and click that. If you're on a Mac, I imagine the Mac link will pop up there. So we have two things here. We've got this sort of baby file at 2.4 megabytes and then we got this main file at 422 megabytes. So, this is an installer, the little one. So, you can install that and then it will download the big file. I've had hassles with that in the past, so I just recommend downloading this guy right here. So, you can download this and again it's going to ask you to log in or sign up, but you don't have to do that. Just come down here and click no thanks, just start my download. And this you could save this anywhere. I would just save it to your desktop. And once it downloads install it.

Now, I'm not going to do that because I've already installed it, but there are a bunch of different screens and it can be confusing. Just take the defaults for basically everything. Don't read into it, just take the default for everything. At one point you'll get to a screen that asked for a password. You need to pick a password. I just picked password123 as we'll see in a minute. Uh pick something, type it in there, remember what that password is cuz we're going to need it. Now, you don't have to set up a new user or anything. We're just going to go with the root user, which gets installed automatically. You may see a screen talking about root, just click yes, next, whatever, and hit the defaults and move on.

So, okay, that's all there is to that. You may have to restart your computer, I'm not sure, after you download that, but uh it'll probably tell you a little message. If this doesn't work, try restarting your computer. There's a little setting during the installation that says run MySQL when your computer starts. So, you'll keep that as the default yes. So, whenever you start your computer, MySQL will start up anyway in the background, so you shouldn't have to worry about it.

Configuring the Flask App for MySQL

So, okay, now we've got that. I should say if you have trouble with the installation, check my Tkinter playlist. I did a bunch of videos on MySQL and Tkinter and in one of those videos I walk through the download. So, I go through all the screens and everything, so you can watch that if you have any trouble or leave a comment in the comment section below.

So, okay, we've got it downloaded, we've got it installed. Now, what do we do? Well, let's head over to our code. And in the last video we installed SQLAlchemy and we also set up this SQLite database called users.db. Right? So, we're not going to be using this anymore. We need to point this to a new MySQL database. Now, we haven't created that database yet, so we'll do that in a minute.

So, what I'm going to do is copy this and paste in another one of these. And let me comment this out and let's say old SQLite DB. And under here, let's go new MySQL DB. All right. So, this stays the same. app.config sequel alchemy_database_uri. The only thing that changes is this address. So, what we want here is MySQL. And then colon forward slash forward slash. And now, we want actually let me just comment this in as well and paste this in. And let's look through this really quickly. So, we want MySQL colon forward slash forward slash. Then you want to put your username. Then you want to put your password. Then you want to put localhost cuz we're hosting this on our computer. And then you want the database name, which we haven't created yet.

So, for username, come back over here. Right here, we want root. That was set up whenever you set up the MySQL database when you downloaded and installed it just a minute ago. So, that's our username. For the password, I picked password123. Now, this is open. If you push this up to GitHub, people will see your password, so I'll talk about later how to hide that. But for now, just getting this going, we'll just put it in there like that. No big deal. All right. Then we put at, and now this is going to be localhost. Right? Because this is where the actual database is. If you're doing this online with a database online, you would put the URL for the actual database, and we'll probably talk about that later when we push this whole thing up to the internet. And now, we just want the database name, and I'm going to call this users. All right. So, that's all there is to that. Now, we're going to have to modify this a little bit, and I'll show you why in just a second.

So, okay, we've got this thing. Now, in order to use this, we have to first create a database.

Installing Python MySQL Connectors

Uh when we did our SQLite database, we didn't really have to create it. It just sort of created it for us. But now, we have to specifically set up this users table. And there's a bunch of different ways you can do that. You can download something like WAMP or XAMPP or maybe even the the MySQL Workbench that got installed when you installed MySQL, but that's kind of complicated. I'm just going to write a quick little Python script to create a database for us. It's probably overkill, but it's, you know, pretty simple. So, I'll just do that. So, I'm going to create a new file, and let's go file, save as, and I'm going to save this as create_db.py. And this is just going to be a little Python file. And we're going to delete this file as soon as we run it one time to create our database. But, uh for one time, I'll just do this.

Now, in order to create a database, we need to be able to connect to the database. So, we need a connector, a MySQL connector. So, we don't have one of those yet. So, let's head back over to our terminal and break out of our server, control C. Clear the screen. Now, there are several MySQL connectors, and some of them work and some of them don't. On any given computer, the first one might work, the second one might work, or the third one might work. Everybody's computer seems to be different. I've never figured out why. So, we're just going to install all three connectors right here, and then hopefully one of them will work. So, let's go pip install, and this is mysql-connector. And that will install that. And the next one is pip install mysql-connector-python. Okay. And then, the last one is my is pip install mysql-connector-python-rf, I believe. Okay, so that's doing that. So, got a bunch of error messages for that one. We don't really care. One of these is going to work. So, okay. So far, so good. That should install the connector.

Creating the Database with a Python Script

And one of those hopefully will work. So, let's head back over to our create_database _db file. And up here at the top, we need to import that connector. So, let's import mysql.connector. Now, I know we installed mysql-connector just now, but we need to put a period here. mysql.connector. That's just how it goes.

So, all right, let's create a variable. I'm going to call this my db. And this will be a mysql.connector.connect. Now, what do we want to connect to? Well, we want to connect to the host equals localhost. That's where our database is sitting. We want the user to equal root. And we want the p a s s w d. Now, that's not a misspelling. That's how you do a pass would. Short for password. To equal whatever password you picked when you installed mysql. So, I did password123. Very complicated. All right, so okay, let's call this my db. Okay, so all right, we've got our little connection here.

So, now let's create a cursor. So, my _cursor and set that equal to my db.cursor. And my db is just our connection we created here. So, a cursor is sort of like a little a little robot or something that will go and and do commands for you, right? It's a little automated thing that does stuff to the database, right? So, what do we want to have that cursor do? Well, we want to create a database. So, let's go my _cursor.execute. And let's go create database. What do we want to call this? We want to call it users. So, this create database command will create a database and it'll create a database called users. Why users? Because up here, that's what we said right here. This is what we want to call this thing. So, we got to create it. That's how we create it. So, that will do it. Strictly speaking, that's all there is to it.

Now, we might we might run into a problem because I've already got a database called users uh because I was playing around with this earlier. So, instead, let's call this our users just to be sure. And we come back over here and change this guy to our_users. All right, so save this. Now, this will do it, but if we want to put something up on the screen and make sure that it was created, we could do that. We could just go my_cursor.execute uh execute another command. This one is show databases. Then, we also want to loop through all of the databases and print them to the screen to see if this new database was in fact created. So, let's go for DB in my_cursor. Let's just print DB. Okay, so that should do the trick.

So, now, if we save this file and run it, create_db.py, that should create our database for us. So, let's come back here and go python create_db.py and boom, it looks like it knocked out, Up, there it is, our users. You can see I've got a bunch of other ones, too. code to me uh there's the users one, world, some system things, performance schema. These get created by default when you set up MySQL, same thing here and here. But, we see our_users, which is the table the database we just created, so we're good to go.

So, okay, almost there. Now, we can come back here and we can just delete this file. I'll leave it in here in case you want to check the code, you know, you can always check a link in the comment section below the video for the code itself, so I'll leave this here, but we're never going to run this file again. If we did, it would create a database again. So, in fact, I'm going to comment this line out just so if accidentally we run this file, it won't create the user database again and you know, delete potentially whatever's in there. I don't know if that would happen or not, but better safe than sorry, so I'll just comment that out.

Troubleshooting the SQLAlchemy Connection

So, okay, we're getting there. Now we've created the database. Now we need to sort of connect it to our app and you would think that this line right here would do that, but no, remember in the last video after we created this thing, we had to use the Python terminal to actually kind of import it and turn it on. But before we do that, we need to make a quick change. And the reason why is because SQL Alchemy comes with a driver or something to connect with my database.

Well, before we make the change, let's just run this and see what the error is that we would get. So, uh if you remember in the last video, we came over here and we ran a Python shell. Since we're using the Git Bash terminal, we have to type in win PTY and then Python. If you're running any other terminal, you would just type in Python. So here, we want to go from hello, we want to import the DB. And that seems okay. Now we need to create it. So, db.create_all, just like we did in the last video and we're going to get an error here. There's no module named MySQLdb.

So, SQL Alchemy needs a module called MySQLdb. It's sort of like a connector, I believe. And for some reason it doesn't get installed in our virtual environment. So, if we copy this and then exit out of here and try to pip install MySQLdb, it says could not find a version. So, I don't even know what this thing is. We could Google it and try and figure it out, but eh, why bother? There's a better solution that I've used in the past. It is called PyMySQL. So, let's go pip install PyMySQL.

Installing PyMySQL and Cryptography

And that also requires something called cryptography to uh, sort of deal with passwords and stuff to hash them and hide them and do all the things, encrypt them and all that good stuff. So, we need to also pip install cryptography. c r y p t o graphy, cryptography. So, okay. That should be fine there. All right.

Updating the URI for PyMySQL

So, now since we're using PyMySQL as sort of like the connector, we need to change our URL in our code just a little bit. So, come down here to our app config and instead of pointing this to MySQL, we're going to point this to MySQL plus PyMySQL. And that's the only change we need to make. So, okay. That looks good. So, let's go ahead and save this.

Initializing and Testing the MySQL Database

Now, we head back over to the terminal and let's winpty again, run our Python interactive shell and let's go from hello import db. Okay. And then, we want db.create_all. That worked just fine and now we can exit out of there. Okay.

So, moment of truth, that should have done it. So, let's go flask run to run our app. And we head back over here, come back here, come back to our add user. You'll notice all of that data is gone because that was in the old SQLite database, which we're not using anymore. It's still in that database, we're just not connecting to it anyway. So, here we can come back here and let's go John Elder and this is [email protected]. We can submit. One user is added. Looks good. Come back. Boom, there it is. So, here we can try it again. Jane Elder. And let's go [email protected]. Submit. User is added. Come back to this page. Boom, Jane Elder is there. So, this is working. We are now using the MySQL database and it was just that easy.

The Power of SQLAlchemy Abstraction

Now, it seemed like a lot of steps here, but it really wasn't that bad. We just have to download and install MySQL, then we manually created a little created a database. Now, you don't have do it this way. If you know a better way, if you have champ on your computer, XAMPP, or WAMP if you're on Mac, I think. Or is WAMP Windows? WAMP is the MySQL Windows server. MAMP is the MySQL Mac server, I think. So, if you have MAMP, WAMP, or XAMPP on your computer, you can create a database in those if you know how to do it. It's a lot easier. You just right click and create a new database. I didn't want to go through setting those up. Also, probably the MySQL Workbench which comes with MySQL when you download it, you could probably use that. But again, I didn't want to mess with it. So, we just created this little file to create our database.

And then, it's just a matter of reconfiguring our database URI. So, here in the last video, we just pointed it to sqlite:///users.db. And if you remember, if we go to file open, we'll see there's that file right there, users.db, right? So, it's still there. So, we could probably Let's see. Let's come back here and undo this and redo that. So, now we've reconfigured to use the sqlite thing again. Nothing else in our code is really changed, but if we come back here and reload, now notice we've got two in the MySQL database. We've got four in the sqlite database. So, it still exists. We can still connect to it. We can still use it. But, we don't want to cuz now we've got the MySQL one. And to use the MySQL one, all we have to do is comment this out and re-uncomment that.

And it really just The only difference is you're pointing it to a different direction. You're pointing it to a different database, right? That's really what it comes down to. It's all we've done. We've changed the location where this thing is pointed. It's not pointing here anymore. Now, it's pointing here. The rest of our code stayed the same. All of this class code, that stayed the same. Our template code on our user HTML page, that all stayed the same, right? All of this stuff that stayed the same. All of this form stuff stayed the same. And that's really all of this stuff, all of our form validation, and all that stuff, adding it to the database, right? Adding a new user. All of this stuff stays exactly the same. We don't have to change it because we're using a different database. That's the beauty of SQL Alchemy. It abstracts that all away. So, you don't have to learn how to do it for MySQL or Postgres or SQLite. You don't have to learn any specific way to do it based on the database. You just do it with the SQL Alchemy way, and then SQL Alchemy converts it into the way it needs to be done for MySQL or Postgres or or SQLite or any of the other databases you want to use, which is why we love SQL Alchemy. It abstracts that away. So, pretty simple.

Conclusion and Next Steps

That's how we do MySQL. Now, you could probably figure out how to do Postgres. You just need to know what the Postgres URL looks like, and that's a 2-minute Google thing. If you already have Postgres installed on your computer, create a new database for it, point your URI towards it, and you should be able to use it. It's That's just that easy. We'll probably go through it just in case, but uh maybe I'll figure that one out on your own. So, give it a try if you want to use Postgres, which we probably will end up using Postgres. It's a little bit better than MySQL. They're both equally okay, but Postgres is a little bit better. Just a little, right? So, we'll probably talk about that in the next video.

So, that's all for this video. If you liked it, be sure to smash the like button below, subscribe to the channel, give me a thumbs up for the YouTube algorithm, and check out codingme.com where you can use coupon code YouTube1 to get $30 off membership so you pay just $49 to access all my courses, over 47 courses, hundreds of videos, and the PDFs of all my best-selling coding books. Join over 100,000 students learning to code just like you. My name is John Elder from codingme.com, and I'll see you in the next video.