Hey, guys in today's video, I'm going to give you an introduction to flasks equal alchemy. So classical alchemy is one of the more popular extensions for flasks. And it adds support for sea alchemy to your flask app. And sequel alchemy is a way to connect your Python code to your database. So basically there will be a mapping between classes that you create in Python and the objects of those classes with tables in the database in the rows in the database.
So in this video we're going to set up a. Simple model that represents a table in the database, and then we're going to insert some data into the model, and it will get data out of that model as well. So we can see what's in the database.
So before I get started I just want to let, you know that you can join a free course on my site called flask seek welcoming basics. This course, will teach you more about sequel alchemy than what I'll cover in the video it's gonna cover more about queries and relationships. So if you're interested in that just. Go to a pretty printed comm, slash flask SQL, and I'll have a link in the description below, but I have a bunch of videos here that can help you with flasks equal alchemy.
If you are interested in learning more after this video. So to get started in this video, I have a directory created already for what I'm going to do, and I'm going to install flask and flask sequel alchemy. So once I install these two things I will write some code actually I'll do that. Now, while they're installing, so I need to import. Flask from flask and I need to import flask underscore or import from flask underscore sequel alchemy, SQL alchemy, capital, s, capital, Q capital, L and then capital, a followed by Ella me. So sequel alchemy.
So the case has to be correct to use this. And then now instantiate a flask app. And then I'll also instantiate the sequel alchemy objects. So by convention is just called DB, but you can call it whatever you want now past in the app. So if you're using a more complicated pattern like with an. Application, Factory, then you'll use an app on DB instead of this. But for our purpose instance, we're going to keep everything in one file I'm going to use this method of instantiating sequel alchemy and I need some configuration before I instantiate sequel alchemy.
So I'll put the two here as placeholders. The first is going to be sequel alchemy track modifications. So the reason why I'm doing this is just so a warning message doesn't appear when I run the app. So this doesn't actually change. Anything, but it just makes a warning message go away. So the second one is the important one so sequel alchemy, database, URI.
So this is the location of the database. So this can be on your local machine, or it can be on a remote machine. So to determine the URI, you can go to the flask sea documentation and go to connection URI format here.
And then you see some examples. So you see one for Postgres. You see one for my sequel. You see one for oracle. So when you're using a remote server, normally you have. A username, you have a password. You have the location of the server so remote server comm or something like that.
And then you have the name of the database. So in this particular example, actually all three you have Scott as the username tiger as a password localhost as the server. In this case, it's 127.0.0.1, you don't, necessarily need a port unless the port is different from the default from the database, but most of the time it's going to be the default for the database. So for example, my sequel the.
Default port is 3306. Then you have the name of the database. And then you can also use sequel Lite, which I'll be using in this video. The reason why I like using sequel Lite in the videos. And actually in a lot of my projects is because you don't have compatibility issues when creating the database. So when you work with my sequel or a post rest, you have to have additional libraries installed, and they can be tricky to get working on certain computers. So I know that sequin light is a little easier to.
Get working so no one should ever probably use sequel light when they watch my videos, whereas if they try to install my sequel, for example, there are a lot of potential issues that they can run into, and I can't cover all the error cases for sequel or for my sequel because it's just too many, so I'll be using sequel Lite, which is just a database that is a file on your system, and it's actually pretty powerful like it seems like it wouldn't be. But um, you can get a lot of mileage out of using a. Sequel Lite database. So the URI here starts with some slashes. So if you use three slashes, that means it's going to be a relative path.
And if you use four slashes it's, an absolute path, so I'm going to use a relative path. So the database can show up in my project directory. So when we have three slashes, and I'll call this DB sequel Lite 3, you can call it whatever you want, but I like calling them DB dot sequel Lite 3. So once you have those two things, those are the configuration that you need for. Sequel alchemy.
So the next thing we need to do is we need to create a table that represents, or I should say we need to create a class that represents a table in the database. So to do that you create a class. And this class needs to inherit from DB model. So I'll create a class called user, and it's a good idea to make sure the name of the class is capitalized and then needs to inherit from DB model. So DB dot model, so it's, taking this DB object that I instantiate it here and calling dot model on it. So this way this class will then map to a table eventually, so the table doesn't exist yet, but it will in just a moment. So one column that all these models require is an ID column or a primary key column.
It isn't necessary need to be ID. But ID is good enough. So to create a column, you have an attribute. And then you have it equal to DB column and inside of DB column, you need to specify a datatype for the column. So in this case, I want it to be an integer column. So DB dot integer, and then I'll set the.
Primary key to be true, and then I need to create my other columns. So the ID column isn't really important for my use it's used by flats equal alchemy. But for me, I require columns to actually sort the data that I'm interested in.
So for our example, we'll have one column call it name, and another column called location and this column. So DB column will be a string and let's say, 50 characters long, and then we'll have another column called location, which can also be 50 characters long and let's. Add one.
More let's have like a date created column so DB dot column. So this column won't be a string and said, it's going to be a date time. So DB dot date time, and we can also set a default, and we can say that the default is going to be whatever the time is now and to do that, we need to import date/time. So from daytime import day time, and we just pass the function here. So date/time dot. Now we don't need to use the parentheses because this function will get executed when a new row is created.
So we have. Those three and if you want to see some other options for columns, you can look in the documentation here. And you see you have integers strings. Text date time flow boolean pickle type large binary, and they're actually more.
Those are some of the more common ones, but I'd say, the most common ones that you would use are the ones that I have here. So integer string and date/time. So now that I have this class I want to create a table in my database based off this.
So it's, basically going to be a table. That has four columns, ID name location, and they created, and it's going to have all these data types. So to do that I need to first create the database, but since I'm, using classical alchemy, I can create the database in the table in the database at the same time. So I'm using a sequel Lite database. So once I do this we'll see a new file pop up here on the left-hand side, so to create the database and a table in the database, you start up a Python rebel. And then you import the DB object from you were. App so from app import DB and module, not found flask that is because I need to start my virtual environment.
So let me just start that really quick. Okay. So Python again. So from app imports DB, and it works so to create the table in the database I'm going to use DB, create underscore all and once I run this, it will create the new file here on the left-hand side.
And also inside that database file we'll see that it has a table called user. So I, create it. We see DB sequel I, three appear on the left hand. Side there. So now if I open it up using the sequel Lite command lines, I can look at the tables and I see user there. Okay, so I know, I have the database.
So let's, actually add some information into the database, so I'll create a route. And this route will have some values that I could take in so a name and a location, and then I'll call this index. And it takes a name location. And what I'm going to do is first create a new row using this information. So to create a new row in the table. You need to. Instantiate an object based on the class that map's to that table, so I'm going to create a user object from the user table.
And you instantiate this user object, using the data that you want to have, so I, don't need an ID because the ideas generated automatically, and I don't need to DE create it, because they created has a default on it. So I just require the name and the location. So to do this name is going to be equal to name in location equals location. So on the left-hand side is the name of the.
Column and on the right-hand side is the actual data. So if I wanted to I can have liked my name here directly, but instead I'm, using the variable here that comes from the URL. So once you create the user object, you can then add it to the session. So DB dot session, not add user. And this prepares it to be inserted into the database and then DB session. It will actually save that insert into the database.
So those are the only three things I need to do to add some data to the database. And then what I can. Do is I can say, added new user. Ok. So let's, go ahead and run this app. And if I go to my app and type in Anthony and California and hit enter it says, new user, added so let's, try doing that a couple of times Stacy, London and Zack, Sydney.
Okay. So I've done this three times. How can we verify that the data is actually in the database? So if I close this out and use sequel lights to open up the database, again, I can write a query to look at my table and I see the three columns. So not only do I, see the names. And locations that I added, but I see the primary key, so I, see one two and three which are generated by the database and I also see the time that each user was created.
And it was done automatically for me. I didn't have to pass in the time directly. So now let's try to get that data back using a name, so we'll create another route. And this one will take in one value name. And if we pass in a name, what we want to do is we want to query the database, look for that particular name and return. Some. Information about that user.
So to query the database using flat sequel alchemy, the most straightforward way is to use the class so user, followed by dot query followed by a filter. So if you use filter by then you're looking for something directly, so I want to look for the name and I can pass a name here. So this is similar to how we create at the rule in the first place. The left-hand side is the name of the column. And the right-hand side is the actual value we're looking for.
So once again, I can. Just put in Anthony directly, but instead I'm going to pass in the variable because it's going to be taken from the URL. And then dot first will give me the first result since I'm only expecting one first works, if you're expecting more results, you can use all, and I'll talk about that more in the course, but first gives you the very first row. And then we can just set this to be something like user. So once we have the user, then we can display something back to the user actually using the app.
So I can. Say, return the user is located in, and then using an F string, I can say user, dot location. So once again, this dot location represents the column in the database, so it's going to take the user that it finds from the query and get their actual location.
So if it looks for Anthony, and it returns Anthony here as a user, then usually that location will be California. So now that I have that let's go ahead and take a look. So if I go, slash Anthony I, see the user is located in California. Let me make this a. Little bigger so it's easier to see just need to restart this server. Okay. So the user is located in California.
I. Do the same thing with Zach and I didn't, spelled, Zach, right, Sydney and then Stacy London. So we see is very easy to put data into the database and retrieve data from the database.
So obviously there's a lot more. You can do with flash seek welcome. E and that's. Why you should check out, the course to learn a little more because I have many more videos on it, but even then there's a ton. That you can do it's a really powerful library. Because when it comes to working with the database, it's, usually the most important part of your app. So you have a ton of potential things that you can do so that's it for this video like I said, if you want to join, that course, just go to pretty brunette comm, slash flask SQL.
And if you have any questions about this video feel free to leave a comment down below, if you like this video, please give me a thumbs up. And if you have subscribes for my channel. Already please subscribe.
So thank you for watching, and I will talk to you next time.
Dated : 18-Apr-2022