How to Use Databases With SQLAlchemy - Flask Fridays #8

what's going on guys john elder here from codeme.com and in this video i'm going to show you how to set up a database with flask and python all right guys like i said in this video we're going to look at setting up a database for flask but before we get started if you like this video you want to 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 codemey.com we have dozens of courses with hundreds of videos to teach you to code use coupon code youtube1 to get 30 off membership with all my courses videos and books for one time via just 49 which is insanely cheap okay it is time to start talking about databases and this is going to take us a few videos because there's a whole lot of stuff to learn but in this video we're going to set up the database we're going to set up a form that we can add things to it you can see we can fill out the form click the button it says submitted okay when we go back we see tim smith is down here and it's very cool now we're going to use something called sql alchemy which is sort of a wrapper that goes around and allows you to use any database that you want so we can use sql lite we can use mysql you can use postgres and we're going to learn how to use all of those we're going to start with sql live because it's the easiest and we'll use that just to set things up and then we can swap out or mysql or postgres or whatever we want to do and i'll show you how to do that so the first thing we need to do is install sql alchemy so let's head over to our terminal here and if your server is running hit control c to break out of there and we just want to pip install flash dash okay so it's installing it and we can pip freeze to make sure that it's in there we see sure enough sql alchemy if this version changes by the time you watch this no big deal this is also installed some other things as well sql alchemy and some other things that were already required may have been installed or may not depending on what's already in there but okay we're good to go now so we can flask run this guy to make sure our server is running again okay so we've installed this now we need to import it so let's head over to our hello.pi file our main flask app form let's go from flask underscore sql alchemy and i always misspell this a-l-c-h-e-m-y all right we want to import sql alchemy now notice the capitalization sql and a all capitalized everything's lower case here and notice this is flask underscore alchemy back here when we pip installed this we pip installed let's see flash dash not underscore that's normal just sort of take note of that so okay we've got this now and while we're at it let's import from date time let's import date time because when we add things to the database we want to keep track of when they were added so we need date time for that so we'll just go ahead and import it now okay so we've got this now the first thing we need to do once we've imported this and installed it is sort of add it to our app here add database so to do that we just go app dot config and square brackets and inside here it's sql alchemy underscore database underscore uri and this is a uniform resource indicator sort of like a url that points to where our database is and like i said we're just going to use sql lite which comes with python right now in the future we'll change this to mysql or postgresql and when we do we'll change this uri to point to whatever whatever other database we want to use but for now we just want to use sqlite and to do that we just go sql lite colon forward slash forward slash forward slash and then let's name a database and i'm going to call it users.db so call this anything you want but right now we're going to create a table that has users so you know if we look back here you see we can put a name and an email address this is going to be a user list eventually we'll turn this into people that can log into the website and stuff but for now we just want to keep track of their names and their email addresses just to get the ball rolling and learn how to use databases with this thing so okay let's create a database called users.db so we've now created it remember here's our secret key comment secret key all right okay so we've got this defined now we need to initialize the database and to do that we just call db or any variable at all but we're going to be working with a database so i'm going to call it db short for database right and this is sql alchemy and we want to pass in app which is this thing right here right so make sure that we spell this right sql alchemy i always misspell alchemy i don't know why so okay that looks right capital c q l and a okay so now let's create a model whenever you're working with databases you need to define the model what do we want to save to the database so let's create one and we do that by creating a class and i'm going to call this users and we want to inherit db dot model okay now here we just define the things we want we want an id we want to be able to keep track of each person each user by giving them a unique id user number four user number 87 right so in the future if we want to delete somebody we don't delete bob smith we delete number 87 because there could be a lot of bob smith's there's only one 87 so we need to create an id i also want to keep track of name i also want to keep track of email and let's also go date added all right so now we just need to define all of these things and we do that by calling db dot column right so we can sort of do this for each of these right and inside of here we sort of define what this is going to be well our id is going to be a number so that's a db.integer that's the data type that we're talking about right there are several different data types depending on the database we're using we're using sqlite so i'm going to call db integer now we also want to set the primary underscore key to true a primary key means basically an id right this has to be unique a primary key is always unique primary key will be added sort of automatically by setting primary key to true so we don't have to assign a number to each person that signs up through our database it'll get assigned automatically using primary key so okay so now our name what do we want this to be this is going to be a db.string it's going to be like a string of characters and we can designate how many characters we want and i'm just going to say 200 which i think is the max i don't i can't imagine a person having a name longer than 50 characters or so we'll just put 200 for fun now we also want to say nullable equals false and nullable means blank right null is nothing blank so we don't want this to be blank so we say nullable equals false so they have to enter their name if they're filling out the form right so okay that looks good and let's just go ahead and grab this stuff and paste it in there as well email address is going to be like i don't know 120 characters we could put probably put this at 100 characters as well and i'll just leave it at 200 for fun okay so nullable equals false now we also want to set the email address to unique so let's go unique equals true why we don't want two people with the same email address signing up right only one person per email address can sign up so we'll put unique equals true so if we come back here for instance and you can see i've got john codaby.com if i try to add another user with john codyme.com it won't let me do that because we've set unique to true okay finally the date added we want this to be a db.datetime and let's set a default we don't want to have to designate the date every time somebody fills out the form we want to we want the database to do that for us so we can set a default on here which will take care of that for us and we'll set that to date time dot utc now which is just a date it's just a time utc is a type of time sort of like a time zone you do i think it's uniform time zone i don't know whatever utc now will put the current date whenever they fill out the form so okay our model has been created now we need to do one more thing we need to come down here and let's uh create a string to sort of designate something here we can go d e f underscore underscore r e p r underscore underscore and we want to pass in self and if you work with django with classes if you want to sort of illustrate the thing that was just put it on the screen you have to create a little string we're sort of doing the same thing here we want to return and then in here let's go name and then percentage r oops one percentage and then outside of here let's go self.name so this will just put name on the screen if we want to return this thing which we probably won't but this is just sort of what you do when you create these models so all right that looks good so we've got this thing but we need to sort of turn it on we need to actually create the model we need to sort of create the database and set this all up from our terminal so we can come over here and run a python shell and let me clear the screen here break out of our server clear the screen so with any other terminal you would just type python to get the interactive shell we're using the git bash terminal so we need to type win pty and then python and you see we get the python interactive shell from here we can create our database so let's go from hello import db so why hello well if we head back over here we call this file hello dot pi that's the name of our app right so from hello we need to import this database guy so okay back over here hit enter okay that looks good now we need to actually create the database so let's go db.create underscore all and this is a function okay and that looks good so now we can exit out of here and let's go flask run just to keep this running as always and we can make sure this was created by looking over here and you see it's not showing up here but if we go to file open we see there's this users.db file that's been created right okay so now we need a form that we can fill out to add stuff to this database so let's come down here and grab this form that we created a couple videos ago and paste it in here and instead of namer forum let's call this user form and we want a name we also want email so let me just sort of grab this and oh actually instead of what's your name let's just type in name here and we also want email and right here let's go email we want the validator so we'll put that in there okay we could probably use an email validator here but for now we'll just leave this as data required and we want a submit button so submit field okay that looks good so now we've got our form we've got our model now we need a route and a function so let's go app dot route and let's put this at let's say user slash add right and we want to set the methods to equal get and post like we learned how to do a couple of videos ago because we're going to be you know posting or getting this page so let's define add underscore user and for now let's just return a thing we don't want to pass anything just yet and let's point this to add underscore user.html okay so we don't have an add underscore user.html page so let's go ahead and create one real quick let's head over here to our templates and right click new file let's go file save as and save this as add underscore user.html and let's head over to our like maybe what index page or name page any one of these grab all of this stuff let's just paste this in and let's see block content leave this in case we want this uh we'll leave this for now because we're gonna do some logic but we don't want this javascript and we don't want this stuff and we don't want this image and for here let's put user list for now and we are going to want a form so we'll leave that and we want probably a couple of these so form this one wants to be email and then form email okay that's probably fine for now okay so that looks good let's go ahead and save this we'll tinker with this in just a second now let's head over to our nav bar and create a link for this so i'm just going to come down here and copy one of these and instead of name let's put this as add user and instead of url for name this is going to be url for add underscore user okay so let's go ahead and save this and run and reload just to see what's going on here oh we've got a routing error and head back over to our index page and uh we've moved our route here so let's copy this and bring it back down there okay so let's save this head back over here hit reload okay so we've got our main page we can go to add user and form is undefined okay we've not passed form yet so let's go ahead and do that so you see in our add user page we've got this form form.hiddenform.whatever but back on our hello.pi file and our add user we haven't created a form yet so we could do that real quick so let's go form equals userform right because up here that's what we called this class for our form that we created earlier right and then let's see where we go inside of here we could just pass in form equals form all right so save this head back over here hit reload this should work now okay and you see we've got this nice user list and email address now this doesn't actually do anything yet right but at least it's up there and we can toggle around and that's cool so okay now how do we make this actually do something well come back over to our add user form and if we look at our add user page we've got this logic for name so let's let's just use that from a previous video so let's set name equals to none for now and we'll talk about that in a minute but for now we need to do some logic to validate our form just like earlier so we can come down here to a last video where we did all of this stuff we're just going to do the same thing right so we could copy and paste it or i'll just type it real quick because it's not a lot we can go if form dot validate underscore on underscore submit all right so if it's validated what do we want to do if the form has been submitted and is valid we need to check to make sure that there aren't any other users in the database that have the same email remember we want a unique email so we can create a variable called user now let's query the database and to do that we go users dot query dot filter underscore by and then we can set email to equal form dot email dot data so that is this field right here when they fill out the field hit the button then that becomes form.email.data and we talked about this in the last video when we talked about forms and what the forms so then here we want to go dot first so what we're saying here is query the database grab all of the users that have the email address of whatever they just typed in to the form and return the first one now there shouldn't be any right because it's unique so this shouldn't return anything right it should return none because there shouldn't be one because this is going to be unique if it returns something then we want to stop but if it doesn't return anything we can run an if statement so we could say if user is none right if there isn't one yet then let's add it to the database right so if we try and submit the form with john codomy.com this will look it up and say are there any users with john codeme.com as their email address if there aren't let's add this new guy if there is that means it already exists and we're not going to add the new guide to the database so how do we add the new guy to the database if there isn't one well if user is none we just call user and we set that equal to users and now we just pass in the stuff from the form so we go name equals form dot name dot data and email equals form dot email dot data right those are the two things we want to add to the database okay so now we go db.session.ad and we want to add this user which we've just defined right here right now we need to commit that so we go to db.session.commit and that's pretty much it so now we can set name to equal form.name.data because up here we've got this name guy we're going to pass back to the page to do some logic with right now we also need to clear the form so let's go form dot name dot data equals nothing and form dot email dot data equals nothing and let's create a little flash message to put up on the screen and let's say uh user added successfully i don't know whatever okay so now down here we want to return form we also want to return name equals name okay so that will add something to the database now we want to actually show what's in the database on the screen so i'm going to create a variable called r underscore users and that's going to be our underscore users now up here we need to define that so let's go our underscore users equals users name of our table right dot query let's look it up and let's order underscore by users.date underscore added so this will return everything in the database right and we're just going to pass all that into the page as our users so okay let's go ahead and save this now let's head over to our page and down here underneath the form perhaps let's put a couple of line breaks and let's create a for loop so let's go for our underscore user in our users all right and we always want to end four right away so we don't forget what do we want to do well let's just put our underscore user dot name space this out and maybe we also want to put our user.email and maybe here we want to put our user.id maybe a period after that i don't know okay so that looks good and let's just put this on a separate line so line break so okay let's go ahead and save this and run it and see how this looks right now there's nothing listed under here so let's go john elder let's go john at kodamy.com we click submit added user addicts successfully we'll hold you on elder we can change that in a minute that's a throwback from the last video now when we come back here to add user we see john elder is listed down here we can say bob elder and we see bob at elder.com somebody here now we could put the database on this screen too if we wanted to go back here and now bob elder is listed so to do that we would just come back over here and let's just sort of grab all of this and we can come up here to this uh this other if statement here so if name instead of saying hello name let's just say uh user added and then below here let's paste that stuff in okay so let's save this head back over here now let's add tina smith tina at smith.com these are added successfully and we see tina smith added right there click that i'm back here and she's there and it's just that easy now i was going to go through here and style this stuff with bootstrap but man this video is getting a little bit long ah it's flash friday let's do it anyway all right so let's go to getbootstrap.com click on the docs come down here to maybe utilities and let's go borders now let's go shadows let's grab a shadow here so here is regular shadow so let's grab regular shadow so we can just copy this head back over here and our user list form let's wrap all this in that div that we just copied so come down here to the form and close it save that come back over here hit reload that does not look good at all oh there we go that should work now save this and hit reload okay so this whole thing is now in a little boxy shadow thing awesome we can add a table here just as easy so let's come down here to all of this stuff and above our for loop let's create a table and then we want to close our table and with bootstrap we can add things to tables let's see where's the tables at content yeah it's under content click on tables and you can scroll down here and see all the things that we can have let's see hoverable rows i want that so we can add table hoverable rows and inside of here we just give this a class of say table hoverable and we have to start out by giving it a class of table and then table hoverable what else do we want let's see borders i probably want a border so i'll put i'll grab this guy space and paste what else uh striped rose i kind of like that here it is table striped save this or copy this paste that in all right that's probably good for now okay so that's the definition of the table now we need to inside of here create a table row and instead of this br let's end our table row and for each of these we need a td and then a closing td and we just have one column so all right save this now we head back over here and hit reload we get this nice little table all right that's looking good and if we want we can do the same thing so i'm just going to copy all of this and bring it up here to this we paste that in there so now whenever we fill this out so mary elder mary at elder dot com click submit boom it looks good there too all right we're moving right along maybe we don't need quite so many line breaks there okay so that's how you set up a database with sql lite and sql alchemy and flask uh you know probably in the next video we'll dive into using my sql and postgres i'll show you how to set those up instead of sqlite because sqlite is great for development purposes but it's not a industry sort of standard database that you want to use for production you want to use mysql or postgresql or something else like that for when we actually make this website live online but for testing purposes and just to learn how to use sql alchemy sqlite works great and this thing is coming right along so that's all for this video if you like to be sure to smash the like button below subscribe to the channel give me a thumbs up for the youtube algorithm and check out coding.com you can use coupon code youtube1 to get 30 off memberships you pay just 49 taxes all my courses over 47 courses hundreds of videos in the pds of all my best-selling coding books join over a hundred thousand students learning to code just like you my name is john elder from codabee.com and i'll see in the next video

Channel: Codemy.com

Views: 63,992

Keywords: flask sqlalchemy tutorial, flask sqlalchemy, flask sqlalchemy sqlite, flask database tutorial, flask database sqlite, flask database connection, flask database setup, flask search database, flask display database table, flask database sqlalchemy, flask add database, codemy.com, john elder, john elder flask, flask tutorial #8, codemy.com flask, sqlalchemy python, sqlalchemy flask tutorial, python flask tutorial for beginners, flask sqlite, flask sqlalchemy datetime

Id: Q2QmST-cSwc

Length: 25min 35sec (1535 seconds)

Published: Fri Mar 05 2021