Python/SQL Alchemy Migrate - "ValueError: too many values to unpack" when migrating changes in db

Question

I have several models in SQLAlchemy written and I just started getting an exception when running my migrate scripts: ValueError: too many values to unpack

Here are my models:

from app import db

ROLE_USER = 0
ROLE_ADMIN = 1


class UserModel(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(25), index=True)
    password = db.Column(db.String(50))
    email = db.Column(db.String(50), index=True, unique=True)
    role = db.Column(db.SmallInteger, default=ROLE_USER)

    def __repr__(self):
        return '<User %r>' % (self.username)


class ConferenceModel(db.Model):
    __tablename__ = 'conference'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(40), index=True, unique=True)
    teams = db.relationship('TeamModel', backref='conference', lazy='dynamic')

    def __repr__(self):
        return '<Conference %r>' % (self.name)


class TeamModel(db.Model):
    __tablename__ = 'team'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), index=True, unique=True)
    conference_id = db.Column(db.Integer, db.ForeignKey('conference.id'))
    players = db.relationship('PlayerModel', backref='team', lazy='dynamic')

    def __repr__(self):
        return '<Team %r>' % (self.name)

class PlayerModel(db.Model):
    __tablename__ = 'player'
    id = db.Column(db.Integer, primary_key=True)
    season = db.Column(db.String(4), index=True)
    name = db.Column(db.String(75), index=True)
    number = db.Column(db.String(3))
    position = db.Column(db.String(4))
    height = db.Column(db.Integer)
    weight = db.Column(db.Integer)
    academic_class = db.Column(db.String(2))
    hometown = db.Column(db.String(40))
    status = db.Column(db.SmallInteger)
    team_id = db.Column(db.Integer, db.ForeignKey('team.id'))

    def __repr__(self):
        return '<player %r>' % (self.name)


class GameModel(db.Model):
    __tablename__ = 'game'

    id = db.Column(db.Integer, primary_key=True)
    espn_id = db.Column(db.Integer, index=True)
    date_time = db.Column(db.DateTime)
    location = db.Column(db.String(100))
    home_final = db.Column(db.Integer)
    away_final = db.Column(db.Integer)
    game_type = db.Column(db.Integer)
    season = db.Column(db.Integer)
    home_team_id = db.Column(db.Integer, db.ForeignKey('team.id'))
    away_team_id = db.Column(db.Integer, db.ForeignKey('team.id'))

    home_team = db.relationship("TeamModel", backref="homegames", foreign_keys=[home_team_id])
    away_team = db.relationship("TeamModel", backref="awaygames", foreign_keys=[away_team_id])


class ScoreDataModel(db.Model):
    __tablename__ = 'scoredata'

    id = db.Column(db.Integer, primary_key=True)
    starter = db.Column(db.Boolean)
    minutes_played = db.Column(db.Integer)
    field_goals_made = db.Column(db.Integer)
    field_goals_attempted = db.Column(db.Integer)
    three_pointers_made = db.Column(db.Integer)
    three_pointers_attempted = db.Column(db.Integer)
    free_throws_made = db.Column(db.Integer)
    free_throws_attempted = db.Column(db.Integer)
    offensive_rebounds = db.Column(db.Integer)
    rebounds = db.Column(db.Integer)
    assists = db.Column(db.Integer)
    steals = db.Column(db.Integer)
    blocks = db.Column(db.Integer)
    turnovers = db.Column(db.Integer)
    personal_fouls = db.Column(db.Integer)
    points = db.Column(db.Integer)


    # Added the columns below and the migrate script blew up...
    # I've taken them out and added other columns, but the error still presents
    player_id = db.Column(db.Integer, db.ForeignKey('player.id'))
    game_id = db.Column(db.Integer, db.ForeignKey('game.id'))

    player = db.relationship("PlayerModel", backref="boxscores")
    game = db.relationship("GameModel", backref="boxscore")

Here is my migrate script (this was taken frmo Miguel Grinberg's Mega Flask tutorial):

#!flask/bin/python
import imp
from migrate.versioning import api
from app import db
from config import SQLALCHEMY_DATABASE_URI
from config import SQLALCHEMY_MIGRATE_REPO
migration = SQLALCHEMY_MIGRATE_REPO + '/versions/%03d_migration.py' % (api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO) + 1)
tmp_module = imp.new_module('old_model')
old_model = api.create_model(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
exec old_model in tmp_module.__dict__
script = api.make_update_script_for_model(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO, tmp_module.meta, db.metadata)
open(migration, "wt").write(script)
api.upgrade(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
print 'New migration saved as ' + migration
print 'Current database version: ' + str(api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO))

And here is the traceback:

/Users/johncaine/anaconda/bin/python /Volumes/Spano/Dropbox/Dropbox/eclipse-workspace/CAUDLE/src/caudle/caudle/db_migrate.py
Traceback (most recent call last):
  File "/Volumes/Spano/Dropbox/Dropbox/eclipse-workspace/CAUDLE/src/caudle/caudle/db_migrate.py", line 11, in <module>
    script = api.make_update_script_for_model(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO, tmp_module.meta, db.metadata)
  File "<string>", line 2, in make_update_script_for_model
  File "/Users/johncaine/anaconda/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 89, in catch_known_errors
    return f(*a, **kw)
  File "<string>", line 2, in make_update_script_for_model
  File "/Users/johncaine/anaconda/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 159, in with_engine
    return f(*a, **kw)
  File "/Users/johncaine/anaconda/lib/python2.7/site-packages/migrate/versioning/api.py", line 321, in make_update_script_for_model
    engine, oldmodel, model, repository, **opts)
  File "/Users/johncaine/anaconda/lib/python2.7/site-packages/migrate/versioning/script/py.py", line 69, in make_update_script_for_model
    genmodel.ModelGenerator(diff,engine).genB2AMigration()
  File "/Users/johncaine/anaconda/lib/python2.7/site-packages/migrate/versioning/genmodel.py", line 197, in genB2AMigration
    for modelCol, databaseCol, modelDecl, databaseDecl in td.columns_different:
ValueError: too many values to unpack

I believe the only change I made was adding the ScoreDataModel when this started blowing up. I can't seem to follow back to where I can fix this.

Answer 1

I came across the same problem with sqlalchemy + mysql. I had boolean fields in my model and they where created in mysql with smallint In migration script, it compares the model datatype with schema datatype (from mysql in my case). sqlalchemy translates the boolean datatype by default to tinyint and in mysql the schema is created with smallint.

The migrate script is throwing the specified error when it identifies this difference between model and schema. I changed my model data type to db.SmallInteger from db.Boolean to solve this.