PYTHON SQLITE - Complete tutorial

welcome to this video about Python and its sequel, remember to subscribe because I will do the exact same video with Python combined with Basra's SQL MySQL, which is my real beam, and I will also do Python with MongoDB in a series of different videos. go ahead subscribe like this video write some comments the only thing i have installed so far on my system is vs cold and

python

if you are not sure if you have

python

installed you can go to the terminal and type PY - - version and I am using the latest version for Python as of today, which is version 3 8.

You can also go to the browser and let me open a new window here and as you do, I'll show you a way to be like them. from google for python go to python.org and go to the downloads part click here and download it when you are downloading python there will be an option in the installation box that says it will ask you if you want to add python to the path you have. To say yes, when you add Python to the path, what you're really doing is allowing you to type Python commands from the command prompt from the terminal, so go ahead and download it now, let me close this window, we don't need any. more, since we are going to have the crowd create, read, update and delete, the first file that we are going to create is actually the cradle py file, in this file we are going to write to the database, we don't need to download anything.

Sequel Lite 3 is built into most systems and in this case Python knows how to build the database for us, so there are no additional downloads. Let's do an import and we're going to import Sequel I 3, we can save, go to the terminal and now. we're going to run this file, so we say PY and then we point to the file we call create py, we click enter and nothing really happens because we don't have anything in this file. If you have a mistake, let's do a little bit of excess there, save. and let's run the file now, you can see that this import is invalid because that file doesn't really exist, so we'll just import the sequin as three again, save and run it again once we've imported it, if we did it correctly.

We need to create a database, we don't have the database created yet, so we need to build it. All good in my Visual Studio application. You can see I don't have anything for the sequel ID, so here we go. We will add it together, just uninstall it, click this icon and type sequel ID. It will have this option and these option cages allow us to interact with the sequel database directly from vs cold. I already installed this and we just enable it now. You don't have to do this if it's your first time running it, so now it's enabled and then I can close it and it's ready to work on.

The next thing we need to do is to create a database so in our script I'm just going to remove the terminal because I'm not using it for now so close it and now we close it too so let's create a database and I'll put some comments . and that's one way we comment in Python, so I'm just going to say database a few lines and I pray that the chin is called DB and this will point to the iDream sequel and we'll connect to a database, the name from the database that I will pass. Here can be literally any name you want to name your database.

I'll call it data dot DB. You can call it Company DB or whatever name you want. So, off topic, I'll save it now. I'll show you what I have here. go to the terminal and I'm going to run the command again as soon as I run the command you can see that the DB file data has been created and that's because Python connected to the sequel ID to the library and created this file for we. the file right now doesn't contain anything, it's not even a database, it's just a file with DB extension, you can call the extension sequel ID if you want, sequel i3, you can call it SQL if that's what you want. just call it V, be the extension, the extension we don't really care about, but I suggest you use Navy on it, so once we have the connection to the database, we will manually create a file here for our sequel commands, like this Let's create a new file, let's call it base dot SQL and this Skrill base will only contain some sequel commands.

Now visco tries to install this extension for you. No, we don't want it, we can close this for now and get rid of it. we are going to create a table in SQL so we can see how it is done and this would just be a simple table create table and we will call it users, well create table users now what we need to do is create it only if it does not exist, then we say create table, yes there are no users, this table will have very basic data, it will have an id and the id is usually available in Chrome and you will get numbers from one to three and so on, but I don't want to do that in every database, it has to be a number integer, the name has to be a varchar, don't worry about that, when you do the sequel id, it doesn't really matter. what type of data you give it so basically write text now it will be text it will be unique and it cannot be so it must contain data we put a comma the name will be text it will not be unique because names can be repeated and not will be known because the name must have some data, we also want to define a primary key, although we won't use this for any relationship, two foreign keys, but just for you to see. how could that be done, we say that the primary key in this table will be the ID and what we need to do now is a sequel.

By default I will try to create ID for you so we say no I want sequel I to credit is so we say we don't know we don't want a role id we can make a semi column there and this should be the base command , save it now, let's go back to our database here if you're right. click on it, you can see this option that says open database and the reason you see this option here is because we installed this extension in this code called sequel ID. We just did that before, so let's click open database and here Let's get this panel, this is open the data DB file.

Now we can go and say we want to run a new query, for example, I right click on New Query and here we want to write a query. I just wrote it here. I could literally just copy it, paste it and then save it again and then I can overwrite the base. Yeah, okay, so I have the exact same thing, so I hope you understand this or go back to the video and do it again. This is now when this data database is going to use this squirrel query base to run the query just so you know that we do an update.

