How can I reduce the number of queries to my PostgreSQL database?

[Frank Millman]

SR wrote:
> As a starter project for learning Python/PostgreSQL, I am building a
> Books database that stores information on the books on my bookshelf.
>
> Say I have three tables.
>
> Table "books" contains rows for book_id, title, subtitle, ISBN.
>
> Table "authors" contains rows for author_id, author surname, author
> first names, biographical notes.
>
> Table "bookauthors" contains two rows: book_id, author_id.
>
> The bookauthors table links the books and authors tables.
>
> Scenario: I have a python script which creates web page listing all
> books in the database, and all authors for each book. My python script
> does essentially three things:
>
> 1.  retrieve a list of all book_ids and book_titles.
>
> 2.  for each book_id, query the bookauthors table and retrieve all
> author names for that book_id.
>
> 3.  display it all out as an html table on a web page.
>
> The script works fine, if a little slow. I think that's because if I
> have 50 books in my database, my script performs 51 database queries (1
> for all book names; then 1 for each book). A colleague of mine
> suggested that I could get away with two queries, 1 to read the book
> ids and titles, and 1 to read the bookauthors table to pull in *all*
> relations, and then do all the work in Python.
>
> I think I know where he's coming from, but I don't know where to begin.
> Any clues? Is there a specific name for this technique?

The specific name you are looking for is to 'join' tables. There will
be many references and tutorials available, but I suggest you start
with the PostgreSQL tutorial, which is part of the documentation
supplied with PostgreSQL.

Here is a link to the 'join' command in the online manual.

http://www.postgresql.org/docs/8.1/interactive/tutorial-join.html

HTH

Frank Millman