/ ITC help system
/ ITC homepage
/ Site map
/ NM Tech homepage
TCC Application Seminars: SQLAlchemy
SQLAlchemy is an object-oriented front end that works with SQL
The original science: The Audubon Christmas Bird Count
Side hunts of the late 1800s: My team can kill more birds than your team!
Frank Chapman, Audubon ornithologist, 1900 (Year 1): Let's count them instead.
Methodology: count all the birds inside a 15-mile-diameter circle in
one day between Dec. 15 and Jan. 5.
Outputs: gross population trends; fun.
of circles in Dec. 1966.
The circle history application
- Circle: One area, usually counted in many years
- Effort: One area counted in one year
- Census: How many of one kind of bird
Entity-relationship model, part I
Building your schema in SQLAlchemy
- Highly object-oriented and Pythonic.
Specify the schema, or use “reflection” to operate with an
Inside the CBCData class:
instance keeps track of all the table definitions.
The Table constructor builds the schema for one table.
You write a class for each table.
- Each instance represents one row.
- Later steps connect the class to its table.
Note the primary_key=True argument.
- Multi-column primary key.
The nullable-False attribute: do not allow null
The .__cmp__() method specifies how a list of
instances will be sorted.
A mapped class like Circle is a good place to put
other handy functions related to the instance, e.g.,
schema.ForeignKeyConstraint declares a relation to
the circles table.
Object-relational mapping, part I
orm.mapper() connects a class to a table.
The properties dictionary adds new attributes you can use
to find related records.
Example: for any Effort instance e, you can
for c in e.census:
# Sets c to each Census instance for a given effort
Example: for any Census instance c,
c.effort is the related Effort instance.
Writing queries, part I
Entity-relationship model, part II: factoring the ERD
There is a many-to-many relationship between regions and circles.
- Utah has many circles.
The Posey County, Indiana circle is also in Illinois and Kentucky.
Always insert a secondary table between many-to-many relations.
- Often just a pair of primary keys (P1, P2)
In this case, the secondary table has an additional column
reg_pos that specifies the relative position of the region
For &ldqueo;IN-IL-KY: Posey County”, IN has a reg_pos
of 0, IL is 1, and KY is 2.
This matters because the Posey County circle is published
with the other Indiana counts, not in Illinois or Kentucky.
The revised ERD.
Writing queries, part II
Find all circles that are in a given region.
The .join() method finds all the circle and
cir_reg rows that have the same (lat, lon) keys
and builds a new row that has all the columns from the
circle table and all the columns from the
cir_reg table: (lat, lon, water, odd, cir_name,
The .filter() method selects only those that have the
given reg_code value.
The .order_by() method sorts the resulting rows by
The results are Circle instances because that was the
table originally queried.
Find all circles in a given primary region.
The .filter_by(reg_pos=0) method selects only circles
for which the given region is the first region.
A triple join example.
A reflection example
See also: TCC free classes: Applications seminars
New Mexico Tech Information Technology and Communications: Help System
New Mexico Tech Information Technology and Communications Computer Center
To report a problem: File a ticket
Send email to the User consultant
on duty or call them at 575-835-5437
New Mexico Tech
John Shipman, email@example.com
Last updated: 2013/03/25 21:57:43 UT