SQL Course (Review)

Lesson 2	Project 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,
tf_user.id AS tf_user_id
FROM tf_user
JOIN user_group ON tf_user.id = user_group.user_id
JOIN tf_group ON tf_group.id = user_group.group_id
JOIN group_permission ON tf_group.id = group_permission.group_id
JOIN tf_permission ON tf_permission.id =
... group_permission.permission_id
WHERE tf_permission.permission_name = ? ORDER BY tf_user.oid
SQLAlchemy

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 Table.select( ) 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
...
(u'123',)
(u'456',)
(u'789',)

To select all columns from the product_table, you would use the Table.select( ) method:

>>> stmt = product_table.select()
>>> 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"))