Write Pythonic Code Like a Seasoned Developer Transcripts
Chapter: Python for Humans
Lecture: Records: SQL for Humans
Login or
purchase this course
to watch this video and the rest of the course contents.
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."