Query using GsshaPy Objects¶
Last Updated: July 30, 2014
Explore the Database¶
To prove that the exercise has actually done something, let’s explore database. Before we do this using the GsshaPy objects, lets explore a little using the psql commandline utility. Open a new terminal or command prompt (leave the terminal with your Python prompt running) and issue the following commands:
$ psql -U gsshapy -d gsshapy_tutorial
Enter the password if prompted, which should be “pass” if you set up the database using the credentials in the last tutorial. You should now have an SQL prompt to your database. Issue the following command:
gsshapy_tutorial=> \dt List of relations Schema | Name | Type | Owner --------+------------------------------+-------+--------- public | cif_bcs_points | table | gsshapy public | cif_breakpoint | table | gsshapy public | cif_channel_input_files | table | gsshapy public | cif_culverts | table | gsshapy public | cif_links | table | gsshapy public | cif_nodes | table | gsshapy public | cif_reservoir_points | table | gsshapy public | cif_reservoirs | table | gsshapy public | cif_trapezoid | table | gsshapy public | cif_upstream_links | table | gsshapy public | cif_weirs | table | gsshapy ... public | prj_project_cards | table | gsshapy public | prj_project_files | table | gsshapy ... public | wms_dataset_files | table | gsshapy public | wms_dataset_rasters | table | gsshapy (61 rows)
This will list all the tables in the gsshapy_tutorial database. If the database was initialized correctly, you should
see a list of 60+ or so tables. The three letter prefix on the filename is associted with the file extension or in some
cases the type of file. For example, there are two tables used to store project files:
prj_project_cards. The project file table is not very interesting, so, we will query the
This can be done as follows:
gsshapy_tutorial=> SELECT * FROM prj_project_cards; id | projectFileID | name | value ----+---------------+--------------------+---------------------------------------------------- 1 | 1 | WMS | WMS 9.1 (64-Bit) 2 | 1 | WATERSHED_MASK | "parkcity.msk" 3 | 1 | PROJECT_PATH | "" 4 | 1 | #LandSoil | "parkcity.lsf" 5 | 1 | #PROJECTION_FILE | "parkcity_prj.pro" 6 | 1 | NON_ORTHO_CHANNELS | 7 | 1 | FLINE | "parkcity.map" 8 | 1 | METRIC | 9 | 1 | GRIDSIZE | 90.000000 10 | 1 | ROWS | 72 11 | 1 | COLS | 67 ... 37 | 1 | IN_HYD_LOCATION | "parkcity.ihl" 38 | 1 | OUT_HYD_LOCATION | "parkcity.ohl" 39 | 1 | CHAN_DEPTH | "parkcity.cdp" (39 rows)
Each record in the
prj_project_cards table stores the name and value of one card in the project file.
prj_project_cards table is related to the
prj_project_files table through a foreign
key column called
projectFileID (the column with all 1’s).
Execute the following command to quit the psql program:
Querying Using GsshaPy Objects¶
The ProjectCard table class maps to the
prj_project_cards table and the ProjectFile class maps to the
prj_project_files table. Instances of these classes can be used to query the database. Suppose we need to retrieve
all of the project cards from a project file. We can use SQLAlchemy session object and SQL expression language to do
this. Back in the Python console, execute the following:
>>> from gsshapy.orm import ProjectCard >>> cards = session.query(ProjectCard).all() >>> for card in cards: ... print card ...
As in the previous tutorial, the query returns a list of
gsshapy.orm.ProjectCard objects that represent the
records in the
prj_project_cards table. The
gsshapy.orm.ProjectCard class also has a relationship property
called projectFile that maps to the associated
gsshapy.orm.ProjectFile class. If we wanted to ensure that we
only queried for project cards that belong to the project file we read in during the first exercise, we could use the
filter() method of the
>>> cards = session.query(ProjectCard).filter(ProjectCard.projectFile == projectFile).all() >>> for card in cards: ... print card ...
The result is the same as before, because we only have one project file read into the database. As illustrated in the previous tutorial, we could also use the relationship properties to issue the queries to the database:
>>> cards = projectFile.projectCards >>> for card in cards: ... print card ...
The later two methods are equivilent. This is only a micro tasting of the power of the SQLAlchemy query language. Please review the SQLAlchemy documentation for a more detailed explanation of querying.