1 00:00:05,100 --> 00:00:07,860 In this video, we're going to follow on 2 00:00:07,860 --> 00:00:10,130 from writing our data to the history table 3 00:00:10,130 --> 00:00:12,780 and look at how we can get it back out again. 4 00:00:12,780 --> 00:00:14,480 Now you ought to be wondering 5 00:00:14,480 --> 00:00:16,100 why I've bothered creating this video, 6 00:00:16,100 --> 00:00:19,720 after all we can just run a simple select query, can't we? 7 00:00:19,720 --> 00:00:21,510 Well let's actually see whether that's true. 8 00:00:21,510 --> 00:00:23,620 So what I'm going to do is start off by creating 9 00:00:23,620 --> 00:00:25,290 a new path and file. 10 00:00:26,670 --> 00:00:29,420 And we'll call this one check db. 11 00:00:32,150 --> 00:00:34,610 And let's actually just try getting some data from the 12 00:00:34,610 --> 00:00:36,820 transactions or rather the history table. 13 00:00:36,820 --> 00:00:38,410 So import, sqlite3. 14 00:00:41,740 --> 00:00:46,380 Then we're gonna do db equals sqlite3 dot connect 15 00:00:46,380 --> 00:00:48,220 and counts dot sqlite. 16 00:00:50,000 --> 00:00:53,010 And we'll just extract the data or retrieve the data 17 00:00:53,010 --> 00:00:57,300 for row in db dot execute, and was select star 18 00:00:57,300 --> 00:01:01,470 from history, and we'll print out the condensed print row. 19 00:01:04,480 --> 00:01:07,880 And then would do a db dot close once that's been done. 20 00:01:07,880 --> 00:01:10,150 Alright, so that looks relatively painless. 21 00:01:10,150 --> 00:01:12,370 To look what happens when we run this, 22 00:01:12,370 --> 00:01:15,290 and I put a colon on the end there. 23 00:01:17,920 --> 00:01:20,360 And you can see we get a tuple printed for each row 24 00:01:20,360 --> 00:01:21,750 in the history table. 25 00:01:21,750 --> 00:01:24,220 Now everything looks fine at a cursory glance 26 00:01:24,220 --> 00:01:25,990 until we look closely. 27 00:01:25,990 --> 00:01:28,430 We can see that the tuple consists of two strings 28 00:01:28,430 --> 00:01:29,350 and an end. 29 00:01:30,350 --> 00:01:32,720 So our time stamp fuel is being retrieved as a string, 30 00:01:32,720 --> 00:01:34,710 and that's not what we want. 31 00:01:34,710 --> 00:01:36,790 Now, if we have a date time value, 32 00:01:36,790 --> 00:01:39,550 we can convert it to a local time for example, 33 00:01:39,550 --> 00:01:41,750 and let's have a go at that. 34 00:01:41,750 --> 00:01:43,870 Commit that out for now, the print. 35 00:01:43,870 --> 00:01:46,790 And we can do something like print, 36 00:01:47,920 --> 00:01:52,530 another replacement field backslash t to add a tab, 37 00:01:52,530 --> 00:01:57,210 another replacement field dot, and then you can do format 38 00:01:57,210 --> 00:01:59,420 local and it's called time, 39 00:01:59,420 --> 00:02:02,170 and type local and just put time. 40 00:02:04,650 --> 00:02:06,860 And I need to actually define local time. 41 00:02:06,860 --> 00:02:10,699 So local underscore time is equal to row zero. 42 00:02:13,870 --> 00:02:16,340 And we use the talk function in the last section 43 00:02:16,340 --> 00:02:18,590 to see what type our penguins were. 44 00:02:18,590 --> 00:02:21,010 So, let me actually run this. 45 00:02:23,110 --> 00:02:24,030 You really can see 46 00:02:24,030 --> 00:02:25,870 that the local underscore time variable 47 00:02:25,870 --> 00:02:28,190 is definitely a string type. 48 00:02:28,190 --> 00:02:30,880 Now we could convert these string types into 49 00:02:30,880 --> 00:02:34,690 date time values by importing the date time module 50 00:02:34,690 --> 00:02:37,570 and using the STRP time method. 51 00:02:37,570 --> 00:02:39,240 You find everything you need 52 00:02:39,240 --> 00:02:40,810 to do that in the documentation. 53 00:02:40,810 --> 00:02:42,640 So do feel free to try it out. 54 00:02:42,640 --> 00:02:46,760 So it's the STRP time method from the date time module. 55 00:02:46,760 --> 00:02:48,220 Now there is one gotcha though, 56 00:02:48,220 --> 00:02:50,010 which I'm going to mention a bit later, 57 00:02:50,010 --> 00:02:52,510 but as long as you use the Python documentation 58 00:02:52,510 --> 00:02:55,010 and not the sqlite date time docs, 59 00:02:55,010 --> 00:02:57,250 you actually won't have any problems. 60 00:02:57,250 --> 00:02:58,690 But we're not actually going to do that here, 61 00:02:58,690 --> 00:03:01,840 because there is another way that I wanted to show you. 62 00:03:01,840 --> 00:03:04,490 Now we've used the python sqlite3 time stamp 63 00:03:04,490 --> 00:03:07,690 contalk for our times, and this relies on the fact 64 00:03:07,690 --> 00:03:11,510 that the sqlite3 library can examine custom data types 65 00:03:11,510 --> 00:03:15,830 for a column and respond to types that it knows about. 66 00:03:15,830 --> 00:03:17,670 Now, it's possible to define and register 67 00:03:17,670 --> 00:03:19,500 your own data types if you want to, 68 00:03:19,500 --> 00:03:22,130 but the date and time stamp types have already been 69 00:03:22,130 --> 00:03:23,870 registered for us. 70 00:03:23,870 --> 00:03:27,740 What we do have to do is tell sqlite3 to respond to them, 71 00:03:27,740 --> 00:03:31,270 and we do that by passing PARSE underscore DECYLTYPES 72 00:03:31,270 --> 00:03:34,120 when we create the connexion. 73 00:03:34,120 --> 00:03:36,740 So let's actually look at how we would do that. 74 00:03:36,740 --> 00:03:41,660 So we go to our line, that is connecting up here, line four. 75 00:03:41,660 --> 00:03:44,690 And what we have to do now is we pass the parameters, 76 00:03:44,690 --> 00:03:47,790 that's going to be, detect underscore types is equal to 77 00:03:47,790 --> 00:03:50,290 sqlite3 dot, then we do PARSE, 78 00:03:51,280 --> 00:03:53,030 underscore DECLTYPES, 79 00:03:54,200 --> 00:03:55,300 like so. 80 00:03:55,300 --> 00:03:59,320 And if we run this again now that we've done that, 81 00:03:59,320 --> 00:04:01,680 you can see that this time we've got a different result. 82 00:04:01,680 --> 00:04:03,840 And you can see quite clearly near the type 83 00:04:03,840 --> 00:04:06,980 coming back is date time dot date time instead of string. 84 00:04:06,980 --> 00:04:08,480 So that's pretty cool. 85 00:04:08,480 --> 00:04:10,630 So Python is performing automatic conversion 86 00:04:10,630 --> 00:04:13,490 when we write the data, and up here 87 00:04:13,490 --> 00:04:16,970 so setting the detect underscore types argument 88 00:04:16,970 --> 00:04:20,269 to sqlite3 dot PARSE underscore DECLTYPES 89 00:04:20,269 --> 00:04:23,670 as we've done here, causes it to perform a conversion 90 00:04:23,670 --> 00:04:26,180 when reading the data back as well. 91 00:04:26,180 --> 00:04:28,730 Now if you want to read the details of how this all works, 92 00:04:28,730 --> 00:04:32,240 let's check out the python sqlite3 documentation. 93 00:04:32,240 --> 00:04:35,160 It is a page specifically for that. 94 00:04:42,910 --> 00:04:44,140 So, there's quite a bit there, 95 00:04:44,140 --> 00:04:47,810 and the relevant bit is in section 12.6.6.2. 96 00:04:49,050 --> 00:04:51,540 Using adapters to store additional Python types 97 00:04:51,540 --> 00:04:53,120 in Sqlite databases. 98 00:04:53,120 --> 00:04:55,860 So basically describing adapters and converters. 99 00:04:55,860 --> 00:04:58,220 So it's definitely also worth searching that page for 100 00:04:58,220 --> 00:05:00,170 PARSE underscore DECYLTYPES. 101 00:05:00,170 --> 00:05:03,400 I'm going to search for PARSE underscore DECYL. 102 00:05:03,400 --> 00:05:05,040 You can see that there is some more information 103 00:05:05,040 --> 00:05:07,160 in there as well as to what that is and how to use it. 104 00:05:07,160 --> 00:05:10,450 Now you don't actually need to know all the ins and outs 105 00:05:10,450 --> 00:05:12,050 of how this is implemented. 106 00:05:12,050 --> 00:05:14,070 All we have to do is add that argument 107 00:05:14,070 --> 00:05:15,860 when calling the connect method, 108 00:05:15,860 --> 00:05:17,580 and then any timestamp fields 109 00:05:17,580 --> 00:05:19,120 will be handled by the library. 110 00:05:19,120 --> 00:05:22,840 So that's again this line here, on line four. 111 00:05:22,840 --> 00:05:26,080 Now this doesn't handle timezone aware dates though, 112 00:05:26,080 --> 00:05:29,260 and I'm going to quickly demonstrate that. 113 00:05:29,260 --> 00:05:32,030 So back in rollback dot pi, 114 00:05:32,030 --> 00:05:34,510 I'm going to change the current time method now. 115 00:05:34,510 --> 00:05:36,430 Let me close this down. 116 00:05:38,880 --> 00:05:41,830 So with this current time method here on line 14. 117 00:05:41,830 --> 00:05:45,740 Let's change that, instead of returning as date time 118 00:05:45,740 --> 00:05:48,190 we'll just current that out for now. 119 00:05:48,190 --> 00:05:50,280 We're actually going to instead, 120 00:05:50,280 --> 00:05:53,640 type local underscore time is equal to 121 00:05:53,640 --> 00:05:55,810 pytz dot utc dot localise. 122 00:05:59,820 --> 00:06:01,260 I'm probably just going to copy that, 123 00:06:01,260 --> 00:06:04,940 so date time dot date time dot utc now, 124 00:06:04,940 --> 00:06:08,690 and if you return local time dot as timezone. 125 00:06:13,990 --> 00:06:15,700 So we're converting it into a timezone, 126 00:06:15,700 --> 00:06:18,400 and then I go back into my database. 127 00:06:18,400 --> 00:06:21,130 And for the history table, we'll just double click that 128 00:06:21,130 --> 00:06:23,650 and we'll just delete any records that are in there. 129 00:06:23,650 --> 00:06:25,560 Until they get recreated. 130 00:06:25,560 --> 00:06:29,350 You're going to delete them and commit the change, 131 00:06:29,350 --> 00:06:32,090 and now we got a commitment button not refresh. 132 00:06:32,090 --> 00:06:35,080 Okay, so that's been committed, 133 00:06:35,080 --> 00:06:37,330 and I'll just close that now. 134 00:06:37,330 --> 00:06:41,490 And if you run this again, and open the database again. 135 00:06:42,510 --> 00:06:44,790 Probably should have left that open. 136 00:06:44,790 --> 00:06:47,700 You can see now in my case it's added the timezone 137 00:06:47,700 --> 00:06:50,020 plus 10 colon 30 which is the current timezone 138 00:06:50,020 --> 00:06:51,990 for Adelaide, Australia. 139 00:06:51,990 --> 00:06:54,030 And if you run this on your computer you will get 140 00:06:54,030 --> 00:06:56,250 whatever your local timezone is. 141 00:06:56,250 --> 00:07:00,310 But, with that said, if I go back to check bd dot pi 142 00:07:00,310 --> 00:07:01,900 and run that again, 143 00:07:06,020 --> 00:07:08,940 and those have the results that we're getting back here 144 00:07:08,940 --> 00:07:10,610 do not include the timezones, 145 00:07:10,610 --> 00:07:12,790 and in other words there is no offset appearing. 146 00:07:12,790 --> 00:07:15,450 And these values aren't timezone aware. 147 00:07:15,450 --> 00:07:18,040 So strange as that may seem there's actually no easy 148 00:07:18,040 --> 00:07:21,580 and reliable way to correctly PARSE those values 149 00:07:21,580 --> 00:07:23,740 into aware date time values 150 00:07:23,740 --> 00:07:26,160 using the standard Python libraries. 151 00:07:26,160 --> 00:07:28,880 Now this is something you really need to do, if it is, 152 00:07:28,880 --> 00:07:31,760 storing and retrieving times with timezone information. 153 00:07:31,760 --> 00:07:35,040 There are actually different libraries that can help. 154 00:07:35,040 --> 00:07:36,810 Now I'm going to link to a couple 155 00:07:36,810 --> 00:07:38,760 in the resources section. 156 00:07:38,760 --> 00:07:41,250 So be sure to check those out and I'll just load up one 157 00:07:41,250 --> 00:07:43,460 just to give you an idea what they look like. 158 00:07:43,460 --> 00:07:44,710 In the browser. 159 00:07:47,010 --> 00:07:48,930 So there's one library. 160 00:07:50,900 --> 00:07:53,910 It says extension to the standard Python datetime module 161 00:07:53,910 --> 00:07:57,040 and it provides powerful extensions to the datetime module 162 00:07:57,040 --> 00:07:59,230 available in the Python standard library. 163 00:07:59,230 --> 00:08:01,140 There is also a couple more, so I'll put links 164 00:08:01,140 --> 00:08:02,510 to this one and the other two 165 00:08:02,510 --> 00:08:04,990 in the resources section of this video. 166 00:08:04,990 --> 00:08:06,800 Alright, so back to the code. 167 00:08:06,800 --> 00:08:09,370 So I'm going to change back to rollback dot pi 168 00:08:09,370 --> 00:08:10,880 to how it was before. 169 00:08:10,880 --> 00:08:13,730 So what I'm going to do is just commit those two lines out 170 00:08:13,730 --> 00:08:15,460 in case you want to test that for yourself with the code, 171 00:08:15,460 --> 00:08:18,870 and I'll just put the original return statement back in 172 00:08:18,870 --> 00:08:21,710 that didn't use a timezone. 173 00:08:21,710 --> 00:08:23,200 Alright and then I go back 174 00:08:23,200 --> 00:08:24,540 to the history table again to refresh it, 175 00:08:24,540 --> 00:08:28,450 and I'm just going to delete the entries again. 176 00:08:31,130 --> 00:08:33,380 Okay, on the programme again. 177 00:08:38,049 --> 00:08:41,929 We got our entries back and there is no timezone, 178 00:08:41,929 --> 00:08:46,110 and obviously we'll get the same result now with checkdb. 179 00:08:46,110 --> 00:08:48,080 Alright, so I'm now going to look at two different ways 180 00:08:48,080 --> 00:08:49,630 to display our timestamps 181 00:08:49,630 --> 00:08:53,030 in local time rather than utc times. 182 00:08:53,030 --> 00:08:55,320 Now the first method is just to convert 183 00:08:55,320 --> 00:08:59,280 the utc times into aware times, then localise them. 184 00:08:59,280 --> 00:09:01,080 And we saw the code to do that a minute ago 185 00:09:01,080 --> 00:09:03,350 when I changed rollback dot pi. 186 00:09:03,350 --> 00:09:05,980 So I'm going to butterfly checkdb dot pi here now 187 00:09:05,980 --> 00:09:10,150 to show the times as local times alongside the utc times. 188 00:09:10,990 --> 00:09:14,890 So we're going to make a change to this checkdb, 189 00:09:14,890 --> 00:09:18,040 and we're going to start by putting utc 190 00:09:18,040 --> 00:09:21,920 underscore time is equal to row zero. 191 00:09:21,920 --> 00:09:26,090 Then we want to local underscore time is equal to pytz 192 00:09:27,690 --> 00:09:30,200 and actually what we need to do is import the library 193 00:09:30,200 --> 00:09:32,750 so let's stop and do that. 194 00:09:32,750 --> 00:09:33,750 Import pytz. 195 00:09:36,780 --> 00:09:38,950 Then pytz now on line nine 196 00:09:40,560 --> 00:09:42,230 dot utc dot localise 197 00:09:43,420 --> 00:09:46,150 and it's going to be utc underscore time 198 00:09:46,150 --> 00:09:47,900 then dot as timezone. 199 00:09:51,600 --> 00:09:53,960 And I'll take those two lines there and we can 200 00:09:53,960 --> 00:09:56,240 terms for printing it out now. 201 00:09:56,240 --> 00:09:58,930 We're just going to print out instead of local time, 202 00:09:58,930 --> 00:10:00,690 we're going to do utc underscore time 203 00:10:00,690 --> 00:10:03,450 as the first bit of output 204 00:10:03,450 --> 00:10:05,610 and the second type of output 205 00:10:05,610 --> 00:10:07,520 is no longer going to be the type, 206 00:10:07,520 --> 00:10:09,990 it's just going to be local time. 207 00:10:09,990 --> 00:10:11,300 Like so. 208 00:10:11,300 --> 00:10:14,230 Alright, now that I run this, 209 00:10:14,230 --> 00:10:17,940 we can see now that we get the dates in my local time. 210 00:10:17,940 --> 00:10:21,060 You can see that it's showing 8:56am. 211 00:10:21,060 --> 00:10:22,240 Which is correct 212 00:10:22,240 --> 00:10:23,500 and if I look at that now 213 00:10:23,500 --> 00:10:25,120 it's actually a couple minutes past. 214 00:10:25,120 --> 00:10:26,370 So it's 8:58am, 215 00:10:27,260 --> 00:10:30,100 and the reason a couple minutes past of course 216 00:10:30,100 --> 00:10:32,940 this was saved about a minute and a half ago in the video 217 00:10:32,940 --> 00:10:35,620 when I reran rollback dot pi. 218 00:10:35,620 --> 00:10:38,940 You can now see the utc times and the same times 219 00:10:38,940 --> 00:10:41,700 in my local timezone with an offset of 10:30. 220 00:10:41,700 --> 00:10:45,220 Which is correct for this part of Australia in summertime. 221 00:10:45,220 --> 00:10:47,840 Now that's one way of doing it. 222 00:10:47,840 --> 00:10:49,860 But there is another way to do this as well, 223 00:10:49,860 --> 00:10:51,580 so lets explore that alternative way 224 00:10:51,580 --> 00:10:54,410 of doing things in the next video.