s = users.select()
rs = s.execute()

Like INSERT statements, SELECT statements are also done by creating a statement object and calling its execute() method. This particular statement is the basic "SELECT * FROM users" with noWHERE clause. Later on we'll see how to do WHERE clauses, or only return data from certain columns, or JOIN two (or more) tables together.

Calling execute() on a SELECT statement object will return a result set, which has fetchone() and fetchall() methods. As you'd expect, fetchone() returns a single row, while fetchall() returns a list of rows. The rows returned aren't simple tuples or dictionaries, but intelligent row objects, as can be seen below:

row = rs.fetchone()
print 'Id:', row[0]
print 'Name:', row['name']
print 'Age:', row.age
print 'Password:', row[users.c.password]

Here we see some of the various ways you can access the data in a row object. First, you can pretend it's a tuple and access its columns by position. SQLAlchemy guarantees that the column order returned from a "SELECT * FROM (table)" statement will be the same as the order in which the columns were declared in that table, so here we know that row[0] will be the user_id column. We can also access the row as if it were a dictionary (row['name']). Next, my favorite: SQLAlchemy lets us access the columns as if they were attributes of the row object. (Some simple __getattr__() magic behind the scenes makes this work). And finally, we can even use the actual Column objects themselves as keys to lookup results from a row. You probably won't use this very often, but it can be extremely useful in some circumstances.

for row in rs:
    print row.name, 'is', row.age, 'years old'

Finally, we see that we can also iterate through the result set via a simple for loop. This is especially useful when you expect your SELECT query to return a huge result set that would be too large to load into memory: the for loop will only fetch one row at a time from the database.

Select Statements

Now let's take a little time to examine some of the various ways in which we can select rows from our database. There are lots of conditions you might want to put in the WHERE clause of a SELECTstatement, and SQLAlchemy makes most of those easy.

Copy the following code into "selectdemo.py":

from sqlalchemy import *

# Let's re-use the same database as before
db = create_engine('sqlite:///tutorial.db')

db.echo = True  # We want to see the SQL we're creating

metadata = BoundMetaData(db)

# The users table already exists, so no need to redefine it. Just
# load it from the database using the "autoload" feature.
users = Table('users', metadata, autoload=True)

def run(stmt):
    rs = stmt.execute()
    for row in rs:
        print row

# Most WHERE clauses can be constructed via normal comparisons
s = users.select(users.c.name == 'John')
run(s)
s = users.select(users.c.age < 40)
run(s)

# Python keywords like "and", "or", and "not" can't be overloaded, so
# SQLAlchemy uses functions instead
s = users.select(and_(users.c.age < 40, users.c.name != 'Mary'))
run(s)
s = users.select(or_(users.c.age < 40, users.c.name != 'Mary'))
run(s)
s = users.select(not_(users.c.name == 'Susan'))
run(s)

# Or you could use &, | and ~ -- but watch out for priority!
s = users.select((users.c.age < 40) & (users.c.name != 'Mary'))
run(s)
s = users.select((users.c.age < 40) | (users.c.name != 'Mary'))
run(s)
s = users.select(~(users.c.name == 'Susan'))
run(s)

# There's other functions too, such as "like", "startswith", "endswith"
s = users.select(users.c.name.startswith('M'))
run(s)
s = users.select(users.c.name.like('%a%'))
run(s)
s = users.select(users.c.name.endswith('n'))
run(s)

# The "in" and "between" operations are also available
s = users.select(users.c.age.between(30,39))
run(s)
# Extra underscore after "in" to avoid conflict with Python keyword
s = users.select(users.c.name.in_('Mary', 'Susan'))
run(s)

# If you want to call an SQL function, use "func"
s = users.select(func.substr(users.c.name, 2, 1) == 'a')
run(s)

# You don't have to call select() on a table; it's got a bare form
s = select([users], users.c.name != 'Carl')
run(s)
s = select([users.c.name, users.c.age], users.c.name != 'Carl')
run(s)

# This can be handy for things like count()
s = select([func.count(users.c.user_id)])
run(s)
# Here's how to do count(*)
s = select([func.count("*")], from_obj=[users])
run(s)

Most of this should be pretty self-explanatory, and should give you an idea of what's possible. A few notes:

# Or you could use &, | and ~ -- but watch out for priority!
s = users.select((users.c.age < 40) & (users.c.name != 'Mary'))
run(s)
s = users.select((users.c.age < 40) | (users.c.name != 'Mary'))
run(s)
s = users.select(~(users.c.name == 'Susan'))
run(s)

In Python, the & (and), | (or), and ~ (not) operators have a higher priority than comparison operators like == and !=. So if you want to use the &, | and ~ operators, you have to be careful to wrap the other clauses in parentheses. If you forget the parentheses, you'll be surprised by the results:

s = users.select(users.c.age < 40 & users.c.name != 'Mary')

will be interpreted as:

s = users.select(users.c.age < (40 & users.c.name) != 'Mary')

which will almost certainly not return the results you were expecting.