1.4.4.2 Selecting Data

The select() method is used to retrieve information from a database and looks like this:

select( columns,table,[as=None,][where=None,][order=None,][rest=None,][autoExecute=None,][fetchMode=None])

columns: A list or tuple of column names to select. Can be a string if only one column is being selected. If selecting from multiple tables, all column names should be in the form 'tableName.columnName'
table: A string containing the name of the table to select from or if selecting from multiple tables, a list or tuple of table names.
as: A list or tuple of aliases in the same order as columns, specified by None if no alias is to be used.
where: The WHERE clause as a properly encoded SQL string
order: The ORDER BY clause as a properly encoded SQL string
rest: Any other SQL to be placed at the end of the generated SQL string
autoExecute: If False the function returns the SQL string to perform the desired operations. If True the SQL is executed and the results converted and returned in the appropriate form. If not specified takes the value specifed in the cursor which by default is True
fetchMode: The format of the results returned. Can be 'dict' to return them as a tuple of dictionary objects, 'tuple' to return them as a tuple of tuples or 'dtuple' to return them as a tuple of dtuple objects which can be treated as a tuple or a dictionary. If not specified takes the value specifed in the cursor which by default is 'dtuple'
distinct=False: If distinct is True then only unique values matching the where clause are selected. Equivalant to the SQL DISTINCT keyword.

To select some information from a database using an SQL string you would use the following command:

SELECT column_name(s) FROM table_name

For example consider the table below:

# Table Person
+----------+-----------+---------+-------------+
| LastName | FirstName | Address | DateOfBirth |
+----------+-----------+---------+-------------+
| Smith    | John      | Bedford | 1980-01-01  |
+----------+-----------+---------+-------------+
| Doe      | John      | Oxford  | 1981-12-25  |
+----------+-----------+---------+-------------+

To retrieve a list of the surnames and dates of birth of all the people in the table you would use the following code:

rows = cursor.select(['LastName', 'DateOfBirth'], 'Person')

The result from this call would be a tuple of TupleDescriptor objects which can be treated as a tuple or a dictionary:

>>> for record in rows:
...    print record[0], record[1]
...    print record['LastName'], record['DateOfBirth']
...
Smith 1980-01-01
Smith 1980-01-01
Doe 1981-12-25
Doe 1981-12-25

Using the select() method, information you select from a field is automatically converted to the correct Python type. Integer fields return Integers, Date fields return datetime.date objects.

Note: If you are only selecting one field you can just specify the fieldname as a string 'LastName' rather than ['LastName'].

The example above selected every LastName and DateOfBirth field from the table. To limit the information selected you need to specify the where parameter in the same way you would for any SQL query.

>>> rows=cursor.select('LastName','Person',where="LastName='Smith'")
>>> for record in rows:
...    print record['LastName'], record['DateOfBirth']
...
'Smith'

Note: we had to specify the value Smith as properly encoded SQL. See the section Encoders and Decoders for information on how to encode these strings automatically.

More complex expressions can be built into where clauses. See a guide on SQL such as the one at www.w3schools.com for more information.

1.4.4.2.2 The order Parameter

You can specify the order in which the results are sorted using the order parameter. It is used as follows:

>>> for record in cursor.select('LastName', 'Person', order="'LastName'"):
...    print record['LastName']
...
'Doe'
'Smith'
>>> for record in cursor.select('LastName', 'Person', order="LastName DESC"):
...    print record['LastName']
...
'Smith'
'Doe'

Note that by placing the word DESC after the column to order by, the order is reversed.

You can place a number of Columns after each other. For example order="LastName DESC DateOfBirth" could be used to order the results in decending order by LastName and if any results have the same last name, order them by DateOfBirth.

1.4.4.2.3 The rest Parameter

If you want to specify any other SQL on the end of the statement you can place it in the rest parameter.

Warning: where, order and rest fields take user-formatted strings as input. It is still possible that the SQL commands contained in these strings will have different results on different databases so please be careful with the SQL you use.

1.4.4.2.4 Disabling autoExecute

If you do not want the SQL to actually be executed you can set the autoExecute parameter of the select() method to False. You can then manually execute it using cursor.execute().

>>> sql = cursor.select(['LastName', 'DateOfBirth'], 'Person', autoExecute=False)
>>> sql
'SELECT LastName, DateOfBirth FROM Person'
>>> cursor.execute(sql)
>>> cursor.fetchall()
(('Smith','1980-01-01'),('Doe','1981-12-25'))

1.4.4.2.5 Using Aliases

Many SQL databases support the use of aliases. An alias is when you select certain data from a table under a different name. The SQL you would use might be:

SELECT LastName AS Surname FROM Person

With the select() method this would be:

>>> rows = cursor.select('LastName', 'Person', as='Surname')
>>> rows[0]['Surname']
'Smith'

In this example we accessed the value of the LastName column as if it was called Surname.

To specify more than one alias you specify the columns as a list or tuple and specify the aliases as a list or tuple with the same number of aliases in the same order. If you don't want to specify aliases for every coulmn use None in place of the alias name.

For example:

>>> rows = cursor.select(['LastName','Address','DateOfBirth'], 'Person', as=['Surname', None, 'Birth'])
>>> rows[0]['Surname']
'Smith'
>>> rows[0]['Address']
'Bedford'
>>> rows[0]['Birth']
'1980-01-01'

1.4.4.2.6 Using Joins

The select() allows you to select information from multiple tables. In order to do this you must specify the tables you wish to select from as a list or tuple and use the fully qualified column name for each table you want to column you want to select from.

For example:

rows = cursor.select(
    ['table1.LastName', 'table2.Surname'],
    ['table1','table2'], 
    where="table1.Surname = table2.Surname"
)
print rows[0]['table2.Surname']
'Smith'

You can also combine joins with aliases by specifying each column as a tuple ('tableName.columName', 'alias') as described in that section above on aliases.

See About this document... for information on suggesting changes.