SQlite migration with batch mode - help needed

[Sylvain Martel]

Hi,
 
   Not knowing much about SQL stuff, I used Flask-Migrate, which use alembic, to deal with migrations.  But now I have to deal with a migration where I can't simply destroy the SQLite database and remake it.(which is how I dealt so far when an ALTER constraint message popped up)

So I read the doc on batch mode for SQLite, but I admit it makes no sense to me.  How can I take this line in the migration script
op.create_foreign_key(None, 'incomes', 'classtype', ['income_classtype'], ['id'])

and transform it to use batch mode so it works with SQLite?

Basically, I'm adding a column to 2 tables with a one-to-one relationship.  Here are the models in case it helps.  The new columns are the last line in both models.

class Income(db.Model):
    __tablename__='incomes'
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime)
    amount = db.Column(Numeric)
    user_ = db.Column(db.Integer, db.ForeignKey('users.id'))
    income_classtype = db.Column(db.Integer,db.ForeignKey('classtype.id'))

class ClassType(db.Model):
    __tablename__ = 'classtype'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    default = db.Column(db.Boolean, default=False, index=True)
    class_ = db.relationship('Classes', backref='classtype', lazy='dynamic')
    punchcard_type = db.relationship('Punchcard', backref='classtype', lazy='dynamic')
    seasonpass_type = db.relationship('SeasonPass', backref='classtype', lazy='dynamic')
    income_type = db.relationship('Income',backref='classtype', lazy ='dynamic')

Thanks

[Sylvain Martel]

P.S.

I tried this:

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table("incomes") as batch_op:
        batch_op.add_column(sa.Column('income_classtype', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(None, 'incomes', 'classtype', ['income_classtype'], ['id'])
    ### end Alembic commands ###

but it gives an error for create_foreign keys, saying there are too many arguments passed.  But I can't find in the doc what parameters I should pass to it.

[Mike Bayer]

On 5/26/15 9:47 AM, Sylvain Martel wrote:

Hi,
 
   Not knowing much about SQL stuff, I used Flask-Migrate, which use alembic, to deal with migrations.  But now I have to deal with a migration where I can't simply destroy the SQLite database and remake it.(which is how I dealt so far when an ALTER constraint message popped up)

So I read the doc on batch mode for SQLite, but I admit it makes no sense to me.  How can I take this line in the migration script
op.create_foreign_key(None, 'incomes', 'classtype', ['income_classtype'], ['id'])

and transform it to use batch mode so it works with SQLite?

Well one thing to note is that SQLite doesn't enforce foreign key constraints anyway unless you are enabling constraints on each connection.  So typical real-world use case, adding an FK constraint is not very useful unless you want to reflect it later.


Batch mode just means, take any op.XYZ() you want, and throw it underneath op.batch_alter_table(), and remove the tablename arg, that's it:


def upgrade():

    with op.batch_alter_table('incomes') as batch_op:

        batch_op.create_foreign_key(None, 'classtype', ['income_classtype'], ['id'])


http://alembic.readthedocs.org/en/latest/ops.html#alembic.operations.BatchOperations.create_foreign_key

Basically, I'm adding a column to 2 tables with a one-to-one relationship.  Here are the models in case it helps.  The new columns are the last line in both models.

class Income(db.Model):
    __tablename__='incomes'
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime)
    amount = db.Column(Numeric)
    user_ = db.Column(db.Integer, db.ForeignKey('users.id'))
    income_classtype = db.Column(db.Integer,db.ForeignKey('classtype.id'))

class ClassType(db.Model):
    __tablename__ = 'classtype'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    default = db.Column(db.Boolean, default=False, index=True)
    class_ = db.relationship('Classes', backref='classtype', lazy='dynamic')
    punchcard_type = db.relationship('Punchcard', backref='classtype', lazy='dynamic')
    seasonpass_type = db.relationship('SeasonPass', backref='classtype', lazy='dynamic')
    income_type = db.relationship('Income',backref='classtype', lazy ='dynamic')

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.

[Sylvain Martel]

Hi,

  Thanks for the reply, I tried removing the tablename arguments but then got
    raise ValueError("Constraint must have a name")
ValueError: Constraint must have a name

So I tried removing the None argument instead and it worked. The database was also in the expected state after the change.

BUT, reading your comment about SQLite not enforcing constraints, I also tried simply removing the line creating the constraint in the migration script, and redid the migration from start, and everything worked still. 

All that pain for nothing!(I had also tried migrating everything to Firebird without success first.)

Big thanks, the migration is a success and I can continue :) (and I get a better understanding of alchemic batch migration)

On Tuesday, 26 May 2015 09:47:03 UTC-4, Sylvain Martel wrote:

[Mike Bayer]

On 5/26/15 10:52 AM, Sylvain Martel wrote:

Hi,

  Thanks for the reply, I tried removing the tablename arguments but then got
    raise ValueError("Constraint must have a name")
ValueError: Constraint must have a name

So I tried removing the None argument instead and it worked. The database was also in the expected state after the change.

because it named your constraint the name of your table name.  this is less than ideal.

the batch mode requires that constraints be given unique names.  Else there's no way you'd be able to DROP this constraint later.     Batch mode is special because it is trying to work around SQLite's limitations, in this case that SQLite allows a constraint to live in a schema with no name at all.  No other database does this; they all assign names if the constraint is otherwise unnamed.

-- 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.