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