WEBVTT 1 00:00:01.808 --> 00:00:04.565 In this video, we're going to follow on 2 00:00:04.565 --> 00:00:06.832 from writing our data to the history table 3 00:00:06.832 --> 00:00:09.482 and look at how we can get it back out again. 4 00:00:09.482 --> 00:00:11.183 Now you ought to be wondering 5 00:00:11.183 --> 00:00:12.801 why I've bothered creating this video, 6 00:00:12.801 --> 00:00:16.427 after all we can just run a simple select query, can't we? 7 00:00:16.427 --> 00:00:18.211 Well let's actually see whether that's true. 8 00:00:18.211 --> 00:00:20.327 So what I'm going to do is start off by creating 9 00:00:20.327 --> 00:00:21.994 a new path and file. 10 00:00:23.378 --> 00:00:26.128 And we'll call this one check db. 11 00:00:28.858 --> 00:00:31.319 And let's actually just try getting some data from the 12 00:00:31.319 --> 00:00:33.527 transactions or rather the history table. 13 00:00:33.527 --> 00:00:35.110 So import, sqlite3. 14 00:00:38.448 --> 00:00:43.088 Then we're gonna do db equals sqlite3 dot connect 15 00:00:43.088 --> 00:00:44.921 and counts dot sqlite. 16 00:00:46.704 --> 00:00:49.712 And we'll just extract the data or retrieve the data 17 00:00:49.712 --> 00:00:54.009 for row in db dot execute, and was select star 18 00:00:54.009 --> 00:00:58.176 from history, and we'll print out the condensed print row. 19 00:01:01.187 --> 00:01:04.587 And then would do a db dot close once that's been done. 20 00:01:04.587 --> 00:01:06.855 Alright, so that looks relatively painless. 21 00:01:06.855 --> 00:01:09.078 To look what happens when we run this, 22 00:01:09.078 --> 00:01:11.995 and I put a colon on the end there. 23 00:01:14.627 --> 00:01:17.067 And you can see we get a tuple printed for each row 24 00:01:17.067 --> 00:01:18.456 in the history table. 25 00:01:18.456 --> 00:01:20.926 Now everything looks fine at a cursory glance 26 00:01:20.926 --> 00:01:22.696 until we look closely. 27 00:01:22.696 --> 00:01:25.136 We can see that the tuple consists of two strings 28 00:01:25.136 --> 00:01:26.053 and an end. 29 00:01:27.056 --> 00:01:29.427 So our time stamp fuel is being retrieved as a string, 30 00:01:29.427 --> 00:01:31.415 and that's not what we want. 31 00:01:31.415 --> 00:01:33.494 Now, if we have a date time value, 32 00:01:33.494 --> 00:01:36.256 we can convert it to a local time for example, 33 00:01:36.256 --> 00:01:38.456 and let's have a go at that. 34 00:01:38.456 --> 00:01:40.576 Commit that out for now, the print. 35 00:01:40.576 --> 00:01:43.493 And we can do something like print, 36 00:01:44.627 --> 00:01:49.234 another replacement field backslash t to add a tab, 37 00:01:49.234 --> 00:01:53.914 another replacement field dot, and then you can do format 38 00:01:53.914 --> 00:01:56.126 local and it's called time, 39 00:01:56.126 --> 00:01:58.876 and type local and just put time. 40 00:02:01.356 --> 00:02:03.568 And I need to actually define local time. 41 00:02:03.568 --> 00:02:07.401 So local underscore time is equal to row zero. 42 00:02:10.572 --> 00:02:13.040 And we use the talk function in the last section 43 00:02:13.040 --> 00:02:15.293 to see what type our penguins were. 44 00:02:15.293 --> 00:02:17.710 So, let me actually run this. 45 00:02:19.810 --> 00:02:20.735 You really can see 46 00:02:20.735 --> 00:02:22.578 that the local underscore time variable 47 00:02:22.578 --> 00:02:24.892 is definitely a string type. 48 00:02:24.892 --> 00:02:27.582 Now we could convert these string types into 49 00:02:27.582 --> 00:02:31.391 date time values by importing the date time module 50 00:02:31.391 --> 00:02:34.275 and using the STRP time method. 51 00:02:34.275 --> 00:02:35.940 You find everything you need 52 00:02:35.940 --> 00:02:37.510 to do that in the documentation. 53 00:02:37.510 --> 00:02:39.346 So do feel free to try it out. 54 00:02:39.346 --> 00:02:43.466 So it's the STRP time method from the date time module. 55 00:02:43.466 --> 00:02:44.926 Now there is one gotcha though, 56 00:02:44.926 --> 00:02:46.718 which I'm going to mention a bit later, 57 00:02:46.718 --> 00:02:49.215 but as long as you use the Python documentation 58 00:02:49.215 --> 00:02:51.716 and not the sqlite date time docs, 59 00:02:51.716 --> 00:02:53.958 you actually won't have any problems. 60 00:02:53.958 --> 00:02:55.391 But we're not actually going to do that here, 61 00:02:55.391 --> 00:02:58.543 because there is another way that I wanted to show you. 62 00:02:58.543 --> 00:03:01.199 Now we've used the python sqlite3 time stamp 63 00:03:01.199 --> 00:03:04.391 contalk for our times, and this relies on the fact 64 00:03:04.391 --> 00:03:08.214 that the sqlite3 library can examine custom data types 65 00:03:08.214 --> 00:03:12.532 for a column and respond to types that it knows about. 66 00:03:12.532 --> 00:03:14.372 Now, it's possible to define and register 67 00:03:14.372 --> 00:03:16.201 your own data types if you want to, 68 00:03:16.201 --> 00:03:18.831 but the date and time stamp types have already been 69 00:03:18.831 --> 00:03:20.572 registered for us. 70 00:03:20.572 --> 00:03:24.440 What we do have to do is tell sqlite3 to respond to them, 71 00:03:24.440 --> 00:03:27.972 and we do that by passing PARSE underscore DECYLTYPES 72 00:03:27.972 --> 00:03:30.823 when we create the connexion. 73 00:03:30.823 --> 00:03:33.445 So let's actually look at how we would do that. 74 00:03:33.445 --> 00:03:38.364 So we go to our line, that is connecting up here, line four. 75 00:03:38.364 --> 00:03:41.394 And what we have to do now is we pass the parameters, 76 00:03:41.394 --> 00:03:44.492 that's going to be, detect underscore types is equal to 77 00:03:44.492 --> 00:03:46.992 sqlite3 dot, then we do PARSE, 78 00:03:47.984 --> 00:03:49.734 underscore DECLTYPES, 79 00:03:50.900 --> 00:03:52.001 like so. 80 00:03:52.001 --> 00:03:56.021 And if we run this again now that we've done that, 81 00:03:56.021 --> 00:03:58.381 you can see that this time we've got a different result. 82 00:03:58.381 --> 00:04:00.541 And you can see quite clearly near the type 83 00:04:00.541 --> 00:04:03.681 coming back is date time dot date time instead of string. 84 00:04:03.681 --> 00:04:05.181 So that's pretty cool. 85 00:04:05.181 --> 00:04:07.331 So Python is performing automatic conversion 86 00:04:07.331 --> 00:04:10.192 when we write the data, and up here 87 00:04:10.192 --> 00:04:13.672 so setting the detect underscore types argument 88 00:04:13.672 --> 00:04:16.971 to sqlite3 dot PARSE underscore DECLTYPES 89 00:04:16.971 --> 00:04:20.371 as we've done here, causes it to perform a conversion 90 00:04:20.371 --> 00:04:22.882 when reading the data back as well. 91 00:04:22.882 --> 00:04:25.432 Now if you want to read the details of how this all works, 92 00:04:25.432 --> 00:04:28.944 let's check out the python sqlite3 documentation. 93 00:04:28.944 --> 00:04:31.861 It is a page specifically for that. 94 00:04:39.613 --> 00:04:40.843 So, there's quite a bit there, 95 00:04:40.843 --> 00:04:44.510 and the relevant bit is in section 12.6.6.2. 96 00:04:45.752 --> 00:04:48.243 Using adapters to store additional Python types 97 00:04:48.243 --> 00:04:49.824 in Sqlite databases. 98 00:04:49.824 --> 00:04:52.563 So basically describing adapters and converters. 99 00:04:52.563 --> 00:04:54.923 So it's definitely also worth searching that page for 100 00:04:54.923 --> 00:04:56.872 PARSE underscore DECYLTYPES. 101 00:04:56.872 --> 00:05:00.104 I'm going to search for PARSE underscore DECYL. 102 00:05:00.104 --> 00:05:01.744 You can see that there is some more information 103 00:05:01.744 --> 00:05:03.865 in there as well as to what that is and how to use it. 104 00:05:03.865 --> 00:05:07.155 Now you don't actually need to know all the ins and outs 105 00:05:07.155 --> 00:05:08.755 of how this is implemented. 106 00:05:08.755 --> 00:05:10.773 All we have to do is add that argument 107 00:05:10.773 --> 00:05:12.563 when calling the connect method, 108 00:05:12.563 --> 00:05:14.282 and then any timestamp fields 109 00:05:14.282 --> 00:05:15.824 will be handled by the library. 110 00:05:15.824 --> 00:05:19.543 So that's again this line here, on line four. 111 00:05:19.543 --> 00:05:22.784 Now this doesn't handle timezone aware dates though, 112 00:05:22.784 --> 00:05:25.966 and I'm going to quickly demonstrate that. 113 00:05:25.966 --> 00:05:28.739 So back in rollback dot pi, 114 00:05:28.739 --> 00:05:31.218 I'm going to change the current time method now. 115 00:05:31.218 --> 00:05:33.135 Let me close this down. 116 00:05:35.589 --> 00:05:38.536 So with this current time method here on line 14. 117 00:05:38.536 --> 00:05:42.440 Let's change that, instead of returning as date time 118 00:05:42.440 --> 00:05:44.897 we'll just current that out for now. 119 00:05:44.897 --> 00:05:46.989 We're actually going to instead, 120 00:05:46.989 --> 00:05:50.349 type local underscore time is equal to 121 00:05:50.349 --> 00:05:52.516 pytz dot utc dot localise. 122 00:05:56.528 --> 00:05:57.960 I'm probably just going to copy that, 123 00:05:57.960 --> 00:06:01.646 so date time dot date time dot utc now, 124 00:06:01.646 --> 00:06:05.396 and if you return local time dot as timezone. 125 00:06:10.694 --> 00:06:12.403 So we're converting it into a timezone, 126 00:06:12.403 --> 00:06:15.104 and then I go back into my database. 127 00:06:15.104 --> 00:06:17.832 And for the history table, we'll just double click that 128 00:06:17.832 --> 00:06:20.356 and we'll just delete any records that are in there. 129 00:06:20.356 --> 00:06:22.261 Until they get recreated. 130 00:06:22.261 --> 00:06:26.052 You're going to delete them and commit the change, 131 00:06:26.052 --> 00:06:28.792 and now we got a commitment button not refresh. 132 00:06:28.792 --> 00:06:31.782 Okay, so that's been committed, 133 00:06:31.782 --> 00:06:34.032 and I'll just close that now. 134 00:06:34.032 --> 00:06:38.199 And if you run this again, and open the database again. 135 00:06:39.212 --> 00:06:41.496 Probably should have left that open. 136 00:06:41.496 --> 00:06:44.402 You can see now in my case it's added the timezone 137 00:06:44.402 --> 00:06:46.723 plus 10 colon 30 which is the current timezone 138 00:06:46.723 --> 00:06:48.695 for Adelaide, Australia. 139 00:06:48.695 --> 00:06:50.739 And if you run this on your computer you will get 140 00:06:50.739 --> 00:06:52.957 whatever your local timezone is. 141 00:06:52.957 --> 00:06:57.017 But, with that said, if I go back to check bd dot pi 142 00:06:57.017 --> 00:06:58.600 and run that again, 143 00:07:02.729 --> 00:07:05.648 and those have the results that we're getting back here 144 00:07:05.648 --> 00:07:07.318 do not include the timezones, 145 00:07:07.318 --> 00:07:09.497 and in other words there is no offset appearing. 146 00:07:09.497 --> 00:07:12.150 And these values aren't timezone aware. 147 00:07:12.150 --> 00:07:14.740 So strange as that may seem there's actually no easy 148 00:07:14.740 --> 00:07:18.284 and reliable way to correctly PARSE those values 149 00:07:18.284 --> 00:07:20.449 into aware date time values 150 00:07:20.449 --> 00:07:22.860 using the standard Python libraries. 151 00:07:22.860 --> 00:07:25.587 Now this is something you really need to do, if it is, 152 00:07:25.587 --> 00:07:28.460 storing and retrieving times with timezone information. 153 00:07:28.460 --> 00:07:31.746 There are actually different libraries that can help. 154 00:07:31.746 --> 00:07:33.516 Now I'm going to link to a couple 155 00:07:33.516 --> 00:07:35.465 in the resources section. 156 00:07:35.465 --> 00:07:37.958 So be sure to check those out and I'll just load up one 157 00:07:37.958 --> 00:07:40.169 just to give you an idea what they look like. 158 00:07:40.169 --> 00:07:41.419 In the browser. 159 00:07:43.713 --> 00:07:45.630 So there's one library. 160 00:07:47.600 --> 00:07:50.612 It says extension to the standard Python datetime module 161 00:07:50.612 --> 00:07:53.740 and it provides powerful extensions to the datetime module 162 00:07:53.740 --> 00:07:55.939 available in the Python standard library. 163 00:07:55.939 --> 00:07:57.845 There is also a couple more, so I'll put links 164 00:07:57.845 --> 00:07:59.216 to this one and the other two 165 00:07:59.216 --> 00:08:01.692 in the resources section of this video. 166 00:08:01.692 --> 00:08:03.509 Alright, so back to the code. 167 00:08:03.509 --> 00:08:06.070 So I'm going to change back to rollback dot pi 168 00:08:06.070 --> 00:08:07.580 to how it was before. 169 00:08:07.580 --> 00:08:10.430 So what I'm going to do is just commit those two lines out 170 00:08:10.430 --> 00:08:12.161 in case you want to test that for yourself with the code, 171 00:08:12.161 --> 00:08:15.577 and I'll just put the original return statement back in 172 00:08:15.577 --> 00:08:18.412 that didn't use a timezone. 173 00:08:18.412 --> 00:08:19.908 Alright and then I go back 174 00:08:19.908 --> 00:08:21.241 to the history table again to refresh it, 175 00:08:21.241 --> 00:08:25.158 and I'm just going to delete the entries again. 176 00:08:27.839 --> 00:08:30.089 Okay, on the programme again. 177 00:08:34.751 --> 00:08:38.639 We got our entries back and there is no timezone, 178 00:08:38.639 --> 00:08:42.810 and obviously we'll get the same result now with checkdb. 179 00:08:42.810 --> 00:08:44.789 Alright, so I'm now going to look at two different ways 180 00:08:44.789 --> 00:08:46.330 to display our timestamps 181 00:08:46.330 --> 00:08:49.730 in local time rather than utc times. 182 00:08:49.730 --> 00:08:52.021 Now the first method is just to convert 183 00:08:52.021 --> 00:08:55.981 the utc times into aware times, then localise them. 184 00:08:55.981 --> 00:08:57.789 And we saw the code to do that a minute ago 185 00:08:57.789 --> 00:09:00.050 when I changed rollback dot pi. 186 00:09:00.050 --> 00:09:02.689 So I'm going to butterfly checkdb dot pi here now 187 00:09:02.689 --> 00:09:06.856 to show the times as local times alongside the utc times. 188 00:09:07.690 --> 00:09:11.597 So we're going to make a change to this checkdb, 189 00:09:11.597 --> 00:09:14.749 and we're going to start by putting utc 190 00:09:14.749 --> 00:09:18.626 underscore time is equal to row zero. 191 00:09:18.626 --> 00:09:22.793 Then we want to local underscore time is equal to pytz 192 00:09:24.392 --> 00:09:26.908 and actually what we need to do is import the library 193 00:09:26.908 --> 00:09:29.454 so let's stop and do that. 194 00:09:29.454 --> 00:09:30.454 Import pytz. 195 00:09:33.487 --> 00:09:35.654 Then pytz now on line nine 196 00:09:37.269 --> 00:09:38.936 dot utc dot localise 197 00:09:40.129 --> 00:09:42.858 and it's going to be utc underscore time 198 00:09:42.858 --> 00:09:44.608 then dot as timezone. 199 00:09:48.308 --> 00:09:50.666 And I'll take those two lines there and we can 200 00:09:50.666 --> 00:09:52.948 terms for printing it out now. 201 00:09:52.948 --> 00:09:55.639 We're just going to print out instead of local time, 202 00:09:55.639 --> 00:09:57.397 we're going to do utc underscore time 203 00:09:57.397 --> 00:10:00.158 as the first bit of output 204 00:10:00.158 --> 00:10:02.319 and the second type of output 205 00:10:02.319 --> 00:10:04.228 is no longer going to be the type, 206 00:10:04.228 --> 00:10:06.697 it's just going to be local time. 207 00:10:06.697 --> 00:10:08.009 Like so. 208 00:10:08.009 --> 00:10:10.937 Alright, now that I run this, 209 00:10:10.937 --> 00:10:14.649 we can see now that we get the dates in my local time. 210 00:10:14.649 --> 00:10:17.761 You can see that it's showing 8:56am. 211 00:10:17.761 --> 00:10:18.940 Which is correct 212 00:10:18.940 --> 00:10:20.201 and if I look at that now 213 00:10:20.201 --> 00:10:21.829 it's actually a couple minutes past. 214 00:10:21.829 --> 00:10:23.079 So it's 8:58am, 215 00:10:23.969 --> 00:10:26.809 and the reason a couple minutes past of course 216 00:10:26.809 --> 00:10:29.649 this was saved about a minute and a half ago in the video 217 00:10:29.649 --> 00:10:32.329 when I reran rollback dot pi. 218 00:10:32.329 --> 00:10:35.641 You can now see the utc times and the same times 219 00:10:35.641 --> 00:10:38.409 in my local timezone with an offset of 10:30. 220 00:10:38.409 --> 00:10:41.920 Which is correct for this part of Australia in summertime. 221 00:10:41.920 --> 00:10:44.540 Now that's one way of doing it. 222 00:10:44.540 --> 00:10:46.561 But there is another way to do this as well, 223 00:10:46.561 --> 00:10:48.281 so lets explore that alternative way 224 00:10:48.281 --> 00:10:51.114 of doing things in the next video.