Getting Started With Flask-SQLAlchemy

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 sequa 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 Elka 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 it 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 sequa 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 using 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 of 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 need 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 need 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 need 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 like 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 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 bit 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

Channel: Pretty Printed

Views: 89,780

Keywords: flask-sqlalchemy, beginner, sqlalchemy, flask, python, database, sqlite, programming, tutorial, flask database

Id: jTiyt6W1Qpo

Length: 15min 27sec (927 seconds)

Published: Fri Jun 14 2019