0

SO virgin and SQLite newbie here, I'm using python 2.7.9.

I would like to use database constraints and executemany method to insert data to my database. While creating tests I noticed that if constraint is applied my import fails with executemany but works if I import with execute.

Specifically my iterator becomes empty with the first applied constraint if I use executemany but has to my understanding proper content if I use execute.

Sample:

    import sqlite3

    con = sqlite3.connect(":memory:")
    cur = con.cursor()
    cur.executescript("""
    CREATE TABLE test(a INTEGER CHECK(a<5));
    """)

    x = iter(range(10))
    for r in x:
        try: cur.execute("REPLACE INTO test VALUES(?)", (r,))
        except sqlite3.IntegrityError:
            break
    print list(x) # [6, 7, 8, 9] are left

    x = iter(range(10))
    try: cur.executemany("REPLACE INTO test VALUES(?)", map(lambda r: (r,), x))
    except sqlite3.IntegrityError:
        pass
    print list(x) # [] is left, why NOT [6, 7, 8, 9]?

    # 3rd version
    x = iter(range(10))
    try: cur.executemany("REPLACE INTO test VALUES(?)", [(r,) for r in x])
    except sqlite3.IntegrityError: pass
    print list(x) # [] is left

Why is x empty in the latter approach? I would like to continue after the exception but there's nothing left.

Thanks :)

melli
  • 1
  • 1
  • 1
  • if you use Python 2, `map()` func "eats" iterator from start to end while creates the list. Both snippets are different. – RandomB Jul 27 '17 at 15:18
  • Thanks, same seems to be true for list comprehension so it seems my best option is to try to wrap the execute method inside a transaction. – melli Jul 27 '17 at 15:34

0 Answers0