# Python Flask Tutorial: Full-Featured Web App Part 4 - Database with Flask-SQLAlchemy ## Метаданные - **Канал:** Corey Schafer - **YouTube:** https://www.youtube.com/watch?v=cYWiDiIUxQc - **Дата:** 04.05.2018 - **Длительность:** 29:58 - **Просмотры:** 698,717 ## Описание In this Python Flask Tutorial, we will be learning how to create a database using Flask-SQLAlchemy. SQLAlchemy is a great tool for working with databases because it allows us to interact with the database in an Object-Oriented manner, which is very intuitive once we get used to it. We will be using a SQLite database to get us started and then move on to a Postgres database when we deploy the application. Let's get started... The code for this series can be found at: https://github.com/CoreyMSchafer/code_snippets/tree/master/Python/Flask_Blog ✅ Support My Channel Through Patreon: https://www.patreon.com/coreyms ✅ Become a Channel Member: https://www.youtube.com/channel/UCCezIgC97PvUuR4_gbFUs5g/join ✅ One-Time Contribution Through PayPal: https://goo.gl/649HFY ✅ Cryptocurrency Donations: Bitcoin Wallet - 3MPH8oY2EAgbLVy7RBMinwcBntggi7qeG3 Ethereum Wallet - 0x151649418616068fB46C3598083817101d3bCD33 Litecoin Wallet - MPvEBY5fxGkmPQgocfJbxP6EmTo5UUXMot ✅ Corey's Public Amazon Wishlist http://a.co/inIyro1 ✅ Equipment I Use and Books I Recommend: https://www.amazon.com/shop/coreyschafer ▶️ You Can Find Me On: My Website - http://coreyms.com/ My Second Channel - https://www.youtube.com/c/coreymschafer Facebook - https://www.facebook.com/CoreyMSchafer Twitter - https://twitter.com/CoreyMSchafer Instagram - https://www.instagram.com/coreymschafer/ #Python #Flask ## Содержание ### [0:00](https://www.youtube.com/watch?v=cYWiDiIUxQc) hey there how's it going everybody in this video we'll be creating a database for our application so that we can create real users and posts instead of relying on dummy data and work with these databases in Python we're going to be using SQL alchemy which is a very popular ORM that people use for different databases now if you don't know what an ORM is it stands for object relational mapper and basically it allows us to access our database and an easy to use object-oriented way and the thing I like about it most is that you can use different databases without changing your Python code so if you want to use an SQLite database for testing and a Postgres database for production then all you need to do is pass in a different database URL for SQL alchemy to connect to but all of the code to create the database will be the same and that's what we will be doing in this series we'll use an SQLite database for development and then when we're ready to deploy this application we'll switch over to a Postgres database for production so let's go ahead and get started so we can see what this looks like so first of all we need to install the flask SQL alchemy package and we can do that with a simple pip install so we can say pip install and that is flask - ### [1:08](https://www.youtube.com/watch?v=cYWiDiIUxQc&t=68s) flask-sql alchemy SQL alchemy now there is a regular SQL alchemy package but flask SQL alchemy is a flat specific extension that provides some useful defaults and helpers for a flask application so let's use that one instead so once that is installed we can import this into our application so I'm going to go back to our project file that we have open here in sublime text and this file is called flask blog dot PI for me and at the top here we can import this by saying from flask underscore SQL alchemy import SQL alchemy and that is uppercase there so now let's copy that and after we import that then we need to specify the URI for the database which is where the database is located now we currently don't have a database so let's just choose a location where we want this database to be so for now let's use an SQLite database because it's the easiest to get up and running with and SQL Lite database will simply be a file on our file system so to set this location we need to set this as a configuration so we can say underneath our secret key here we'll say app dot config and this is going to be equal to SQL alchemy and actually this is going to be all uppercase here so SQL alchemy underscore database underscore URI and we'll set that equal to an empty string for now okay so with sequel Lite we can specify a relative path with three ford slashes in the URI so we can say for this sequel Lite database we can say SQL Lite and then a colon and then three sports slashes to specify a relative path and then we'll just call this site dot DB so these three slashes are a relative path from the current file so that means that this site DB file should get created here in our project directory alongside our Python module that we're currently in okay so now that we have that location set now we need to create a database instance and to do that we can come up here right below our configurations and we can say DB is equal to and this will be equal to SQL alchemy and we're going to pass in the app as an argument to that okay so now we have an SQL alchemy database instance and are ready to work with our databases now the great thing about SQL alchemy is that we can represent our database structure as classes and you'll be hearing those classes be called models and doing the database structure this way is actually very intuitive after you get the hang of it now we could put these classes into a separate file like we did with our forms and we will do that in a later video in the series but for now I'm just going to put these within our main application because the imports can get a little weird with these dependencies if I was to separate them right now so in the next video we'll learn how to split these files up properly but for now let's go ahead and create those class models that will be our database structure so each class is going to be its own table in the database so first let's create the user class to hold our users and first let me clean this up here I'm going to separate this app from our imports and put all of this configuration and stuff together and I will put the models right above our dummy post data okay so to create this user model I'm going to create a class and I'm going to call this user and now we're going to import from DB dot model and now within our user model now we can ### [4:56](https://www.youtube.com/watch?v=cYWiDiIUxQc&t=296s) add the columns for this table add the columns for this table so first we're going to have an ID which is going to be a unique ID and this equal to a DB dot column and in the column now we specify what type this is so this is going to be an integer so this will be D B dot integer and this is also going to be our primary key which just means that it is a unique ID for our user so I'll say primary key is equal to true okay and now we can move on to our next column is going to be a username so we'll set that equal to DB column now this username is going to be a string so we can say dB and if you remember in our validation I said that the max length of this is going to be 20 characters so let's just put in a 20 there to say that it's a string of 20 characters now we want the usernames to be unique also so we can pass in a unique attribute or a unique argument here and we can say unique is equal to true and also we have to have a username so this can't be null so we can say nullable is equal to false okay so our email is going to be similar to our username column so I'm just going to copy that and paste this in below here now for our email let's just say that we don't want anything above 120 characters or so we want these to be unique as well so we'll say unique is equal to true and in all the bol equal to false now we're also going to have an image file now this is going to be for the user's profile pictures so we will set this equal to a DB column as well so I'm just going to copy and paste this one in here so this will be a string and I'm going to set 20 characters as the max here and later in the video series we'll see why because we're going to hash these image files that are 20 characters long so that they are all unique now there's going to be a default profile picture that every user starts out with that is the same so users aren't going to have unique profile pictures because sometimes they're going to be able to share those like the default picture for example so I'll take away the unique is equal to true and I'll keep this nullable is equal to false because they have to have at least the default image and now let's set a default value here so this is if we do not create the user with any specific profile image then what is going to be the default and we'll just set this equal to default dot jpg and that is something that we will add later in the series okay so let's keep adding some columns here okay so now let's add a password column because users are going to have passwords and that's going to be equal to DB column of type DB string and I'll paste that in now the string for the passwords again these are going to be hashed so the hashing algorithm that we're going to use is going to make this 60 characters long so I'll do a 60 there and we will again pass in a nullable equal to false now we didn't need a unique of true because you know people can have the same passwords okay so now I'm also going to specify an re PR method and that is a double underscore method which people called under methods or magic methods now if you don't know what this does this specifically is how our object is printed whenever we print it out so this and the double underscore STR method as well so we're just going to set this already PR one for now and we'll see what this looks like in just a second so I'm going to return what we want this to look like when we print out a user object so I'm gonna print out this term user and then I'm going to put some parentheses here and I'll just print out this user's username and email and image file so I'll say self dot username and then I will copy this section here and then put a comma and print out the self dot email image file and I won't print out the password for this user when we print out a user object now if you're new to using classes in a Python and would like to know more about the self variable that we have here and also these magic methods then I do have a complete series on object oriented programming in Python where I go over all of those concepts if this is something that is new to you but I'm not going to go into detail about it here because really it does require its own series to go over ok so now let's create the post class to hold our post so this is going to be similar to our user so I'll just copy and paste this in here but this will be for posts and again we're going to be inheriting from our D model and now we're going to create similar columns so we want another ID column which is going to be exactly the same so I'll post that in there now we want a title and a title is going to be a string column so I'll copy this up to this point here and copy that and paste it in now let's say that we want the title to be a max of let's say 100 characters and now we will say that we have to have a title for each of our posts so nullable will be equal to false okay so now we are going to want a date posted column to hold the date that our post was made and this is going to be a DB column and the type of this column is going to be a DB dot date/time and each of our posts will have to have a date so nullable again is going to be equal to false and now we want a default for this as well so if we don't actually specify a date for when our post was created then we're just going to say that it was posted at the current time and so to get the current time we're going to have to ### [11:25](https://www.youtube.com/watch?v=cYWiDiIUxQc&t=685s) import the date / time module import the date/time module so I'm going to go to the top of our file here and I will say from date/time import the date/time class and copy that and now down here we can say that we want the default to be equal to date/time dot UTC now I'm not going to put parentheses to actually run that function or else we'd be saying that we want the default to be the current time right now we don't want the parentheses there because we want to actually pass in that function as the argument and not the current time and you always want to use UTC times when saving dates and times to a database so that they are consistent okay and lastly all of our posts are also going to have content so we will say content is equal to and this is going to be a DB column and the type of this column is going to be DB dot text and again the content will be required for every post so we say nullable is equal to false and save that now we'll be using an re PR for this class as well so let's paste that in and instead this will be we'll print this out as a post and we want to print out the self dot title and we'll also date posted so that we can see those values of each instance and I will remove that user file I'm not gonna print out the content because the content could be extremely long and if you're looping through posts and printing them all out I just want a short description of that post and the title and the date posted will be good enough ok so one thing you may have noticed is that I haven't added the author to the post model yet so the post model and the user model are going to have a relationship since users will author posts so specifically this is going to be called a one-to-many relationship because one user can have multiple posts but a post can only have one author and this is how we do this in SQL alchemy so we can say up in our users model here I will create a post attribute and set this equal to now this isn't going to be equal to a column this is DB dot relationship I spelled that wrong relationship there we go okay so now we're gonna say that this has a relationship to our post model pass that in as a string and then we're gonna specify a back ref here and we'll go over this in just a second I'm gonna set a back ref equal to author and then also pass in this lazy argument and set the lazy argument equal to true okay so again we're saying that our post attribute has a relationship to the post model now the back ref is similar to adding another column to the post model what the back ref allows us to do is when we have a post we can simply use this author attribute to get the user who created the post and if that doesn't make sense yet then we'll see this in action and just a bit now the lazy argument just defines when SQL alchemy loads the data from the database so true means that SQL alchemy will load the data as necessary in one go so this is convenient because with this relationship we'll be able to simply use this post attribute to get all of the posts created by an individual user now notice that this is a relationship and not a column so if we were to actually look at our actual database structure in some kind of SQL client we wouldn't see this Paula this post column here this is actually just running an additional query in the background that will get all of the posts this user has created okay so now to specify the user in the post model we can add the user ID for the author so if I come down here into our post model then we are going to create an attribute called a user ID and we're going to set this equal to and this will be a DB column dot integer because it's going to be the primary key of our user and now we need to specify that this is a foreign key which means that it has a relationship to our user model so we're gonna say DB dot foreign key and then we specify what that is so with a string we can say user dot ID now it'll be required that each post has an author so we're also going to pass in a nullable is equal to false as usual okay so again this is going to be the ID of the user who authored the post now it might be a little confusing because we're using an uppercase P here in the user model when we define the relationship with the post but we're using a lowercase U for the user ID in the post model and that is because in the user model we're referencing the actual post class and in the foreign key we're actually referencing the table name and the column name so it's a lower case so the user model automatically has this table name set the lower case user and the post mom we'll have a table name automatically set to lowercase post now if you want to set your own table names then you can ### [16:55](https://www.youtube.com/watch?v=cYWiDiIUxQc&t=1015s) set a specific table name set a specific table name attribute but since our models are pretty simple we'll just leave those as the default lowercase values oh and one mistake that I see here that I made you probably notice this when I did it but whenever I created this email this is supposed to be email here and I actually left that as username so that should have been email so sorry about that okay so now that we have these data base models that represent the structure of our database we can now use those to create the database so to do this we're going to use the command line so I'm going to open up my terminal here and I've still got my virtual environment activated and am navigated to the project directory so within here I'm going to say Python and you have to be within your project directory to get these imports to work so now I'm going to say from flask blog import DB so flask blog is my application file and DB is the instance of our database that we created so I'll import that now we didn't get any errors but we did get this output from SQL alchemy don't worry about that it's just some information so now we can actually create our database by saying DB dot create underscore all and use that create all method so if I hit enter you can see that we don't get any errors so that should have created our database now since we're using a sequel Lite database for now this is actually going to be a file on our file system and we set our database URI to be a file in the same directory as our application and called that site dot DB so if we look at our project directory then we can see that now we have a site DB file here in our project directory now I know that might be a little hard for you to see in my sidebar but I have a Finder window open here with my project and we can see that we have a site DB file here that was created ok so now we have that database but it's currently empty now it's also easy to add data to our database using the command line now we'll be using our application to add this data later but let's go ahead and add a user and a post just so we can see how this works so I will go back to the command line and now I want to import those models so I'll say from flask blog import and the name of our models was user and post so let's import those and now let's create an instance of our user so I'm just going to call this user 1 and I will set this equal to our user class and now I'm going to pass in all of the attributes that this needs so it needs a user name so I'll just set that equal to Cory and also I'll pass in an email so for an email i'll just say see at demo comm and it also needs a password so we'll say set a password equal to password for now so we're setting a plaintext password for this example but we're going to be hashing these passwords when we do this through our application also notice that we didn't specify an ID or an image file for this user so since the ID is our primary key it will assign this user a unique ID automatically and we have a default value for our image file if you remember if we don't provide that then it will just get that value of default dot jpg that we specified in our model so now let's tell our database that we're going to want to add this user so we can just do a DB dot session dot add and we want to add that user one that we just created so if we run that we can see we don't get any errors now that hasn't actually added the user to our database we've just told it that we have a change that we want to make to our database so we could have several changes at a time and add them like this and then when we commit those changes then it will make all those changes to the database at once so for example if I add another user here so I'm going to call this user too and I will set the username equal to John Doe and set the email to JD at demo calm and we'll keep the password sent to password and now if I also add this user so I'll add user to now let's commit those changes to actually commit those to the database so we can say DB session dot commit and now that those are committed they should now actually be in the database and SQL alchemy makes querying the database extremely easy so let's look at a couple of common queries so if we want to just get all of the users then we can simply say and I'm gonna clear my screen here then we can simply say user dot query dot all to get all of our users and we can see here that it returns a list of both users that were created so here is our first and here is our second now if we just wanted to get the first user then we could access it from the list that we that was just returned or we could use the first method that gives you the very first result so I could say user dot query dot first and if I run that you can see that we just get that first user back now we can also filter ### [22:09](https://www.youtube.com/watch?v=cYWiDiIUxQc&t=1329s) filter the results by using the filter by method the results by using the filter by method so if we wanted to filter by user names then we could say so user dot query and use this filter by method and we could say that we want to filter by users who have a username equal to Cori and then we can do a dot all to return all of those results and we can see that we just get a list of one user there with that matching username now if there were multiple people with that same username then that would return a list of all those but that should never happen in our application since our username is unique and we can use the first method here as well so if we just grab that first result without it being in the list then we can just do a dot first there and we can see that we just get that user with no list okay so now let's look at this user that is getting returned so I'm going to use that same query again but I'm going to capture it in a variable so I will say user is equal to user dot query filter by user name equals query dot first so if I hit enter now our user is that user that was returned from that query but now we should have access to some additional attributes so I could say user dot ID and we can see that user has an ID of one now we can actually perform ### [23:26](https://www.youtube.com/watch?v=cYWiDiIUxQc&t=1406s) perform queries using the id queries using the ID as well so we already saw user dot query all and user dot query dot first but if we were to say user is equal to user dot query dot get then this will fetch a user with a specific ID so if I get a user with the ID of one which should be that same user then we can see that we get that same user as a result so now let's look at this users post by saying user dot posts and we can see that returns an empty list because this user doesn't have any post at the moment now remember what I said when we wrote our model so let me bring this up just so we can see it here so when we wrote the model I said that the post attribute isn't actually a column itself that is actually running an additional query on the post table that grabs any post from that user so let's create some posts written by this user so that we can see what this looks like so I'm going to go back to our command line now I'm going to clear our screen here again so that we have more room now we still have access to that user so I still have that first user with a user ID of one so now let's create a post so I'll say post underscore one is equal to post and now we need to pass in all of the values that a post needs so it needs a title so I can just do a title of blog one it also needs some content so I'll set the content equal to first post content with an exclamation point and then it needs an author and the author is a user ID so I'll say user ID is equal to our current user dot ID so let's run that and also notice that when we created this post we didn't specify a date for this post so if we remember from our model we have a default date of UTC now so it should populate the date with the current UTC time if we didn't provide anything which we didn't ok so now let's add one more post that is similar so I will go up here and change the variable name I'll change this to post two and we will set the title equal to blog to the content equal - second post content and we will keep the user ID the same okay now remember we've created these posts but they're not added to our database yet to add them to the database we need to do a DB session dot ad and we can add post one and also add post two and then we can do a DB session commit to commit both of those at the same time okay so now that those posts are committed to the database now let's look at this users posts again so I'll say user dot posts and we can see that now this user has a list of two blog posts and this result is just a list so we can loop through these just like any other list so I could say for post in user dot posts and within this list I will print out or within this loop the post dot title so if I run that then you can see it loops through our user posts and prints out both of the titles for that okay so now let me get the first of these posts by actually querying the post table directly so I'll say post is equal to post dot query dot first like we've seen before and if I look at that then we can see that we got the first post then now we can get the ID of the user who created this post just by accessing that column so I could do a post dot user ID and we can see that user ID is equal to one so we got the right ID for the user who created this post but in the post table that's the only information about the user that we have we might want more information about that author now if we remember back to our model if I open this up here then the post relationship from our user model lets us not only access that users post but it also adds this back ref of author to each post and that's not an actual column down here in our post model but it allows us to use that to access the user who created the post so if I switch back here to the command line then we can see what that back ref does so if I was to say post dot author which again isn't in our post model but if I was to run that then we can see that we get the and user object that we can work with so that's an extremely nice feature with SQL alchemy okay so now that we've added some sample data just to make sure that everything was working properly now let's delete all of this data so that in the next video we're starting from scratch with a nice new clean database so this is easy to do just like we did with DB dot create all before this time we can simply say and let me clear the screen here we can simply say DB drop all and that drops all of our database tables and rows so now to create those tables fresh one more time then we can just create them again so I'll say DB dot create underscore all and run that and that should have recreated that database structure and now if we query all of our users or posts so I'll say user query dot all then we can see that returns an empty list so that's good and if we do the same for posts or post query dot all and we can see that is an empty list too so those are both empty okay so I think that is going to do it for this video we now have our database created and a way to add data to that database and in the next video we'll be restructuring our application to a package so that we can move these models into their own file and also learn about the other benefits of using packages as an application structure but if anyone has any questions about what we covered in this video then feel free to ask in the comment section below and I'll do my best to answer those and if you enjoy these tutorials and would like to support them then there are several ways you can do that the easiest way is to simply like the video and give it a thumbs up and also it's a huge help to share these videos with anyone who you think would find them useful and if you have the means you can contribute through patreon and there's a link to that page and it's scripts in section below be sure to subscribe for future videos and thank you all for watching you --- *Источник: https://ekstraktznaniy.ru/video/12346*