Detailed Requirements Database Access All database access will be through the me

Question

Detailed Requirements

Database Access

All database access will be through the methods described in this section. These are the only methods in the application that should connect to the database and issue SQL commands. Other parts of the application will call these methods to get data as needed. These methods should be in a module named db_access.1

You may find it convenient to define other functions inside the module, but they should not be used outside the module.

Assume that a database file named measures.sqlite is available in the current directory. Reference that file with a relative path, the layout of the directories on the testing system is not likely to be like yours.

Utility Functions

Provide the following functions to support the application you are developing. These functions should use the database access functions described in the previous section to get data for the application. Put these functions in a module named db_utility.

Scripts

Write a script that will display a table of data with each row dedicated to an area. The columns should line up. There should be headers on the columns. The data in each column is:

Area id

Area name

Number of locations in the area

Average measurements for all measurements for all locations in the area

If there are no measurements, display the string “——-“

List of categories to which the area belongs

If the area belongs to no categories, leave the entry blank.

Here is what the output would might like:

Unit test

A unit test similar to this one will be used to test the two modules: test2.py

------------------------------

Environment

A database file, named measures.sqlite will be available in the directory from which the scripts will be run.

The scripts and unit tests will be run from PyCharm.

Explanation / Answer

def get_all_areas():
con = psycopg2.connect(database='database_name',user='postgres')
cur = con.cursor()
cur.execute("select * from area")
ver = cur.fetchall()
interest = make_list(ver[0].split(','))
return interest

def get_locations_for_area(area_id):
'''
Return a list of dictionaries giving the locations for the given area.
'''
con = psycopg2.connect(database='project',user='postgres')
cur = con.cursor()
cur.execute("select location from area where area_id = %s", (area_id,))
ver = cur.fetchall()
interest = make_list(ver[0].split(','))
length = len(interest)

def get_measurements_for_location(location_id):
'''
Return a list of dictionaries giving the measurement rows for the given location.
'''
con = psycopg2.connect(database='project',user='postgres')
cur = con.cursor()
cur.execute("select measurement from location where location_id = %s", (location_id,))
ver = cur.fetchall()
interest = make_list(ver[0].split(','))
length = len(interest)

def get_categories_for_area(area_id):
'''
Return a list of rows from the category table that all contain the given area.
'''
con = psycopg2.connect(database='project',user='postgres')
cur = con.cursor()
cur.execute("select * from category where area_id = %s", (area_id,))
ver = cur.fetchall()
interest = make_list(ver[0].split(','))
length = len(interest)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.