Issue: create_or_get sqlite3.InterfaceError with cursor because of commit/rollback
Author: bsuire
State: closed
Created: 2015-07-24 12:56:06

The create_or_get method yields a pretty weird error for me:

```
 File "./script.py", line 137, in load_data
    for name,country in rows:
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/peewee.py", line 1957, in next
    obj = self.iterate()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/peewee.py", line 1934, in iterate
    row = self.cursor.fetchone()
sqlite3.InterfaceError: Cursor needed to be reset because of commit/rollback and can no longer be fetched from. 
```

Here is the offending code:

```
for i in range(1, nb_rows // CHUNK_SIZE + 2):                                                                                                                                                                                                                                                                                                     
    rows = (Source.select(Source.name,Source.country)                                                                                                                       
           .where(Source.status == 'new').paginate(i, CHUNK_SIZE).tuples())                                                                                                     

    for name,country in rows:                                                                                                                                                                                                                                                                                                               
        person, created = Person.create_or_get(name = name, new = True)                                                                                                        

        if country:                                                                                                                                                                   
            country, created = Countries.create_or_get(name = country)                                                                                                                         
            Person_Country(person_id = person.id, country_id = country.id).save()   
```

I replaced the create_or_get statements with try: create(), except get(), and now it works just fine...

Note: using Sqlite3 3.8.9                                                                                                        


Here's my init code:

```
class SqliteFKDatabase(SqliteDatabase):                                                                                                                                              
    def initialize_connection(self, conn):                                                                                                                                           
        self.execute_sql('PRAGMA foreign_keys=ON;`)
```

```
db = SqliteFKDatabase(None)

def init_db(DB):                                                                                                                                                        
    db.init(DB) 

  init_db(TEST_SQL)  
```


Well, your cursor for the outer `SELECT` statement is still open, so by consuming the outer `SELECT` you should get your code working:

``` python
for i in range(1, nb_rows // CHUNK_SIZE + 2):                                                                                                                                                                                                                                                                                                     
    rows = (Source.select(Source.name,Source.country)                                                                                                                       
           .where(Source.status == 'new').paginate(i, CHUNK_SIZE).tuples())                                                                                                     

    # CONSUME SELECT
    rows = list(rows)

    for name,country in rows:                                                                                                                                                                                                                                                                                                               
        person, created = Person.create_or_get(name = name, new = True)                                                                                                        

        if country:                                                                                                                                                                   
            country, created = Countries.create_or_get(name = country)                                                                                                                         
            Person_Country(person_id = person.id, country_id = country.id).save()   
```

`create_or_get` will use a transaction (or savepoint if you're already in a transaction). When this is committed or rolled back, the cursor for the `SELECT` can no longer be used.