WEBVTT 1 00:00:02.339 --> 00:00:03.888 So this video we're gonna start storing 2 00:00:03.888 --> 00:00:07.075 our camp details in a database. 3 00:00:07.075 --> 00:00:08.401 What we're gonna do is go to the top 4 00:00:08.401 --> 00:00:10.996 of this RollingBack.py folder that we created. 5 00:00:10.996 --> 00:00:15.163 We're gonna start off by importing SQLite3, import sqlite3 6 00:00:20.319 --> 00:00:23.106 and then we're going to continue on 7 00:00:23.106 --> 00:00:26.289 and we're going to start putting in some commands 8 00:00:26.289 --> 00:00:29.577 to actually create the table and the two tables we want 9 00:00:29.577 --> 00:00:31.501 which we're going to talk about shortly. 10 00:00:31.501 --> 00:00:34.861 Which will be the accounts and the transactions tables. 11 00:00:34.861 --> 00:00:39.028 We're going to start with db = sqlite3.connect 12 00:00:42.504 --> 00:00:45.694 and we're going to go with "accounts.sqlite" 13 00:00:45.694 --> 00:00:47.747 is the name of the database 14 00:00:47.747 --> 00:00:50.022 and we're going to be putting two statements here. 15 00:00:50.022 --> 00:00:54.189 So db.execute first one "CREATE TABLE IF NOT EXISTS" 16 00:00:57.983 --> 00:00:59.916 we talked about this before and it's gonna be 17 00:00:59.916 --> 00:01:03.166 accounts name TEXT PRIMARY KEY NOT NULL 18 00:01:10.978 --> 00:01:14.728 and balance which will be an INTEGER NOT NULL 19 00:01:18.719 --> 00:01:20.412 so that's our accounts table. 20 00:01:20.412 --> 00:01:22.337 We also want transactions table. 21 00:01:22.337 --> 00:01:24.820 Again we're doing this for this account class 22 00:01:24.820 --> 00:01:28.203 that we created so db.execute this time it's going to be 23 00:01:28.203 --> 00:01:31.786 CREATE TABLE IF NOT EXISTS and transactions 24 00:01:38.511 --> 00:01:39.969 this one's gonna be time 25 00:01:39.969 --> 00:01:43.302 which is going to be TIMESTAMP NOT NULL, 26 00:01:47.161 --> 00:01:48.911 account TEXT NOT NULL 27 00:01:51.044 --> 00:01:53.481 trying to be consistent here with the case. 28 00:01:53.481 --> 00:01:56.351 Leaving all the SQL statements in upper case 29 00:01:56.351 --> 00:01:58.604 and the name of the columns in our tables 30 00:01:58.604 --> 00:02:00.716 all the table names in lowercase. 31 00:02:00.716 --> 00:02:04.483 Next one's gonna be amount which is gonna be 32 00:02:04.483 --> 00:02:07.816 INTEGER NOT NULL it's getting a bit long 33 00:02:09.802 --> 00:02:12.802 so let's just continue on like that. 34 00:02:15.130 --> 00:02:18.963 And I'm gonna finish this off with PRIMARY KEY 35 00:02:20.088 --> 00:02:22.171 is gonna be time, account 36 00:02:25.334 --> 00:02:27.917 alright so that should be that. 37 00:02:28.983 --> 00:02:33.371 Alright and two blank lines so that IntelliJ is happy. 38 00:02:33.371 --> 00:02:36.359 Now there is a reason up here on line three 39 00:02:36.359 --> 00:02:40.009 why I've given the database file a SQLite extension. 40 00:02:40.009 --> 00:02:42.192 So make sure you do that too, even if you're using 41 00:02:42.192 --> 00:02:44.895 an operating system that doesn't rely on file extensions 42 00:02:44.895 --> 00:02:49.118 to determine the top of the file and we'll see why shortly. 43 00:02:49.118 --> 00:02:51.953 Now both tables are pretty straight forward. 44 00:02:51.953 --> 00:02:55.417 The accounts table just stores the name of the account 45 00:02:55.417 --> 00:02:56.868 and the account balance. 46 00:02:56.868 --> 00:02:58.825 Although in a real application you'll probably 47 00:02:58.825 --> 00:03:02.383 store other information for example, an overdraft limit, 48 00:03:02.383 --> 00:03:06.783 the date the account was created and so on and so forth. 49 00:03:06.783 --> 00:03:09.163 Now the transactions table 50 00:03:09.163 --> 00:03:11.195 stores the time of the transaction, 51 00:03:11.195 --> 00:03:13.884 the account that the transaction relates to, 52 00:03:13.884 --> 00:03:15.928 as well as the amount. 53 00:03:15.928 --> 00:03:18.110 We're gonna store deposits as positive 54 00:03:18.110 --> 00:03:21.221 and withdrawals as negative. 55 00:03:21.221 --> 00:03:25.252 Now the PRIMARY KEY for the transactions table 56 00:03:25.252 --> 00:03:28.485 this part down here, is defined a bit differently 57 00:03:28.485 --> 00:03:30.993 to how we've done it before and that's because we need 58 00:03:30.993 --> 00:03:33.862 what's called a composite key. 59 00:03:33.862 --> 00:03:35.653 Now it's quite possible for many transactions 60 00:03:35.653 --> 00:03:38.959 to happen at the same time and obviously 61 00:03:38.959 --> 00:03:41.293 one account will have many transactions. 62 00:03:41.293 --> 00:03:44.252 So it's the combination of time and account 63 00:03:44.252 --> 00:03:47.206 that will uniquely identify a particular transaction 64 00:03:47.206 --> 00:03:48.539 in our database. 65 00:03:49.832 --> 00:03:53.091 Now perhaps a more usual way would be to add an ID column 66 00:03:53.091 --> 00:03:56.132 but we've already done that and I haven't shown you 67 00:03:56.132 --> 00:03:58.498 how to define composite keys, so that's why 68 00:03:58.498 --> 00:04:00.390 I've done it this way here. 69 00:04:00.390 --> 00:04:03.185 Even if you don't create a composite PRIMARY KEY 70 00:04:03.185 --> 00:04:05.717 composite keys can be very useful. 71 00:04:05.717 --> 00:04:07.988 Remember that you can have other keys 72 00:04:07.988 --> 00:04:10.114 in addition to the PRIMARY KEY. 73 00:04:10.114 --> 00:04:12.521 If you want to create additional keys 74 00:04:12.521 --> 00:04:15.309 you kind of just drop the PRIMARY from PRIMARY KEY. 75 00:04:15.309 --> 00:04:18.427 Instead you use the word UNIQUE. 76 00:04:18.427 --> 00:04:20.174 Now if there's a transactions table storing 77 00:04:20.174 --> 00:04:22.727 all the transactions on the account you might be wondering 78 00:04:22.727 --> 00:04:26.444 why the balance is stored in the accounts table. 79 00:04:26.444 --> 00:04:28.527 You can see this up here. 80 00:04:29.444 --> 00:04:31.523 Strictly speaking that's not necessary 81 00:04:31.523 --> 00:04:34.631 and in fact is breaking normalisation. 82 00:04:34.631 --> 00:04:36.963 The balance can be calculated by summing 83 00:04:36.963 --> 00:04:39.935 all the amounts in the transactions table. 84 00:04:39.935 --> 00:04:42.957 Over time though, there will be a lot of transactions 85 00:04:42.957 --> 00:04:46.555 and calculating the balance each time will be expensive. 86 00:04:46.555 --> 00:04:49.599 You'll find examples like this in real world databases 87 00:04:49.599 --> 00:04:52.598 where the theoretical rules of database normalisation 88 00:04:52.598 --> 00:04:55.431 are broken for performance reasons. 89 00:04:55.431 --> 00:04:58.169 Provided everything works correctly 90 00:04:58.169 --> 00:05:00.930 there should be no disparity between the balance stored 91 00:05:00.930 --> 00:05:03.261 in the accounts table and when calculated 92 00:05:03.261 --> 00:05:05.625 by summing all the transactions. 93 00:05:05.625 --> 00:05:08.224 A real financial application may run a job 94 00:05:08.224 --> 00:05:10.635 during quiet times to compare the two values 95 00:05:10.635 --> 00:05:14.106 and make sure in fact they do agree. 96 00:05:14.106 --> 00:05:17.505 So moving on, we're actually storing the time 97 00:05:17.505 --> 00:05:22.238 as a TIMESTAMP property, this one here on line five. 98 00:05:22.238 --> 00:05:24.162 If you check the SQLite documentation 99 00:05:24.162 --> 00:05:26.443 you might be wondering where I got TIMESTAMP from 100 00:05:26.443 --> 00:05:28.875 and that's because SQLite only has five tops 101 00:05:28.875 --> 00:05:31.463 for its columns and just to confirm that 102 00:05:31.463 --> 00:05:34.796 if I just open link and I open a browser 103 00:05:44.903 --> 00:05:46.392 and you can see those five main types 104 00:05:46.392 --> 00:05:49.392 NULL, INTEGER, REAL, TEXT, and BLOB. 105 00:05:50.227 --> 00:05:54.162 Now they're storage classes rather than data types. 106 00:05:54.162 --> 00:05:56.396 Apart from INTEGER PRIMARY KEY fields 107 00:05:56.396 --> 00:06:00.548 you can store any kind of value in any type of column. 108 00:06:00.548 --> 00:06:03.731 So INTEGER PRIMARY KEY columns are handled differently 109 00:06:03.731 --> 00:06:06.379 and in fact can only hold INTEGER values. 110 00:06:06.379 --> 00:06:08.303 Now the system is very flexible though 111 00:06:08.303 --> 00:06:11.533 and the Python SQLite3 library includes support 112 00:06:11.533 --> 00:06:13.554 for datetime values. 113 00:06:13.554 --> 00:06:15.774 So it performs conversion automatically 114 00:06:15.774 --> 00:06:18.456 to and from datetime values. 115 00:06:18.456 --> 00:06:20.293 But we do have to tell it to do that 116 00:06:20.293 --> 00:06:23.001 and we will see how to do that shortly. 117 00:06:23.001 --> 00:06:25.598 Now I'm gonna go back and refer to the Python documentation 118 00:06:25.598 --> 00:06:28.694 when we come to read our TIMESTAMP values back in. 119 00:06:28.694 --> 00:06:31.516 Notice I mentioned in the earlier section of this course 120 00:06:31.516 --> 00:06:34.084 when we looked at the date and time modules 121 00:06:34.084 --> 00:06:36.303 we'll be storing UTC times. 122 00:06:36.303 --> 00:06:38.853 Unless if you have a very specific reason 123 00:06:38.853 --> 00:06:42.610 for doing otherwise, always store times in UTC 124 00:06:42.610 --> 00:06:44.264 and you can refer back to that earlier section 125 00:06:44.264 --> 00:06:46.196 to find out why. 126 00:06:46.196 --> 00:06:47.665 Now you should be happy with storing text 127 00:06:47.665 --> 00:06:50.083 and numbers in a SQLite database 128 00:06:50.083 --> 00:06:51.765 so I've added this TIMESTAMP field 129 00:06:51.765 --> 00:06:53.827 to demonstrate how to cope with dates. 130 00:06:53.827 --> 00:06:56.070 It's really no harder than storing text and numbers 131 00:06:56.070 --> 00:06:58.021 you just need to know how to do it. 132 00:06:58.021 --> 00:06:59.938 We go back to our code. 133 00:07:01.431 --> 00:07:03.090 Looking at these warnings over here, 134 00:07:03.090 --> 00:07:06.795 no data sources are configured to run this SQL. 135 00:07:06.795 --> 00:07:09.733 So we're going to ignore those warnings for now. 136 00:07:09.733 --> 00:07:11.940 In fact we're going to get a few more as we progress. 137 00:07:11.940 --> 00:07:14.059 But we will look at them a little bit later 138 00:07:14.059 --> 00:07:15.517 and see how to get rid of them. 139 00:07:15.517 --> 00:07:18.728 They're actually nothing to do with the Python code per se 140 00:07:18.728 --> 00:07:20.822 they're actually cause by IntelliJ being unable 141 00:07:20.822 --> 00:07:24.249 to verify things like the table and column names. 142 00:07:24.249 --> 00:07:26.756 Now that we've got the tables created, 143 00:07:26.756 --> 00:07:29.918 I'm gonna make a change to the account classes init method 144 00:07:29.918 --> 00:07:33.242 so that it retrieves the account details from the database 145 00:07:33.242 --> 00:07:37.546 or saves them if the account doesn't exist. 146 00:07:37.546 --> 00:07:40.943 Quick looking down here, in our init method, line 11. 147 00:07:40.943 --> 00:07:44.589 We're going to start with getting our cursors 148 00:07:44.589 --> 00:07:48.756 going to type cursor is equal to db.execute and the SQL's 149 00:07:50.661 --> 00:07:54.744 gonna be SELECT name, balance FROM accounts WHERE 150 00:08:00.624 --> 00:08:04.791 and (name = ?) for prepared statement, 151 00:08:09.060 --> 00:08:14.016 then we're gonna put (name,) wrap parentheses 152 00:08:14.016 --> 00:08:16.381 as you can see there to end the line. 153 00:08:16.381 --> 00:08:20.548 Then we're gonna do row = cursor.fetchone() 154 00:08:21.962 --> 00:08:23.962 meaning a single record. 155 00:08:25.775 --> 00:08:28.442 Then we're going to type if row: 156 00:08:29.601 --> 00:08:30.916 and we're gonna change this a little bit, 157 00:08:30.916 --> 00:08:35.083 we're gonna put self.name, self.balance is = row 158 00:08:38.550 --> 00:08:42.717 then we're going to put print("Retrieved record for 159 00:08:44.106 --> 00:08:45.619 and set up a replacement filed with 160 00:08:45.619 --> 00:08:49.786 left and right curly braces {}. ". format(self.name), 161 00:08:55.573 --> 00:08:57.643 and end='') 162 00:08:57.643 --> 00:08:58.722 I guess I could have copied some of 163 00:08:58.722 --> 00:09:00.818 this other source code here 164 00:09:00.818 --> 00:09:01.775 in fact let's try and do that for 165 00:09:01.775 --> 00:09:04.313 some of this other code. 166 00:09:04.313 --> 00:09:07.855 else so if you weren't able to retrieve a record 167 00:09:07.855 --> 00:09:10.394 from the database presumably it doesn't exist 168 00:09:10.394 --> 00:09:14.842 so we're gonna change this self.name = name 169 00:09:14.842 --> 00:09:17.175 and self.balance will actually in fact 170 00:09:17.175 --> 00:09:19.118 equal the opening balance. 171 00:09:19.118 --> 00:09:22.293 Then we need to add some code here, so that's going to be 172 00:09:22.293 --> 00:09:25.710 some SQL code to save this cursor.execute 173 00:09:28.439 --> 00:09:32.606 INSERT INTO accounts VALUES watching the extra spaces 174 00:09:34.389 --> 00:09:36.806 then two question marks there 175 00:09:40.811 --> 00:09:44.978 and then we want (name, opening_balance) 176 00:09:47.577 --> 00:09:52.234 then we'll do a cursor.connection.commit() 177 00:09:52.234 --> 00:09:54.317 to immediately save that. 178 00:09:56.699 --> 00:09:58.812 We're gonna leave that message there, 179 00:09:58.812 --> 00:10:01.818 print("Account created for that's gonna be exactly the same 180 00:10:01.818 --> 00:10:04.381 as what it was before so we're going to leave 181 00:10:04.381 --> 00:10:06.154 the self.show_balance on the next line 182 00:10:06.154 --> 00:10:09.044 so that's gonna be executed whether we receive the data 183 00:10:09.044 --> 00:10:11.629 from the table, if it was already in existence. 184 00:10:11.629 --> 00:10:13.838 That would be if the row was found here 185 00:10:13.838 --> 00:10:17.518 on line 15 or if didn't exist when we're creating it 186 00:10:17.518 --> 00:10:19.425 as per new here, either way we're gonna show 187 00:10:19.425 --> 00:10:21.363 the balance once we're done. 188 00:10:21.363 --> 00:10:23.715 Basically, again what we're doing here is running 189 00:10:23.715 --> 00:10:26.353 a simple SELECT query to retrieve the row 190 00:10:26.353 --> 00:10:28.690 where the name matches the one passed in 191 00:10:28.690 --> 00:10:30.316 when creating the account instance. 192 00:10:30.316 --> 00:10:33.463 So using this name here you actually do a query 193 00:10:33.463 --> 00:10:35.167 and we're using that there as you can see 194 00:10:35.167 --> 00:10:38.145 they're setting it up to replace the argument there 195 00:10:38.145 --> 00:10:41.885 with the name that's been passed to the init method. 196 00:10:41.885 --> 00:10:44.396 So the cursors fetch one method will return either tuple 197 00:10:44.396 --> 00:10:47.216 containing the values from all the database columns 198 00:10:47.216 --> 00:10:51.027 otherwise it will return none if no record was found. 199 00:10:51.027 --> 00:10:54.746 You've seen conditions like this with this if row before. 200 00:10:54.746 --> 00:10:56.027 The condition will be true if row 201 00:10:56.027 --> 00:10:59.039 isn't either zero or an empty string, 202 00:10:59.039 --> 00:11:01.223 list, set, dictionary, or none. 203 00:11:01.223 --> 00:11:03.271 So I guess we could have written this instead 204 00:11:03.271 --> 00:11:07.438 as if row is not None: would be and alternative way 205 00:11:08.553 --> 00:11:11.068 to do that, in fact the effect would be the same 206 00:11:11.068 --> 00:11:14.988 and with that said you'll often see the shorthand way 207 00:11:14.988 --> 00:11:17.607 of writing it when you read other programmers code. 208 00:11:17.607 --> 00:11:21.274 In other words, either way is actually okay. 209 00:11:22.663 --> 00:11:26.089 Based on this query, if we get a record back 210 00:11:26.089 --> 00:11:28.409 from the database we unpack the tuple 211 00:11:28.409 --> 00:11:31.408 you can see on the next line here on line 16 212 00:11:31.408 --> 00:11:33.941 to set the values for the account name and balance. 213 00:11:33.941 --> 00:11:36.610 Now if a matching row isn't found in the database 214 00:11:36.610 --> 00:11:39.136 the else clause is triggered and down here 215 00:11:39.136 --> 00:11:43.703 we're actually saving the name and account balance 216 00:11:43.703 --> 00:11:45.738 then we insert a new row. 217 00:11:45.738 --> 00:11:48.672 Because we're doing an INSERT we have to commit the change 218 00:11:48.672 --> 00:11:52.469 by calling the commit method on the cursors connexion. 219 00:11:52.469 --> 00:11:54.503 I think it's time now to run this. 220 00:11:54.503 --> 00:11:56.615 So let's just run this and see 221 00:11:56.615 --> 00:11:57.773 whether it actually works or not. 222 00:11:57.773 --> 00:12:00.273 So let's go ahead and do that. 223 00:12:02.180 --> 00:12:04.251 We've actually got a error here. 224 00:12:04.251 --> 00:12:05.863 And the problem's actually up here 225 00:12:05.863 --> 00:12:08.112 you see that I've got a left parentheses 226 00:12:08.112 --> 00:12:10.727 in the SQL statement but I'm not ending 227 00:12:10.727 --> 00:12:13.273 the right parentheses and in fact here you can see 228 00:12:13.273 --> 00:12:14.624 that IntelliJ is helpfully telling us 229 00:12:14.624 --> 00:12:15.760 that there's a problem there. 230 00:12:15.760 --> 00:12:17.913 So I'm going to change that and put the right parentheses 231 00:12:17.913 --> 00:12:21.218 to fix that and just check the next one 232 00:12:21.218 --> 00:12:23.748 I think I've got the right parentheses there OK. 233 00:12:23.748 --> 00:12:27.915 So that's looks ok let' just try running it again. 234 00:12:29.073 --> 00:12:32.119 Alright so this time we can now see the normal text here 235 00:12:32.119 --> 00:12:34.930 that the account got created for John. 236 00:12:34.930 --> 00:12:37.120 Balance on account John is zero. 237 00:12:37.120 --> 00:12:38.267 And we've got these deposits 238 00:12:38.267 --> 00:12:40.531 that are going through and withdrawals. 239 00:12:40.531 --> 00:12:43.105 At this stage, we haven't actually updated 240 00:12:43.105 --> 00:12:46.543 the balance value in the database row for John's account. 241 00:12:46.543 --> 00:12:47.507 So that's something that we're 242 00:12:47.507 --> 00:12:50.492 going to need to do shortly in those methods. 243 00:12:50.492 --> 00:12:52.464 Firstly though I want to create a few more accounts 244 00:12:52.464 --> 00:12:54.759 and then close the database when we're done. 245 00:12:54.759 --> 00:12:58.139 So let's just go ahead and do that down here. 246 00:12:58.139 --> 00:13:00.295 We'll just add a couple more, 247 00:13:00.295 --> 00:13:04.128 terry = Account("Terry") 248 00:13:05.993 --> 00:13:10.160 and graham = Account("Graham") 249 00:13:12.693 --> 00:13:16.860 and we'll also specify an initial balance there, 9000. 250 00:13:18.499 --> 00:13:22.666 And eric = Account("Eric") and also I pass 251 00:13:27.336 --> 00:13:31.503 a value there 7,000 and we'll do a db.close() 252 00:13:34.382 --> 00:13:35.882 So let's run this. 253 00:13:38.796 --> 00:13:42.053 This time we can see that it's retrieved the record for John 254 00:13:42.053 --> 00:13:43.523 balance on account John is zero 255 00:13:43.523 --> 00:13:47.690 so we can see that our row got retrieved successfully. 256 00:13:50.964 --> 00:13:53.380 It's actually retrieved the data, even though 257 00:13:53.380 --> 00:13:55.448 we're not yet updating the balance with the amount 258 00:13:55.448 --> 00:13:57.447 you can see that it has retrieved it successfully 259 00:13:57.447 --> 00:13:59.831 so that's off to a good start. 260 00:13:59.831 --> 00:14:01.530 But you can also see here looking down 261 00:14:01.530 --> 00:14:02.815 that the account has been created 262 00:14:02.815 --> 00:14:05.119 for Terry, Graham and Eric as well 263 00:14:05.119 --> 00:14:08.125 you can see there's a balance there which is correct 264 00:14:08.125 --> 00:14:12.217 because in Terry's case we didn't pass an opening balance 265 00:14:12.217 --> 00:14:15.138 so it defaulted to zero, but in the case of Graham and Eric 266 00:14:15.138 --> 00:14:16.704 we've actually got 90 and 70, 267 00:14:16.704 --> 00:14:19.362 remembering that we're storing these as integers 268 00:14:19.362 --> 00:14:22.764 so it's divided by a hundred to get the actual value. 269 00:14:22.764 --> 00:14:24.364 Alright so I'm going to stop the video here. 270 00:14:24.364 --> 00:14:25.891 In the next one, we're going to look at a way 271 00:14:25.891 --> 00:14:28.715 to view these records from within IntelliJ 272 00:14:28.715 --> 00:14:32.093 and we'll also take care of these warnings that I showed you 273 00:14:32.093 --> 00:14:34.477 that IntelliJ's is giving us. 274 00:14:34.477 --> 00:14:36.894 So see you in the next video.