1 00:00:04,460 --> 00:00:05,490 In the last few videos, we've seen 2 00:00:05,490 --> 00:00:07,150 how to store time information in 3 00:00:07,150 --> 00:00:08,810 our SQLite database. 4 00:00:09,800 --> 00:00:11,240 Now we've stored the times in UTC, 5 00:00:11,240 --> 00:00:13,740 and so there are a couple of ways to display 6 00:00:13,740 --> 00:00:16,990 those times in a user's local timezone. 7 00:00:16,990 --> 00:00:20,270 Now, often, that will be all you need to do. 8 00:00:20,270 --> 00:00:21,750 You may need to recover the original 9 00:00:21,750 --> 00:00:23,540 date and time that was entered for 10 00:00:23,540 --> 00:00:25,570 some applications, so I've included 11 00:00:25,570 --> 00:00:28,420 this challenge to show you a way to do that 12 00:00:28,420 --> 00:00:30,470 without using another external library. 13 00:00:30,470 --> 00:00:32,229 Now there's another reason that I've 14 00:00:32,229 --> 00:00:34,410 included this, but I can't actually 15 00:00:34,410 --> 00:00:37,750 tell you what it is just yet without giving too much away. 16 00:00:37,750 --> 00:00:41,750 All right, so let's swing over to the challenge. 17 00:00:45,490 --> 00:00:46,810 All right, so the challenge is to 18 00:00:46,810 --> 00:00:49,410 store time information in the database 19 00:00:49,410 --> 00:00:53,210 so that the original time can be reconstructed. 20 00:00:53,210 --> 00:00:55,250 Now, whatever else you decide to store, 21 00:00:55,250 --> 00:00:57,740 you must continue to store the UTC 22 00:00:57,740 --> 00:00:59,860 time, that's the only reliable 23 00:00:59,860 --> 00:01:02,060 way to store time so that they're 24 00:01:02,060 --> 00:01:05,250 all correct relative to each other. 25 00:01:05,250 --> 00:01:06,820 So you wanna go ahead and modify the 26 00:01:06,820 --> 00:01:09,050 rollback programme so that it includes 27 00:01:09,050 --> 00:01:11,720 an extra column in the database. 28 00:01:14,800 --> 00:01:16,200 Now the extra column should be used 29 00:01:16,200 --> 00:01:18,470 to store either timezone information 30 00:01:18,470 --> 00:01:21,220 or the original times offset from UTC or the tzinfo. 31 00:01:23,600 --> 00:01:25,720 It's really up to you what you store. 32 00:01:25,720 --> 00:01:27,140 So modify the code to store the correct 33 00:01:27,140 --> 00:01:29,780 information, then modify checkdb to 34 00:01:29,780 --> 00:01:31,840 retrieve the original time and 35 00:01:31,840 --> 00:01:35,540 display it along with the UTC time. 36 00:01:35,540 --> 00:01:37,450 Now remember to close any open tables 37 00:01:37,450 --> 00:01:39,370 and delete the database in the 38 00:01:39,370 --> 00:01:41,320 project pane before running your 39 00:01:41,320 --> 00:01:44,320 modified programme for the first time. 40 00:01:46,910 --> 00:01:48,260 And a few hints here. 41 00:01:48,260 --> 00:01:50,360 Firstly, if you're considering storing 42 00:01:50,360 --> 00:01:54,480 a timezone name in the database, stop and reconsider. 43 00:01:54,480 --> 00:01:57,630 You can waste a lot of time finding out that it won't work. 44 00:01:57,630 --> 00:01:59,790 Now, you may also, as the second hint, 45 00:01:59,790 --> 00:02:01,390 want to review the videos on pickle 46 00:02:01,390 --> 00:02:04,140 in section ten, although it is possible 47 00:02:04,140 --> 00:02:07,050 to solve this challenge without actually using pickle. 48 00:02:07,050 --> 00:02:08,960 Now we're gonna need rollback.py and 49 00:02:08,960 --> 00:02:11,530 checkdb.py a little bit later, so make 50 00:02:11,530 --> 00:02:13,620 sure you copy them into new files 51 00:02:13,620 --> 00:02:15,310 before making any changes here, 52 00:02:15,310 --> 00:02:18,080 or just to ensure that you've actually got a backup. 53 00:02:18,080 --> 00:02:19,500 So that's the challenge. 54 00:02:19,500 --> 00:02:21,610 Pause the video, see how you go, and 55 00:02:21,610 --> 00:02:23,270 I'll see you when you get back. 56 00:02:23,270 --> 00:02:25,180 So pause the video now. 57 00:02:26,810 --> 00:02:28,610 All right, so how did you get on? 58 00:02:28,610 --> 00:02:29,900 Hopefully, you managed to solve it. 59 00:02:29,900 --> 00:02:31,570 So I'm gonna start out by saying 60 00:02:31,570 --> 00:02:34,630 there's at least three approaches to this problem. 61 00:02:34,630 --> 00:02:36,650 Now the simplest is probably just to 62 00:02:36,650 --> 00:02:39,330 store the local time as a String, just 63 00:02:39,330 --> 00:02:42,180 as we did when we stored them a few videos ago. 64 00:02:42,180 --> 00:02:43,650 So if you've done it that way, 65 00:02:43,650 --> 00:02:46,900 and stored local time in addition to UTC, 66 00:02:46,900 --> 00:02:49,100 then well done for solving the challenge. 67 00:02:49,100 --> 00:02:50,520 Now I'm not gonna use that approach 68 00:02:50,520 --> 00:02:52,210 because we've seen the code to do that 69 00:02:52,210 --> 00:02:55,180 already, so I wouldn't be showing you anything new. 70 00:02:55,180 --> 00:02:56,460 Another approach is to store the 71 00:02:56,460 --> 00:02:59,250 time offset in another column. 72 00:02:59,250 --> 00:03:02,080 So you can use an awaretimes UTC offset 73 00:03:02,080 --> 00:03:04,330 method to get the offset and even 74 00:03:04,330 --> 00:03:06,620 store that as a date-time value, 75 00:03:06,620 --> 00:03:09,380 or call the total_seconds method 76 00:03:09,380 --> 00:03:12,410 to get a float value of the number of seconds. 77 00:03:12,410 --> 00:03:14,240 And if you've done that, then add 78 00:03:14,240 --> 00:03:15,800 the offset back to work out the 79 00:03:15,800 --> 00:03:17,840 original time, that's also a valid 80 00:03:17,840 --> 00:03:20,480 solution to the challenge, well done if you've done that. 81 00:03:20,480 --> 00:03:21,890 But I'm also not gonna do it that 82 00:03:21,890 --> 00:03:23,830 way either, because that approach 83 00:03:23,830 --> 00:03:25,260 is very similar to what I am going to 84 00:03:25,260 --> 00:03:29,030 do, which is something perhaps you may not have thought of. 85 00:03:29,030 --> 00:03:30,660 So the approach I'm gonna take you is 86 00:03:30,660 --> 00:03:33,950 to store the original time's tzinfo. 87 00:03:33,950 --> 00:03:35,510 Now, an awaretime value contains 88 00:03:35,510 --> 00:03:39,530 timezone information in its tzinfo object. 89 00:03:39,530 --> 00:03:42,890 That's what makes it aware, rather than naive. 90 00:03:42,890 --> 00:03:44,370 Now, we couldn't just go writing 91 00:03:44,370 --> 00:03:45,930 class instances into a database 92 00:03:45,930 --> 00:03:48,030 column, because that doesn't work. 93 00:03:48,030 --> 00:03:50,900 But what we can do is pickle a class 94 00:03:50,900 --> 00:03:53,860 instance, then this converts the instance 95 00:03:53,860 --> 00:03:58,140 into a byte stream that can be stored in a database column. 96 00:03:58,140 --> 00:04:00,300 So think of it as similar to serialising a class 97 00:04:00,300 --> 00:04:03,900 instance in Java, if you're familiar with that language. 98 00:04:03,900 --> 00:04:06,160 Now, as I mentioned in the description 99 00:04:06,160 --> 00:04:08,480 for the challenge, we looked at the 100 00:04:08,480 --> 00:04:10,650 pickle module back in section ten, 101 00:04:10,650 --> 00:04:12,180 so consequently, we're gonna use 102 00:04:12,180 --> 00:04:13,990 the pickle dumps() function to 103 00:04:13,990 --> 00:04:17,019 convert the tzinfo into a byte stream 104 00:04:17,019 --> 00:04:19,329 that loads to convert it back again 105 00:04:19,329 --> 00:04:21,649 after reading it from the database. 106 00:04:21,649 --> 00:04:23,010 Now whichever approach we take, we need 107 00:04:23,010 --> 00:04:25,370 to add a new column to the database, 108 00:04:25,370 --> 00:04:27,160 so let's start with that. 109 00:04:27,160 --> 00:04:30,540 Now, as I also mentioned in the challenge 110 00:04:30,540 --> 00:04:32,450 description, we need these original 111 00:04:32,450 --> 00:04:35,750 files later, so I'm gonna take a copy of this. 112 00:04:35,750 --> 00:04:38,530 That's firstly starting with rollback.py. 113 00:04:38,530 --> 00:04:39,800 Click on that. 114 00:04:39,800 --> 00:04:42,140 Right click and select Copy. 115 00:04:43,770 --> 00:04:45,920 And then I'm just going to do a Paste. 116 00:04:45,920 --> 00:04:50,090 And instead of rollback.py, let's call this one tztest. 117 00:04:51,940 --> 00:04:53,390 And therefore we've still got 118 00:04:53,390 --> 00:04:55,200 the original file if we need it. 119 00:04:55,200 --> 00:04:56,900 All right, so our pickled object is 120 00:04:56,900 --> 00:05:01,630 a ByteStream, so I'll need to use an integer column for it. 121 00:05:01,630 --> 00:05:03,200 Now, it doesn't really matter as we've 122 00:05:03,200 --> 00:05:04,940 seen, but choosing the most appropriate 123 00:05:04,940 --> 00:05:07,450 type documents your intent, even 124 00:05:07,450 --> 00:05:10,170 if SQLite really doesn't care. 125 00:05:10,170 --> 00:05:12,380 So, seeing as we're at the top of 126 00:05:12,380 --> 00:05:14,420 this file at the moment, I'm going 127 00:05:14,420 --> 00:05:16,580 to add the pickle import first. 128 00:05:16,580 --> 00:05:17,740 Import pickle. 129 00:05:20,610 --> 00:05:21,830 Then what we wanna do here is change 130 00:05:21,830 --> 00:05:25,620 the code for our history table, because 131 00:05:25,620 --> 00:05:27,400 that's the one we're gonna be changing. 132 00:05:27,400 --> 00:05:31,850 So we've got amount text NOT NULL integer NOT NULL. 133 00:05:31,850 --> 00:05:32,900 And what we're gonna do is we're gonna 134 00:05:32,900 --> 00:05:34,930 add a new line there, and what we'll 135 00:05:34,930 --> 00:05:37,060 do just to be consistent here is we're 136 00:05:37,060 --> 00:05:39,170 gonna space at the start of this line. 137 00:05:39,170 --> 00:05:41,480 And we're gonna add the zone column. 138 00:05:41,480 --> 00:05:43,870 Timezone, so we're gonna call it zone 139 00:05:43,870 --> 00:05:46,450 INTEGER NOT NULL and then put a 140 00:05:50,180 --> 00:05:52,930 comma there, and leave the rest the line. 141 00:05:52,930 --> 00:05:54,680 The PRIMARY KEY TIME, ACCOUNT 142 00:05:54,680 --> 00:05:57,050 because we don't need to change that. 143 00:05:57,050 --> 00:05:58,070 All right, so we've defined the new 144 00:05:58,070 --> 00:06:02,310 column for the database, for our history table. 145 00:06:02,310 --> 00:06:06,380 Next, we need a tzinfo object for the local timezone. 146 00:06:06,380 --> 00:06:08,840 Now, we can get that by converting our UTC 147 00:06:08,840 --> 00:06:11,450 to local time, so consequently, 148 00:06:11,450 --> 00:06:15,470 we probably wanna modify the current_time method. 149 00:06:15,470 --> 00:06:19,800 So first, if we're just gonna comment out this return. 150 00:06:19,800 --> 00:06:20,980 Comment all that out, and the code 151 00:06:20,980 --> 00:06:22,960 we're gonna put here is gonna be 152 00:06:22,960 --> 00:06:27,130 UTCTime is equal to pytz.UTC.localize(). 153 00:06:33,930 --> 00:06:38,090 That's gonna be datetime.datetime.utcNow(). 154 00:06:39,850 --> 00:06:42,750 Then we want our local time, so local_time 155 00:06:42,750 --> 00:06:46,910 is equal to UTC_time.stimezone(). 156 00:06:51,070 --> 00:06:55,240 And then we want zone to be equal to local_time.tzinfo. 157 00:06:59,370 --> 00:07:03,540 And then we actually wanna return UTC_time comma space zone. 158 00:07:05,490 --> 00:07:07,310 So you can see that what we're now 159 00:07:07,310 --> 00:07:10,440 doing is returning the UTC time and the zone as a table 160 00:07:10,440 --> 00:07:13,220 on line 27, and the ability to 161 00:07:13,220 --> 00:07:15,040 use tables to return several values 162 00:07:15,040 --> 00:07:18,040 at once is a pretty neat feature in python. 163 00:07:18,040 --> 00:07:19,500 All right, so the final change we need 164 00:07:19,500 --> 00:07:22,500 to make is here in this save_update, 165 00:07:23,360 --> 00:07:26,530 and what we need to do is unpack 166 00:07:26,530 --> 00:07:29,290 the two values returned from the current_time 167 00:07:29,290 --> 00:07:31,770 method, and then pickle its own. 168 00:07:31,770 --> 00:07:33,780 So to do that, I'm gonna change this line 169 00:07:33,780 --> 00:07:36,800 a little bit so deposit_time comma space 170 00:07:36,800 --> 00:07:40,300 zone is equal to accounts._currentTime and 171 00:07:42,140 --> 00:07:44,070 basically what that's doing there, 172 00:07:44,070 --> 00:07:48,240 just so we're clear, we're now unpacking the returned table. 173 00:07:52,530 --> 00:07:54,420 And then we need to pickle the zone. 174 00:07:54,420 --> 00:07:57,680 We do that by typing pickle_zone 175 00:07:57,680 --> 00:08:01,680 is equal to pickle.dumps(zone). 176 00:08:05,080 --> 00:08:07,770 And then we need to change our insert, 177 00:08:07,770 --> 00:08:09,800 so we've got insert into history, 178 00:08:09,800 --> 00:08:11,930 we've got three columns there. 179 00:08:11,930 --> 00:08:14,080 We need to add the fourth for the zone, a comma 180 00:08:14,080 --> 00:08:15,880 and another question mark, and 181 00:08:15,880 --> 00:08:17,420 then all we need to do that is add 182 00:08:17,420 --> 00:08:21,090 that after amount, comma space pickled_zone. 183 00:08:23,330 --> 00:08:24,340 So I've now updated their history 184 00:08:24,340 --> 00:08:26,650 table and added the fourth column. 185 00:08:26,650 --> 00:08:28,140 And that's actually it. 186 00:08:28,140 --> 00:08:29,050 We're now storing the original 187 00:08:29,050 --> 00:08:31,790 timezone information in the database. 188 00:08:31,790 --> 00:08:33,620 So before running the programme, what 189 00:08:33,620 --> 00:08:37,260 we need to do is close any open database table tabs. 190 00:08:37,260 --> 00:08:39,280 Which we've got at least one open 191 00:08:39,280 --> 00:08:41,690 here, which is the history one, so I'll close that. 192 00:08:41,690 --> 00:08:44,000 Then we also need to delete the 193 00:08:44,000 --> 00:08:45,900 database, and we can actually do that 194 00:08:45,900 --> 00:08:47,660 over here in the project pane, so let's 195 00:08:47,660 --> 00:08:49,460 come over here to accounts.SQLite. 196 00:08:49,460 --> 00:08:52,030 Right click and select delete, and that's 197 00:08:52,030 --> 00:08:53,980 because we're recreating the structure again. 198 00:08:53,980 --> 00:08:55,520 So delete that entirely. 199 00:08:55,520 --> 00:08:59,580 Click on Do Refactor, and that's now deleted. 200 00:08:59,580 --> 00:09:01,120 So if you run the programme again now, 201 00:09:01,120 --> 00:09:04,290 or at least run it for the first time, 202 00:09:05,130 --> 00:09:07,050 we've got our database back again. 203 00:09:07,050 --> 00:09:08,800 If we click on Databases, we've got our 204 00:09:08,800 --> 00:09:10,860 accounts table here, and if you're getting 205 00:09:10,860 --> 00:09:13,390 something weird happening there, you 206 00:09:13,390 --> 00:09:15,410 may have to refresh the table, even 207 00:09:15,410 --> 00:09:17,290 though we've deleted it, and that would 208 00:09:17,290 --> 00:09:19,480 be if IntelliJ was giving you some warnings. 209 00:09:19,480 --> 00:09:20,860 Which wasn't happening here, but if you 210 00:09:20,860 --> 00:09:22,260 are getting that problem, just click on 211 00:09:22,260 --> 00:09:24,660 Refresh and that should fix the problem. 212 00:09:24,660 --> 00:09:26,350 But now if we look at our history table, 213 00:09:26,350 --> 00:09:28,650 we can see we've got the extra column there 214 00:09:28,650 --> 00:09:30,740 zone, and if we open this up, the 215 00:09:30,740 --> 00:09:32,490 history table, you'll notice that the 216 00:09:32,490 --> 00:09:36,130 zone column does look a little bit funny. 217 00:09:36,130 --> 00:09:37,450 And the reason for that, it's not really 218 00:09:37,450 --> 00:09:39,490 intended to be read by humans. 219 00:09:39,490 --> 00:09:40,980 Now that's one reason why we didn't pick the 220 00:09:40,980 --> 00:09:43,040 accounts object and store each 221 00:09:43,040 --> 00:09:45,380 one in a single database column. 222 00:09:45,380 --> 00:09:47,710 You can't really query and filter pickled 223 00:09:47,710 --> 00:09:49,830 values, so they're not really a suitable 224 00:09:49,830 --> 00:09:51,020 replacement for storing the 225 00:09:51,020 --> 00:09:54,020 individual attributes of your class. 226 00:09:55,710 --> 00:09:57,900 All right, so let's have a look at checkdb. 227 00:09:57,900 --> 00:09:59,280 And what we're gonna do here, and I'll just 228 00:09:59,280 --> 00:10:01,840 close this database over to the right here. 229 00:10:01,840 --> 00:10:03,830 We're gonna take a copy of this as well, 230 00:10:03,830 --> 00:10:05,810 so we've still got the original to work with. 231 00:10:05,810 --> 00:10:09,120 So let's right click that, select Copy, 232 00:10:09,120 --> 00:10:13,120 and then Paste, and we'll call this one tzcheck. 233 00:10:17,030 --> 00:10:18,410 And what we're gonna do now is 234 00:10:18,410 --> 00:10:22,160 change this code to read the data back again. 235 00:10:23,130 --> 00:10:25,820 So firstly, we need to add a couple imports. 236 00:10:25,820 --> 00:10:29,910 So import pytz, then we also wanna import pickle. 237 00:10:33,480 --> 00:10:35,990 Next, we need to look at our tables, 238 00:10:35,990 --> 00:10:37,900 and now we're not gonna be looking at the view. 239 00:10:37,900 --> 00:10:39,500 We want to actually look at the table again. 240 00:10:39,500 --> 00:10:40,850 So I'm gonna delete local history 241 00:10:40,850 --> 00:10:43,010 and make that history, and we'll delete 242 00:10:43,010 --> 00:10:45,060 this print now because we're gonna be changing that. 243 00:10:45,060 --> 00:10:49,220 So I'm gonna start by typing UTC_time is equal to row zero. 244 00:10:50,770 --> 00:10:53,480 Zero in left and right brackets. 245 00:10:53,480 --> 00:10:56,730 And pickled_zone is equal to row three. 246 00:10:59,020 --> 00:11:03,020 And zone equals pickle.loads(). 247 00:11:05,610 --> 00:11:08,150 That's gonna be pickled_zone. 248 00:11:08,150 --> 00:11:11,900 Local_time is equal to pytz.UTC.localize, and 249 00:11:15,220 --> 00:11:19,380 it's gonna be UTCTime.stimezone, and passing zone to that. 250 00:11:24,380 --> 00:11:25,520 And then we wanna print some of this 251 00:11:25,520 --> 00:11:26,780 stuff out, so I'm gonna do print 252 00:11:26,780 --> 00:11:30,760 replacement fields, curly braces, slash T. 253 00:11:30,760 --> 00:11:31,650 And now instead of replacement 254 00:11:31,650 --> 00:11:33,710 fields, two curly braces again. 255 00:11:33,710 --> 00:11:36,100 And another one, slash T, and curly 256 00:11:36,100 --> 00:11:39,420 braces again, and the output's gonna 257 00:11:39,420 --> 00:11:43,590 be .format UTC_time, local_time, and then local_time.tzinfo. 258 00:11:50,820 --> 00:11:53,650 And that should be that. 259 00:11:53,650 --> 00:11:56,360 I meant it to be a right parentheses there. 260 00:11:56,360 --> 00:11:57,930 Okay. 261 00:11:57,930 --> 00:11:59,400 So I firstly, I added the two imports 262 00:11:59,400 --> 00:12:02,670 for pytz and pickle on line 213. 263 00:12:02,670 --> 00:12:04,230 And we also changed the query, as you 264 00:12:04,230 --> 00:12:05,570 saw on line 8, so that we're not 265 00:12:05,570 --> 00:12:07,620 using the view anymore, but we're 266 00:12:07,620 --> 00:12:11,050 actually using the table that we've updated. 267 00:12:11,050 --> 00:12:13,360 Next step was to get our UTCDateTime value, 268 00:12:13,360 --> 00:12:15,620 and that's the first row of the table, 269 00:12:15,620 --> 00:12:16,600 and we also want the zone 270 00:12:16,600 --> 00:12:19,340 information, which was the fourth item. 271 00:12:19,340 --> 00:12:20,960 Now in practise, you'd probably wanna unpack 272 00:12:20,960 --> 00:12:23,160 all four columns into variables, but 273 00:12:23,160 --> 00:12:24,820 we're not really interested in the 274 00:12:24,820 --> 00:12:26,540 account and amount columns at the moment, 275 00:12:26,540 --> 00:12:28,210 so we've actually ignored those. 276 00:12:28,210 --> 00:12:32,540 And we then, on line 13, I'm pickling the zone 277 00:12:32,540 --> 00:12:35,580 data using the pickle module's loads() function. 278 00:12:35,580 --> 00:12:38,330 And then we used the tzinfo zone object 279 00:12:38,330 --> 00:12:41,250 when we called stimezone on line 14 to 280 00:12:41,250 --> 00:12:43,260 revert the UTC time back to the 281 00:12:43,260 --> 00:12:45,520 time in its original timezone. 282 00:12:45,520 --> 00:12:47,440 So let's just run that. 283 00:12:50,430 --> 00:12:51,490 I can see here that I had printed 284 00:12:51,490 --> 00:12:53,560 the timezone after UTC time and local 285 00:12:53,560 --> 00:12:55,510 time, just as a check that we're 286 00:12:55,510 --> 00:12:58,200 getting the original tzinfo object back. 287 00:12:58,200 --> 00:13:00,030 And you can see that when I've run that, 288 00:13:00,030 --> 00:13:01,810 you can see the original time, which 289 00:13:01,810 --> 00:13:04,210 was with an offset of 10 hours 30, 290 00:13:04,210 --> 00:13:07,150 10 column 30, from the ACDT timezone, 291 00:13:07,150 --> 00:13:09,490 which is the Australian Central Daylight Time, 292 00:13:09,490 --> 00:13:11,400 which, of course, is my timezone. 293 00:13:11,400 --> 00:13:13,190 All right, so that's the challenge completed. 294 00:13:13,190 --> 00:13:15,900 Now, I mentioned that one of the possible 295 00:13:15,900 --> 00:13:18,300 solutions was storing timezone 296 00:13:18,300 --> 00:13:20,280 names and why it's a waste of time. 297 00:13:20,280 --> 00:13:22,100 Let's actually talk about that more in the 298 00:13:22,100 --> 00:13:24,030 next video and see why that's not 299 00:13:24,030 --> 00:13:26,340 a good idea to actually work with. 300 00:13:26,340 --> 00:13:28,910 So I'll see you in that next video.