"no such table" OperationalError despite CREATE TABLE being issued

[James]

Hi, this concerns running functional tests in TurboGears2, using SA
0.5.1.

As part of the functional test set up, all the model's tables are
CREATEd, and DROPped as part of the tear down.

However, despite seeing the expected sequence of CREATE, 1st test,
DROP, CREATE, 2nd test, DROP, the second test fails with "no such
table" errors.

Condensed INFO level logging of sqlalchemy.engine:
...
INFO PRAGMA table_info("tg_user")
INFO ()
CREATE TABLE tg_user (
user_id INTEGER NOT NULL,
user_name VARCHAR(16) NOT NULL,
email_address VARCHAR(255) NOT NULL,
display_name VARCHAR(255),
password VARCHAR(80),
created TIMESTAMP,
PRIMARY KEY (user_id),
UNIQUE (user_name),
UNIQUE (email_address)
)
INFO ()
INFO COMMIT
...
INFO BEGIN
[DB interactions for first test]
INFO COMMIT
...
INFO PRAGMA table_info("tg_user")
INFO ()
...
DROP TABLE tg_user
INFO ()
INFO COMMIT
...
INFO PRAGMA table_info("tg_user")
INFO ()
...
CREATE TABLE tg_user (
user_id INTEGER NOT NULL,
user_name VARCHAR(16) NOT NULL,
email_address VARCHAR(255) NOT NULL,
display_name VARCHAR(255),
password VARCHAR(80),
created TIMESTAMP,
PRIMARY KEY (user_id),
UNIQUE (user_name),
UNIQUE (email_address)
)
INFO ()
INFO COMMIT
...

Result in this stack trace when trying to interact with the tg_user
table during the second test:
Traceback (most recent call last):
...
File "/Users/james/virtual/unit_tests/UnitTests/unit_tests/
websetup.py", line 54, in setup_app
model.DBSession.flush()
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/orm/scoping.py", line 121, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/orm/session.py", line 1347, in
flush
self._flush(objects)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/orm/session.py", line 1417, in
_flush
flush_context.execute()
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/orm/unitofwork.py", line 243, in
execute
UOWExecutor().execute(self, tasks)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/orm/unitofwork.py", line 706, in
execute
self.execute_save_steps(trans, task)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/orm/unitofwork.py", line 721, in
execute_save_steps
self.save_objects(trans, task)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/orm/unitofwork.py", line 712, in
save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/orm/mapper.py", line 1346, in
_save_obj
c = connection.execute(statement.values(value_params), params)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/engine/base.py", line 824, in
execute
return Connection.executors[c](self, object, multiparams, params)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/engine/base.py", line 874, in
_execute_clauseelement
return self.__execute_context(context)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/engine/base.py", line 896, in
__execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/engine/base.py", line 950, in
_cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File "/Users/james/virtual/unit_tests/lib/python2.5/site-packages/
SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/engine/base.py", line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
OperationalError: (OperationalError) no such table: tg_user u'INSERT
INTO tg_user (user_name, email_address, display_name, password,
created) VALUES (?, ?, ?, ?, ?)' [u'manager',
u'man...@somedomain.com', u'Example manager',
u'276e4c1a24e5c8005f71dc8a2a86912347355f4dae87891e37ccea9c5fdc2753c49549168c8d558e',
'2009-11-07 10:51:40.039211']


Can anyone see why a new created table wouldn't be found by
SQLAlchemy? What more information could I give that would be useful?

This is using sqlalchemy.url = sqlite:///:memory:

Thanks!
James

[Michael Bayer]

On Nov 7, 2009, at 6:06 AM, James wrote:

>
> Can anyone see why a new created table wouldn't be found by
> SQLAlchemy? What more information could I give that would be useful?
>
> This is using sqlalchemy.url = sqlite:///:memory:

the most obvious cause would be that two different engines are being
used, since sqlite memory databases are local only to a single
connection.

[James]

On Nov 7, 5:28 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> the most obvious cause would be that two different engines are being  
> used, since sqlite memory databases are local only to a single  
> connection.

Thanks for this pointer Michael!

I tried putting extra logging in the _cursor_execute method of
sqlalchemy.engine.base.py to see which connection is being used for
various statements, and it seems that different
sqlalchemy.engine.base.Connection objects are being used even during
my first test, which works as expects.

I think I must be looking in the wrong place to see which connection
is being used for each statement; can you tell me the best way to get
my hands on the current connection? I was using context.connection in
the _cursor_execute method...

James

[Michael Bayer]

i would think you need to get a hold on where "create_engine" is being
called twice. if your connection is sqlite "memory", there should
only be one engine in use throughout the appication. the engine with
sqlite will use a "thread local" connection strategy such that each
call to connect() on the same engine produces the same connection
within the same thread.


>
> James
> >