Databases & SQLAlchemy - Flask Tutorial Series #7

Summary

Highlights

• Welcome back to NeuralNine's Flask series! Today we're diving into databases using Flask SQLAlchemy and Flask-Migrate. 🎉
• Start by avoiding those pesky circular imports using the factory pattern - it’s a lifesaver! 🔄
• Learn to set up your database connection string for easy swapping between SQLite and PostgreSQL. 🔀
• We lay the groundwork for your Flask app with essential packages installed via pip. 📦
• Discover how SQLAlchemy helps you map Python classes to database tables effortlessly. 📊
• Unravel the magic behind CRUD operations - create, read, update, and delete database entries with ease. ✨
• See how the app structure is modified to handle models and routes efficiently without errors. 🛠️
• Finally, run the app, connect to the database, and witness the data flow through your Flask application. 🚀

Key Takeaways

• Understanding the importance of avoiding circular imports in Flask applications is crucial when working with databases. 🔄
• Mastering the usage of Flask-SQLAlchemy and Flask-Migrate can significantly streamline database management. 📊
• Switching between different database systems is as easy as changing the connection string, making it highly flexible and adaptable. 🔀
• CRUD operations form the backbone of database interactions and are executed smoothly with SQLAlchemy. 🛠️
• Flask's factories and routings were restructured for optimal performance, showcasing the importance of good app architecture. 🏗️

Overview

Chapters

• 00:00 - 01:00: Introduction to Databases in Flask The chapter introduces the concept of working with databases in Flask by utilizing Flask SQLAlchemy and Flask Migrate packages.
• 01:00 - 03:00: Setting Up the Application This chapter focuses on setting up the application, particularly dealing with databases and models. It emphasizes the importance of avoiding circular imports, which can occur when a database object defined in the application file is used in the models file, and then models are imported back into the application file. Such practices could lead to endless loops causing circular import issues. The chapter proposes adjusting the structure using a specific pattern to avoid such problems.
• 03:00 - 06:00: Creating Models and Avoiding Circular Imports The chapter focuses on setting up the necessary tools for creating models and handling databases in a Flask application. First, it emphasizes the installation of required packages for database operations, specifically flask, SQLAlchemy, and flask migrate, using pip in a virtual environment. The narrator notes that, although they are using the base environment for the tutorial, users should employ a virtual environment for better practice.
• 06:00 - 09:00: SQLAlchemy Configuration and Application Structure The chapter discusses the installation of two required packages and the initial setup of a new application directory named 'DB application'. It introduces the creation of an 'app.py' file within this directory. To prevent circular imports, a strategic approach is necessary for managing the directory structure and code organization. A DB (database) object is introduced as part of the application's structure. Initially, this DB object is set as an empty string, but it is crucial as it will be utilized in another part of the application.
• 09:00 - 13:00: Migrating and Upgrading the Database In this chapter titled 'Migrating and Upgrading the Database,' the focus is on explaining the necessity of a models.py file in a Flask application. The chapter discusses that in Flask, you work with classes instead of database tables to define data structures. Specifically, the chapter illustrates this through the definition of a Person class, which includes attributes such as ID, name, and age. This serves as a bridge between Python classes and database tables when connecting to different database systems such as SQLite, PostgreSQL, or MySQL.
• 13:00 - 18:00: Implementing Routes and Views The chapter titled 'Implementing Routes and Views' delves into the usage of Object Relational Mappers (ORMs), specifically focusing on SQLAlchemy. It explains how ORM facilitates the interaction between Python classes and database tables by migrating these classes into database tables. The benefit of ORM is highlighted as it allows the use of Python syntax to perform database operations such as updates, insertions, and selections. This enables developers to work with database tables using Python classes and obtain results in the form of Python objects, streamlining the integration of database and application layers in Flask applications.
• 18:00 - 22:00: Creating and Displaying Data In the chapter titled 'Creating and Displaying Data,' the discussion focuses on the process of setting up a model within an application. It begins with the need to create a class in the models file and the necessity of importing a DB object into this file. The chapter highlights a common issue of circular imports that might occur when models are imported into an application, and vice versa, which can potentially create a loop. A proposed solution to this problem is using the factory pattern design. This pattern involves creating a function that constructs the application, thereby avoiding circular import issues.
• 22:00 - 26:00: Connecting to Different Databases In the chapter titled 'Connecting to Different Databases,' the focus is on setting up the application to connect with various databases using Flask, SQL Alchemy, and Flask Migrate. The process begins by importing the necessary modules: Flask, SQL Alchemy, and Migrate. The chapter discusses the creation of a database object using these tools, emphasizing the use of SQL Alchemy. This setup forms the foundation for interacting with and managing different databases within a Flask application.
• 26:00 - 33:00: Deleting and Fetching Details of Records This chapter discusses the concept of creating and managing application instances in Flask. It explains the importance of defining a function to create the app, which returns an application object. This ensures that the app is not executed immediately upon import, as importing executes all the code in the file. Instead, encapsulating the app creation in a function means the app is only created when the function is explicitly called. The chapter outlines these steps with examples, using a placeholder name 'uncore' for demonstration purposes.
• 33:00 - 34:00: Conclusion and Further Steps In the concluding chapter, the focus is on setting up and configuring a connection string for an SQL database. It begins with creating a simple SQLite database, which is chosen for its ease of use since it automatically creates the database if it does not exist. The chapter provides options to connect to other databases like MySQL or PostgreSQL, offering guidance on establishing a connection to PostgreSQL. However, it notes that the detailed process of installing and configuring PostgreSQL is not covered, assuming the reader already has it running.

