Python Database API

Querying a database

Use the sqlite3 module in the Python standard library to complete the following exercises.

Write a script containing a function that displays information about a table in a database: information about the table and all the rows in a database.

Apply your script to the database file data01.db in this directory.

Here are some SQL queries that you may find helpful:

sql = 'select * from plants'
sql = 'select * from plants where p_name = banana'
sql = 'insert into plants values (
sql = 'insert into plants values ("tangerine", "sweet and juicy", "9")
sql = 'delete from plants where p_name = "peach"'
sql = 'update plants set p_descrip = "most amazing fruit" where p_name = "banana"'
  1. Open/create a connection to the database.

  2. Execute a query on the database -- example:

    select * from plants
    
  3. Display the descriptions of the columns returned by the query.

  4. Display the number of rows returned as a result of the query.

  5. Display the "raw" results, that is each of the rows/tuples returned by the query.

  6. Display each field (that is, each column) as follows:

    • Name -- left justified in 12 spaces
    • Description -- left justified in 30 spaces
    • Rating -- right justified in 4 spaces

What you will learn:


Delete a row from a database

Write a function that deletes a row from the plants table in the sample database.

First check to see whether the row exists in the database. Print an error message if it does not.

Modify your script so that it processes the following command lines:

python db_actions.py show
python db_actions.py delete plant_name

What you will learn:

Add a row to a database

Write a function that adds a row to the plants table in the sample database.

Modify your script so that it processes the following additional command line:

python db_actions.py add plant_name description rating

What you will learn:

Create a description-row dictionary

Write a function that takes the query (result) description (cursor.description) and a row as arguments, and which creates and returns a dictionary whose keys are the names of the columns and whose values are the corresponding values from that row.

Additional information