I expand the data database. There are no tables here. I can right click and say run query. We can also control the change Q. on my computer I want to click on it and now the computer asks me which file I want to run this query on and I want to run the query on the data that ppp which is my selected database and now this option appears It doesn't tell us much, but if we update we get the users table with a V and the name, so this base SQL can be closed now that we have the database created, we have the users table with two columns, now we need to insert some data I will encode a couple of variables so that the ID is unique and therefore we import UUID.

This UUID will allow us to create unique IDs, so let's try it I'm going to say the ID is going to be the UUID and I want to get UUID, let me run this to see if I get any errors. I save and then want py to help you again. I don't see any errors, but I don't see this. I'll just do it. print the ID, save it again and now we just run the command again, so this is the ID I'm getting from this UUID. Now we need to get a name for the name. I want us to call the name and then it will be the letter a letter. let's say Beyonce, I'll also print the name, save it, we run the command, we get the id and the name, so the last thing we need to do is run a sequel command so we can insert the name and id into the database so that all insert data to insert the data is quite simple, you can create a new statement, you can call it STM tv12, come on, it suggests a query if that's what we want, but actually we won't get any data usually when you use a database where the id is generates automatically then you want to get the last inserted id but in this case we are creating Devi ourselves so we just want to run the command on the database so we say database let me get rid of the terminal for now we're going to run a command, the command is going to be very simple, we're going to insert it into the users and we're going to insert the values ​​and here to make it safe against SQL injections, which will put placeholders the placeholder for the first question mark is a B and the second question mark will be the name, so with a comma we open and close parentheses again and now we pass announcement and we pass the name first first second second you can make any space for everyone.

If you just ran as is now, the changes won't commit, the push won't commit, and therefore you need to do dB. confirming this will actually tell the database to save the data, so let's test it before running this. I'm going to check the users table again, right click on show table and you'll see that there are no results in these user tables, so now I'm going to run the file, I'm going to say py create hey and I'm going to expand this a little bit and here we have a problem that says we can't insert the ideal name values ​​because we are not binding the parameters correctly, there is an unsupported type, the reason you are seeing that message is because this UUID needs to be converted to a string, so we will convert it to the string , so we do STR, let's save it, let's run the command again and there are no errors, there are no errors if We look at the database, now let me put this back and then we inspect the table.

We can see we have our first user swapped right there, so it's a little annoying to open this, right click and say show table, so in my basic squirrel I'll do something else now let's just close this and that so we don't confuse you and the table has been created. I don't need it anymore, so I can just say select all from users and then I'll do the Ctrl Shift key. and then I get the users here you can also right click and run the query and you will get the users there so if I want to insert a new user I will open the py control file I will run this file in the terminal hello crepey why there are no arrows and now I need to see the users there, so I just use Ctrl Shift Q on your computer.

This is much better because you have a lot of room to play with them, this is how you create a user. a database in the next part of the video, we'll show you how we do the reading, so let's create a new file. I was actually reading too, but this was reading the post essay, now we need to read through a Python script. so create a new file let's call it read py please subscribe you will like the videos I will make for MongoDB and I will post Chris also for the read part and we also need to import if this was in a full file it doesn't.

I need to continue importing, but this is in a separate file, so we'll import the sequel I 3. I don't need to import the UUID because I'm not going to generate unique IDs. I need to connect, so we'll connect. the database and if we just look at the desired file this is how we connect so we need to collect the reconnection and then the database name let's just copy and paste save it and I suggest you always try the script right away , so 'I'm going to test it here PI and we're going to test the reading py.

There are no errors so you can continue reading. We need to create a sequel statement again, so this will be Jesus Eminence. I select all the users, so let's do it. I'm going to say that VB executes and the sequel statement selects everything from the users. Let's run this so you can see what happens, nothing because I'm not printing anything and if I put the results of this statement into a variable, I'm going to call it. rose because that's what we want to get and then I print rose, save it, run the command again. I can run it without the dot, follow the forward slash.

Here we have a problem because Rose points to something in the database, but we don't actually get anything. outside of it, so we can say find all, pay attention to the letter a is not uppercase, it is lowercase, so save it, run the command again and now you can see that what we get is this data, it is actually a matrix with which I would say. key-value pairs is actually a tuple here because it's split with a comma, so we could try to convert this to a JSON object if that's what we want or we could just leave it as is, but if we pass this data to a client or be API, the peer won't know how to interpret this and that, so let's convert this to a JSON array with JSON objects.

The syntax is a little complicated, so I'll just say that Rose will be an array within the array. I'm going to create a dictionary so thatbe a dict like this and I want to transport this value and that value to key-value pairs and this is going to be really complicated, guys, you need to know a lot about Python, but I'll do it. show you here is this. I'm going to create the dictionary. I'm going to extract values, so we leave it open and closed and then I'm going to literally create a loop inside this array. One loop says for row 4.

