Abfragen normalisierter Daten

1. Abfragen aller Bücher sortiert nach language_id und title:

   def select_all_records_ordered_by_language_number(cursor):
       print("All books ordered by language id and title:")
       for row in cursor.execute(
           """SELECT language_code, author, title FROM books
                                    ORDER BY language_code,title"""
       ):
           print(row)
   

   >>> import create_db
   >>> import create_data_from_csv
   >>> import normalise
   >>> import query_normalised
   All books ordered by language id and title:
   (1, 'Veit Schiele', 'Jupyter Tutorial')
   (2, 'Veit Schiele', 'Jupyter Tutorial')
   (2, 'Veit Schiele', 'PyViz Tutorial')
   (2, 'Veit Schiele', 'Python basics')
   

2. Um nun nicht nur die ID der Sprachen zu erhalten sondern die zugehörigen Sprachcodes wird mit JOIN über die id-Spalte in der languages-Tabelle eine Verbindung zu den dort hinterlegten Sprachcodes hergestellt:

   def select_all_records_ordered_by_language_code(cursor):
       print("All books ordered by language code and title:")
       for row in cursor.execute(
           """SELECT languages.language_code, books.author, books.title
                                    FROM books
                                    JOIN languages ON (books.language_code = languages.id)
                                    ORDER BY languages.language_code,title"""
       ):
           print(row)
   

   >>> import query_normalised
   …
   All books ordered by language code and title:
   ('de', 'Veit Schiele', 'Jupyter Tutorial')
   ('en', 'Veit Schiele', 'Jupyter Tutorial')
   ('en', 'Veit Schiele', 'PyViz Tutorial')
   ('en', 'Veit Schiele', 'Python basics')