Efrin Efrin - 3 months ago 52
Python Question

Alembic - sqlalchemy does not detect existing tables

I've asked a question (Alembic - sqlalchemy initial migration) on how to detect tables by using

target_metadata = Base.metadata


for

alembic revision --autogenerate -m "initial migration"


After I've imported my models to env.py file it seemed to work fine but it does not detect actually existing tables so it creates a migration file with all tables, for example:

def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('Brand',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.Column('slug', sa.String(), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('date_updated', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'),
schema='Products'
)

def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_table('ProductFile', schema='Products')


I've tried:

alembic stamp head


but after running that and running autogenerate command the system generates all models once again.

from project.apps.users.models import *
from project.apps.orders.models import *
from project.apps.products.models import *

from project.base import Base, metadata

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
target_metadata = Base.metadata


How do I avoid that problem?

Edit:

ENV.py:

https://gist.github.com/pypetey/bb65807ce773d8baeaf1


I dropped the db and ran a migration

(env) D:\projekty\test>alembic revision --autogenerate
INFO [alembic.migration] Context impl MSSQLImpl.
INFO [alembic.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table u'Users.Country'
INFO [alembic.autogenerate.compare] Detected added table u'Products.Brand'
INFO [alembic.autogenerate.compare] Detected added table u'Users.User'
INFO [alembic.autogenerate.compare] Detected added table u'Products.Product'
INFO [alembic.autogenerate.compare] Detected added table u'Products.ProductFile
'
INFO [alembic.autogenerate.compare] Detected added table u'Orders.Order'
INFO [alembic.autogenerate.compare] Detected added table u'Products.Category'
INFO [alembic.autogenerate.compare] Detected added table u'Products.Review'
INFO [alembic.autogenerate.compare] Detected added table u'Users.UserAddress'
INFO [alembic.autogenerate.compare] Detected added table u'Orders.OrderItem'
INFO [alembic.autogenerate.compare] Detected added table u'Orders.OrderStatus'
Generating D:\projekty\test\alembic\versions\1c6337c144a7_.py ... done

(env) D:\projekty\test>alembic upgrade head
INFO [alembic.migration] Context impl MSSQLImpl.
INFO [alembic.migration] Will assume transactional DDL.
INFO [alembic.migration] Running upgrade None -> 1c6337c144a7, empty message

(env) D:\projekty\test>alembic revision --autogenerate
INFO [alembic.migration] Context impl MSSQLImpl.
INFO [alembic.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table u'Users.Country'
INFO [alembic.autogenerate.compare] Detected added table u'Products.Brand'
INFO [alembic.autogenerate.compare] Detected added table u'Users.User'
INFO [alembic.autogenerate.compare] Detected added table u'Products.Product'
INFO [alembic.autogenerate.compare] Detected added table u'Products.ProductFile
'
INFO [alembic.autogenerate.compare] Detected added table u'Orders.Order'
INFO [alembic.autogenerate.compare] Detected added table u'Products.Category'
INFO [alembic.autogenerate.compare] Detected added table u'Products.Review'
INFO [alembic.autogenerate.compare] Detected added table u'Users.UserAddress'
INFO [alembic.autogenerate.compare] Detected added table u'Orders.OrderItem'
INFO [alembic.autogenerate.compare] Detected added table u'Orders.OrderStatus'
Generating D:\projekty\test\alembic\versions\5abb204549f_.py ... done

Answer

I had this exact same issue - I don't know if it still affects you. For me, the problem was caused because the schema I was using was not the default - I think the same thing is happening for you, since you're using a "Products" schema. I posted an issue at:

https://bitbucket.org/zzzeek/alembic/issue/281/autogenerate-fails-to-detect-existing

And with a little bit of guidance, managed to resolve the problem by setting include_schemas=True in both run_migrations_* functions in the alembic/env.py module.

See the docs:

If True, autogenerate will scan across all schemas located by the SQLAlchemy get_schema_names() method, and include all differences in tables found across all those schemas. When using this option, you may want to also use the EnvironmentContext.configure.include_object option to specify a callable which can filter the tables/schemas that get included.