SQLAlchemy-migrate - Create and drop a column

[Thomas Mansencal]

Hi,

I'm using SQLAlchemy-migrate to evolve my db, it's working nicely to
upgrade the db but I'm failing to have it downgrade (I'm certainly
doing something wrong).

Here is my version script (Notice that I upgrade or downgrade only if
the column exists because the db, depending the release of my
application may or not have the column):

import sqlalchemy
from migrate import *

metadata = sqlalchemy.MetaData()

def upgrade(migrate_engine):
metadata.bind = migrate_engine
table = sqlalchemy.Table("Sets", metadata, autoload=True,
autoload_with=migrate_engine)

columnName = "previewImage"
if columnName not in table.columns:
column = sqlalchemy.Column(columnName, sqlalchemy.String)
column.create(table)

def downgrade(migrate_engine):
metadata.bind = migrate_engine
table = sqlalchemy.Table("Sets", metadata, autoload=True,
autoload_with=migrate_engine)

columnName = "previewImage"
if columnName in table.columns:
column = sqlalchemy.Column(columnName, sqlalchemy.String)
print table.columns
column.drop(table)

I'm using this command to test the version script:

python migrations/manage.py test --repository=migrations --
url=sqlite:///sIBL_Database.sqlite

And here is the error on downgrade:

