ally, let's take a little bit of a closer look at the bare form of select():
# 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)
Remember that the first argument to the bare form of select() is a list. If you forget and pass it a table, you'll get a TypeError complaining about iteration over a non-sequence.
# This can be handy for things like count()
s = select([func.count(users.c.user_id)])
run(s)
When there's a table involved in the select() call (e.g., when you're counting the occurrences of a single column, such as users.c.user_id), select() knows what to do. But if you're trying to do something like a COUNT(*), the Select object won't be able to guess which table you want to select from unless you explicitly pass the from_obj parameter, like so:
# Here's how to do count(*)
s = select([func.count("*")], from_obj=[users])
run(s)
Note that from_obj expects a list, just like the "what to select" parameter does. Here, too, if you forget and pass a bare table, you'll get a TypeError about iteration over a non-sequence.
Joins
At this point, you're probably wondering about using multiple tables in a single select() statement. Wonder no more. Copy the following into "joindemo.py":
from sqlalchemy import *
db = create_engine('sqlite:///joindemo.db')
db.echo = True
metadata = BoundMetaData(db)
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('name', String(40)),
Column('age', Integer),
)
users.create()
emails = Table('emails', metadata,
Column('email_id', Integer, primary_key=True),
Column('address', String),
Column('user_id', Integer, ForeignKey('users.user_id')),
)
emails.create()
i = users.insert()
i.execute(
{'name': 'Mary', 'age': 30},
{'name': 'John', 'age': 42},
{'name': 'Susan', 'age': 57},
{'name': 'Carl', 'age': 33}
)
i = emails.insert()
i.execute(
# There's a better way to do this, but we haven't gotten there yet
{'address': 'mary@example.com', 'user_id': 1},
{'address': 'john@nowhere.net', 'user_id': 2},
{'address': 'john@example.org', 'user_id': 2},
{'address': 'carl@nospam.net', 'user_id': 4},
)
def run(stmt):
rs = stmt.execute()
for row in rs:
print row
# This will return more results than you are probably expecting.
s = select([users, emails])
run(s)
# The reason is because you specified no WHERE clause, so a full join was
# performed, which returns every possible combination of records from
# tables A and B. With an appropriate WHERE clause, you'll get the
# restricted record set you really wanted.
s = select([users, emails], emails.c.user_id == users.c.user_id)
run(s)
# If you're interested in only a few columns, then specify them explicitly
s = select([users.c.name, emails.c.address],
emails.c.user_id == users.c.user_id)
run(s)
# There are also "smart" join objects that can figure out the correct join
# conditions based on the tables' foreign keys
s = join(users, emails).select()
run(s)
# If you want all the users, whether or not they have an email address,
# then you want an "outer" join.
s = outerjoin(users, emails).select()
run(s)
# Order of outer joins is important! Default is a "left outer join", which
# means "all records from the left-hand table, plus their corresponding
# values from the right-hand table, if any". Notice how this time, Susan's
# name will *not* appear