SQLModel is the Pydantic inspired Python ORM we’ve been waiting for

there's a good chance if you've started building any kind of web applications that you've come across an orm an object relational mapper which basically means that we can write python code to interact with our database using an object so one of the great things is you don't have to write any sql at all the actual mapper will do it all for you and you can control it as you wish so the most common one is sql alchemy in python but what i'm going to show you now is a demo project using sql model which was written by sebastian ramirez who also wrote fast api and that's kind of why i wanted to give this a go so what i wanted to do is i wanted to actually show you in a small project that i've done so you get a better idea of how it gets used but before we get to that i'd like to thank the sponsor of today's video skillshare so skillshare is a learning community with thousands of classes for anyone who wants to make their 2022 a year of learning and growth if there's a specific skill you want to discover maybe video editing and videography or you're looking to make a career pivot with freelancing and web development you'll discover a class that suits you and it'll even cover those soft skills that often get overlooked as a youtuber and freelancer staying productive and managing my workload is so crucial to staying on track i recently finished watching thomas frank's class called mastering productivity create a custom system that works which i was really excited about thomas is a great youtuber and podcast that i've been following for a while now taking on board what i've learned i've now gone from a mess of notes all over the place to a concise and manageable system that works for me and not against me so as the sponsor of the video skillshare would like to offer the first 1000 people who use the link in the description below one month free with new premium classes launched every week on their ad free platform definitely worth checking out using that link below once again thank you very much to skillshare for sponsoring this video so this is the first file that i've got here this is my database dot py file i've written a few comments here and um one of the one of the main things that's great about using sql alchemy or an orm is that we can actually test and do our development using an sqlite database but when we actually want to go ahead to production we can use something more appropriate like pros i like postgres so if we were to use postgres all we would need to do is put in the correct the the correct url to our progress postgres database here what we want to do is we want to create an engine which is the interface to our database and this is going to allow us to actually execute those sql commands like the tables insert and read and all of that stuff now in my project i've got it set up like this so you can see i have my database folder and my two files will cover the models in just a second i have the services that basically are the responsible for getting the information that we're going to be storing and doing something with it and then i have my main file so if we just pay attention here we're just importing sql model from the top and the create engine and we're basically saying here's our function that's going to create everything for us using that engine and this function is going to go across to the main pi file which i'll show you in just a second so the model is basically where we want to determine what we're going to what information we're going to be storing in our database in the table so sql model is kind of like a mix of pedantic and sql alchemy so it does run on top of sql sql alchemy but we're using pedantic as well which gives us access to these type hints um and i think this is just a a nice really neat way of doing it so that the type pins and the annotations basically means that throughout our project we'll get that hint to say oh hey this is supposed to be an integer or this is supposed to be a string and it also allows you to have a lot more autocomplete in your code editor as well which is really handy when we're writing larger projects so what i've got here is i've got my imports at the top so this is my class here this is the model and as you can see it's a it's inheriting from the sql model class and i have chosen these fields here that i'm going to be storing for this this specific project the first thing and the most important thing is the id so i've decided to actually have this as the primary key and it's going to auto increment for me and the reason why we want to put optional in here which might be quite confusing because you would think well why would the id field be optional that's not actually what it means what it means is that it should be an integer but it's up so also optional and that's because when we do this the value will be none before it gets sent to the database this is just stopping it throwing up any any errors in our code because if you think about it before it actually reaches the database it doesn't have an id so that id would be none i've got a store id which i'm saying is going to be an integer and that is just a basic field and again store name string title handle published and you can see i have an index here on the store name just so if this gets bigger it's easier to pull the information by the store name another interesting one is nullable is equal to true i found that i needed this in here because some of the information i was pulling didn't actually have this handle information what that what this means is it will allow you to actually have this as nothing in the uh data when you send it to the database whereas all of these other fields are actually required and you can see i've got nullable as equal to false in this case because this is the main id we have to actually have information here so how does this all tie together um well i've got my main pi file here and i'm doing my imports in here it's all at the top the bit that we're interested in the most is you can see my main function which is going to run that create database and tables function here and it's also going to then run my configure which is getting my environment variables so this is also this is the main pi file that's going to run everything together so i'll just show you the actual inserting which i've got and this file i think so we have a look at these you can see some of my functions that's pulling the information out if we scroll down you can see that i'm storing the data in a tuple like this this is just my preferred method to use a tuple because it's easy to actually put the data where you want it i'm saying with session here with the engine so this is going to create a actual database session for us that we can use to execute those commands and i'm basically looping through each item i'm creating a temporary variable and this is matching the model here so we've got the store id name title handle unpublished you can see that here that mimics that because this is the information that we want to put in and from here i'm basically taking that and i'm inserting it now one problem that you might find is that you might want to actually add things to your database that um don't exist already and if they do you just want to pass over them or maybe you want to update in this case i'm just saying like i'm going to select the product where the product title is equal to the new item that i'm trying to insert you see i've got item two on the index here which is the title so if it finds a product in the database that already has that title then it's basically just going to leave it it's going to move on and we get if not res.first so res is the results from executing that statement so this is where the sql is actually being created and run and then i'm saying if we don't find anything let's add it in so i'm actually really liking this so far um i've i've used the other typical orms sql alchemy etc and flask sql alchemy um but i find that so far i've actually quite enjoyed using sql model um one of the best things about it actually is that the documentation is fantastic i'll put a link down to that below and it's really easy to follow along and i hope i've given you like a decent overview of what it can be and how it fits a bit more into a real project as opposed to just having one file where we put in a couple of bits of data i'll just run this now on the whole thing i don't know if there's gonna be any more information we'll see if there are any more products from these stores that i want to save uh it doesn't look like there is in this case but if you've enjoyed this video guys i think you're going to enjoy this one right here that talks a bit more about scraping data from these sites for you to save in your own database

Channel: John Watson Rooney

Views: 30,182

Keywords: sqlmodel, python orm, python database, python project, john watson rooney, sqlmodel python, python

Id: 1oGW-ItQG94

Length: 8min 35sec (515 seconds)

Published: Thu Mar 24 2022