how begin using alembic with existing database and autogenerate?

[sqlalchemy-bot]

Migrated issue, originally created by Anonymous

Sorry that I write here.

If I use existing database with tables and want add some tables alembic works. But if I add --autogenerate option and add new table that does not exist in database alembic trying remove all tables which he don't know and add my new one :)
How I can fix this?

Maybe in alembic add option like "scan database" or "scan metadata and database" ?
And after this it generate base revision.

Thanks!

[sqlalchemy-bot]

Changes by Anonymous:

• edited description

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

You mean here that your app doesn't have a Table() object for every actual table in the DB, right ? We could add a flag to configure() that disregards table drops. But that means it won't detect table drops at all. Why not have your metadata do a reflect_all() first (that would be exactly what you mean by "scan database". --autogenerate certainly "scans" but just assumes the MetaData you give it is the state you want)? This also would render detection of table drops useless but at least already works.

[sqlalchemy-bot]

Anonymous wrote:

I try this steps:

1. alembic init alembic
2. change sqlalchemy.url in alembic.ini
3. set my db.metadata in env.py
4. alembic revision --autogenerate -m "Base revision"

And I get this errors:
http://pastebin.com/2H6j57PL

How I can begin use alembic with existing database?

All tables in database reflected in models (and db.metadata).

Thanks!

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

that's a bug in the reflection. Recreate as a test script:

#!python
from sqlalchemy import *
engine = create_engine("<db>")
engine.execute("CREATE TABLE advertisement <YOUR TABLE DEF HERE> ")
m = MetaData()
t = Table('advertisement', m, autoload=True, autoload_with=engine)

Fill in , ensure you get that same stack trace, then post it to http://www.sqlalchemy.org/trac/newticket.

[sqlalchemy-bot]

Anonymous wrote:

Ok, I drop table advertisement in my db (with cascade 3 tables that depend on it).

I don't give same stack trace...
if I do your steps I don't get error.

t = Table('advertisement', m, autoload=True, autoload_with=engine)

/Volumes/storage/code/venv/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py:47: SAWarning: Did not recognize type '"TYPE_ADVERTISEMENT_PUBLISH_STATUS"' of column 'publish_status'
ret = fn(self, con, *args, **kw)

/Volumes/storage/code/venv/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py:47: SAWarning: Did not recognize type '"TYPE_SEX"' of column 'sex'
ret = fn(self, con, *args, **kw)

/Volumes/storage/code/venv/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py:47: SAWarning: Did not recognize type '"TYPE_RELATIONSHIP"' of column 'relationship_status'
ret = fn(self, con, *args, **kw)

/Volumes/storage/code/venv/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py:47: SAWarning: Skipped unsupported reflection of expression-based index user_email_lower_idx
ret = fn(self, con, *args, **kw)

/Volumes/storage/code/venv/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py:47: SAWarning: Skipped unsupported reflection of expression-based index user_username_lower_idx
ret = fn(self, con, *args, **kw)

and this t is:

t

Table('advertisement', MetaData(bind=None), Column(u'id', INTEGER(), table=, primary_key=True, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.expression._TextClause object at 0x110b0f350>, for_update=False)), Column(u'user_id', BIGINT(), ForeignKey(u'user.id'), table=, nullable=False), Column(u'category_id', INTEGER(), ForeignKey(u'advertisement_category.id'), table=), Column(u'has_image', BOOLEAN(), table=, server_default=DefaultClause(<sqlalchemy.sql.expression._TextClause object at 0x110b1a390>, for_update=False)), Column(u'title', VARCHAR(length=33), table=, nullable=False), Column(u'announcement', VARCHAR(length=77), table=, nullable=False), Column(u'text', TEXT(), table=), Column(u'location', VARCHAR(length=100), table=), Column(u'comments_count', INTEGER(), table=), Column(u'views_count', INTEGER(), table=), Column(u'publish_status', NullType(), table=, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.expression._TextClause object at 0x110b1af50>, for_update=False)), Column(u'rate', INTEGER(), table=), Column(u'time_create', TIMESTAMP(), table=, nullable=False), Column(u'time_expire', TIMESTAMP(), table=, server_default=DefaultClause(<sqlalchemy.sql.expression._TextClause object at 0x110b1b3d0>, for_update=False)), schema=None)

my table from models:

db.metadata.tables.get('advertisement')

Table('advertisement', MetaData(bind=None), Column('id', Integer(), table=, primary_key=True, nullable=False), Column('user_id', BigInteger(), ForeignKey('user.id'), table=, nullable=False), Column('category_id', Integer(), ForeignKey('advertisement_category.id'), table=), Column('has_image', Boolean(), table=, default=ColumnDefault(False), server_default=DefaultClause(<sqlalchemy.sql.expression._TextClause object at 0x110967310>, for_update=False)), Column('title', String(length=33), table=, nullable=False), Column('announcement', String(length=77), table=, nullable=False), Column('text', Text(), table=), Column('location', String(length=100), table=), Column('comments_count', Integer(), table=, default=ColumnDefault(<function at 0x11095eb90>)), Column('views_count', Integer(), table=), Column('publish_status', Enum('declined', 'approved', 'pending'), table=, nullable=False, default=ColumnDefault('pending'), server_default=DefaultClause('pending', for_update=False)), Column('rate', Integer(), table=), Column('time_create', DateTime(), table=, nullable=False), Column('time_expire', DateTime(), table=, default=ColumnDefault(<sqlalchemy.sql.functions.now at 0x11096a150; now>), server_default=DefaultClause(<sqlalchemy.sql.expression._TextClause object at 0x11096a0d0>, for_update=False)), schema=None)

I try my steps again:
http://pastebin.com/RLduMvxi

and it works.

In my alembic revision 7de9b9f9817.py I see that it create only 7 tables (instead of my 82 in db).

But if I drop table advertisement, and create it via create_all()
(and it create other advertisement depending tables).
then I get again my stack trace from http://pastebin.com/2H6j57PL

I don't know how detect table that send this error.

Thanks.

[sqlalchemy-bot]

Anonymous wrote:

I found table that give that error.

http://pastebin.com/TsbYbXZa

If I uncomment this line:

#advertisement_id = db.Column(db.Integer, db.ForeignKey('advertisement.id'), nullable=False)

that I get error like in http://pastebin.com/2H6j57PL

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

can you move this into a .py script I can run ? do i just create those three tables then try to reflect? sorry I can't see what's happening so I need reproducing code.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: feature
• added labels: bug

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: easy

[sqlalchemy-bot]

Anonymous wrote:

I remove all tables in my project and find bug that if I move models from myapp.mods.mytestmod.models.py to one file, everything works fine, but why?

I upload my files http://www.mediafire.com/?p9v7wwjkz81926o

I don't know why it raise error in this structure.

I do in python console:

1. from myapp import init_db
2. init_db()

in bash:
3) alembic revision --autogenerate -m "test"

and it raise
sqlalchemy.exc.ArgumentError: Trying to redefine primary-key column 'id' as a non-primary-key column on table 'table1'

Thanks!

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

found that, it's in [[https://bitbucket.org/zzzeek/alembic/changeset/6741cf359bcb7ad9a48fc50cf64ea024671d7788|6741cf359bcb7ad9a48fc50cf64ea024671d7788]]. if we need to talk more about workflow maybe we can take that onto the list until we figure out if a feature needs to be added to alembic.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed