Issue: Iterator fails when accessing db inside loop
Author: maxekman
State: closed
Created: 2012-05-09 23:18:33

Accessing other db objects (moving the cursor) inside a loop with a foreign key results in this error:

"InterfaceError: Error binding parameter 0 - probably unsupported type."

Similar usage to this crashes:

``` python
class Container(Model):
    name = CharField()

class Item(Model):
    name = CharField()
    container = ForeignKeyField(Container, related_name = 'items')

class Other(Model):
    name = CharField()
    container = ForeignKeyField(Container, related_name = 'others')

# stupid loop that creates a "other" for every item
for item in a_container.items:
    Other.create(name = item.name, container = a_container)
```

It will run more times than there are items in the container, probably because of the cursor being disrupted.

It works by grabbing all results to a list and using that for the loop:

``` python
class Container(Model):
    name = CharField()

class Item(Model):
    name = CharField()
    container = ForeignKeyField(Container, related_name = 'items')

class Other(Model):
    name = CharField()
    container = ForeignKeyField(Container, related_name = 'others')

# stupid loop that creates a "other" for every item
items = [i for i in a_container.items]
for item in items:
    Other.create(name = item.name, container = a_container)
```


I think I know what causes the "running more times than there are items in the container" problem. See [this comment from earlier today](https://github.com/coleifer/peewee/issues/12#issuecomment-5614404) on a related issue for the details.


@tmoertel nailed it in that comment.  Basically, there are a couple workarounds -- you found one, which is to consume the cursor first storing the models in memory.

``` python

for item in list(a_container.items):
    Other.create(name = item.name, container = a_container)
```

Another possibility is to use transactions:

``` python

db = SqliteDatabase(':memory:')

@db.commit_on_success
def create_others(a_container):
    for item in a_container.items:
        Other.create(name = item.name, container = a_container)
```

Or do it by hand...

``` python

db.set_autocommit(False)
for item in a_container.items:
    Other.create(name = item.name, container = a_container)
db.commit()
db.set_autocommit(True)
```

Or you can turn autocommit off and do everything by hand:

``` python

db = SqliteDatabase(':memory:', autocommit=False)

# model definitions, etc

for item in a_container.items:
    Other.create(name = item.name, container = a_container)
db.commit()
```


Thanks for all the help!


Is it possible by the way to create a transaction syntax using the "with" statement? That could be a really neat way to handle it, and you wouldn't need a function as with the decorator.

Here is something similar: http://blog.gramant.com/2011/04/29/python-transactions-with-statement/

Thoughts?


Great idea, added it: https://github.com/coleifer/peewee/blob/master/tests.py#L3376


I've added some docs on working with transactions, http://peewee.readthedocs.org/en/latest/peewee/cookbook.html#working-with-transactions


That's excellent! Really quick feedback.


I have tried this now and it's a really nice syntax addition!