1 00:00:00,000 --> 00:00:06,899 All right, in this lecture you're going to learn how to use sqlite3. So sqlite3 is 2 00:00:06,899 --> 00:00:12,840 a Python library to interact with SQLite databases, so think of it 3 00:00:12,840 --> 00:00:18,570 as a Python wrapper of SQL code, so you can actually write SQL code inside Python 4 00:00:18,570 --> 00:00:24,060 using sqlite3. You don't need to install sqlite3 because sqlite3 is 5 00:00:24,060 --> 00:00:33,120 a built-in Python library. So what we have to do is import sqlite3. And let me test it. 6 00:00:33,120 --> 00:00:39,600 Script1, yeah, it's working. Now a standard process of interacting with 7 00:00:39,600 --> 00:00:45,590 a database would consist of five steps. First is you connect to the database 8 00:00:45,590 --> 00:00:52,320 Second is you create a cursor object and the cursor object is like a pointer to 9 00:00:52,320 --> 00:00:58,500 actually access rows from a table for database. Then the third step is you 10 00:00:58,500 --> 00:01:05,309 apply an SQL query, so you may want to insert data in a database or select data 11 00:01:05,309 --> 00:01:10,409 from the table and so on and so forth. And then the fourth step is you commit 12 00:01:10,409 --> 00:01:17,610 your changes to the database and then you close the connection, so let's go 13 00:01:17,610 --> 00:01:22,700 ahead and write an example with all these five steps that I just mentioned. 14 00:01:22,700 --> 00:01:29,310 So the first thing you need to create a connection and you want to store that 15 00:01:29,310 --> 00:01:34,619 connection object in a variable, so let's conn that should be equal to sqlite3 16 00:01:34,619 --> 00:01:43,579 dot connect and what you pass here is the database file, so let's say 17 00:01:43,579 --> 00:01:51,720 lite.db and if you don't have the database file yet, your database will be 18 00:01:51,720 --> 00:01:56,399 created by this line of code, and the connection will be established. 19 00:01:56,399 --> 00:02:00,689 If you have a database, a connection will be established with your existing database. 20 00:02:00,689 --> 00:02:08,009 As simple as that. Then you have to create a cursor object, so cur, 21 00:02:08,009 --> 00:02:13,050 the variable and you need to refer to your connection, and then to the 22 00:02:13,050 --> 00:02:20,040 cursor method which is a method of the connection object. So far so good. 23 00:02:20,040 --> 00:02:26,610 Then here comes the actual SQL code. You want to point to your cursor object and then 24 00:02:26,610 --> 00:02:32,340 use the execute method, and then inside brackets is where you enter your SQL code. 25 00:02:32,340 --> 00:02:39,690 Remember your SQL code always goes inside quotes, so you pass the SQL code 26 00:02:39,690 --> 00:02:46,440 as a string to the execute method of the cursor object on the sqlite3 library. 27 00:02:46,440 --> 00:02:50,820 So if you're familiar with SQL this should be easy for you. If you're not familiar 28 00:02:50,820 --> 00:02:56,100 with SQL, SQL is quite simple, so you'll be able to learn it quite quickly. 29 00:02:56,100 --> 00:03:00,840 So at this point we have a database but we don't have any tables in the database. 30 00:03:00,840 --> 00:03:07,290 Because you know a database is made of tables and tables have rows and columns, 31 00:03:07,290 --> 00:03:13,470 so first you want to create a table and a good practice is to use capital 32 00:03:13,470 --> 00:03:20,310 letters for the SQL keywords, so create is an SQL keyword and table this is SQL 33 00:03:20,310 --> 00:03:23,880 keywords as well, and then you want to specify that the name that you want to 34 00:03:23,880 --> 00:03:30,270 give to the table. Let's say store. that's how you order Python via SQL to create a 35 00:03:30,270 --> 00:03:34,500 table, but then you need to specify what columns you want, what fields you want in 36 00:03:34,500 --> 00:03:39,630 your table. So let's suppose we are creating a table. We have a store, we have 37 00:03:39,630 --> 00:03:46,230 a shop and we want to store our products in a SQLite database table. So we would 38 00:03:46,230 --> 00:03:53,010 want to have the item, so that would be the item name and the item would be 39 00:03:53,010 --> 00:03:59,180 a text data type. So text is like a string in Python and then you'd want to have 40 00:03:59,180 --> 00:04:07,590 quantity and that would be an integer. So how many of these item you have in your 41 00:04:07,590 --> 00:04:15,390 store and the price as well, and let's say real for price which would be 42 00:04:15,390 --> 00:04:21,720 a float in Python so a decimal point number and that's it. This closes the quotes 43 00:04:21,720 --> 00:04:26,580 on the SQL code, and here is last bracket of the execute method. 44 00:04:26,580 --> 00:04:31,650 Then you need to commit these changes to the database, so you refer to the 45 00:04:31,650 --> 00:04:37,620 connection and commit. And then you want to close the connection, so that's it. 46 00:04:37,620 --> 00:04:44,759 You save that and go and execute. Qlite is no defined of course because I 47 00:04:44,759 --> 00:04:52,409 have made an error here, a typo and again. As you see a database was created. 48 00:04:52,409 --> 00:05:00,210 Now if you run the program again you get an error because the table 49 00:05:00,210 --> 00:05:06,750 already exists in the database, so what you would do here is a simple trick. 50 00:05:06,750 --> 00:05:14,430 You need to add some code in your SQL line line you say. Create table if not exist. 51 00:05:14,430 --> 00:05:20,280 So save and if run your code again now nothing will happen because what 52 00:05:20,280 --> 00:05:25,949 Python does is it goes and connects to the database, then it creates a cursor 53 00:05:25,949 --> 00:05:30,630 and then if there is a table called store it doesn't do anything and it goes 54 00:05:30,630 --> 00:05:35,099 to the next line. If there was no table it would create a table, so it's good to 55 00:05:35,099 --> 00:05:40,889 have these simple conditional here inside the SQL code. How about adding 56 00:05:40,889 --> 00:05:47,909 some data to our empty table? Let's try that. Cur execute, so we are executing 57 00:05:47,909 --> 00:05:52,949 some SQL code again and the keyword that that is Thursday inserts data 58 00:05:52,949 --> 00:05:58,740 to the database is insert. Then you want to specify where you want to insert the data. 59 00:05:58,740 --> 00:06:04,199 60 So insert it to store. And what values you 61 00:06:04,199 --> 00:06:11,219 want to insert through your store table? So values, then you open brackets and 62 00:06:11,219 --> 00:06:17,039 inside the brackets you enter the values in the correct order, so in the order 63 00:06:17,039 --> 00:06:22,199 that you have defined your columns so you'd have item, quantity, and price. 64 00:06:22,199 --> 00:06:32,840 And let's say wine glass. Now this is a string so it should go inside quotes 65 00:06:32,840 --> 00:06:37,710 and you should use single quotes here because you're using double quotes for 66 00:06:37,710 --> 00:06:42,240 the alter expression, so if you use double who's here you will confuse Python and 67 00:06:42,240 --> 00:06:47,399 you'll get an error, so use single quotes. That's why we have different kinds of 68 00:06:47,399 --> 00:06:52,499 quotes to wrap around strings. So we have the string Wine Glass and let's say we 69 00:06:52,499 --> 00:07:00,589 have quantity of eight and the price of 10.5. Save that and go ahead and execute. 70 00:07:00,589 --> 00:07:06,180 We didn't get an error so that was successful. Now what if we want to insert 71 00:07:06,180 --> 00:07:12,539 another row? Well we could add that line in here under this, but then when we 72 00:07:12,539 --> 00:07:18,029 execute the code we would get this line executed as well, so we would 73 00:07:18,029 --> 00:07:23,699 have a duplicate of this row, therefore what we could do in this case is we 74 00:07:23,699 --> 00:07:31,559 would use the function to wrap around our separate SQL statements. So you can 75 00:07:31,559 --> 00:07:39,479 indent this block of code if you like. You can go to edit lines, indent, or you 76 00:07:39,479 --> 00:07:44,729 could use the shortcut which can be specific in your keyboard so just look 77 00:07:44,729 --> 00:07:49,949 here for your shortcut, keyboard shortcut, and you can just use that. And so the 78 00:07:49,949 --> 00:07:54,229 reason I am intending the block of code is that I want to create a function here. 79 00:07:54,229 --> 00:08:04,559 Let's say function create table. Don't forget a colon there. And then I want 80 00:08:04,559 --> 00:08:13,800 a separate function for inserting data. So that would be the SQL statement for the 81 00:08:13,800 --> 00:08:18,389 insert function, but you can see the difference between this function and 82 00:08:18,389 --> 00:08:23,009 this function. Here we are not creating a connection to the database which is wrong. 83 00:08:23,009 --> 00:08:31,469 So you'd want to insert these lines in here so that you establish 84 00:08:31,469 --> 00:08:39,509 a connection with the database and then you correctly close that connection. 85 00:08:39,509 --> 00:08:45,329 So you know how to use Python functions now. And naturally the need comes to pass 86 00:08:45,329 --> 00:08:51,750 parameters to your function here so you'd want to say item, so just some variables. 87 00:08:51,750 --> 00:08:57,900 Quantity and price and then you would 88 00:08:57,900 --> 00:09:03,690 want to somehow replace these parameters in here so that every time you call the 89 00:09:03,690 --> 00:09:08,730 function you insert some arguments and then those values are inserted to your 90 00:09:08,730 --> 00:09:11,700 table. Now you can use string placeholders, 91 00:09:11,700 --> 00:09:17,130 Python string placeholders to replace these values with the values that the user 92 00:09:17,130 --> 00:09:21,660 will pass when they call the function, but this is not a good practice because 93 00:09:21,660 --> 00:09:27,390 your code would be prone to SQL injections for mockers and that applies 94 00:09:27,390 --> 00:09:33,210 when of course your web application is on the web, but anyway 95 00:09:33,210 --> 00:09:40,560 a good practice though is to put question marks in here and then after the SQL 96 00:09:40,560 --> 00:09:46,350 statement, so after the double quote you'd want to pass the variables that 97 00:09:46,350 --> 00:09:51,900 this question marks will be replaced with. So you'd who want item, so in the 98 00:09:51,900 --> 00:09:58,050 correct order, quantity, and price. So let's try this out now. I would like to 99 00:09:58,050 --> 00:10:03,980 execute the insert function that I just created with some new items so let's say 100 00:10:03,980 --> 00:10:12,270 water glass, and we have 10 of them. This would be less expensive. Save that 101 00:10:12,270 --> 00:10:20,490 and go and execute. I made a terrible error here. I wrapped close all 102 00:10:20,490 --> 00:10:25,140 the arguments, so water glass should be in quotes and these are numbers, so save 103 00:10:25,140 --> 00:10:30,900 again, try it again. And your data should be now in the database, so one more 104 00:10:30,900 --> 00:10:39,870 time and let's say coffee cup. Save, execute again 105 00:10:39,870 --> 00:10:44,640 and now I am sure you would want to see the data that you have been inserting 106 00:10:44,640 --> 00:10:49,140 in your database, in your table, so let's create a function that does just that. 107 00:10:49,140 --> 00:10:56,130 Let's say def view and you'd want to establish a connection with the database, 108 00:10:56,130 --> 00:11:04,350 so let's copy that and paste it here. And then here goes the SQL code, 109 00:11:04,350 --> 00:11:09,570 so cur execute, so we always use the execute method to send SQL code 110 00:11:09,570 --> 00:11:15,270 to the database and then we have quotes. Let's say we want to select the data of 111 00:11:15,270 --> 00:11:24,410 all the columns of the store table. So select an asterisk which means all from 112 00:11:24,410 --> 00:11:31,110 store, all right. So we selected them but how do we actually fetch this data so 113 00:11:31,110 --> 00:11:35,580 that we can use them in Python? Well the way to fetch them is by using the 114 00:11:35,580 --> 00:11:41,370 fetch all method and we want to select, to store those rows in a variables, 115 00:11:41,370 --> 00:11:50,040 so let's say rows cur fetchall and then we need to close the connection. 116 00:11:50,040 --> 00:11:54,810 So in contrast to the other methods where we were writing data to the table, here we 117 00:11:54,810 --> 00:12:00,930 are just selecting data, so we don't have to use the commit method. And we want to 118 00:12:00,930 --> 00:12:11,220 return the rows variable. And I'd just like to print out the output of the function. 119 00:12:11,220 --> 00:12:17,160 So the output of the function is the rows variable, so I'll get the rows value 120 00:12:17,160 --> 00:12:26,460 printed out on the command line. Save the script, execute. Of course I have a type 121 00:12:26,460 --> 00:12:36,120 here and should be return. Try again. And here are our data. So we have 122 00:12:36,120 --> 00:12:41,850 wine glass, water glass, and we have two lines of coffee cups and that's 123 00:12:41,850 --> 00:12:47,700 because we executed the function earlier, the insert function and then now that 124 00:12:47,700 --> 00:12:52,530 I executed the script again with a purpose to get the view function 125 00:12:52,530 --> 00:12:56,700 executed, the insert function was executed as well for the second time. 126 00:12:56,700 --> 00:13:03,270 So coffee cup was inserted two times in the table. All right, that's for this 127 00:13:03,270 --> 00:13:07,560 lecture, and in our next lecture I'll show you some more operations you can do 128 00:13:07,560 --> 00:13:11,960 with the database, so talk to you later.