import sqlite3

conn = sqlite3.connect(':memory:')
conn.row_factory = sqlite3.Row
conn.set_trace_callback(print)

# Setup
conn.execute('CREATE TABLE t1 (a INTEGER, b INTEGER)')

conn.executemany(
    'INSERT INTO t1 (a, b) VALUES (?, ?)',
    zip(range(5), range(5, 10)),
)
conn.commit()
for a, b in conn.execute('SELECT a, b FROM t1'):
    assert a - b == -5

# Swapping column a and b and supplying dicts
rows = [dict(r) for r in conn.execute('SELECT a, b FROM t1')]
conn.executemany('UPDATE t1 SET a = :b, b = :a WHERE a = :a AND b = :b', rows)
conn.commit()
for a, b in conn.execute('SELECT a, b FROM t1'):
    # Swapped
    assert b - a == -5

# Swapping column a and b and supplying Row objects - this will fail
rows = conn.execute('SELECT a, b FROM t1')
conn.executemany('UPDATE t1 SET a = :b, b = :a WHERE a = :a AND b = :b', rows)
conn.commit()
for a, b in conn.execute('SELECT a, b FROM t1'):
    # Swapped
    try:
        assert a - b == -5
    except AssertionError:
        print('assertion failed')

# Swapping column a and b and supplying Row objects
# with a nonsensical modified statement will still not work.
rows = conn.execute('SELECT a, b FROM t1')
# swapping :a and :b in the statement
conn.executemany('UPDATE t1 SET a = :a, b = :b WHERE a = :b AND b = :a', rows)
conn.commit()
for a, b in conn.execute('SELECT a, b FROM t1'):
    # Swapped
    try:
        assert a - b == -5
    except AssertionError:
        print('assertion failed')

# However, changing how the rows were originally fetched:
# SELECT b, a instead of SELECT a, b
rows = conn.execute('SELECT b, a FROM t1')
conn.executemany('UPDATE t1 SET a = :b, b = :a WHERE a = :a AND b = :b', rows)
conn.commit()
# and the UPDATE will work as expected.
for a, b in conn.execute('SELECT a, b FROM t1'):
    # Swapped
    assert a - b == -5

# This seems to suggest that Row objects are being used as tuples
# even if the statement is written with named parameter substitutions.
# This behavior is not documented.


# Also, using non-Mappings with statements that use named parameter substitutions
# shouldn't be allowed in the first place as it will cause confusion:
try:
    conn.executemany('INSERT INTO t1 (a, b) VALUES (:b, :a)', [(41, 42)])
except Exception:
    pass
else:
    print('Inserted: ', [dict(r) for r in conn.execute('SELECT a, b FROM t1')][-1])
    print("This shouldn't work")