Upgrading...
done
Downgrading...
['Sets.id', 'Sets.name', 'Sets.path', 'Sets.osStats',
'Sets.collection', 'Sets.title', 'Sets.author', 'Sets.link',
'Sets.icon', 'Sets.previewImage', 'Sets.backgroundImage',
'Sets.lightingImage', 'Sets.reflectionImage', 'Sets.location',
'Sets.latitude', 'Sets.longitude', 'Sets.date', 'Sets.time',
'Sets.comment']
Traceback (most recent call last):
File "migrations/manage.py", line 3, in <module>
main()
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/sqlalchemy_migrate-0.7.1-py2.7.egg/migrate/
versioning/shell.py", line 207, in main
ret = command_func(**kwargs)
File "<string>", line 2, in test
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/sqlalchemy_migrate-0.7.1-py2.7.egg/migrate/
versioning/util/__init__.py", line 159, in with_engine
return f(*a, **kw)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/sqlalchemy_migrate-0.7.1-py2.7.egg/migrate/
versioning/api.py", line 223, in test
script.run(engine, -1)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/sqlalchemy_migrate-0.7.1-py2.7.egg/migrate/
versioning/script/py.py", line 145, in run
script_func(engine)
File "migrations/versions/001_table_Sets_Column_previewImage.py",
line 23, in downgrade
column.drop(table)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/sqlalchemy_migrate-0.7.1-py2.7.egg/migrate/
changeset/schema.py", line 551, in drop
engine._run_visitor(visitorcallable, self, connection, **kwargs)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/engine/base.py", line 2178, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/engine/base.py", line 1857, in _run_visitor
**kwargs).traverse_single(element)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/sqlalchemy_migrate-0.7.1-py2.7.egg/migrate/
changeset/ansisql.py", line 57, in traverse_single
ret = super(AlterTableVisitor, self).traverse_single(elem)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/visitors.py", line 86, in traverse_single
return meth(obj, **kw)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/sqlalchemy_migrate-0.7.1-py2.7.egg/migrate/
changeset/databases/sqlite.py", line 92, in visit_column
super(SQLiteColumnDropper,self).visit_column(column)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/sqlalchemy_migrate-0.7.1-py2.7.egg/migrate/
changeset/databases/sqlite.py", line 55, in visit_column
self.recreate_table(table,column,delta)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/sqlalchemy_migrate-0.7.1-py2.7.egg/migrate/
changeset/databases/sqlite.py", line 42, in recreate_table
table.create()
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/schema.py", line 522, in create
checkfirst=checkfirst)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/engine/base.py", line 2178, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/engine/base.py", line 1857, in _run_visitor
**kwargs).traverse_single(element)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/visitors.py", line 86, in traverse_single
return meth(obj, **kw)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/engine/ddl.py", line 82, in visit_table
self.connection.execute(schema.CreateTable(table))
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/engine/base.py", line 1358, in execute
params)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/engine/base.py", line 1443, in _execute_ddl
compiled = ddl.compile(dialect=dialect)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/expression.py", line 1636, in compile
return self._compiler(dialect, bind=bind, **kw)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/schema.py", line 2752, in _compiler
return dialect.ddl_compiler(dialect, self, **kw)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/engine/base.py", line 699, in __init__
self.string = self.process(self.statement)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/engine/base.py", line 718, in process
return obj._compiler_dispatch(self, **kwargs)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/visitors.py", line 59, in _compiler_dispatch
return getter(visitor)(self, **kw)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/compiler.py", line 1278, in visit_create_table
const = self.create_table_constraints(table)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/compiler.py", line 1298, in create_table_constraints
for constraint in constraints
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/compiler.py", line 1296, in <genexpr>
return ", \n\t".join(p for p in
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/compiler.py", line 1304, in <genexpr>
not getattr(constraint, 'use_alter', False)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/engine/base.py", line 718, in process
return obj._compiler_dispatch(self, **kwargs)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/visitors.py", line 59, in _compiler_dispatch
return getter(visitor)(self, **kw)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/dialects/sqlite/base.py", line 380, in
visit_foreign_key_constraint
return super(SQLiteDDLCompiler,
self).visit_foreign_key_constraint(constraint)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/compiler.py", line 1428, in
visit_foreign_key_constraint
preparer.format_constraint(constraint)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/compiler.py", line 1721, in format_constraint
return self.quote(constraint.name, constraint.quote)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/compiler.py", line 1693, in quote
if self._requires_quotes(ident):
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/
python2.7/site-packages/SQLAlchemy-0.7.1-py2.7-macosx-10.3-fat.egg/
sqlalchemy/sql/compiler.py", line 1674, in _requires_quotes
lc_value = value.lower()
AttributeError: 'int' object has no attribute 'lower'

Here is my database and the migrations repository for testing purpose:
http://kelsolaar.hdrlabs.com/sIBL_GUI/Support/Others/sIBL_GUI_SQLAlchemy-migrate_001.zip

Cheers,

Thomas

[Nate Lowrie]

Thomas,

I see no one has responded yet so I'll take a stab at this. This is a
script that I just wrote recently for adding and dropping a column.
Hope this helps.

from sqlalchemy import *
from migrate import *

def upgrade(migrate_engine):
# Upgrade operations go here. Don't create your own engine; bind
migrate_engine
# to your metadata
meta = MetaData(migrate_engine)
PurchaseOrderLineItems = Table("purchase_order_line_items", meta,
Column("id", Integer, primary_key=True),
Column("type", String(10)),
Column("item_id", Integer),
Column("order_id", Integer, nullable=False, default=0),
Column("quantity", Integer, nullable=False, default=0),
Column("unit_price", Float, nullable=False, default=0.0),
Column("description", String(255), default="")
)
ExpenseCategoryID = Column("expense_category_id", Integer)
create_column(ExpenseCategoryID, PurchaseOrderLineItems)

def downgrade(migrate_engine):
# Operations to reverse the above upgrade go here.
meta = MetaData(migrate_engine)
PurchaseOrderLineItems = Table("purchase_order_line_items", meta,
Column("id", Integer, primary_key=True),
Column("type", String(10)),
Column("item_id", Integer),
Column("order_id", Integer, nullable=False, default=0),
Column("quantity", Integer, nullable=False, default=0),
Column("unit_price", Float, nullable=False, default=0.0),
Column("description", String(255), default=""),
Column("expense_category_id", Integer)
)
drop_column(PurchaseOrderLineItems.c.expense_category_id)

Regards,

Nate

> --
> You received this message because you are subscribed to the Google Groups "migrate-users" group.
> To post to this group, send email to migrat...@googlegroups.com.
> To unsubscribe from this group, send email to migrate-user...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/migrate-users?hl=en.
>
>