Autogenerate -> unknown table popping up in sqlalchemy.exc.NoSuchTableError

[Wavemaker]

Hello All,

I'm trying to use Alembic for versioning my Camelot (http://www.python-camelot.com) schema, which is essentially sqlalchemy on the database side. When using autogenerate I get a NoSuchTableError. The odd thing is that I get this error for a table "auth_user" that cannot find in my schema:

[snip]
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\util\langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\sql\schema.py", line 432, in __new__
    table._init(name, metadata, *args, **kw)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\sql\schema.py", line 513, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\sql\schema.py", line 528, in _autoload
    self, include_columns, exclude_columns
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\engine\base.py", line 1481, in run_callable
    return callable_(self, *args, **kwargs)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\engine\default.py", line 364, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\engine\reflection.py", line 578, in reflecttable
    raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError: auth_user

Note: line numbers may be slightly different from the official alembic/sqlalchemy releases as I inserted some debug print/log statements. If helpful I can of course resort to the stock release.

It doesn't matter whether I use an existing or a freshly initialized database. I was trying to follow the code to see where Alembic comes up with this additional table, but I get lost. Can somebody tell me some hints on how Alembic can come up with tables not directly defined in my schema? I then hope I can figure whether I need that table at all. Another question however is why would alembic not handle the situation and simple create the new table, even if I am unaware that I need it?

Thanks!

[Mike Bayer]

not really sure, that trace is just within SQLAlchemy and I can't see where Alembic is attempting to locate a table of that name.    Would need to at least see the stack trace going up into alembic's autogenerate logic.

Alembic shouldn't be trying to reflect a table of a certain name unless it detected that name when it asked the database for all table names.

Things to suspect are, casing problems, e.g. if your database has something like Auth_User on it, I see this is windows, if this is MySQL (is it?) the casing behavior changes based on operating system.

It doesn't matter whether I use an existing or a freshly initialized database. I was trying to follow the code to see where Alembic comes up with this additional table, but I get lost. Can somebody tell me some hints on how Alembic can come up with tables not directly defined in my schema?

it really can't.   Share your env.py as well.

I then hope I can figure whether I need that table at all. Another question however is why would alembic not handle the situation and simple create the new table, even if I am unaware that I need it?

Thanks!

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Wavemaker]

Well, upon digging further myself, I noticed that was an old foreign key in my Pictures table referring to the non-existant auth_user table:
     sql: "last_changed_user_id" integer NOT NULL REFERENCES "auth_user" ("id")
This was not referenced anymore in the model. So, I guess that in order to use alembic properly I should not have manually deleted that table (in fact I did that before starting to use alembic), but to remove both the table and the foreign key in the model and then let alembic apply both changes to the database in one go. Correct?

For reference purposes still find here the complete stack trace without having the solution applied yet. Note again that I inserted some debug statements here and there in the sqlalchemy/alembic code, so line numbers may deviate.

Traceback (most recent call last):
  File "virtualenv\Scripts\alembic-script.py", line 11, in <module>
    load_entry_point('alembic==0.7.6', 'console_scripts', 'alembic')()
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\config.py", line 439, in main
    CommandLine(prog=prog).main(argv=argv)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\config.py", line 433, in main
    self.run_cmd(cfg, options)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\config.py", line 416, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\command.py", line 113, in revision
    script.run_env()
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\script.py", line 390, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\util.py", line 243, in load_python_file
    module = load_module_py(module_id, path)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "alembic\env.py", line 99, in <module>
    run_migrations_online()
  File "alembic\env.py", line 92, in run_migrations_online
    context.run_migrations()
  File "<string>", line 7, in run_migrations
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\environment.py", line 738, in run_migrations
    self.get_context().run_migrations(**kw)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\migration.py", line 300, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\command.py", line 95, in retrieve_migrations
    autogen._produce_migration_diffs(context, template_args, imports)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\autogenerate\api.py", line 154, in _produce_migration_diffs
    autogen_context, object_filters, include_schemas)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\autogenerate\api.py", line 256, in _produce_net_changes
    inspector, metadata, diffs, autogen_context)
  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\autogenerate\compare.py", line 84, in _compare_tables
    inspector.reflecttable(t, None)

  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc

hemy\engine\reflection.py", line 590, in reflecttable
    exclude_columns, reflection_options)

  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc

hemy\engine\reflection.py", line 720, in _reflect_fk
    **reflection_options

  File "F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc

hemy\sql\schema.py", line 441, in __new__
    metadata._remove_table(name, schema)

[Mike Bayer]

On 5/31/15 7:00 PM, Wavemaker wrote:

Well, upon digging further myself, I noticed that was an old foreign key in my Pictures table referring to the non-existant auth_user table:
     sql: "last_changed_user_id" integer NOT NULL REFERENCES "auth_user" ("id")
This was not referenced anymore in the model. So, I guess that in order to use alembic properly I should not have manually deleted that table (in fact I did that before starting to use alembic), but to remove both the table and the foreign key in the model and then let alembic apply both changes to the database in one go. Correct?

that doesn't make too much sense because a relational database can't have a FOREIGN KEY to a table that doesn't exist.     *unless* this is SQLite, which is just an odd case.   feel free to manually delete whatever tables you want, you just need to make sure you keep track of what's going on when you point a schema reflection tool at that database at some later point.

[Wavemaker]

Well, it was sqlite! Currently moving to postgresql principally for other reasons. Thanks anyway for your response!

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsub...@googlegroups.com.