title
Python Flask Tutorial: Full-Featured Web App Part 4 - Database with Flask-SQLAlchemy

description
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

detail
{'title': 'Python Flask Tutorial: Full-Featured Web App Part 4 - Database with Flask-SQLAlchemy', 'heatmap': [{'end': 95.352, 'start': 74.457, 'weight': 0.816}, {'end': 1117.734, 'start': 1037.301, 'weight': 0.861}, {'end': 1316.901, 'start': 1293.923, 'weight': 0.711}], 'summary': 'Tutorial series on python flask explores creating a database with sqlalchemy, setting up sqlite database, switching to postgres, defining relationships, creating database structure, adding users and posts, and using sqlalchemy queries in python for efficient database operations.', 'chapters': [{'end': 38.202, 'segs': [{'end': 38.202, 'src': 'embed', 'start': 0.209, 'weight': 0, 'content': [{'end': 1.189, 'text': "Hey there, how's it going everybody?", 'start': 0.209, 'duration': 0.98}, {'end': 8.192, 'text': "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.", 'start': 1.53, 'duration': 6.662}, {'end': 15.795, 'text': "And to work with these databases in Python, we're going to be using SQLAlchemy, which is a very popular ORM that people use for different databases.", 'start': 8.552, 'duration': 7.243}, {'end': 24.658, 'text': "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 in an easy to use,", 'start': 16.195, 'duration': 8.463}, {'end': 25.538, 'text': 'object oriented way.', 'start': 24.658, 'duration': 0.88}, {'end': 31.46, 'text': 'And the thing I like about it most is that you can use different databases without changing your Python code.', 'start': 26.018, 'duration': 5.442}, {'end': 38.202, 'text': 'So, if you want to use an SQLite database for testing and a Postgres database for production,', 'start': 31.8, 'duration': 6.402}], 'summary': 'Creating a database using sqlalchemy for accessing databases in an object-oriented way, enabling use of different databases without changing python code.', 'duration': 37.993, 'max_score': 0.209, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc209.jpg'}], 'start': 0.209, 'title': 'Creating a database with sqlalchemy in python', 'summary': 'Discusses the process of creating a database for the application using sqlalchemy, an orm for accessing databases in an object-oriented way. it highlights the flexibility of sqlalchemy to work with different databases without changing python code.', 'chapters': [{'end': 38.202, 'start': 0.209, 'title': 'Creating database with sqlalchemy in python', 'summary': 'Discusses creating a database for the application using sqlalchemy, an orm for accessing databases in an object-oriented way, and its flexibility to work with different databases without changing python code.', 'duration': 37.993, 'highlights': ['SQLAlchemy is used to create a database for real users and posts, eliminating the reliance on dummy data. This emphasizes the purpose of using SQLAlchemy to create a real database.', 'ORM (Object Relational Mapper) allows easy access to the database in an object-oriented manner. Explains the concept of ORM and its benefits for database access.', 'SQLAlchemy enables the use of different databases without modifying Python code, such as SQLite for testing and Postgres for production. Highlights the flexibility of SQLAlchemy in working with various databases without changing the Python code.']}], 'duration': 37.993, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc209.jpg', 'highlights': ['SQLAlchemy enables the use of different databases without modifying Python code, such as SQLite for testing and Postgres for production.', 'ORM (Object Relational Mapper) allows easy access to the database in an object-oriented manner.', 'SQLAlchemy is used to create a database for real users and posts, eliminating the reliance on dummy data.']}, {'end': 585.361, 'segs': [{'end': 110.151, 'src': 'heatmap', 'start': 74.457, 'weight': 0, 'content': [{'end': 76.979, 'text': 'Now there is a regular SQLAlchemy package,', 'start': 74.457, 'duration': 2.522}, {'end': 83.604, 'text': 'but Flask SQLAlchemy is a Flask specific extension that provides some useful defaults and helpers for our Flask application.', 'start': 76.979, 'duration': 6.625}, {'end': 85.585, 'text': "So let's use that one instead.", 'start': 83.924, 'duration': 1.661}, {'end': 89.808, 'text': 'So once that is installed, we can import this into our application.', 'start': 86.005, 'duration': 3.803}, {'end': 95.352, 'text': "So I'm going to go back to our project file that we have open here in Sublime Text.", 'start': 89.828, 'duration': 5.524}, {'end': 98.996, 'text': 'And this file is called flaskblog.py for me.', 'start': 95.852, 'duration': 3.144}, {'end': 110.151, 'text': 'And at the top here, we can import this by saying from flask underscore SQL alchemy, import SQL alchemy.', 'start': 99.337, 'duration': 10.814}], 'summary': "Flask sqlalchemy is a flask-specific extension providing useful defaults and helpers for the flask application, to be imported using 'from flask_sqlalchemy import sqlalchemy'.", 'duration': 35.694, 'max_score': 74.457, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc74457.jpg'}, {'end': 185.6, 'src': 'embed', 'start': 156.874, 'weight': 2, 'content': [{'end': 160.477, 'text': 'So SQLAlchemy underscore database underscore URI.', 'start': 156.874, 'duration': 3.603}, {'end': 164.841, 'text': "And we'll set that equal to an empty string for now.", 'start': 161.758, 'duration': 3.083}, {'end': 171.527, 'text': 'Okay, so with SQLite, we can specify a relative path with three forward slashes in the URI.', 'start': 165.121, 'duration': 6.406}, {'end': 183.658, 'text': 'So we can say for this SQLite database, we can say SQLite, and then a colon, and then three forward slashes to specify a relative path.', 'start': 171.867, 'duration': 11.791}, {'end': 185.6, 'text': "And then we'll just call this site.db.", 'start': 183.959, 'duration': 1.641}], 'summary': 'Setting sqlalchemy database uri to an empty string for now. using sqlite to specify relative path for site.db.', 'duration': 28.726, 'max_score': 156.874, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc156874.jpg'}, {'end': 239.317, 'src': 'embed', 'start': 213.318, 'weight': 1, 'content': [{'end': 219.521, 'text': "and this will be equal to, SQLAlchemy and we're going to pass in the app as an argument to that.", 'start': 213.318, 'duration': 6.203}, {'end': 225.486, 'text': 'Okay, so now we have an SQL Alchemy database instance and are ready to work with our databases.', 'start': 219.961, 'duration': 5.525}, {'end': 231.27, 'text': 'Now, the great thing about SQL Alchemy is that we can represent our database structure as classes,', 'start': 225.806, 'duration': 5.464}, {'end': 233.612, 'text': "and you'll be hearing those classes be called models.", 'start': 231.27, 'duration': 2.342}, {'end': 239.317, 'text': 'And doing the database structure this way is actually very intuitive after you get the hang of it.', 'start': 234.133, 'duration': 5.184}], 'summary': 'Using sqlalchemy to represent database structure as classes, making it intuitive.', 'duration': 25.999, 'max_score': 213.318, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc213318.jpg'}, {'end': 278.335, 'src': 'embed', 'start': 250.232, 'weight': 3, 'content': [{'end': 255.039, 'text': 'because the imports can get a little weird with these dependencies if I was to separate them right now.', 'start': 250.232, 'duration': 4.807}, {'end': 258.62, 'text': "So in the next video, we'll learn how to split these files up properly.", 'start': 255.499, 'duration': 3.121}, {'end': 263.064, 'text': "But for now, let's go ahead and create those class models that will be our database structure.", 'start': 258.88, 'duration': 4.184}, {'end': 266.487, 'text': 'So each class is going to be its own table in the database.', 'start': 263.405, 'duration': 3.082}, {'end': 270.349, 'text': "So first, let's create the user class to hold our users.", 'start': 266.827, 'duration': 3.522}, {'end': 272.311, 'text': 'And first, let me clean this up here.', 'start': 270.71, 'duration': 1.601}, {'end': 278.335, 'text': "I'm going to separate this app from our imports and put all of this configuration and stuff together.", 'start': 272.331, 'duration': 6.004}], 'summary': 'Creating class models for database, starting with user class.', 'duration': 28.103, 'max_score': 250.232, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc250232.jpg'}, {'end': 322.434, 'src': 'embed', 'start': 293.33, 'weight': 4, 'content': [{'end': 298.677, 'text': 'And now within our user model, now we can add the columns for this table.', 'start': 293.33, 'duration': 5.347}, {'end': 302.902, 'text': "So first, we're going to have an ID, which is going to be a unique ID.", 'start': 299.037, 'duration': 3.865}, {'end': 307.685, 'text': 'And this is going to be equal to a db.column.', 'start': 303.602, 'duration': 4.083}, {'end': 312.528, 'text': 'And in the column, now we specify what type this is.', 'start': 308.365, 'duration': 4.163}, {'end': 314.569, 'text': 'So this is going to be an integer.', 'start': 312.868, 'duration': 1.701}, {'end': 317.051, 'text': 'So this will be db.integer.', 'start': 314.709, 'duration': 2.342}, {'end': 322.434, 'text': 'And this is also going to be our primary key, which just means that it is a unique ID for our user.', 'start': 317.391, 'duration': 5.043}], 'summary': 'Adding an id column to the user model as a unique integer primary key.', 'duration': 29.104, 'max_score': 293.33, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc293330.jpg'}, {'end': 494.279, 'src': 'embed', 'start': 463.186, 'weight': 5, 'content': [{'end': 465.707, 'text': "Okay, so let's keep adding some columns here.", 'start': 463.186, 'duration': 2.521}, {'end': 470.628, 'text': "Okay, so now let's add a password column because users are going to have passwords.", 'start': 465.967, 'duration': 4.661}, {'end': 477.47, 'text': "And that's going to be equal to a DB column of type DB string.", 'start': 471.188, 'duration': 6.282}, {'end': 479.151, 'text': "And I'll paste that in.", 'start': 478.111, 'duration': 1.04}, {'end': 482.912, 'text': 'Now the string for the passwords, again, these are going to be hashed.', 'start': 479.531, 'duration': 3.381}, {'end': 487.694, 'text': "So the hashing algorithm that we're going to use is going to make this 60 characters long.", 'start': 483.333, 'duration': 4.361}, {'end': 488.575, 'text': "So I'll do a 60 there.", 'start': 487.854, 'duration': 0.721}, {'end': 494.279, 'text': 'and we will again pass in a nullable equal to false.', 'start': 490.175, 'duration': 4.104}], 'summary': 'Adding password column of 60-character hashed strings.', 'duration': 31.093, 'max_score': 463.186, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc463186.jpg'}], 'start': 38.202, 'title': 'Setting up sqlite database and creating database structure', 'summary': 'Discusses setting up an sqlite database for development and switching to a postgres database for production, using flask sql alchemy, and creating database structure by defining class models, specifying columns with types and attributes, and implementing methods for object representation and hashing algorithms.', 'chapters': [{'end': 233.612, 'start': 38.202, 'title': 'Setting up sqlite database with flask', 'summary': 'Discusses setting up an sqlite database for development and switching to a postgres database for production, using flask sql alchemy to create the database and specifying the uri for the database.', 'duration': 195.41, 'highlights': ['Using Flask SQL Alchemy provides some useful defaults and helpers for our Flask application. Flask SQLAlchemy is a Flask specific extension that provides useful defaults and helpers for the Flask application.', 'Setting the location for an SQLite database involves specifying a relative path with three forward slashes in the URI. For SQLite database, a relative path with three forward slashes in the URI is specified, creating the database file in the project directory.', 'Representing the database structure as classes in SQL Alchemy allows for easier manipulation of the database. SQL Alchemy allows representing the database structure as classes, enabling easier manipulation of the database.']}, {'end': 585.361, 'start': 234.133, 'title': 'Creating database structure', 'summary': 'Explains the process of creating database structure by defining class models, specifying columns with types and attributes, and implementing methods for object representation and hashing algorithms.', 'duration': 351.228, 'highlights': ['The chapter emphasizes creating class models for database structure, with each class representing a table in the database.', 'The transcript details the process of defining columns within the user class, including specifying types, attributes, and constraints such as unique and nullable.', 'The explanation includes the implementation of a hashing algorithm for password security, specifically a 60-character length, and the addition of methods for object representation. ']}], 'duration': 547.159, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc38202.jpg', 'highlights': ['Flask SQLAlchemy provides useful defaults and helpers for our Flask application.', 'Representing the database structure as classes in SQL Alchemy allows for easier manipulation of the database.', 'Setting the location for an SQLite database involves specifying a relative path with three forward slashes in the URI.', 'The chapter emphasizes creating class models for database structure, with each class representing a table in the database.', 'Defining columns within the user class, including specifying types, attributes, and constraints such as unique and nullable.', 'Implementation of a hashing algorithm for password security, specifically a 60-character length, and the addition of methods for object representation.']}, {'end': 1023.251, 'segs': [{'end': 612.965, 'src': 'embed', 'start': 585.621, 'weight': 0, 'content': [{'end': 591.567, 'text': "But I'm not going to go into detail about it here because really it does require its own series to go over.", 'start': 585.621, 'duration': 5.946}, {'end': 595.931, 'text': "Okay, so now let's create the post class to hold our post.", 'start': 591.907, 'duration': 4.024}, {'end': 600.034, 'text': 'So this is going to be similar to our user.', 'start': 596.291, 'duration': 3.743}, {'end': 605.039, 'text': "So I'll just copy and paste this in here, but this will be for posts.", 'start': 600.154, 'duration': 4.885}, {'end': 608.522, 'text': "And again, we're going to be inheriting from our db.model.", 'start': 605.459, 'duration': 3.063}, {'end': 612.965, 'text': "and now we're going to create similar columns.", 'start': 609.162, 'duration': 3.803}], 'summary': 'Creating a post class with similar columns to the user, to be inherited from db.model.', 'duration': 27.344, 'max_score': 585.621, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc585621.jpg'}, {'end': 748.696, 'src': 'embed', 'start': 724.418, 'weight': 3, 'content': [{'end': 730.543, 'text': 'and you always want to use UTC times when saving dates and times to a database so that they are consistent.', 'start': 724.418, 'duration': 6.125}, {'end': 735.366, 'text': 'okay. and lastly, all of our posts are also going to have content.', 'start': 730.543, 'duration': 4.823}, {'end': 739.709, 'text': 'so we will say content is equal to, and this is going to be,', 'start': 735.366, 'duration': 4.343}, {'end': 748.696, 'text': '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.', 'start': 739.709, 'duration': 8.987}], 'summary': 'Use utc times for database consistency; all posts require content.', 'duration': 24.278, 'max_score': 724.418, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc724418.jpg'}, {'end': 822.971, 'src': 'embed', 'start': 793.497, 'weight': 1, 'content': [{'end': 800.24, 'text': 'So the post model and the user model are going to have a relationship since users will author posts.', 'start': 793.497, 'duration': 6.743}, {'end': 810.425, 'text': '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.', 'start': 800.62, 'duration': 9.805}, {'end': 813.466, 'text': 'And this is how we do this in SQLAlchemy.', 'start': 810.945, 'duration': 2.521}, {'end': 822.971, 'text': 'So we can say up in our users model here I will create a post attribute and set this equal to now.', 'start': 813.506, 'duration': 9.465}], 'summary': 'In sqlalchemy, the user model and post model have a one-to-many relationship, where one user can have multiple posts and a post can have only one author.', 'duration': 29.474, 'max_score': 793.497, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc793497.jpg'}, {'end': 871.696, 'src': 'embed', 'start': 843.822, 'weight': 2, 'content': [{'end': 852.986, 'text': "i'm going to set a back riff equal to author and then also pass in this lazy argument and set the lazy argument equal to true.", 'start': 843.822, 'duration': 9.164}, {'end': 859.009, 'text': "okay, so again we're saying that our post attribute has a relationship to the post model.", 'start': 852.986, 'duration': 6.023}, {'end': 864.092, 'text': 'now the back ref is similar to adding another column to the post model.', 'start': 859.009, 'duration': 5.083}, {'end': 871.696, 'text': '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.', 'start': 864.092, 'duration': 7.604}], 'summary': 'Setting backref to author in post model, allowing easy access to post creator.', 'duration': 27.874, 'max_score': 843.822, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc843822.jpg'}], 'start': 585.621, 'title': 'Creating post class and defining relationships', 'summary': 'Discusses creating a post class with specific constraints and covers defining one-to-many relationships between user and post models in sqlalchemy, including backref, lazy loading, and foreign key addition.', 'chapters': [{'end': 775.187, 'start': 585.621, 'title': 'Creating post class with title, date, and content', 'summary': 'Discusses creating a post class with a title limited to 100 characters, a date posted with a default value of current time in utc, and a required content for every post.', 'duration': 189.566, 'highlights': ['The chapter discusses creating a post class with a title limited to 100 characters, a date posted with a default value of current time in UTC, and a required content for every post.', 'The default value for the date posted is set to the current time in UTC to ensure consistency in saving dates and times to a database.', 'The title for each post is limited to a maximum of 100 characters, and it is a required field for every post.', 'The content for every post is required and is of type DB.text.']}, {'end': 1023.251, 'start': 775.527, 'title': 'Defining relationships in sqlalchemy', 'summary': 'Covers defining one-to-many relationships between user and post models in sqlalchemy, specifying backref and lazy loading, and adding user id as a foreign key to the post model.', 'duration': 247.724, 'highlights': ['One user can have multiple posts, but a post can only have one author. Explains the one-to-many relationship between user and post models, indicating the quantifiable data of one user to multiple posts.', "Defining the relationship using db.relationship and specifying the backref 'author' with lazy loading set to true. Describes the process of defining the relationship between user and post models, specifying backref and lazy loading, which are essential technical details for implementation.", 'Adding user ID as a foreign key to the post model and specifying its relationship to the user model. Explains the process of adding user ID as a foreign key to the post model and specifies its relationship to the user model, providing insights into the database structure and the relationship between the models.']}], 'duration': 437.63, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc585621.jpg', 'highlights': ['The chapter discusses creating a post class with a title limited to 100 characters, a date posted with a default value of current time in UTC, and a required content for every post.', 'One user can have multiple posts, but a post can only have one author. Explains the one-to-many relationship between user and post models, indicating the quantifiable data of one user to multiple posts.', "Defining the relationship using db.relationship and specifying the backref 'author' with lazy loading set to true. Describes the process of defining the relationship between user and post models, specifying backref and lazy loading, which are essential technical details for implementation.", 'The default value for the date posted is set to the current time in UTC to ensure consistency in saving dates and times to a database.']}, {'end': 1280.36, 'segs': [{'end': 1117.734, 'src': 'heatmap', 'start': 1037.301, 'weight': 0.861, 'content': [{'end': 1044.848, 'text': 'Okay, so now that we have these database models that represent the structure of our database, we can now use those to create the database.', 'start': 1037.301, 'duration': 7.547}, {'end': 1047.57, 'text': "So to do this, we're going to use the command line.", 'start': 1045.148, 'duration': 2.422}, {'end': 1056.118, 'text': "So I'm going to open up my terminal here, and I've still got my virtual environment activated and navigated to the project directory.", 'start': 1047.81, 'duration': 8.308}, {'end': 1062.163, 'text': "So within here, I'm going to say Python, and you have to be within your project directory to get these imports to work.", 'start': 1056.498, 'duration': 5.665}, {'end': 1067.905, 'text': "So now I'm going to say from flask blog, import DB.", 'start': 1062.663, 'duration': 5.242}, {'end': 1070.326, 'text': 'So flask blog is my application file.', 'start': 1068.045, 'duration': 2.281}, {'end': 1073.908, 'text': 'And DB is the instance of our database that we created.', 'start': 1070.746, 'duration': 3.162}, {'end': 1075.248, 'text': "So I'll import that.", 'start': 1074.248, 'duration': 1}, {'end': 1080.67, 'text': "Now we didn't get any errors, but we did get this output from SQL alchemy.", 'start': 1075.688, 'duration': 4.982}, {'end': 1081.471, 'text': "Don't worry about that.", 'start': 1080.75, 'duration': 0.721}, {'end': 1082.491, 'text': "It's just some information.", 'start': 1081.551, 'duration': 0.94}, {'end': 1090.296, 'text': 'so now we can actually create our database by saying db.create, underscore all and use that create all method.', 'start': 1082.831, 'duration': 7.465}, {'end': 1093.639, 'text': "so if i hit enter, you can see that we don't get any errors.", 'start': 1090.296, 'duration': 3.343}, {'end': 1097.742, 'text': "so that should have created our database now, since we're using a sqlite database.", 'start': 1093.639, 'duration': 4.103}, {'end': 1108.99, 'text': '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.db.', 'start': 1097.742, 'duration': 11.248}, {'end': 1117.734, 'text': '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.', 'start': 1108.99, 'duration': 8.744}], 'summary': 'Database models used to create database via command line, creating site.db file.', 'duration': 80.433, 'max_score': 1037.301, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1037300.jpg'}, {'end': 1121.296, 'src': 'embed', 'start': 1093.639, 'weight': 0, 'content': [{'end': 1097.742, 'text': "so that should have created our database now, since we're using a sqlite database.", 'start': 1093.639, 'duration': 4.103}, {'end': 1108.99, 'text': '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.db.', 'start': 1097.742, 'duration': 11.248}, {'end': 1117.734, 'text': '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.', 'start': 1108.99, 'duration': 8.744}, {'end': 1121.296, 'text': 'Now, I know that might be a little hard for you to see in my sidebar,', 'start': 1117.994, 'duration': 3.302}], 'summary': "Created sqlite database file 'site.db' in project directory.", 'duration': 27.657, 'max_score': 1093.639, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1093639.jpg'}, {'end': 1159.689, 'src': 'embed', 'start': 1135.182, 'weight': 3, 'content': [{'end': 1143.108, 'text': "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.", 'start': 1135.182, 'duration': 7.926}, {'end': 1148.513, 'text': 'so I will go back to the command line and now I want to import those models.', 'start': 1143.108, 'duration': 5.405}, {'end': 1155.946, 'text': "so I'll say from flask blog import, And the name of our models was user and post.", 'start': 1148.513, 'duration': 7.433}, {'end': 1157.147, 'text': "So let's import those.", 'start': 1156.126, 'duration': 1.021}, {'end': 1159.689, 'text': "And now let's create an instance of our user.", 'start': 1157.467, 'duration': 2.222}], 'summary': 'Demonstrating the addition of a user and a post using the flask application.', 'duration': 24.507, 'max_score': 1135.182, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1135182.jpg'}, {'end': 1216.461, 'src': 'embed', 'start': 1186.632, 'weight': 1, 'content': [{'end': 1193.097, 'text': "So we're setting a plain text password for this example, but we're going to be hashing these passwords when we do this through our application.", 'start': 1186.632, 'duration': 6.465}, {'end': 1198.883, 'text': "Also notice that we didn't specify an ID or an image file for this user.", 'start': 1193.858, 'duration': 5.025}, {'end': 1205.209, 'text': 'So since the ID is our primary key, it will assign this user a unique ID automatically.', 'start': 1199.243, 'duration': 5.966}, {'end': 1209.313, 'text': 'And we have a default value for our image file, if you remember.', 'start': 1205.67, 'duration': 3.643}, {'end': 1216.461, 'text': "If we don't provide that, then it will just get that value of default.jpg that we specified in our model.", 'start': 1210.174, 'duration': 6.287}], 'summary': 'Setting plain text password, hashing in application, auto-assigning unique id, default image file.', 'duration': 29.829, 'max_score': 1186.632, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1186632.jpg'}, {'end': 1263.936, 'src': 'embed', 'start': 1235.249, 'weight': 2, 'content': [{'end': 1238.771, 'text': "we've just told it that we have a change that we want to make to our database.", 'start': 1235.249, 'duration': 3.522}, {'end': 1242.252, 'text': 'so we could have several changes at a time and add them like this.', 'start': 1238.771, 'duration': 3.481}, {'end': 1247.933, 'text': 'And then when we commit those changes, then it will make all those changes to the database at once.', 'start': 1242.612, 'duration': 5.321}, {'end': 1252.094, 'text': 'So, for example, if I add another user here,', 'start': 1248.273, 'duration': 3.821}, {'end': 1263.936, 'text': "so I'm going to call this user two and I will set the username equal to John Doe and set the email to JD at demo.com.", 'start': 1252.094, 'duration': 11.842}], 'summary': 'Demonstrating how to make multiple changes to the database and commit them at once.', 'duration': 28.687, 'max_score': 1235.249, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1235249.jpg'}], 'start': 1023.732, 'title': 'Database creation and user addition', 'summary': 'Covers creating a database using command line, adding data such as user and post instances, and adding users to the database using sqlalchemy with python, including primary key assignment and default values.', 'chapters': [{'end': 1159.689, 'start': 1023.732, 'title': 'Creating database and adding data', 'summary': 'Explains the process of creating a database using command line and adding data to it, including creating database models, using command line to create the database, and adding data such as user and post instances.', 'duration': 135.957, 'highlights': ['The chapter demonstrates creating database models to represent the structure of the database, using command line to create the database, and adding data such as user and post instances.', "The database is created using the command 'db.create_all' method, and the sqlite database is stored as a file called site.db in the project directory.", 'An instance of the user is created to add data to the database using the command line, showcasing the process of adding data to the database.']}, {'end': 1280.36, 'start': 1159.949, 'title': 'Adding users to database', 'summary': 'Demonstrates adding users to the database using sqlalchemy with python, including setting attributes, adding users, and committing changes, with an emphasis on primary key assignment and default values.', 'duration': 120.411, 'highlights': ['We set attributes for a user, including username, email, and password, and demonstrated how SQLAlchemy automatically assigns a unique ID as the primary key. (e.g. username: Corey, email: c@demo.com, password: password)', 'The process of adding a user to the database was illustrated using db.session.add, followed by committing the changes to the database using db.session.commit. (e.g. db.session.add(user1), db.session.commit)', 'Highlighted the importance of hashing passwords for security when implementing the process through an application, emphasizing best practices for user data protection.', 'Explained the concept of making multiple changes at once and committing them together to the database, showcasing the efficiency of batch processing for database updates.']}], 'duration': 256.628, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1023732.jpg', 'highlights': ["The database is created using the command 'db.create_all' method, and the sqlite database is stored as a file called site.db in the project directory.", 'We set attributes for a user, including username, email, and password, and demonstrated how SQLAlchemy automatically assigns a unique ID as the primary key. (e.g. username: Corey, email: c@demo.com, password: password)', 'Explained the concept of making multiple changes at once and committing them together to the database, showcasing the efficiency of batch processing for database updates.', 'The chapter demonstrates creating database models to represent the structure of the database, using command line to create the database, and adding data such as user and post instances.']}, {'end': 1785.212, 'segs': [{'end': 1347.617, 'src': 'heatmap', 'start': 1293.923, 'weight': 3, 'content': [{'end': 1303.892, 'text': "then we can simply say and I'm going to clear my screen here then we can simply say user.query.all to get all of our users.", 'start': 1293.923, 'duration': 9.969}, {'end': 1307.955, 'text': 'And we can see here that it returns a list of both users that were created.', 'start': 1304.172, 'duration': 3.783}, {'end': 1310.858, 'text': 'So here is our first and here is our second.', 'start': 1307.995, 'duration': 2.863}, {'end': 1316.901, 'text': 'Now, if we just wanted to get the first user, then we could access it from the list that we that was just returned.', 'start': 1311.118, 'duration': 5.783}, {'end': 1321.164, 'text': 'Or we could use the first method that gives you the very first result.', 'start': 1317.241, 'duration': 3.923}, {'end': 1325.426, 'text': 'So I could say user dot query dot first.', 'start': 1321.464, 'duration': 3.962}, {'end': 1329.248, 'text': 'And if I run that, you can see that we just get that first user back.', 'start': 1325.826, 'duration': 3.422}, {'end': 1333.79, 'text': 'now we can also filter the results by using the filter by method.', 'start': 1329.568, 'duration': 4.222}, {'end': 1342.174, 'text': 'so if we wanted to filter by usernames, then we could say so user.query and use this filter by method,', 'start': 1333.79, 'duration': 8.384}, {'end': 1347.617, 'text': 'and we could say that we want to filter by users who have a username equal to corey,', 'start': 1342.174, 'duration': 5.443}], 'summary': 'Demonstration of querying and filtering users in a database using python, returning a list of users and filtering by username.', 'duration': 43.445, 'max_score': 1293.923, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1293923.jpg'}, {'end': 1438.307, 'src': 'embed', 'start': 1409.708, 'weight': 0, 'content': [{'end': 1415.192, 'text': 'So we already saw user.query.all and user.query.first.', 'start': 1409.708, 'duration': 5.484}, {'end': 1425.501, 'text': 'but if we were to say user is equal to user.query.get, then this will fetch a user with a specific ID.', 'start': 1415.192, 'duration': 10.309}, {'end': 1433.326, 'text': 'So if I get a user with the ID of 1, which should be that same user, then we can see that we get that same user as a result.', 'start': 1425.821, 'duration': 7.505}, {'end': 1438.307, 'text': "So now let's look at this user's post by saying user.post.", 'start': 1433.586, 'duration': 4.721}], 'summary': 'The user.query.get method fetches a user with a specific id, such as user with id 1, and accessing their posts with user.post.', 'duration': 28.599, 'max_score': 1409.708, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1409708.jpg'}, {'end': 1564.381, 'src': 'embed', 'start': 1536.541, 'weight': 2, 'content': [{'end': 1546.608, 'text': "I'll change this to post two and we will set the title equal to blog, to the content equal to second post content,", 'start': 1536.541, 'duration': 10.067}, {'end': 1548.929, 'text': 'and we will keep the user ID the same.', 'start': 1546.608, 'duration': 2.321}, {'end': 1552.792, 'text': "Okay, now remember we've created these posts, but they're not added to our database yet.", 'start': 1549.27, 'duration': 3.522}, {'end': 1564.381, 'text': 'To add them to the database, we need to do a dbsession.add, and we can add post1 and also add post2, and then we can do a dbsession.commit,', 'start': 1553.092, 'duration': 11.289}], 'summary': 'Modify post two, set title to blog, add to database.', 'duration': 27.84, 'max_score': 1536.541, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1536541.jpg'}, {'end': 1661.488, 'src': 'embed', 'start': 1633.826, 'weight': 1, 'content': [{'end': 1636.488, 'text': 'We might want more information about that author.', 'start': 1633.826, 'duration': 2.662}, {'end': 1641.372, 'text': 'Now, if we remember, back to our model, if I open this up here,', 'start': 1636.868, 'duration': 4.504}, {'end': 1652.081, 'text': "then the post relationship from our user model lets us not only access that user's post, but it also adds this back ref of author to each post.", 'start': 1641.372, 'duration': 10.709}, {'end': 1661.488, 'text': "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.", 'start': 1652.581, 'duration': 8.907}], 'summary': "The post relationship in the user model allows access to the user's posts and adds an author reference to each post.", 'duration': 27.662, 'max_score': 1633.826, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1633826.jpg'}, {'end': 1735.06, 'src': 'embed', 'start': 1707.926, 'weight': 5, 'content': [{'end': 1710.968, 'text': 'And that drops all of our database tables and rows.', 'start': 1707.926, 'duration': 3.042}, {'end': 1715.932, 'text': 'So now to create those tables fresh one more time, then we can just create them again.', 'start': 1711.289, 'duration': 4.643}, {'end': 1720.935, 'text': "So I'll say db.create underscore all and run that.", 'start': 1716.072, 'duration': 4.863}, {'end': 1725.237, 'text': 'And that should have recreated that database structure.', 'start': 1721.415, 'duration': 3.822}, {'end': 1734.38, 'text': "And now if we query all of our users or posts, so I'll say user.query.all, then we can see that that returns an empty list.", 'start': 1725.597, 'duration': 8.783}, {'end': 1735.06, 'text': "So that's good.", 'start': 1734.48, 'duration': 0.58}], 'summary': 'Dropped and recreated database, returning empty user list.', 'duration': 27.134, 'max_score': 1707.926, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1707926.jpg'}], 'start': 1281.3, 'title': 'Sqlalchemy queries and usage in python', 'summary': 'Covers common queries like retrieving all users, filtering by username, and working with sqlalchemy in python. it emphasizes making querying the database easy and efficient, covering adding and querying data, creating and deleting database tables, and restructuring the application into a package.', 'chapters': [{'end': 1536.501, 'start': 1281.3, 'title': 'Sqlalchemy querying', 'summary': 'Covers common queries using sqlalchemy, including retrieving all users, filtering by username, performing queries using user ids, and creating and retrieving posts, with an emphasis on making querying the database extremely easy and efficient.', 'duration': 255.201, 'highlights': ["The chapter demonstrates how to retrieve all users using the 'user.query.all' method, which returns a list of all users created.", "The 'user.query.first' method is used to retrieve the first user, and the 'filter_by' method is used to filter results by usernames, returning a list of matching users.", "The chapter explains how to perform queries using user IDs with the 'user.query.get' method, and how to create and retrieve posts, emphasizing the efficiency and ease of querying the database using SQLAlchemy."]}, {'end': 1785.212, 'start': 1536.541, 'title': 'Working with sqlalchemy in python', 'summary': 'Demonstrates working with sqlalchemy in python, covering adding and querying data, creating and deleting database tables, and restructuring the application into a package.', 'duration': 248.671, 'highlights': ['The chapter covers adding posts to a database using SQLAlchemy, with the user adding two blog posts and committing them simultaneously, resulting in the user having a list of two blog posts.', 'The demonstration includes querying the post table directly to retrieve the first post and accessing the ID of the user who created the post, obtaining the correct user ID for the post.', 'The tutorial showcases the use of backref in the post relationship from the user model, allowing access to the user who created the post by using post.author, thereby obtaining the entire user object for further manipulation.', 'The video concludes with the deletion of all data from the database using db.dropAll and the recreation of the database structure with db.create_all, resulting in empty lists when querying all users and posts, ensuring a clean slate for the next video.', "The chapter ends with a preview of the next video's topic, which involves restructuring the application into a package to move the models into their own file and explore the advantages of using packages as an application structure."]}], 'duration': 503.912, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/cYWiDiIUxQc/pics/cYWiDiIUxQc1281300.jpg', 'highlights': ["The chapter explains how to perform queries using user IDs with the 'user.query.get' method, and how to create and retrieve posts, emphasizing the efficiency and ease of querying the database using SQLAlchemy.", 'The tutorial showcases the use of backref in the post relationship from the user model, allowing access to the user who created the post by using post.author, thereby obtaining the entire user object for further manipulation.', 'The chapter covers adding posts to a database using SQLAlchemy, with the user adding two blog posts and committing them simultaneously, resulting in the user having a list of two blog posts.', "The 'user.query.first' method is used to retrieve the first user, and the 'filter_by' method is used to filter results by usernames, returning a list of matching users.", "The chapter demonstrates how to retrieve all users using the 'user.query.all' method, which returns a list of all users created.", 'The video concludes with the deletion of all data from the database using db.dropAll and the recreation of the database structure with db.create_all, resulting in empty lists when querying all users and posts, ensuring a clean slate for the next video.']}], 'highlights': ['ORM (Object Relational Mapper) allows easy access to the database in an object-oriented manner.', 'SQLAlchemy enables the use of different databases without modifying Python code, such as SQLite for testing and Postgres for production.', 'Flask SQLAlchemy provides useful defaults and helpers for our Flask application.', 'Representing the database structure as classes in SQL Alchemy allows for easier manipulation of the database.', 'The chapter discusses creating a post class with a title limited to 100 characters, a date posted with a default value of current time in UTC, and a required content for every post.', "The database is created using the command 'db.create_all' method, and the sqlite database is stored as a file called site.db in the project directory.", "The chapter explains how to perform queries using user IDs with the 'user.query.get' method, and how to create and retrieve posts, emphasizing the efficiency and ease of querying the database using SQLAlchemy."]}