Databases & SQLAlchemy - Flask Tutorial Series #7 Transcription

• 00:00 - 00:30 what is going on guys welcome back to the flask tutorial series here on neural 9 in this video today we're going to learn how to work with databases in flask by using the packages flask SQL Alchemy and flask migrate so let us get right into it [Music] noted all right so we're going to learn how to work with databases in flask in this video today and for this we're going to create a new project or application from scratch because we're going to slightly change the structure
• 00:30 - 01:00 of the files and of the code because in this case here since we're working with databases and with models we have to avoid circular Imports because the idea is if you have an application file an appy file for example and you define a database object and this database object is then used in the models file where you define all the models and then you also import the models back into the application file you can end up in an endless Circle in a circular import and that can cause some issues so we're going to adjust the structure here we're going to use a fact pattern we're going
• 01:00 - 01:30 to talk about this here in a second but first of all what we need to do is we need to install two packages that we need for working with databases and for this you can open up your terminal of course use your virtual environment in this case now for the sake of the tutorial I'm just going to use my base environment but you want to do pip or pip 3 install flask and then SQL Alchemy and flask migrate these are the two packages that you need to install for this video today let me zoom in a little bit they there you go these are
• 01:30 - 02:00 the two packages and once you have them installed we can go ahead and we can create now a new application directory let's call this DB application and let's create now an app py file now this app py file in order to avoid circular Imports we need to now change the idea a little bit because what we're going to have here is we're going to have a DB object now let's say this DB object is now an empty string but this DB object will be needed in another file that we're going to have
• 02:00 - 02:30 called models.py now why do we need a models py file think about it that way in our flask application we don't have database tables we have classes so for example what we're going to do in this video is we're going to define a person Class A person can have an ID a person can have a name an age uh and other attributes but this is all just a class in python in the database if you're connecting to a sqlite database or to a postgress database or mySQL database you are connecting to database tables now what
• 02:30 - 03:00 the omm does an object relational mapper which is SQL Alchemy in this case what it does is it convert it migrates so to say the classes into database tables it connects it to Worlds so that you can work with classes and flask and you can basically translate all the actions all the updates all the Creations all the insertions and so on in database language so that it all ends up in tables and that you can also select tables and get uh python objects as an answer now in order to Define that what
• 03:00 - 03:30 we need to do in the models file is we need to create a class and so on but we also need to import this DB object in the models file now however once we have the model defined here we also need to Define we also need to import the model from the models file into the application and then what happens is you import models and app you import app and models and it creates this circular import so we're going to use a design pattern uh I think it's the factory pattern where we have a function that creates the application then we have a
• 03:30 - 04:00 separate file called run py which is going to run the application so the basic idea here is we're going to import from flask import flask from flask SQL Alchemy we're going to import SQL Alchemy and from flask uh migrate we're going to import migrate these are the Imports now and what we do now is we Define a database object we say DB equals SQL Alchemy so
• 04:00 - 04:30 that we can import it in models but then what we do is we create a function which we call create app and this function creates the application and returns it as an object so that it's not always executed when you import this um this appy file because when you import something you execute all of the code however if it's a function you don't call the function so what we're going to do here now is I'm going to say um equals flask uncore uncore name uncore uncore
• 04:30 - 05:00 template folder equals templates as we did it before now what we need to do here is we need to configure the string for the SQL database so I need to say okay what am I going to connect to and we're going to start here with a simple SQL light database because if it doesn't exist it's just going to create it it's the most simple database if you want to connect this to mySQL or postgress you can do that I can show you how to do that with postgress I'm not going to go through the installation of postgress and setting up postgress if you don't have post already running it's not going
• 05:00 - 05:30 to work but you can connect this to any type of database so you can say here app.config and then you have to say SQL Alchemy uncore database uncore URI is equal to and then you need this string and in the case of a SQL light database you can use it out of the box already you can say sqlite and then colon SL SL slash and then the pth path to the database so if it exists
• 05:30 - 06:00 the path to the database otherwise just the name of the database so in this case we can say current directory and in this current directory I want to have a test db. DB for example so this would now create a database test dbdb in this directory here that is the config now what we also do is we say DB initialize application so DB init app app and then what we do now now is uh we
• 06:00 - 06:30 do a couple of imports now we we're not going to do the Imports yet um because we don't have uh the stuff written out yet but what we're going to do here is we're going to import the routes so the views that we had before like app route Index app route create something or so on uh and we're going to have in these routes they're going to import the models which is again where the circular input uh import would happen uh but for now we're going to leave this empty so we're going to say here Imports later on and then what we want to do is we
• 06:30 - 07:00 want to say migrate is equal to migrate app and DB and in the end we return the application so what we want to do in our run file is quite simple all we do in our run file is we say from App import create app and then we say flask application here is equal to create app and then we just say if uncore name uncore uncore is equal toore uncore
• 07:00 - 07:30 maincore uncore if that is the case just do flask app run host is equal to 000000 and debug equals true that's down this is how you run the application so now instead of running the app py file We Run The Run py file it's as simple as that so let us move on now to the model uh the model is going to be whatever you
• 07:30 - 08:00 want to have in the database so you can either create the database first and then connect uh your application to the database or you can also create the databased tables using uh SQL Alchemy and flask migrate so what we're going to do here is we're going to say from App import DB and then we can define a class let's call it person and this class has to inherit from db. model so we're defining a database model now and we need to have a under _ variable here
• 08:00 - 08:30 called table name like this and this is basically the name of the table in the database let's call our table in the database people and all you have to do now is you have to define the field so I can say PID is equal to db. column and what I do here now is I specify the data type DB integer for example and then I can also do some stuff like primary key equals
• 08:30 - 09:00 true which makes the P ID the primary key integer column in the database table um then I can do name equals DB column and then I can say DB and then text now it depends on the database type what you want to use here so for example in the case of sqlite I think there is only text you don't have something like varar or something but if you're working with postgress you might want to do something like string and then I don't know 255 or something or 128 I don't know know whatever you want to do here
• 09:00 - 09:30 but I'm going to keep it simple here with text and I can say nullable equals false so it's not allowed to be empty then I can do something like H is equal to DB column DB integer and then maybe job is equal to DB column DB text like this then I can also Define a representation Dunder method here just what happens when I print this so I can see more information I can say person with name self. name
• 09:30 - 10:00 and AG self. H that is just a string that you get uh when you when you print a person object here all right now this is a model I have in my application what I need to do now is I need to somehow create a database but in order to do that I first need to use this model in my application and for this we're going to create a fourth F python file which is going to be routes. py you can also call this
• 10:00 - 10:30 views py it doesn't really matter and this routes py here is going to import from flask uh render template and request but it's also going to import from Models the person class and in order again to avoid circular Imports we're going to do this now with a function again so we're going to say def register routes takes the parameter app and DB or the parameters app and DB and to this we
• 10:30 - 11:00 pass now the application and here we can register the route so we can say app route and by the way this is not necessarily the only way you can do that or even the best way it's one way you can do that while avoiding circular Imports it's not the only way and you can uh try different patterns and different approaches here but let's just let's just go ahead and say we have the index function and all we want to do here is we want to say people
• 11:00 - 11:30 is equal to person query all then maybe I want to print string version or actually not print I want to return the string version of this people result here that would be now using the model uh now we don't use any of these but we're going to use them here in a second uh let's also maybe create here a templates directory
• 11:30 - 12:00 but now what I can do is I can go into my application here and I can say in appy um in the function this is important now I want to do the import in the function this is how I'm avoiding the circular import I'm not doing it all the time I'm doing it when I call the function so what I do here now is I say import or actually from routes import register routes and then I call register routes with app and DB like this
• 12:00 - 12:30 so I think if I didn't mess up anything I mean it's not going to work because we didn't uh create anything but I'm not sure if we can even run the application let's see it should probably cause some issue though there you go because we don't have a table people so what we need to do now is we need to somehow migrate this to a database for this we're going to open up the terminal in the directory of our uh project for this I'm going to actually open up this terminal here I'm going to navigate to
• 12:30 - 13:00 um to my working directory so to current here let me just zoom in a little bit and actually I'm going to go into DB application now here I'm going to run the following command flask DB init flask DB init and you can see that this worked and then I can do flask DB migrate and then you can see detected added
• 13:00 - 13:30 table people and in order to now actually create the migration to actually upgrade it I can say flask DB upgrade there you go it's running the upgrade now the important thing is you only do this flask DB in it once and you do the migrate and upgrade every time you make a change in the scheme add a new class add a new model change a field you can always do that here you have now the database I can always change something I can always say I want to have an additional field or I don't want
• 13:30 - 14:00 to have the job field anymore or or I want to change the nullable or the data type I can do all of that but I have to migrate and upgrade every single time so I can open up here this sqlite database can open up a connection and I can say select everything from people and you will see that I have P name H job in the database which is of course great so now what I can do is I can just run this
• 14:00 - 14:30 application and you can see that I get an empty list but it loaded all the people from the database because we don't have any people in the database now what I can do is I can open up again the connection here and I can say insert into people and I can specify the fields uh Fields name H for example values and I can say I want to have Mike being 25 years old and maybe Bob being 30 years old then I can run
• 14:30 - 15:00 this and then I can say select everything from people again you can then see I have these entries in the database and now I can again go run my application actually it was running already I can open it up and you can see I have these two people loaded from the database into flask mapped into a class now I have the objects that I can work with that's the most simple way to do
• 15:00 - 15:30 that so you have again a run file you have an app file where you have a function that creates the application just in order to avoid circular Imports here because routes think about it routes import models models Imports uh from App Imports DB from app and app itself again Imports routes routes Imports models models app and so on if we don't do this in a function here it's going to cause issues so that's the basic idea what we're going to do now in
• 15:30 - 16:00 this video is we're going to Define um or we're not going to Define we're going to create a base template again we're going to create an index and we're going to create a uh Details page where we can actually create users we can or create people uh delete people and also show more details about the people so we're going to say here base HTML and we're going to have the same uh layout that we had before so for the title we're going to have here a block which is going to be the title
• 16:00 - 16:30 block end block and the default title is just going to be default and then here we're going to have a block content and we're going to end the block um yeah that's basically it now for the index what we're going to do is we're going to have index HTML the index HTML is going to extend
• 16:30 - 17:00 this template extends base. HTML and we're going to fill first of all the block title with the value index and we're going to have also the block content and in here we're going to put our content which is going to be a heading index page and then we can have uh we
• 17:00 - 17:30 can list all the people from the database so we can say give me an unordered list and I want to do four person in people and for I want to have a list item here and this list item will be just the person and of course for this to work I need to pass first of all I need to
• 17:30 - 18:00 render the HTML render template index. HTML and I need to pass people come on I need to pass people equals to people so then there you go we get all the people from the database now what we want to do is we want to provide a form that allows for the creation of a new person so I'm going to go down here here I'm going to say now heading of size three new
• 18:00 - 18:30 person I'm going to create a form I'm going to say that the action of the form is going to go to URL 4 index HTML um and we're going to have a post request so we're going to say method equals post like this and of course for this we need to adjust a route to also accept post so method is going to be equal to get and
• 18:30 - 19:00 post this is all stuff we already covered nothing new and we're going to go ahead now and say we're going to have the following Fields input type text which is going to be the name placeholder is also going to be name and it's going to be required like this then I can copy this and I can say I want to have also a type number which is going to be
• 19:00 - 19:30 H with a placeholder H not going to be required and we're going to have also a job with a placeholder job and it's also not required and then I want to have an input type submit with a value create all right so that's quite simple um and I also want to have line breaks here
• 19:30 - 20:00 there you go so that will not work because we need to say URL for index not URL for HTML but now you can see we have this form here and all I have to do now to use the information of this form and create a new user is I have to go into the route I have to distinguish between the two cases so I can say um if request. method is equal to get then
• 20:00 - 20:30 just do this and otherwise if the request method is equal to post then what I want to do is I want to create a new um a new entry in the database how do I do that first of all I get all the information from the form so request. form doget name I do the same thing for AG I do the same
• 20:30 - 21:00 thing for job H job and then all I have to do is I have to create a new instance of person so I can say person is equal to person name is equal to name H is equal to H and job is equal to job like this uh what's the problem here yeah of course we need to type cast this into an integer there you go and now this object here this python class instance this
• 21:00 - 21:30 python object basically I can just add it to the database by saying db. session. adders and db. session. commit and that is it so I can just copy these two lines here as well so that I can get again the full database table and display it but that is all you need to create a new database instance so let me just rerun this run it open it and then I can say I want to
• 21:30 - 22:00 have uh Sarah 29 programmer create there you go we have a new instance in the database I can say uh John 89 clerk something like this there you go I can create Now new instances which are also of course in the database they're not just in my flask application so I can actually select from people and you can see that they're part of the database and this works not only with SQL light it works with um postgress so
• 22:00 - 22:30 I can easily go ahead now since I have postgress configured I can say create uh DB FL tutorial DB there you go created it and now I can change the connection string to be something else uh I can easily change it to let me just copy this comment this out and say that the string is now going to be postgress ql colon sl/ poost the user
• 22:30 - 23:00 postgress the password very creative at Local Host Port 5432 SL flash tutorial DB now I can run the full application again or actually I need to first uh I think I need to to where is it I need to migrate so I need to say flask DB migrate class DB upgrade
• 23:00 - 23:30 and then I can just uh run the application here so stop and rerun open of course it's going to be an empty database but I can create new entries here as well there you go now I'm connected to my postgress database not to my sqlite database and I have a new database with a new table with new data um this is how easy it is to switch from one database to another one you can do the same thing with my SQL you just need to know how to configure the uh database of course
• 23:30 - 24:00 installing post setting it up might be a little bit more complex but once you have a database running you can just easily change the connection string and everything stays the same I don't need to adjust anything about the model here um yeah so that is the basic idea uh what we're also going to do now is we're going to get we're going to create two more routes and the two more routes are going to be for getting details about a person and deleting a person so I'm going to say here down below Define delete and delete is going to
• 24:00 - 24:30 be an app rout which is going to be delete and then we're going to pass a p ID so a person ID and this is only going to take the method want to keep it professional here delete so this endpoint will only accept the delete method and what we pass here is the P ID so we delete a specific user with an ID and all we have to do in order to accompl accomplish this is we
• 24:30 - 25:00 need to say person query filter and we need to specify which person are we looking for we're looking for a person with a p ID that is equal to P ID and for this person we want to delete it there you go that's it and again the only thing we need to do is we need to commit the session like this and then we can return render template index HTML with uh probably we should go and
• 25:00 - 25:30 say people equals person query all and then we can say people equals people as simple as that so let me just reconnect to the MySQL uh not the MySQL to the sqlite database because we had some data there let me just rerun this um what I can do now is I can just go ahead and say slash delete one for example uh of course this
• 25:30 - 26:00 doesn't work because I'm using a get method when I'm doing it in the browser so we're not going to do it in the browser we're going going to do it directly uh as a JavaScript function so we have this method here uh this route here this function and what we need to do is we need to send a delete request from the front end so from the python uh from the HTML file so we're going to index and for each entry that I have here I'm going to add a button or a link
• 26:00 - 26:30 we could say and this button or link I'm actually going to do it I'm going to make it a button uh this button is going to call a JavaScript function that takes the ID of the specific person and deletes it so we're going to say here button the onclick is going to be equal to delete person and the parameter here is going to be the
• 26:30 - 27:00 person. PID that is what this button does and the text is going to be just delete so for every person we have a button that has an onclick event which calls delete person with the ID of the specific person in this row now we need to Define this function so we're going to say script type text JavaScript and we're going to define the function delete person with P ID as a
• 27:00 - 27:30 parameter and what we need to do here is we need to fetch so we need to send a request we're going to say I want to send a request to URL 4 delete I think this is what we called it right where is it routes delete and we can say also that the uh or actually we should use quotation marks here uh we can also say that the P
• 27:30 - 28:00 ID is equal to P ID because that is a URL parameter we need to pass it here and then we're going to say plus P ID um and I'm going to say here that the function is going to be or actually the method is going to be uh Delete now please excuse that I'm looking at my second monitor C of times I'm not a JavaScript fan so I have to
• 28:00 - 28:30 double check my code here um but this is what we do we send a delete request to this URL endpoint and what we do then is we if it was successful we get to response and we forward it to the if statement if response is okay then we're going to just say window location reload so that we can reload the page and see see what happens uh so we can see the changes and
• 28:30 - 29:00 otherwise we're going to lock into the console and error message console error fail to delete item with P ID and we're going to display the P ID here and finally of course we're going to say catch if we have any errors here we're going to say what we want to do is console
• 29:00 - 29:30 error error during fetching P ID or let's just do it like this error during fetching and then we can print the error message as well so I hope this works let me just indent this properly but the whole Magic happens actually here we're fetching the URL for delete and we're sending uh we're using a delete method to do
• 29:30 - 30:00 that uh yeah so that's basically it let's see if this works now let's open up the application and let's press delete there you go it deletes the entries from the database I can create some entries here delete oh actually it doesn't work when I just post it so let me just load the page again delete there you go so the entries are now gone from the database and you can also right click inspect and see that the delete button actually
• 30:00 - 30:30 contains the ID so delete person for delete person 2 depending on the role so this is how you delete the objects now finally what we want to do is we want to also display some more detail so we want to say app route details and want to get the details for a specific person so for this we're going to also get here the PID and we're going to define the
• 30:30 - 31:00 details method that takes the P ID and all we're going to do here is we're going to load the person object from the database and we're going to show the information so we're going to say person equals person query filter where the person P ID is equal to the P ID from the URL and we get the first of these it's always going to be one since the p ID is unique but in case we have many we're just going to do uh we're just going to
• 31:00 - 31:30 get the first one and we're now going to render actually the details HTML which is going to be a new one and the person is going to be equal to person like this and here now we're going to just copy paste the index we're going to call it detail. HTML I think I used just detail right details actually okay let's just call it detail then um and here now what I'm going to do is I'm just going to
• 31:30 - 32:00 say [Music] person and then person P ID and I can list some information something like name going to be person name AG is going to be person H and job is going to be person job like this and the only thing that we need to do
• 32:00 - 32:30 now is we need to add a button or actually this time we're going to use an anchor tag so we're going to use a link uh for each individual person here we want to have uh a link to the details page so a a is going to be uh URL for details and the P ID is going to be equal to
• 32:30 - 33:00 person uh person. PID like this and we're going to say that the text for this is going to be details that should be enough to go to The Details page of the respective person and that's it basically this is how you work with databases in flask this is how you you can migrate how you can use any database
• 33:00 - 33:30 basically the important thing is again to avoid circular Imports this is one way to do it with a factory pattern you have a create app you have a register uh routes method but that is one way to do it you just have to swap out the connection string here and you can use any database you like as long as you set it up properly you define models you migrate them with flask DB migrate flask DB upgrade and that is how you work with databases in flask so that's it for today's video I hope you enjoyed it and hope you learn something if so let me know by hitting a like button and
• 33:30 - 34:00 leaving a comment in the comment section down below and of course don't forget to subscribe to this Channel and hit the notification Bell to not miss a single future video for free other than that thank you much for watching see you in the next video and bye