WEBVTT 1 00:00:01.392 --> 00:00:02.424 line:15% In the last few videos, we've seen 2 00:00:02.424 --> 00:00:04.082 line:15% how to store time information in 3 00:00:04.082 --> 00:00:05.749 our SQLite database. 4 00:00:06.736 --> 00:00:08.170 Now we've stored the times in UTC, 5 00:00:08.170 --> 00:00:10.675 and so there are a couple of ways to display 6 00:00:10.675 --> 00:00:13.923 those times in a user's local timezone. 7 00:00:13.923 --> 00:00:17.200 Now, often, that will be all you need to do. 8 00:00:17.200 --> 00:00:18.682 You may need to recover the original 9 00:00:18.682 --> 00:00:20.475 date and time that was entered for 10 00:00:20.475 --> 00:00:22.507 some applications, so I've included 11 00:00:22.507 --> 00:00:25.350 this challenge to show you a way to do that 12 00:00:25.350 --> 00:00:27.400 without using another external library. 13 00:00:27.400 --> 00:00:29.167 Now there's another reason that I've 14 00:00:29.167 --> 00:00:31.346 included this, but I can't actually 15 00:00:31.346 --> 00:00:34.687 tell you what it is just yet without giving too much away. 16 00:00:34.687 --> 00:00:38.687 All right, so let's swing over to the challenge. 17 00:00:42.426 --> 00:00:43.746 All right, so the challenge is to 18 00:00:43.746 --> 00:00:46.345 store time information in the database 19 00:00:46.345 --> 00:00:50.142 so that the original time can be reconstructed. 20 00:00:50.142 --> 00:00:52.186 Now, whatever else you decide to store, 21 00:00:52.186 --> 00:00:54.672 you must continue to store the UTC 22 00:00:54.672 --> 00:00:56.794 time, that's the only reliable 23 00:00:56.794 --> 00:00:58.999 way to store time so that they're 24 00:00:58.999 --> 00:01:02.183 all correct relative to each other. 25 00:01:02.183 --> 00:01:03.754 So you wanna go ahead and modify the 26 00:01:03.754 --> 00:01:05.986 rollback programme so that it includes 27 00:01:05.986 --> 00:01:08.653 an extra column in the database. 28 00:01:11.735 --> 00:01:13.138 Now the extra column should be used 29 00:01:13.138 --> 00:01:15.407 to store either timezone information 30 00:01:15.407 --> 00:01:18.157 or the original times offset from UTC or the tzinfo. 31 00:01:20.530 --> 00:01:22.658 It's really up to you what you store. 32 00:01:22.658 --> 00:01:24.072 So modify the code to store the correct 33 00:01:24.072 --> 00:01:26.710 information, then modify checkdb to 34 00:01:26.710 --> 00:01:28.775 retrieve the original time and 35 00:01:28.775 --> 00:01:32.474 display it along with the UTC time. 36 00:01:32.474 --> 00:01:34.385 Now remember to close any open tables 37 00:01:34.385 --> 00:01:36.303 and delete the database in the 38 00:01:36.303 --> 00:01:38.250 project pane before running your 39 00:01:38.250 --> 00:01:41.250 modified programme for the first time. 40 00:01:43.842 --> 00:01:45.194 And a few hints here. 41 00:01:45.194 --> 00:01:47.298 Firstly, if you're considering storing 42 00:01:47.298 --> 00:01:51.417 a timezone name in the database, stop and reconsider. 43 00:01:51.417 --> 00:01:54.562 You can waste a lot of time finding out that it won't work. 44 00:01:54.562 --> 00:01:56.728 Now, you may also, as the second hint, 45 00:01:56.728 --> 00:01:58.324 want to review the videos on pickle 46 00:01:58.324 --> 00:02:01.075 in section ten, although it is possible 47 00:02:01.075 --> 00:02:03.987 to solve this challenge without actually using pickle. 48 00:02:03.987 --> 00:02:05.899 Now we're gonna need rollback.py and 49 00:02:05.899 --> 00:02:08.460 checkdb.py a little bit later, so make 50 00:02:08.460 --> 00:02:10.554 sure you copy them into new files 51 00:02:10.554 --> 00:02:12.244 before making any changes here, 52 00:02:12.244 --> 00:02:15.012 or just to ensure that you've actually got a backup. 53 00:02:15.012 --> 00:02:16.434 So that's the challenge. 54 00:02:16.434 --> 00:02:18.546 Pause the video, see how you go, and 55 00:02:18.546 --> 00:02:20.200 I'll see you when you get back. 56 00:02:20.200 --> 00:02:22.117 So pause the video now. 57 00:02:23.747 --> 00:02:25.548 All right, so how did you get on? 58 00:02:25.548 --> 00:02:26.836 Hopefully, you managed to solve it. 59 00:02:26.836 --> 00:02:28.506 So I'm gonna start out by saying 60 00:02:28.506 --> 00:02:31.564 there's at least three approaches to this problem. 61 00:02:31.564 --> 00:02:33.580 Now the simplest is probably just to 62 00:02:33.580 --> 00:02:36.260 store the local time as a String, just 63 00:02:36.260 --> 00:02:39.116 as we did when we stored them a few videos ago. 64 00:02:39.116 --> 00:02:40.580 So if you've done it that way, 65 00:02:40.580 --> 00:02:43.834 and stored local time in addition to UTC, 66 00:02:43.834 --> 00:02:46.036 then well done for solving the challenge. 67 00:02:46.036 --> 00:02:47.451 Now I'm not gonna use that approach 68 00:02:47.451 --> 00:02:49.140 because we've seen the code to do that 69 00:02:49.140 --> 00:02:52.112 already, so I wouldn't be showing you anything new. 70 00:02:52.112 --> 00:02:53.396 Another approach is to store the 71 00:02:53.396 --> 00:02:56.188 time offset in another column. 72 00:02:56.188 --> 00:02:59.018 So you can use an awaretimes UTC offset 73 00:02:59.018 --> 00:03:01.260 method to get the offset and even 74 00:03:01.260 --> 00:03:03.552 store that as a date-time value, 75 00:03:03.552 --> 00:03:06.316 or call the total_seconds method 76 00:03:06.316 --> 00:03:09.340 to get a float value of the number of seconds. 77 00:03:09.340 --> 00:03:11.172 And if you've done that, then add 78 00:03:11.172 --> 00:03:12.738 the offset back to work out the 79 00:03:12.738 --> 00:03:14.779 original time, that's also a valid 80 00:03:14.779 --> 00:03:17.418 solution to the challenge, well done if you've done that. 81 00:03:17.418 --> 00:03:18.826 But I'm also not gonna do it that 82 00:03:18.826 --> 00:03:20.764 way either, because that approach 83 00:03:20.764 --> 00:03:22.196 is very similar to what I am going to 84 00:03:22.196 --> 00:03:25.966 do, which is something perhaps you may not have thought of. 85 00:03:25.966 --> 00:03:27.598 So the approach I'm gonna take you is 86 00:03:27.598 --> 00:03:30.886 to store the original time's tzinfo. 87 00:03:30.886 --> 00:03:32.446 Now, an awaretime value contains 88 00:03:32.446 --> 00:03:36.469 timezone information in its tzinfo object. 89 00:03:36.469 --> 00:03:39.822 That's what makes it aware, rather than naive. 90 00:03:39.822 --> 00:03:41.302 Now, we couldn't just go writing 91 00:03:41.302 --> 00:03:42.862 class instances into a database 92 00:03:42.862 --> 00:03:44.964 column, because that doesn't work. 93 00:03:44.964 --> 00:03:47.835 But what we can do is pickle a class 94 00:03:47.835 --> 00:03:50.798 instance, then this converts the instance 95 00:03:50.798 --> 00:03:55.070 into a byte stream that can be stored in a database column. 96 00:03:55.070 --> 00:03:57.230 So think of it as similar to serialising a class 97 00:03:57.230 --> 00:04:00.838 instance in Java, if you're familiar with that language. 98 00:04:00.838 --> 00:04:03.094 Now, as I mentioned in the description 99 00:04:03.094 --> 00:04:05.414 for the challenge, we looked at the 100 00:04:05.414 --> 00:04:07.589 pickle module back in section ten, 101 00:04:07.589 --> 00:04:09.117 so consequently, we're gonna use 102 00:04:09.117 --> 00:04:10.926 the pickle dumps() function to 103 00:04:10.926 --> 00:04:13.950 convert the tzinfo into a byte stream 104 00:04:13.950 --> 00:04:16.269 that loads to convert it back again 105 00:04:16.269 --> 00:04:18.588 after reading it from the database. 106 00:04:18.588 --> 00:04:19.942 Now whichever approach we take, we need 107 00:04:19.942 --> 00:04:22.302 to add a new column to the database, 108 00:04:22.302 --> 00:04:24.092 so let's start with that. 109 00:04:24.092 --> 00:04:27.470 Now, as I also mentioned in the challenge 110 00:04:27.470 --> 00:04:29.389 description, we need these original 111 00:04:29.389 --> 00:04:32.686 files later, so I'm gonna take a copy of this. 112 00:04:32.686 --> 00:04:35.462 That's firstly starting with rollback.py. 113 00:04:35.462 --> 00:04:36.739 Click on that. 114 00:04:36.739 --> 00:04:39.072 Right click and select Copy. 115 00:04:40.702 --> 00:04:42.853 And then I'm just going to do a Paste. 116 00:04:42.853 --> 00:04:47.020 And instead of rollback.py, let's call this one tztest. 117 00:04:48.870 --> 00:04:50.326 And therefore we've still got 118 00:04:50.326 --> 00:04:52.134 the original file if we need it. 119 00:04:52.134 --> 00:04:53.830 All right, so our pickled object is 120 00:04:53.830 --> 00:04:58.566 a ByteStream, so I'll need to use an integer column for it. 121 00:04:58.566 --> 00:05:00.134 Now, it doesn't really matter as we've 122 00:05:00.134 --> 00:05:01.870 seen, but choosing the most appropriate 123 00:05:01.870 --> 00:05:04.389 type documents your intent, even 124 00:05:04.389 --> 00:05:07.101 if SQLite really doesn't care. 125 00:05:07.101 --> 00:05:09.316 So, seeing as we're at the top of 126 00:05:09.316 --> 00:05:11.356 this file at the moment, I'm going 127 00:05:11.356 --> 00:05:13.510 to add the pickle import first. 128 00:05:13.510 --> 00:05:14.677 Import pickle. 129 00:05:17.549 --> 00:05:18.766 Then what we wanna do here is change 130 00:05:18.766 --> 00:05:22.550 the code for our history table, because 131 00:05:22.550 --> 00:05:24.334 that's the one we're gonna be changing. 132 00:05:24.334 --> 00:05:28.780 So we've got amount text NOT NULL integer NOT NULL. 133 00:05:28.780 --> 00:05:29.838 And what we're gonna do is we're gonna 134 00:05:29.838 --> 00:05:31.869 add a new line there, and what we'll 135 00:05:31.869 --> 00:05:33.998 do just to be consistent here is we're 136 00:05:33.998 --> 00:05:36.102 gonna space at the start of this line. 137 00:05:36.102 --> 00:05:38.414 And we're gonna add the zone column. 138 00:05:38.414 --> 00:05:40.805 Timezone, so we're gonna call it zone 139 00:05:40.805 --> 00:05:43.388 INTEGER NOT NULL and then put a 140 00:05:47.114 --> 00:05:49.864 comma there, and leave the rest the line. 141 00:05:49.864 --> 00:05:51.610 The PRIMARY KEY TIME, ACCOUNT 142 00:05:51.610 --> 00:05:53.981 because we don't need to change that. 143 00:05:53.981 --> 00:05:55.005 All right, so we've defined the new 144 00:05:55.005 --> 00:05:59.242 column for the database, for our history table. 145 00:05:59.242 --> 00:06:03.317 Next, we need a tzinfo object for the local timezone. 146 00:06:03.317 --> 00:06:05.773 Now, we can get that by converting our UTC 147 00:06:05.773 --> 00:06:08.381 to local time, so consequently, 148 00:06:08.381 --> 00:06:12.403 we probably wanna modify the current_time method. 149 00:06:12.403 --> 00:06:16.733 So first, if we're just gonna comment out this return. 150 00:06:16.733 --> 00:06:17.917 Comment all that out, and the code 151 00:06:17.917 --> 00:06:19.893 we're gonna put here is gonna be 152 00:06:19.893 --> 00:06:24.060 UTCTime is equal to pytz.UTC.localize(). 153 00:06:30.861 --> 00:06:35.028 That's gonna be datetime.datetime.utcNow(). 154 00:06:36.787 --> 00:06:39.682 Then we want our local time, so local_time 155 00:06:39.682 --> 00:06:43.849 is equal to UTC_time.stimezone(). 156 00:06:48.004 --> 00:06:52.171 And then we want zone to be equal to local_time.tzinfo. 157 00:06:56.309 --> 00:07:00.476 And then we actually wanna return UTC_time comma space zone. 158 00:07:02.429 --> 00:07:04.245 So you can see that what we're now 159 00:07:04.245 --> 00:07:07.373 doing is returning the UTC time and the zone as a table 160 00:07:07.373 --> 00:07:10.153 on line 27, and the ability to 161 00:07:10.153 --> 00:07:11.972 use tables to return several values 162 00:07:11.972 --> 00:07:14.971 at once is a pretty neat feature in python. 163 00:07:14.971 --> 00:07:16.437 All right, so the final change we need 164 00:07:16.437 --> 00:07:19.437 to make is here in this save_update, 165 00:07:20.293 --> 00:07:23.460 and what we need to do is unpack 166 00:07:23.460 --> 00:07:26.229 the two values returned from the current_time 167 00:07:26.229 --> 00:07:28.701 method, and then pickle its own. 168 00:07:28.701 --> 00:07:30.718 So to do that, I'm gonna change this line 169 00:07:30.718 --> 00:07:33.735 a little bit so deposit_time comma space 170 00:07:33.735 --> 00:07:37.235 zone is equal to accounts._currentTime and 171 00:07:39.071 --> 00:07:41.005 basically what that's doing there, 172 00:07:41.005 --> 00:07:45.172 just so we're clear, we're now unpacking the returned table. 173 00:07:49.463 --> 00:07:51.359 And then we need to pickle the zone. 174 00:07:51.359 --> 00:07:54.615 We do that by typing pickle_zone 175 00:07:54.615 --> 00:07:58.615 is equal to pickle.dumps(zone). 176 00:08:02.015 --> 00:08:04.700 And then we need to change our insert, 177 00:08:04.700 --> 00:08:06.735 so we've got insert into history, 178 00:08:06.735 --> 00:08:08.861 we've got three columns there. 179 00:08:08.861 --> 00:08:11.014 We need to add the fourth for the zone, a comma 180 00:08:11.014 --> 00:08:12.814 and another question mark, and 181 00:08:12.814 --> 00:08:14.356 then all we need to do that is add 182 00:08:14.356 --> 00:08:18.023 that after amount, comma space pickled_zone. 183 00:08:20.261 --> 00:08:21.271 So I've now updated their history 184 00:08:21.271 --> 00:08:23.583 table and added the fourth column. 185 00:08:23.583 --> 00:08:25.077 And that's actually it. 186 00:08:25.077 --> 00:08:25.989 We're now storing the original 187 00:08:25.989 --> 00:08:28.727 timezone information in the database. 188 00:08:28.727 --> 00:08:30.551 So before running the programme, what 189 00:08:30.551 --> 00:08:34.190 we need to do is close any open database table tabs. 190 00:08:34.190 --> 00:08:36.213 Which we've got at least one open 191 00:08:36.213 --> 00:08:38.623 here, which is the history one, so I'll close that. 192 00:08:38.623 --> 00:08:40.935 Then we also need to delete the 193 00:08:40.935 --> 00:08:42.838 database, and we can actually do that 194 00:08:42.838 --> 00:08:44.599 over here in the project pane, so let's 195 00:08:44.599 --> 00:08:46.399 come over here to accounts.SQLite. 196 00:08:46.399 --> 00:08:48.967 Right click and select delete, and that's 197 00:08:48.967 --> 00:08:50.917 because we're recreating the structure again. 198 00:08:50.917 --> 00:08:52.452 So delete that entirely. 199 00:08:52.452 --> 00:08:56.519 Click on Do Refactor, and that's now deleted. 200 00:08:56.519 --> 00:08:58.055 So if you run the programme again now, 201 00:08:58.055 --> 00:09:01.222 or at least run it for the first time, 202 00:09:02.063 --> 00:09:03.983 we've got our database back again. 203 00:09:03.983 --> 00:09:05.731 If we click on Databases, we've got our 204 00:09:05.731 --> 00:09:07.799 accounts table here, and if you're getting 205 00:09:07.799 --> 00:09:10.327 something weird happening there, you 206 00:09:10.327 --> 00:09:12.348 may have to refresh the table, even 207 00:09:12.348 --> 00:09:14.223 though we've deleted it, and that would 208 00:09:14.223 --> 00:09:16.414 be if IntelliJ was giving you some warnings. 209 00:09:16.414 --> 00:09:17.799 Which wasn't happening here, but if you 210 00:09:17.799 --> 00:09:19.199 are getting that problem, just click on 211 00:09:19.199 --> 00:09:21.599 Refresh and that should fix the problem. 212 00:09:21.599 --> 00:09:23.285 But now if we look at our history table, 213 00:09:23.285 --> 00:09:25.581 we can see we've got the extra column there 214 00:09:25.581 --> 00:09:27.679 zone, and if we open this up, the 215 00:09:27.679 --> 00:09:29.424 history table, you'll notice that the 216 00:09:29.424 --> 00:09:33.063 zone column does look a little bit funny. 217 00:09:33.063 --> 00:09:34.383 And the reason for that, it's not really 218 00:09:34.383 --> 00:09:36.423 intended to be read by humans. 219 00:09:36.423 --> 00:09:37.919 Now that's one reason why we didn't pick the 220 00:09:37.919 --> 00:09:39.975 accounts object and store each 221 00:09:39.975 --> 00:09:42.311 one in a single database column. 222 00:09:42.311 --> 00:09:44.647 You can't really query and filter pickled 223 00:09:44.647 --> 00:09:46.765 values, so they're not really a suitable 224 00:09:46.765 --> 00:09:47.959 replacement for storing the 225 00:09:47.959 --> 00:09:50.959 individual attributes of your class. 226 00:09:52.644 --> 00:09:54.837 All right, so let's have a look at checkdb. 227 00:09:54.837 --> 00:09:56.215 And what we're gonna do here, and I'll just 228 00:09:56.215 --> 00:09:58.775 close this database over to the right here. 229 00:09:58.775 --> 00:10:00.767 We're gonna take a copy of this as well, 230 00:10:00.767 --> 00:10:02.742 so we've still got the original to work with. 231 00:10:02.742 --> 00:10:06.052 So let's right click that, select Copy, 232 00:10:06.052 --> 00:10:10.052 and then Paste, and we'll call this one tzcheck. 233 00:10:13.967 --> 00:10:15.340 And what we're gonna do now is 234 00:10:15.340 --> 00:10:19.090 change this code to read the data back again. 235 00:10:20.063 --> 00:10:22.757 So firstly, we need to add a couple imports. 236 00:10:22.757 --> 00:10:26.840 So import pytz, then we also wanna import pickle. 237 00:10:30.415 --> 00:10:32.927 Next, we need to look at our tables, 238 00:10:32.927 --> 00:10:34.831 and now we're not gonna be looking at the view. 239 00:10:34.831 --> 00:10:36.439 We want to actually look at the table again. 240 00:10:36.439 --> 00:10:37.789 So I'm gonna delete local history 241 00:10:37.789 --> 00:10:39.949 and make that history, and we'll delete 242 00:10:39.949 --> 00:10:41.991 this print now because we're gonna be changing that. 243 00:10:41.991 --> 00:10:46.158 So I'm gonna start by typing UTC_time is equal to row zero. 244 00:10:47.703 --> 00:10:50.412 Zero in left and right brackets. 245 00:10:50.412 --> 00:10:53.662 And pickled_zone is equal to row three. 246 00:10:55.950 --> 00:10:59.950 And zone equals pickle.loads(). 247 00:11:02.543 --> 00:11:05.087 That's gonna be pickled_zone. 248 00:11:05.087 --> 00:11:08.837 Local_time is equal to pytz.UTC.localize, and 249 00:11:12.151 --> 00:11:16.318 it's gonna be UTCTime.stimezone, and passing zone to that. 250 00:11:21.316 --> 00:11:22.455 And then we wanna print some of this 251 00:11:22.455 --> 00:11:23.711 stuff out, so I'm gonna do print 252 00:11:23.711 --> 00:11:27.692 replacement fields, curly braces, slash T. 253 00:11:27.692 --> 00:11:28.589 And now instead of replacement 254 00:11:28.589 --> 00:11:30.647 fields, two curly braces again. 255 00:11:30.647 --> 00:11:33.039 And another one, slash T, and curly 256 00:11:33.039 --> 00:11:36.357 braces again, and the output's gonna 257 00:11:36.357 --> 00:11:40.524 be .format UTC_time, local_time, and then local_time.tzinfo. 258 00:11:47.759 --> 00:11:50.581 And that should be that. 259 00:11:50.581 --> 00:11:53.293 I meant it to be a right parentheses there. 260 00:11:53.293 --> 00:11:54.863 Okay. 261 00:11:54.863 --> 00:11:56.335 So I firstly, I added the two imports 262 00:11:56.335 --> 00:11:59.604 for pytz and pickle on line 213. 263 00:11:59.604 --> 00:12:01.167 And we also changed the query, as you 264 00:12:01.167 --> 00:12:02.503 saw on line 8, so that we're not 265 00:12:02.503 --> 00:12:04.551 using the view anymore, but we're 266 00:12:04.551 --> 00:12:07.980 actually using the table that we've updated. 267 00:12:07.980 --> 00:12:10.292 Next step was to get our UTCDateTime value, 268 00:12:10.292 --> 00:12:12.557 and that's the first row of the table, 269 00:12:12.557 --> 00:12:13.535 and we also want the zone 270 00:12:13.535 --> 00:12:16.271 information, which was the fourth item. 271 00:12:16.271 --> 00:12:17.892 Now in practise, you'd probably wanna unpack 272 00:12:17.892 --> 00:12:20.093 all four columns into variables, but 273 00:12:20.093 --> 00:12:21.751 we're not really interested in the 274 00:12:21.751 --> 00:12:23.479 account and amount columns at the moment, 275 00:12:23.479 --> 00:12:25.143 so we've actually ignored those. 276 00:12:25.143 --> 00:12:29.476 And we then, on line 13, I'm pickling the zone 277 00:12:29.476 --> 00:12:32.517 data using the pickle module's loads() function. 278 00:12:32.517 --> 00:12:35.263 And then we used the tzinfo zone object 279 00:12:35.263 --> 00:12:38.184 when we called stimezone on line 14 to 280 00:12:38.184 --> 00:12:40.198 revert the UTC time back to the 281 00:12:40.198 --> 00:12:42.453 time in its original timezone. 282 00:12:42.453 --> 00:12:44.370 So let's just run that. 283 00:12:47.366 --> 00:12:48.422 I can see here that I had printed 284 00:12:48.422 --> 00:12:50.493 the timezone after UTC time and local 285 00:12:50.493 --> 00:12:52.447 time, just as a check that we're 286 00:12:52.447 --> 00:12:55.134 getting the original tzinfo object back. 287 00:12:55.134 --> 00:12:56.965 And you can see that when I've run that, 288 00:12:56.965 --> 00:12:58.748 you can see the original time, which 289 00:12:58.748 --> 00:13:01.140 was with an offset of 10 hours 30, 290 00:13:01.140 --> 00:13:04.087 10 column 30, from the ACDT timezone, 291 00:13:04.087 --> 00:13:06.428 which is the Australian Central Daylight Time, 292 00:13:06.428 --> 00:13:08.334 which, of course, is my timezone. 293 00:13:08.334 --> 00:13:10.127 All right, so that's the challenge completed. 294 00:13:10.127 --> 00:13:12.839 Now, I mentioned that one of the possible 295 00:13:12.839 --> 00:13:15.239 solutions was storing timezone 296 00:13:15.239 --> 00:13:17.213 names and why it's a waste of time. 297 00:13:17.213 --> 00:13:19.037 line:15% Let's actually talk about that more in the 298 00:13:19.037 --> 00:13:20.967 line:15% next video and see why that's not 299 00:13:20.967 --> 00:13:23.277 line:15% a good idea to actually work with. 300 00:13:23.277 --> 00:13:25.849 line:15% So I'll see you in that next video.