ould have run an INSERT SQL statement. In SQLAlchemy 0.2, you need an explicit session.save() call first, to associate the instance with one particular session. Read the above three paragaphs for more details.
session.delete(fred)
session.flush()
Now that we've looked at how to do INSERTs by creating new object instances, let's look at how to do DELETEs. DELETEs, like INSERTs, are done by calling a method on the Session object. Note that this does not delete the fred instance from your own code, it just flags it as "deleted" in the Session object's internal object tracker. The fred instance will still be around, accessible to your own code, until you run a "del fred" statement.
New in 0.2: In SQLAlchemy 0.1, the delete() method used to be called on the data class or on the mapper. In SQLAlchemy 0.2, you call delete() on the Session instance instead.
Transactions
If you want to control the database transactions yourself, instead of letting SQLAlchemy do it for you, you can obtain a Transaction object by calling session.create_transaction(). You can then callcommit() or rollback() on the Transaction object. This allows you to safely do things like the following:
transaction = session.create_transaction()
try:
# Do some work here that might fail
session.flush()
# Do some more work here that might fail
session.flush()
# Success, commit everything
transaction.commit()
except:
# Make sure the transaction is rolled back ...
transaction.rollback()
# ... then propagate the error upwards to be handled elsewhere
raise
Data mapping, continued
Remember how, in the joindemo.py tutorial, one of the comments said:
# There's a better way to do this, but we haven't gotten there yet
Well, now's the time to look at the better way to handle one-to-many and many-to-many relations. Copy the following into "mapper2.py":
from sqlalchemy import *
db = create_engine('sqlite:///joindemo.db')
db.echo = True
metadata = BoundMetaData(db)
users = Table('users', metadata, autoload=True)
emails = Table('emails', metadata, autoload=True)
session = create_session()
# Let's give our User and Email classes a little more smarts
class User(object):
def __init__(self, name=None, age=None, password=None):
self.name = name
self.age = age
self.password = password
def __repr__(self):
return self.name
class Email(object):
def __init__(self, address=None):
self.address = address
def __repr__(self):
return self.address
# Here we look at several alternate ways to do the same thing. Try
# running this program multiple times, enabling a different one of
# these code blocks each time.
# Let's look at several ways to do one-to-many relationships.
# We create the Email mapper first...
emailmapper = mapper(Email, emails)
# ... so that we can use it in the User mapper
usermapper = mapper(User, users, properties={
'emails': relation(emailmapper), # Here's where the magic happens
})
mary = session.query(User).get_by(name='Mary')
print mary.emails
# If we try to create multiple mappers associated with a single data
# class, we have to specify which one is the "primary" mapper associated
# with the class. Since we're demonstrating how to create one-to-many
# relationships in multiple different ways, it's simplest to just clear
# all the mappers and start over.
clear_mappers()
# We could also use the data class instead of the mapper as the parameter
# to relation()
emailmapper = mapper(Email, emails)
usermapper = mapper(User, users, properties={
'em