Issue: Adding a column with SqliteMigrator fails on tables which already have an index
Author: tfeldmann
State: closed
Created: 2015-04-28 10:40:41

Hello Charles,

I have a problem with adding a column to one of my tables. I created a short example program which reproduces the error.

I found the problem was introduced in peewee 2.4.3

Greetings,
Thomas

``` python
from peewee import *
from peewee import create_model_tables
from playhouse.migrate import SqliteMigrator, migrate

db = SqliteDatabase(':memory:')

class Measurement(Model):
    some_field = CharField(index=True)

    class Meta:
        database = db

create_model_tables([Measurement])

migrator = SqliteMigrator(db)
migrate(
    migrator.add_column('measurement', 'location', CharField(default=''))
)
```

Traceback: 

```
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-2-f9b29a548522> in <module>()
     15 migrator = SqliteMigrator(db)
     16 migrate(
---> 17     migrator.add_column('measurement', 'location', CharField(default=''))
     18 )

/usr/local/lib/python3.4/site-packages/playhouse/migrate.py in migrate(*operations, **kwargs)
    533 
    534     @operation
--> 535     def add_not_null(self, table, column):
    536         def _add_not_null(column_name, column_def):
    537             return column_def + ' NOT NULL'

/usr/local/lib/python3.4/site-packages/playhouse/migrate.py in run(self)
    142         kwargs['generate'] = True
    143         self._handle_result(
--> 144             getattr(self.migrator, self.method)(*self.args, **kwargs))
    145 
    146 

/usr/local/lib/python3.4/site-packages/playhouse/migrate.py in _handle_result(self, result)
    136         elif isinstance(result, (list, tuple)):
    137             for item in result:
--> 138                 self._handle_result(item)
    139 
    140     def run(self):

/usr/local/lib/python3.4/site-packages/playhouse/migrate.py in _handle_result(self, result)
    133             self.execute(result)
    134         elif isinstance(result, Operation):
--> 135             result.run()
    136         elif isinstance(result, (list, tuple)):
    137             for item in result:

/usr/local/lib/python3.4/site-packages/playhouse/migrate.py in run(self)
    142         kwargs['generate'] = True
    143         self._handle_result(
--> 144             getattr(self.migrator, self.method)(*self.args, **kwargs))
    145 
    146 

/usr/local/lib/python3.4/site-packages/playhouse/migrate.py in _handle_result(self, result)
    133             self.execute(result)
    134         elif isinstance(result, Operation):
--> 135             result.run()
    136         elif isinstance(result, (list, tuple)):
    137             for item in result:

/usr/local/lib/python3.4/site-packages/playhouse/migrate.py in run(self)
    142         kwargs['generate'] = True
    143         self._handle_result(
--> 144             getattr(self.migrator, self.method)(*self.args, **kwargs))
    145 
    146 

/usr/local/lib/python3.4/site-packages/playhouse/migrate.py in inner(self, *args, **kwargs)
    150         generate = kwargs.pop('generate', False)
    151         if generate:
--> 152             return fn(self, *args, **kwargs)
    153         return Operation(self, fn.__name__, *args, **kwargs)
    154     return inner

/usr/local/lib/python3.4/site-packages/playhouse/migrate.py in _update_column(self, table, column_to_update, fn)
    434         index_to_sql = dict(cursor.fetchall())
    435         indexed_columns = {}
--> 436         for index_name in sorted(index_to_sql):
    437             cursor = self.database.execute_sql(
    438                 'PRAGMA index_info("%s")' % index_name)

/usr/local/lib/python3.4/site-packages/playhouse/migrate.py in _get_indexes(self, table)
    426             ['table', table])
    427         return res.fetchone()[0]
--> 428 
    429     def _get_indexes(self, table):
    430         cursor = self.database.execute_sql(

/usr/local/lib/python3.4/site-packages/peewee.py in get_indexes(self, table, schema)
   2944 
   2945     register_unicode = True
-> 2946 
   2947     def _connect(self, database, **kwargs):
   2948         if not psycopg2:

ValueError: too many values to unpack (expected 3)
```


