sqlalchemy tutorial example not working

Question

I'm trying to work my way through the example given in the sqlalchemy tutorial but I'm getting errors. As far as I can tell I'm following the example to the letter. Here's the code that I have from it so far. It fails when I .first() after I query the DB.

I'm on version 0.7.5 and python 2.7

from sqlalchemy                 import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy                 import create_engine
from sqlalchemy.orm             import sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)
engine.execute("select 1").scalar() # works fine

Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
       return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

jeff_user = User("jeff", "Jeff", "foo")
session.add(jeff_user)

our_user = session.query(User).filter_by(name='jeff').first() # fails here

jeff_user.password = "foobar"

session.add_all([
                User('wendy', 'Wendy Williams', 'foobar'),
                User('mary', 'Mary Contrary', 'xxg527'),
                User('fred', 'Fred Flinstone', 'blah')])

session.dirty # shows nothing as dirty
session.new   # shows nothing as new

Here is the error message

2012-02-25 17:48:33,879 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-02-25 17:48:33,886 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
2012-02-25 17:48:33,886 INFO sqlalchemy.engine.base.Engine ('jeff', 'Jeff', 'foo')
2012-02-25 17:48:33,887 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "learning_sql.py", line 35, in <module>
    our_user = session.query(User).filter_by(name='ed').first() # fails here
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2024, in first
    ret = list(self[0:1])
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 1918, in __getitem__
    return list(res)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2092, in __iter__
    self.session._autoflush()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 983, in _autoflush
    self.flush()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 1559, in flush
    self._flush(objects)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 1630, in _flush
    flush_context.execute()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 331, in execute
    rec.execute(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 475, in execute
    uow
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 2291, in _save_obj
    execute(statement, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1405, in execute
    params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1538, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1646, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1639, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 330, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) no such table: users u'INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)' ('jeff', 'Jeff', 'foo')

The expected print out is this

>>> our_user = session.query(User).filter_by(name='ed').first() 
BEGIN (implicit)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('ed', 'Ed Jones', 'edspassword')
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
FROM users
WHERE users.name = ?
 LIMIT 1 OFFSET 0
('ed',)
>>> our_user
<User('ed','Ed Jones', 'edspassword')>

For some reason my code is causing a ROLLBACK when it should be SELECT.

Answer 1

You are getting this error because database is missing the table structure (you apparently missed this line in the tutorial, it's in the Declare Mapping subsection). Create the schema by adding following just after your table/model definitions:

Class User(Base)
    ...

# Initialize database schema (create tables)
Base.metadata.create_all(engine)