Write Pythonic Code Like a Seasoned Developer Transcripts
Chapter: Python for Humans
Lecture: Records: SQL for Humans
0:01 Next, sticking with our some amazing package for humans, let's look at Records.
0:06 Records is also by Kenneth Reitz and I chose his work because I really admire him
0:09 and I think it brings a great simplicity and powerfulness all together at the same time.
0:14 So here we have a thing called Records, it's an improvement on the built in DB API that lets you query databases in a really nice way,
0:23 see it supports things like Postgres, MySql, SQL Server, Oracle and so on. So let's go see how we use this to access to the simple little database.
0:33 Now here I have a little bit of starter code and we are going to go,
0:36 let's just look at this little support file, it's going to go and find this demo_db.sqlite and it's going to generate a connection string to that file.
0:45 And if we look what's in here, let's go over here, you can see there is some ids, x y and values.
0:53 And so what I want to do is do a query based on this value, so I want to find all the measurements that have a value greater than 0.9, 0.95 and so on.
1:02 So how do we do that? Well, I've already installed Records, here you can see Records and hey we have the latest version,
1:09 cool, but again we could go install it from PyPi, with pip or with PyCharm, so we'll say "import records", and the way we get started is
1:17 we create a database and we give it the connection string, like so, and then we say "db.query()" over here and we just give it some SQL.
1:28 Remember the tagline, "just write SQL", so we are going to come over here and we are going to do a query, now what I am going to write
1:34 would normally just be a string, this is SQL embedded in Python, so there is no less support for whatever that means anyway,
1:41 but because PyCharm over here has this database registered when I drag it across like that, watch what happens,
1:46 this is, I'll never get tired of seeing this, so if I type "SELECT" it's going to start to think oh,
1:51 maybe you are writing a database query, not yet, if I say "* FROM", now it has, OK, we have two of these databases active so "SELECT * FROM"
2:01 and now notice how I got syntax highlighting inside the string and now as I hit space I actually get completion on the column,
2:09 so we get id and value, x and y, I want to say "where the value is > than 0.9", so there is a query, that's pretty easy, now let's loop over it,
2:20 so "for r in" and let's just print it out to see what the heck came back here,
2:26 so if I run this, there, you can see we got our records back from the database,
2:32 and notice there is a bunch and they are not quite ordered the way I'd like, so let's say "ORDER BY value DESC", how easy is that?
2:42 Connection string, create a database, create a query, done. And notice, we can come out if we just want to print the value,
2:50 these have access to all the values, if I want to get just say the top 3 highest measurements or let's say top 5,
2:58 we can use slicing, right on the results, boom, there is the top 5, beautiful. So again, here is how you use Records, one import statement,
3:07 create the database, run the query, done. 3 lines of code including the import and connecting to the database.
3:14 Again, just another example of how important it is to look around at what's available when you are working with Python apps,
3:21 there is so much out there, one of the biggest challenges is actually finding it, hopefully, you are inspired to look around by some of these examples.
3:29 All right so the final takeaway of this whole section is: "It is Pythonic to leverage PyPi and open source
3:35 more than it is to implement the coolest clever algorithm on your own code, and keep it private."