Issue: Writing under a select query iterator results in unexpected behavior
Author: josephschorr
State: closed
Created: 2014-10-01 19:41:51

I'm seeing very odd behavior as a result of a loop over a select query which creates rows in another table.

```
from peewee import Model, SqliteDatabase, CharField, ForeignKeyField, create_model_tables

db = SqliteDatabase(':memory:')

class Person(Model):
  name = CharField()
  gender = CharField()
  class Meta:
    database = db

class Creature(Model):
  name = CharField(default='acreature')
  class Meta:
    database = db

create_model_tables([Person, Creature])

Person.create(name = 'Bob', gender = 'M')
Person.create(name = 'James', gender = 'M')
Person.create(name = 'Jesse', gender = 'M')
Person.create(name = 'Alice', gender = 'F')
Person.create(name = 'Kate', gender = 'F')
Person.create(name = 'Alex', gender = 'F')

print "List: %s" % list(Person.select().where(Person.gender == 'M'))

print ''
print "Expected (with list):"
for person in list(Person.select().where(Person.gender == 'M')):
  print person.name
  Creature.create(name = person.name + "'s pet")

print ''
print "Broken (without list):"
for person in Person.select().where(Person.gender == 'M'):
  print person.name
  Creature.create(name = person.name + "'s pet")

print ''
print "Causes exception:"
print list(Person.select().where(Person.gender == 'M'))
```

Output:

```
List: [<__main__.Person object at 0x104ece950>, <__main__.Person object at 0x104ecec10>, <__main__.Person object at 0x104ecea50>]

Expected (with list):
Bob
James
Jesse

Broken (without list):
Bob
James
Bob
James
Jesse

Causes exception:
Traceback (most recent call last):
  File "repro.py", line 38, in <module>
    print list(Person.select().where(Person.gender == 'M'))
  File "/Library/Python/2.7/site-packages/peewee.py", line 2466, in __iter__
    return iter(self.execute())
  File "/Library/Python/2.7/site-packages/peewee.py", line 2459, in execute
    self._qr = ResultWrapper(model_class, self._execute(), query_meta)
  File "/Library/Python/2.7/site-packages/peewee.py", line 2155, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/Library/Python/2.7/site-packages/peewee.py", line 2760, in execute_sql
    self.commit()
  File "/Library/Python/2.7/site-packages/peewee.py", line 2629, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "/Library/Python/2.7/site-packages/peewee.py", line 2752, in execute_sql
    cursor.execute(sql, params or ())
peewee.InterfaceError: Error binding parameter 0 - probably unsupported type.
```

As seen above, if we loop over a `select` wrapped in a `list`, then the three rows are returned as expected. If, however, we try to loop over the `select` directly, we encounter the first two rows twice (!) and then any subsequent calls to retrieve the full list of the query results in a SQL exception.


Note: The above code works fine when executed on a MySQL database.


https://github.com/coleifer/peewee/issues/12#issuecomment-5614404

And #109 and #327 and #388 and #437.

Perhaps I need to add this very clearly to the docs?


Might help, but I think it would best to raise an exception if we can detect this case, since the code does work with other engines and it is non-obvious that switching engines can break it.

Interestingly enough, the code did work with peewee 1.0.0.


Added 613aa2025f4faa6ccfdc40c79766b24ff1cc731d which documents the behavior.