RelationalDBDesign RelationalDBDesign

SQL Reporting  «Prev  Next»
Lesson 2Project review
Objective Prepare for the course project.

Complex Queries for use against a SQL Engine

When you create more complex queries for use against a SQL engine, you will find yourself using the different clauses and techniques we have outlined throughout this course. Just pulling rows from a table, as the case is with a SELECT * FROM... approach, is not going to be enough as you need to start working from larger, more diverse databases and tables.
That is where the different techniques come into play. You will need to consider the results to determine what the logical breaks are in the information.
Look for ways to group things, ways to eliminate some information from the results set, and so on.
Each of the sidebars below contains a quick review of the main topics we have covered in this course. Feel free to skip them if you remember the general concepts, and refer back to them if you need some guidance during the course project.

The power of SQLAlchemy to construct complex queries becomes clear when we compare the previous code to the SQL generated:
SELECT tf_user.first_name AS tf_user_first_name,
tf_user.last_name AS tf_user_last_name,
tf_user.created AS tf_user_created,
tf_user.user_name AS tf_user_user_name,
tf_user.password AS tf_user_password,
tf_user.email_address AS tf_user_email_address, AS tf_user_id
FROM tf_user
JOIN user_group ON = user_group.user_id
JOIN tf_group ON = user_group.group_id
JOIN group_permission ON = group_permission.group_id
JOIN tf_permission ON =
... group_permission.permission_id
WHERE tf_permission.permission_name = ? ORDER BY tf_user.oid

Database Design and Theory

Basic Query Construction

SQLAlchemy makes simple SQL queries easy to express, while also enabling the construction of quite complex queries in a straightforward manner. This section describes the basic building blocks of query construction in SQLAlchemy.

Select( ) function versus the select( ) Method

Like the DML statements INSERT, UPDATE, and DELETE, SELECT statements can be generated using either a function or a Table method. Unlike the DML statements, however, there is a minor difference in functionality between the select( ) function and the ) method. The select( ) function requires you to specify which columns you want in your result set. So, to select one column from the prod uct_table shown previously, you could use the select( ) function:

>>> stmt = select([product_table.c.sku])
>>> for row in stmt.execute():
... print row

To select all columns from the product_table, you would use the ) method:
>>> stmt =
>>> for row in stmt.execute():
... print row
(u'123', Decimal("12.34"))
(u'456', Decimal("22.12"))
(u'789', Decimal("41.44"))

To achieve the same result using the select( ) function, simply provide the table in lieu of columns:
>>> stmt = select([product_table])
>>> for row in stmt.execute():
... print row
(u'123', Decimal("12.34"))
(u'456', Decimal("22.12"))
(u'789', Decimal("41.44"))

  2. SubQuery Statement
  5. Functions