External Exam Download Resources Web Applications Games Recycle Bin

Displaying Restaurant Results

display1.py

import sqlite3

try:
  db = sqlite3.connect('restaurant.db')
  cursor = db.cursor()
  cursor.execute("SELECT * FROM menu")
  result = cursor.fetchall()

  items = ""
  if len(result) > 0: #if there are some rows to display:
    for row in result:
      items += row[1] + "\n" #have to know which column is which...
  print(items)
  
except Exception as error_msg:
  print("An error occured:", error_msg)
finally:
  db.close()

display2.py

import sqlite3

try:
  db = sqlite3.connect('restaurant.db')
  db.row_factory = sqlite3.Row #so we can access rows by column name
  cursor = db.cursor()
  cursor.execute("SELECT * FROM menu")
  result = cursor.fetchall()

  items = ""
  if len(result) > 0:
    for row in result:
      items += row['title'] + "\n" #now we don't have to know column indexes
  print(items)
  
except Exception as error_msg:
  print("An error occured:", error_msg)
finally:
  db.close()

#note that now print(result) won't work as it has in the past

display3.py

import sqlite3

querystring = '''
SELECT COUNT(*), category
FROM menu
GROUP BY category
HAVING COUNT(*) > ( /* having more than the minimum: */
  SELECT COUNT(*)
  FROM menu
  GROUP BY category
  ORDER BY 1 ASC LIMIT 1 /* smallest category of foods */ 
)
'''

try:
  db = sqlite3.connect('restaurant.db')
  db.row_factory = sqlite3.Row
  cursor = db.cursor()
  cursor.execute(querystring)
  result = cursor.fetchall()
  
  items = ""
  if len(result) > 0:
    for row in result:
      items += row['category'] + " " + str(row['COUNT(*)']) + "\n"
  print(items)
  
except Exception as error_msg:
  print("An error occured:", error_msg)
finally:
  db.close()

What happens when you change the querystring in the final Python example to "SELECT * FROM table_that_doesnt_exist"?