Accessing Data in Relation Objects

00:00 In the previous lesson, I showed you how to use untyped CSV data in DuckDB. Did you miss that sound? In this lesson, I’ll show you how to access the data inside of a relation object.

00:13 So far, I’ve been playing about in the REPL. Anytime I’ve run a query and got back a relation object, the REPL has shown the result. Like with any REPL response, this is invoking the object’s underlying .__repr__() method, which in this case prints out a pretty table.

00:30 That’s great if you’re just playing around, but if you’re writing code to get at stuff in the database, it isn’t helpful. The relation objects themselves are stateful query objects.

00:40 They contain the results and provide methods that allow you to access them one or more rows at a time. What I mean by stateful is if you fetch a row, you’ll get the first result.

00:50 If you fetch again, you’ll get the second result. You can think of it kind of like using an iterator. This is common practice with database APIs. If you’re used to just slicing things, it takes a little getting used to, but it’s the norm in this field.

01:06 Let’s take a dip into the REPL. This time actually fetching some data rather than just displaying it. I’ve imported DuckDB and connected to our database created in previous lessons.

01:18 Now I’ll run a query.

01:27 Nothing you haven’t seen before, but this time, let me do it again, storing the result.

01:38 Now just what is that result?

01:41 It’s a DuckDBPyRelation object. If I want to get at the data in the object, I can use one of the fetch methods on the object. fetch one() gets a single row as a tuple containing the sequence and last name asked for in the select query.

02:00 As I mentioned, it’s stateful. If I do it again, I get the second row, and then the third. This keeps going until I run out of rows. If I want Washington again, I can’t get it out of this object, I have to rerun the query.

02:24 That gave me a new relation object overriding our existing one. So fetchone() returns Washington again. There are a few other fetch methods.

02:38 fetchmany() allows you to specify how many rows to fetch. It returns a list of tuples. If instead you want everything,

02:49 fetchall(), which also results in a list of tuples, fetches everything that’s left in the call. Seeing as I got Washington through Madison in previous calls, this result starts at Monroe.

03:02 Happy birthday, Mr. President. Wait, wrong Monroe. Would you prefer an obscure Kennedy reference or more duck puns? That other Monroe was seductive. See, you don’t have to make a choice.

03:18 You can have both. Next up, some more database goodness: selecting data across multiple tables using a join.