Actually, I'll only get one for role in roles. This lesson is not about Python, so I'll just give you the line of code and you'll see the result. If you are curious, you can Google how to create a dictionary with sippin. Dating and then I'll do the IV and I'll do the name and now we take a role, so what I'm doing, explaining this to you briefly, I'm going to go through all the rows, I'm going to take each one of them as a role, so we'll take this as a role which is going to be this row and then I'm going to extract the first value for today being the second value in the name and I'm going to create a dictionary for it inside this array, so save it and try it.

Now we have a problem, it says that line number 4 which is the dictionary I built is not running as expected so we need to check it and we say it is a dictionary, it is the form and here is the message: this id and this name. they have to be converted or they actually expect it to be inside an array because you can have multiple of these elements, you can have a key and another key, etc. I hope that's the only book, let me run the file again the same way. the sheep made the name and I think I have this as an extra parenthesis that shouldn't be that should be there sorry guys if I'm confusing you a little, but this is the end result, so we wish you.

We'll run it so this is what we get an array with JSON objects, the first object and the second object. This is complicated, it's hard to explain now because I'm focusing on the sequel part, this is how we read the data. the database you can add a limit here you can pass variables to it that will depend on you let's go to the next part of the video we have the box we have the cane now we are going to update a user so these are the users I will just copy this ID for now or probably no because I will copy another part of the code, let's create a new file, let's call it updated py and now copy what I have from one of the other files from the deleted file. for example, paste it to import the sequel ID, I'll connect and when I update I'm not going to get everything back, actually when I go to update something I just want to see how many items were updated, that will be the idea.

From this, this is not necessary and that is not necessary, so we say we want to run a command and this will be updated, let's say where we are going to update the users, we are going to set the name to be a placeholder where the ID is equal . a placeholder and this command is pretty much the same command as this so we create the run part and then we pass the placeholders between it so this is the run command and then we make a comma and then we want to pass the ID. and the name we don't have the id, so we just take it from the terminal.

This is Devi that I want to update, so I want you to copy it and pay attention to the name, by the way, it's ABC, so I'm going to break this. in lines you can see that ID and the name will be the name now I will change it to g HJ I will save it remember we have to commit so once we do that the execution of this command will take place in the database let's open the terminal let's point to updated py with just this coming automatically so hopefully it's been updated this has to be in the database now so we'll slightly open up the sequel of the place and I'll run the query and it's not there it's just ABC I.

I don't see the updated command but I also don't see any errors here, so the problem I see here is that the placeholders respect the element I declare them on, so the name is the first element and that is the second element and you can see what I'm doing here. I'm reversing the values ​​so that the name is the first element and the ID is the second Elron, save it, run the command again. I don't see any problem, let's run the query. and now you can see that the data has been updated successfully. Now let's go to the delete file, create a new file, call it lid dot py and we are going to copy the chesties command, throw it inside the lid, we import, we connect to the database and now we are going to delete where is the ID in an ID specific, this is the element that contains these values ​​just so you remember ghj, so we change the command now.

I will say that users will direct the daily question equal to mark that means the first argument that I passed inside the parentheses will be the ID. Split this inline so you can see and then I'm going to commit if we check the values ​​in the database to see that we have J H J, so now I'm going to try to run the delete command. I'll save it. I'll run the removed py. Click Enter. We have a problem on line number three. It is because this command is invalid. Let's check it out. Delete from users. Why was D questionable?

Let me. Quickly zoom out just for a second to see my MA setting and closing the parentheses correctly compared to the parentheses she had there, so it looks like that's correct. I have two parentheses. I have two parentheses. I have one. I have one. It seems that this is it. okay, but we have a problem and you will have these two when you read the message here, it says that the wrong number of links were provided and we are seeing this because this here should be interpreted as in a string, so I could do the string there. or sorry, I take it back guys, it's not because it's extreme because I'm using quotes, it's actually a string.

I see the problem here, let me zoom back in and close this. I don't want them to lose this woman like I did. before and I can see that the problem is that Python uses between these parentheses what is known as at topple. I think spell this about this and even though this is the string that represents Devi, we need to add a comma to the end so Python knows that. This will be the first argument because takedowns come in groups of two. You can read more about this if you are interested in knowing how it is happening.

I think that's the problem. Let me check it. I'll save it. Remove this collapse so now save it again and then I'll run py delete. I don't see any pictures. I'll run the query and now we can see that J Age G has been deleted, so that element no longer exists. Alright guys, so I cover a lot. of basic stuff, this is really complicated, you will forget and forget it many times, just remember the final comma at the end of the header. I hope you liked the video, please subscribe again. I can't emphasize it more, just subscribe.

I'll show you. how to do exactly the same thing with MongoDB and I will continue to show these videos with new information on how to create for example a RESTful API where we validate the data and we also have to take care of the routing part so that we have get post put and delete bye guys thanks for watching this video and cover the next videos that will be available soon