This SQLite Database Model is Easy and Ready for your Python Projects!

all right so in this video i'm going to show you how to create a real basic database model.py file that you can then import into the rest of your code and easily save your products to a database we're going to be using sqlite3 so i'm going to import that into the top and then we're going to click create our database class of which i'm going to call product because this is going to be a product database use whatever name fits your project we then need to initialize this class with our init function and the self command now basically what this means is that anything in here is going to be executed when we call our instance of this class so the first thing that we want to do is obviously connect to our database because it's our with our class we need to do self.con is equal to sqlite3 dot connect and then the name or the path and the path of your database that you're connecting to let's just call this one test.db for now and now we want to create our cursor which lets us execute commands so self.curv for the cursor is equal to now because we want to use the connection that we're creating we do self.con dot cursor like that so these are the two main things that we need to have in here we're going to add in the create table as well in a minute but we're going to create that function first and then put it in so now like i said we want to create a table so let's do create table and self again because we're within our class so let's create our table so now we want to use the cursor that we created up here so self.cur dot execute and now we can use the triple quotes to do our sql commands so we're going to do create table i'm going to put in if not exists what that means is that when we run this it will only create this table if it doesn't already exist so we're going to do products and then we're not going to do i'll put our classes product so let's let's just call it products that's fine and now we need to decide what information is going to go into this database so we need to know the table headers and the column headers for our database so in here you need to decide what bits of information you're going to be saving so it's important because we need to create the columns in our database table which we're creating here to make sure that our data goes into the right place now we're also going to say in here what type of data it is that we're putting in and whether or not it is the primary key now that's really important and i'll show you why so the first header i'm going to do is date and we can actually have that as a date object now i'm going to make this one the primary key because in my case here what we're going to do is we're going to want to only have one entry per day for the price and the information for this specific product that was going into this table now by creating the primary key we can only have one instance of that date in that case now you might want to have the primary key as maybe like the sku or the product id or the identifier or maybe you want to just have a general id that is incremented automatically for your primary key then we need to have a comma let's have a category cat so gory which is going to be a text field then we're going to have a store code of some description again a text field and then the product name text and the price which will make a real number and then they link to the actual product which can be text as well so we have six bits of information here which is going to be creating six table headers or column headers in our database so one other thing i find quite useful especially when you're testing is to write in a drop table uh line in here that we can comment out so we would just do self doc dot execute not next execute and then we would have uh drop table products so i'm going to comment this out for now but what would happen if we wanted to delete the table every time that we ran it so we were testing we could just run this as is uncommented so now we're going to come back to our initialization because we want to actually create this table so we want to have create table running every time we call an instance of this class this is important and because we have if not exists we can do that and it won't throw up an error so we will just do self dot create table there the next thing we want to think about is having the actual data inserted into our table so we have a nice easy function for this so let's just call this insert self and let's move this up here and we can basically give it the item that we're going to give it and then import insert it into the table itself so we want to give this the item as well to insert a bit of data into this database we're going to do self dot cur dot execute because we want to execute commands our triple quotes and now we're going to do insert or ignore ignore into the table that we've created products and then values and then our brackets and then question marks to represent the data that is going to go in here because we're going to be passing in an actual item into this so i think we had six so let's have one two three four five six bits of data then our comma and then the item so what this is saying is that we're going to take this item that we're going to pass to this function it's got to have the right amount of data to match these columns otherwise it will fail we're going to execute inserting or ignore into products which is the table so we're going to say put it in or ignore it if it already exists and that's where our primary key comes in so with this primary key a date if it goes and tries to insert another entry with the same date it will fail and it will just ignore it and move on so we don't get any error we don't have to do any error handling which is quite useful and good now there's one more thing that we need to do is we need to actually commit this to the database when we insert so we're going to do self.con dot commit cursor commits there we go and that basically just writes it all to the database now if you were having if you had lots and lots of bits of data you might want to move this to a different function so you would insert first and then commit all at the end but in this case i'm just going to put it here what i'm also going to do which you may or may not want to do inside your actual database model is i'm going to put in a quick read function in here just so we can easily see what's in our database whilst we're looking at it this way again you would probably have this elsewhere in your code but we're going to do uh def and we'll call this read self and then all we need to do is to search within our database so let's do self.cur.execute because again we're executing commands on our database there's our sql triple quotes to execute our sql commands we're going to do select star from products and then we can do rows is equal to we need a variable to save this into self dot i think it's ker ker.fetch fetch all there we go and now we can print actually let's just return the rows we'll do the print statement elsewhere return rows so all this is doing is it's just selecting everything from this table now i wouldn't recommend doing this if you've got a lot of data you want to have much more specific targeted sql queries but for testing purposes we just want to make sure that the data is in there and we can easily access it like this you didn't want to do this you could use a database browser like db lite which is useful you can just open up your sql database and see all the information that way so now that we've done this that's pretty much it done i'm going to reformat my file occupy charm and what we're going to do is we're going to actually run through an example usage of this and i have this already stored in this pi file over here so let's say that we had some items that we wanted to him to input into our database so we need to call in our database model so from models imports product so that's the models is the file and product is the class here see there we go we don't need to import sqlite or anything here because it will going to come over when we import this in so as i said one of our columns is date so we need to create the date which is what i've done here using datetime.today.stringtime and this is the format i've chosen which is year month and day it's quite common format that way then we need to instantiate the class so i've said db is equal to product so we can see that this is the product model with the product we're importing from our model and then we have six columns of data so we need to match this exact data which is what i put here date category store name price and link date category store name price and link so when you're importing into a database it's much easier to use a tuple that just works so much better than a dictionary you can do it with a dictionary but it kind of contrasts because if you think our database already has keys it already has headers so why would we need extra ones from our dictionary so i would always recommend storing your data into a tuple if you are planning on using a database this is the matching data that i have it's uh i've called it item and we have the to date and then some strings so we have our category and the store text um and there's the store yes it all matches up so what we're going to do is we're going to call our db which matches the in the class name that we instantiated up here the instance we created and we're going to use the insert that we wrote down here to insert that data into the database now because we called it up here it's going to create the connection and our database and the table when we run this and then it's going to insert it and then after that i've actually got our read all back here and we're going to print that item out so i'm going to run the example usage we get a quick error that's because i called it read not read all in the example there we go there we are so this has come back from our database we inserted this line in and we can see that we have all that information we put in so if i try and run this again we're only going to have one bit of data still because i have that date as the primary key now if i was to let's say change this up so let's change it from primary key and not have it as a primary key and let's just quickly delete our database yes bye bye and now let's run it again we're going to be able to add multiple instances of the same bits of data you can see there's two down there my head's in the way there we go let's put that there and and so on so every time i run it we're going to add a new line with the same bits of data and that's where the primary key really helps so if you've enjoyed this video you're going to find this one useful it's about scraping data to store in your new database

Channel: John Watson Rooney

Views: 1,033

Rating: 4.9574466 out of 5

Keywords: sqlite3, sqlite, sqlite3 python, learn sqlite3, sqlite3 insert, tutorial, sqlite database, setup sqlite3, python database, sqlite tutorial, sqlite python, save to db python, python sqlite3, python

Id: LFG2Kx1m-Dc

Length: 11min 16sec (676 seconds)

Published: Thu Sep 23 2021