Peewee is currently at 2.6.0, and this code runs correctly under 2.6.0, so I'm not sure what more I can tell you besides _upgrade_! :)


Ok, this is strange. I tested it in Ubuntu 15.04 and Windows 8.1 VMs and it works. But I can reproduce the error every time on my system in both python2 and python3.

peewee==2.4.2 works
peewee==2.4.3 error
peewee==2.6.0 error

I'm using Mac OS X 10.10.3
SQLite 3.8.9, Python 3.4.3 installed with homebrew.

Do you have any idea how to investigate this?


Are you sure about 2.6.0 being broken? With peewee 2.6.0, python 2 or 3, and sqlite 3.8.9 the script works as expected:

``` python
from peewee import *
from peewee import create_model_tables
from playhouse.migrate import SqliteMigrator, migrate

db = SqliteDatabase(':memory:')

class Measurement(Model):
    some_field = CharField(index=True)

    class Meta:
        database = db

create_model_tables([Measurement])

migrator = SqliteMigrator(db)
migrate(
    migrator.add_column('measurement', 'location', CharField(default=''))
)

print(db.get_columns('measurement'))
```

Output:

``` python
[
  ColumnMetadata(name='id', data_type='INTEGER', null=False, primary_key=True, table='measurement'), 
  ColumnMetadata(name='some_field', data_type='VARCHAR(255)', null=False, primary_key=False, table='measurement'), 
  ColumnMetadata(name='location', data_type='VARCHAR(255)', null=False, primary_key=False, table='measurement')]
```


If you're running the above "test" script you might also add something like the following, just to be sure:

``` python
from peewee import __version__ as peewee_version
print('Peewee version %s' % peewee_version)
```


This returns:

```
Peewee version 2.6.0
Traceback (most recent call last):
  File "test 2.py", line 22, in <module>
    migrator.add_column('measurement', 'location', CharField(default=''))
  File "/usr/local/lib/python3.4/site-packages/playhouse/migrate.py", line 575, in migrate
    operation.run()
  File "/usr/local/lib/python3.4/site-packages/playhouse/migrate.py", line 144, in run
    getattr(self.migrator, self.method)(*self.args, **kwargs))
  File "/usr/local/lib/python3.4/site-packages/playhouse/migrate.py", line 138, in _handle_result
    self._handle_result(item)
  File "/usr/local/lib/python3.4/site-packages/playhouse/migrate.py", line 135, in _handle_result
    result.run()
  File "/usr/local/lib/python3.4/site-packages/playhouse/migrate.py", line 144, in run
    getattr(self.migrator, self.method)(*self.args, **kwargs))
  File "/usr/local/lib/python3.4/site-packages/playhouse/migrate.py", line 135, in _handle_result
    result.run()
  File "/usr/local/lib/python3.4/site-packages/playhouse/migrate.py", line 144, in run
    getattr(self.migrator, self.method)(*self.args, **kwargs))
  File "/usr/local/lib/python3.4/site-packages/playhouse/migrate.py", line 152, in inner
    return fn(self, *args, **kwargs)
  File "/usr/local/lib/python3.4/site-packages/playhouse/migrate.py", line 449, in _update_column
    indexes = self.database.get_indexes(table)
  File "/usr/local/lib/python3.4/site-packages/peewee.py", line 3184, in get_indexes
    for _, name, is_unique in cursor.fetchall():
ValueError: too many values to unpack (expected 3)
```

If I set `index=False`, it works as expected.


Ahh, I think this is due to changes in SQLite. This was just recently fixed in:

6d616e25c9748abc22ecf3f413b81ee91c390699

So you might try out master for now until I release 2.6.1.


Thank you for the feedback, I thought there was something wrong with my system :+1: