1 00:00:05,540 --> 00:00:07,090 So this video we're gonna start storing 2 00:00:07,090 --> 00:00:10,280 our camp details in a database. 3 00:00:10,280 --> 00:00:11,610 What we're gonna do is go to the top 4 00:00:11,610 --> 00:00:14,200 of this RollingBack.py folder that we created. 5 00:00:14,200 --> 00:00:18,370 We're gonna start off by importing SQLite3, import sqlite3 6 00:00:23,520 --> 00:00:26,310 and then we're going to continue on 7 00:00:26,310 --> 00:00:29,490 and we're going to start putting in some commands 8 00:00:29,490 --> 00:00:32,780 to actually create the table and the two tables we want 9 00:00:32,780 --> 00:00:34,710 which we're going to talk about shortly. 10 00:00:34,710 --> 00:00:38,070 Which will be the accounts and the transactions tables. 11 00:00:38,070 --> 00:00:42,230 We're going to start with db = sqlite3.connect 12 00:00:45,710 --> 00:00:48,900 and we're going to go with "accounts.sqlite" 13 00:00:48,900 --> 00:00:50,950 is the name of the database 14 00:00:50,950 --> 00:00:53,230 and we're going to be putting two statements here. 15 00:00:53,230 --> 00:00:57,390 So db.execute first one "CREATE TABLE IF NOT EXISTS" 16 00:01:01,190 --> 00:01:03,120 we talked about this before and it's gonna be 17 00:01:03,120 --> 00:01:06,370 accounts name TEXT PRIMARY KEY NOT NULL 18 00:01:14,180 --> 00:01:17,930 and balance which will be an INTEGER NOT NULL 19 00:01:21,920 --> 00:01:23,620 so that's our accounts table. 20 00:01:23,620 --> 00:01:25,540 We also want transactions table. 21 00:01:25,540 --> 00:01:28,030 Again we're doing this for this account class 22 00:01:28,030 --> 00:01:31,410 that we created so db.execute this time it's going to be 23 00:01:31,410 --> 00:01:34,990 CREATE TABLE IF NOT EXISTS and transactions 24 00:01:41,720 --> 00:01:43,170 this one's gonna be time 25 00:01:43,170 --> 00:01:46,510 which is going to be TIMESTAMP NOT NULL, 26 00:01:50,370 --> 00:01:52,120 account TEXT NOT NULL 27 00:01:54,250 --> 00:01:56,690 trying to be consistent here with the case. 28 00:01:56,690 --> 00:01:59,560 Leaving all the SQL statements in upper case 29 00:01:59,560 --> 00:02:01,810 and the name of the columns in our tables 30 00:02:01,810 --> 00:02:03,920 all the table names in lowercase. 31 00:02:03,920 --> 00:02:07,690 Next one's gonna be amount which is gonna be 32 00:02:07,690 --> 00:02:11,020 INTEGER NOT NULL it's getting a bit long 33 00:02:13,010 --> 00:02:16,010 so let's just continue on like that. 34 00:02:18,340 --> 00:02:22,170 And I'm gonna finish this off with PRIMARY KEY 35 00:02:23,290 --> 00:02:25,380 is gonna be time, account 36 00:02:28,540 --> 00:02:31,120 alright so that should be that. 37 00:02:32,190 --> 00:02:36,580 Alright and two blank lines so that IntelliJ is happy. 38 00:02:36,580 --> 00:02:39,560 Now there is a reason up here on line three 39 00:02:39,560 --> 00:02:43,210 why I've given the database file a SQLite extension. 40 00:02:43,210 --> 00:02:45,400 So make sure you do that too, even if you're using 41 00:02:45,400 --> 00:02:48,100 an operating system that doesn't rely on file extensions 42 00:02:48,100 --> 00:02:52,320 to determine the top of the file and we'll see why shortly. 43 00:02:52,320 --> 00:02:55,160 Now both tables are pretty straight forward. 44 00:02:55,160 --> 00:02:58,620 The accounts table just stores the name of the account 45 00:02:58,620 --> 00:03:00,070 and the account balance. 46 00:03:00,070 --> 00:03:02,030 Although in a real application you'll probably 47 00:03:02,030 --> 00:03:05,590 store other information for example, an overdraft limit, 48 00:03:05,590 --> 00:03:09,990 the date the account was created and so on and so forth. 49 00:03:09,990 --> 00:03:12,370 Now the transactions table 50 00:03:12,370 --> 00:03:14,400 stores the time of the transaction, 51 00:03:14,400 --> 00:03:17,090 the account that the transaction relates to, 52 00:03:17,090 --> 00:03:19,130 as well as the amount. 53 00:03:19,130 --> 00:03:21,320 We're gonna store deposits as positive 54 00:03:21,320 --> 00:03:24,430 and withdrawals as negative. 55 00:03:24,430 --> 00:03:28,460 Now the PRIMARY KEY for the transactions table 56 00:03:28,460 --> 00:03:31,690 this part down here, is defined a bit differently 57 00:03:31,690 --> 00:03:34,200 to how we've done it before and that's because we need 58 00:03:34,200 --> 00:03:37,070 what's called a composite key. 59 00:03:37,070 --> 00:03:38,860 Now it's quite possible for many transactions 60 00:03:38,860 --> 00:03:42,160 to happen at the same time and obviously 61 00:03:42,160 --> 00:03:44,500 one account will have many transactions. 62 00:03:44,500 --> 00:03:47,460 So it's the combination of time and account 63 00:03:47,460 --> 00:03:50,410 that will uniquely identify a particular transaction 64 00:03:50,410 --> 00:03:51,740 in our database. 65 00:03:53,040 --> 00:03:56,300 Now perhaps a more usual way would be to add an ID column 66 00:03:56,300 --> 00:03:59,340 but we've already done that and I haven't shown you 67 00:03:59,340 --> 00:04:01,700 how to define composite keys, so that's why 68 00:04:01,700 --> 00:04:03,600 I've done it this way here. 69 00:04:03,600 --> 00:04:06,390 Even if you don't create a composite PRIMARY KEY 70 00:04:06,390 --> 00:04:08,920 composite keys can be very useful. 71 00:04:08,920 --> 00:04:11,190 Remember that you can have other keys 72 00:04:11,190 --> 00:04:13,320 in addition to the PRIMARY KEY. 73 00:04:13,320 --> 00:04:15,730 If you want to create additional keys 74 00:04:15,730 --> 00:04:18,510 you kind of just drop the PRIMARY from PRIMARY KEY. 75 00:04:18,510 --> 00:04:21,630 Instead you use the word UNIQUE. 76 00:04:21,630 --> 00:04:23,380 Now if there's a transactions table storing 77 00:04:23,380 --> 00:04:25,930 all the transactions on the account you might be wondering 78 00:04:25,930 --> 00:04:29,650 why the balance is stored in the accounts table. 79 00:04:29,650 --> 00:04:31,730 You can see this up here. 80 00:04:32,650 --> 00:04:34,730 Strictly speaking that's not necessary 81 00:04:34,730 --> 00:04:37,840 and in fact is breaking normalisation. 82 00:04:37,840 --> 00:04:40,170 The balance can be calculated by summing 83 00:04:40,170 --> 00:04:43,140 all the amounts in the transactions table. 84 00:04:43,140 --> 00:04:46,160 Over time though, there will be a lot of transactions 85 00:04:46,160 --> 00:04:49,760 and calculating the balance each time will be expensive. 86 00:04:49,760 --> 00:04:52,800 You'll find examples like this in real world databases 87 00:04:52,800 --> 00:04:55,800 where the theoretical rules of database normalisation 88 00:04:55,800 --> 00:04:58,640 are broken for performance reasons. 89 00:04:58,640 --> 00:05:01,370 Provided everything works correctly 90 00:05:01,370 --> 00:05:04,140 there should be no disparity between the balance stored 91 00:05:04,140 --> 00:05:06,470 in the accounts table and when calculated 92 00:05:06,470 --> 00:05:08,830 by summing all the transactions. 93 00:05:08,830 --> 00:05:11,430 A real financial application may run a job 94 00:05:11,430 --> 00:05:13,840 during quiet times to compare the two values 95 00:05:13,840 --> 00:05:17,310 and make sure in fact they do agree. 96 00:05:17,310 --> 00:05:20,710 So moving on, we're actually storing the time 97 00:05:20,710 --> 00:05:25,440 as a TIMESTAMP property, this one here on line five. 98 00:05:25,440 --> 00:05:27,370 If you check the SQLite documentation 99 00:05:27,370 --> 00:05:29,650 you might be wondering where I got TIMESTAMP from 100 00:05:29,650 --> 00:05:32,080 and that's because SQLite only has five tops 101 00:05:32,080 --> 00:05:34,670 for its columns and just to confirm that 102 00:05:34,670 --> 00:05:38,000 if I just open link and I open a browser 103 00:05:48,110 --> 00:05:49,600 and you can see those five main types 104 00:05:49,600 --> 00:05:52,600 NULL, INTEGER, REAL, TEXT, and BLOB. 105 00:05:53,430 --> 00:05:57,370 Now they're storage classes rather than data types. 106 00:05:57,370 --> 00:05:59,600 Apart from INTEGER PRIMARY KEY fields 107 00:05:59,600 --> 00:06:03,750 you can store any kind of value in any type of column. 108 00:06:03,750 --> 00:06:06,940 So INTEGER PRIMARY KEY columns are handled differently 109 00:06:06,940 --> 00:06:09,580 and in fact can only hold INTEGER values. 110 00:06:09,580 --> 00:06:11,510 Now the system is very flexible though 111 00:06:11,510 --> 00:06:14,740 and the Python SQLite3 library includes support 112 00:06:14,740 --> 00:06:16,760 for datetime values. 113 00:06:16,760 --> 00:06:18,980 So it performs conversion automatically 114 00:06:18,980 --> 00:06:21,660 to and from datetime values. 115 00:06:21,660 --> 00:06:23,500 But we do have to tell it to do that 116 00:06:23,500 --> 00:06:26,210 and we will see how to do that shortly. 117 00:06:26,210 --> 00:06:28,800 Now I'm gonna go back and refer to the Python documentation 118 00:06:28,800 --> 00:06:31,900 when we come to read our TIMESTAMP values back in. 119 00:06:31,900 --> 00:06:34,720 Notice I mentioned in the earlier section of this course 120 00:06:34,720 --> 00:06:37,290 when we looked at the date and time modules 121 00:06:37,290 --> 00:06:39,510 we'll be storing UTC times. 122 00:06:39,510 --> 00:06:42,060 Unless if you have a very specific reason 123 00:06:42,060 --> 00:06:45,820 for doing otherwise, always store times in UTC 124 00:06:45,820 --> 00:06:47,470 and you can refer back to that earlier section 125 00:06:47,470 --> 00:06:49,400 to find out why. 126 00:06:49,400 --> 00:06:50,870 Now you should be happy with storing text 127 00:06:50,870 --> 00:06:53,290 and numbers in a SQLite database 128 00:06:53,290 --> 00:06:54,970 so I've added this TIMESTAMP field 129 00:06:54,970 --> 00:06:57,030 to demonstrate how to cope with dates. 130 00:06:57,030 --> 00:06:59,280 It's really no harder than storing text and numbers 131 00:06:59,280 --> 00:07:01,230 you just need to know how to do it. 132 00:07:01,230 --> 00:07:03,140 We go back to our code. 133 00:07:04,640 --> 00:07:06,300 Looking at these warnings over here, 134 00:07:06,300 --> 00:07:10,000 no data sources are configured to run this SQL. 135 00:07:10,000 --> 00:07:12,940 So we're going to ignore those warnings for now. 136 00:07:12,940 --> 00:07:15,150 In fact we're going to get a few more as we progress. 137 00:07:15,150 --> 00:07:17,260 But we will look at them a little bit later 138 00:07:17,260 --> 00:07:18,720 and see how to get rid of them. 139 00:07:18,720 --> 00:07:21,930 They're actually nothing to do with the Python code per se 140 00:07:21,930 --> 00:07:24,030 they're actually cause by IntelliJ being unable 141 00:07:24,030 --> 00:07:27,450 to verify things like the table and column names. 142 00:07:27,450 --> 00:07:29,960 Now that we've got the tables created, 143 00:07:29,960 --> 00:07:33,120 I'm gonna make a change to the account classes init method 144 00:07:33,120 --> 00:07:36,450 so that it retrieves the account details from the database 145 00:07:36,450 --> 00:07:40,750 or saves them if the account doesn't exist. 146 00:07:40,750 --> 00:07:44,150 Quick looking down here, in our init method, line 11. 147 00:07:44,150 --> 00:07:47,790 We're going to start with getting our cursors 148 00:07:47,790 --> 00:07:51,960 going to type cursor is equal to db.execute and the SQL's 149 00:07:53,870 --> 00:07:57,950 gonna be SELECT name, balance FROM accounts WHERE 150 00:08:03,830 --> 00:08:08,000 and (name = ?) for prepared statement, 151 00:08:12,270 --> 00:08:17,220 then we're gonna put (name,) wrap parentheses 152 00:08:17,220 --> 00:08:19,590 as you can see there to end the line. 153 00:08:19,590 --> 00:08:23,750 Then we're gonna do row = cursor.fetchone() 154 00:08:25,170 --> 00:08:27,170 meaning a single record. 155 00:08:28,980 --> 00:08:31,650 Then we're going to type if row: 156 00:08:32,809 --> 00:08:34,120 and we're gonna change this a little bit, 157 00:08:34,120 --> 00:08:38,289 we're gonna put self.name, self.balance is = row 158 00:08:41,760 --> 00:08:45,920 then we're going to put print("Retrieved record for 159 00:08:47,310 --> 00:08:48,820 and set up a replacement filed with 160 00:08:48,820 --> 00:08:52,990 left and right curly braces . ". format(self.name), 161 00:08:58,780 --> 00:09:00,850 and end='') 162 00:09:00,850 --> 00:09:01,930 I guess I could have copied some of 163 00:09:01,930 --> 00:09:04,020 this other source code here 164 00:09:04,020 --> 00:09:04,980 in fact let's try and do that for 165 00:09:04,980 --> 00:09:07,520 some of this other code. 166 00:09:07,520 --> 00:09:11,060 else so if you weren't able to retrieve a record 167 00:09:11,060 --> 00:09:13,600 from the database presumably it doesn't exist 168 00:09:13,600 --> 00:09:18,050 so we're gonna change this self.name = name 169 00:09:18,050 --> 00:09:20,380 and self.balance will actually in fact 170 00:09:20,380 --> 00:09:22,320 equal the opening balance. 171 00:09:22,320 --> 00:09:25,500 Then we need to add some code here, so that's going to be 172 00:09:25,500 --> 00:09:28,920 some SQL code to save this cursor.execute 173 00:09:31,640 --> 00:09:35,810 INSERT INTO accounts VALUES watching the extra spaces 174 00:09:37,590 --> 00:09:40,010 then two question marks there 175 00:09:44,020 --> 00:09:48,180 and then we want (name, opening_balance) 176 00:09:50,780 --> 00:09:55,440 then we'll do a cursor.connection.commit() 177 00:09:55,440 --> 00:09:57,520 to immediately save that. 178 00:09:59,900 --> 00:10:02,020 We're gonna leave that message there, 179 00:10:02,020 --> 00:10:05,020 print("Account created for that's gonna be exactly the same 180 00:10:05,020 --> 00:10:07,590 as what it was before so we're going to leave 181 00:10:07,590 --> 00:10:09,360 the self.show_balance on the next line 182 00:10:09,360 --> 00:10:12,250 so that's gonna be executed whether we receive the data 183 00:10:12,250 --> 00:10:14,830 from the table, if it was already in existence. 184 00:10:14,830 --> 00:10:17,040 That would be if the row was found here 185 00:10:17,040 --> 00:10:20,720 on line 15 or if didn't exist when we're creating it 186 00:10:20,720 --> 00:10:22,630 as per new here, either way we're gonna show 187 00:10:22,630 --> 00:10:24,570 the balance once we're done. 188 00:10:24,570 --> 00:10:26,920 Basically, again what we're doing here is running 189 00:10:26,920 --> 00:10:29,560 a simple SELECT query to retrieve the row 190 00:10:29,560 --> 00:10:31,900 where the name matches the one passed in 191 00:10:31,900 --> 00:10:33,520 when creating the account instance. 192 00:10:33,520 --> 00:10:36,670 So using this name here you actually do a query 193 00:10:36,670 --> 00:10:38,370 and we're using that there as you can see 194 00:10:38,370 --> 00:10:41,350 they're setting it up to replace the argument there 195 00:10:41,350 --> 00:10:45,090 with the name that's been passed to the init method. 196 00:10:45,090 --> 00:10:47,600 So the cursors fetch one method will return either tuple 197 00:10:47,600 --> 00:10:50,420 containing the values from all the database columns 198 00:10:50,420 --> 00:10:54,230 otherwise it will return none if no record was found. 199 00:10:54,230 --> 00:10:57,950 You've seen conditions like this with this if row before. 200 00:10:57,950 --> 00:10:59,230 The condition will be true if row 201 00:10:59,230 --> 00:11:02,240 isn't either zero or an empty string, 202 00:11:02,240 --> 00:11:04,430 list, set, dictionary, or none. 203 00:11:04,430 --> 00:11:06,480 So I guess we could have written this instead 204 00:11:06,480 --> 00:11:10,640 as if row is not None: would be and alternative way 205 00:11:11,760 --> 00:11:14,270 to do that, in fact the effect would be the same 206 00:11:14,270 --> 00:11:18,190 and with that said you'll often see the shorthand way 207 00:11:18,190 --> 00:11:20,810 of writing it when you read other programmers code. 208 00:11:20,810 --> 00:11:24,480 In other words, either way is actually okay. 209 00:11:25,870 --> 00:11:29,290 Based on this query, if we get a record back 210 00:11:29,290 --> 00:11:31,610 from the database we unpack the tuple 211 00:11:31,610 --> 00:11:34,610 you can see on the next line here on line 16 212 00:11:34,610 --> 00:11:37,150 to set the values for the account name and balance. 213 00:11:37,150 --> 00:11:39,820 Now if a matching row isn't found in the database 214 00:11:39,820 --> 00:11:42,340 the else clause is triggered and down here 215 00:11:42,340 --> 00:11:46,910 we're actually saving the name and account balance 216 00:11:46,910 --> 00:11:48,940 then we insert a new row. 217 00:11:48,940 --> 00:11:51,880 Because we're doing an INSERT we have to commit the change 218 00:11:51,880 --> 00:11:55,670 by calling the commit method on the cursors connexion. 219 00:11:55,670 --> 00:11:57,710 I think it's time now to run this. 220 00:11:57,710 --> 00:11:59,820 So let's just run this and see 221 00:11:59,820 --> 00:12:00,980 whether it actually works or not. 222 00:12:00,980 --> 00:12:03,480 So let's go ahead and do that. 223 00:12:05,390 --> 00:12:07,460 We've actually got a error here. 224 00:12:07,460 --> 00:12:09,070 And the problem's actually up here 225 00:12:09,070 --> 00:12:11,320 you see that I've got a left parentheses 226 00:12:11,320 --> 00:12:13,930 in the SQL statement but I'm not ending 227 00:12:13,930 --> 00:12:16,480 the right parentheses and in fact here you can see 228 00:12:16,480 --> 00:12:17,830 that IntelliJ is helpfully telling us 229 00:12:17,830 --> 00:12:18,970 that there's a problem there. 230 00:12:18,970 --> 00:12:21,120 So I'm going to change that and put the right parentheses 231 00:12:21,120 --> 00:12:24,420 to fix that and just check the next one 232 00:12:24,420 --> 00:12:26,950 I think I've got the right parentheses there OK. 233 00:12:26,950 --> 00:12:31,120 So that's looks ok let' just try running it again. 234 00:12:32,280 --> 00:12:35,320 Alright so this time we can now see the normal text here 235 00:12:35,320 --> 00:12:38,140 that the account got created for John. 236 00:12:38,140 --> 00:12:40,330 Balance on account John is zero. 237 00:12:40,330 --> 00:12:41,470 And we've got these deposits 238 00:12:41,470 --> 00:12:43,740 that are going through and withdrawals. 239 00:12:43,740 --> 00:12:46,310 At this stage, we haven't actually updated 240 00:12:46,310 --> 00:12:49,750 the balance value in the database row for John's account. 241 00:12:49,750 --> 00:12:50,710 So that's something that we're 242 00:12:50,710 --> 00:12:53,700 going to need to do shortly in those methods. 243 00:12:53,700 --> 00:12:55,670 Firstly though I want to create a few more accounts 244 00:12:55,670 --> 00:12:57,960 and then close the database when we're done. 245 00:12:57,960 --> 00:13:01,340 So let's just go ahead and do that down here. 246 00:13:01,340 --> 00:13:03,500 We'll just add a couple more, 247 00:13:03,500 --> 00:13:07,330 terry = Account("Terry") 248 00:13:09,200 --> 00:13:13,370 and graham = Account("Graham") 249 00:13:15,900 --> 00:13:20,070 and we'll also specify an initial balance there, 9000. 250 00:13:21,700 --> 00:13:25,870 And eric = Account("Eric") and also I pass 251 00:13:30,540 --> 00:13:34,710 a value there 7,000 and we'll do a db.close() 252 00:13:37,590 --> 00:13:39,090 So let's run this. 253 00:13:42,000 --> 00:13:45,260 This time we can see that it's retrieved the record for John 254 00:13:45,260 --> 00:13:46,730 balance on account John is zero 255 00:13:46,730 --> 00:13:50,900 so we can see that our row got retrieved successfully. 256 00:13:54,170 --> 00:13:56,590 It's actually retrieved the data, even though 257 00:13:56,590 --> 00:13:58,650 we're not yet updating the balance with the amount 258 00:13:58,650 --> 00:14:00,650 you can see that it has retrieved it successfully 259 00:14:00,650 --> 00:14:03,040 so that's off to a good start. 260 00:14:03,040 --> 00:14:04,740 But you can also see here looking down 261 00:14:04,740 --> 00:14:06,020 that the account has been created 262 00:14:06,020 --> 00:14:08,320 for Terry, Graham and Eric as well 263 00:14:08,320 --> 00:14:11,330 you can see there's a balance there which is correct 264 00:14:11,330 --> 00:14:15,420 because in Terry's case we didn't pass an opening balance 265 00:14:15,420 --> 00:14:18,340 so it defaulted to zero, but in the case of Graham and Eric 266 00:14:18,340 --> 00:14:19,910 we've actually got 90 and 70, 267 00:14:19,910 --> 00:14:22,570 remembering that we're storing these as integers 268 00:14:22,570 --> 00:14:25,970 so it's divided by a hundred to get the actual value. 269 00:14:25,970 --> 00:14:27,570 Alright so I'm going to stop the video here. 270 00:14:27,570 --> 00:14:29,100 In the next one, we're going to look at a way 271 00:14:29,100 --> 00:14:31,920 to view these records from within IntelliJ 272 00:14:31,920 --> 00:14:35,300 and we'll also take care of these warnings that I showed you 273 00:14:35,300 --> 00:14:37,680 that IntelliJ's is giving us. 274 00:14:37,680 --> 00:14:40,100 So see you in the next video.