6
votes

I made a module with the following contents:

import sqlite3 as sq
connection = sq.connect("test.db")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS test")
cursor.execute("CREATE TABLE test (st TEXT)")
cursor.execute("INSERT INTO test VALUES ('testing')")
cursor.execute("SELECT * FROM test")
print(cursor.fetchall())
cursor.close()
connection.close()
connection2 = sq.connect("test.db")
cursor2 = connection2.cursor()
cursor2.execute("SELECT * FROM test")
print(cursor2.fetchall())

But when I ran it, it printed the following:

[('testing',)]
[]

It should have printed:

[('testing',)]
[('testing',)]

What is wrong?

1
Note: I am using Python 3.3 - Bob

1 Answers

10
votes

You did not commit your changes into the DB. When you discard the connection, the transaction will be rolled back. This works

import sqlite3 as sq
connection = sq.connect("test.db")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS test")
cursor.execute("CREATE TABLE test (st TEXT)")
cursor.execute("INSERT INTO test VALUES ('testing')")
connection.commit() # !!!

cursor.execute("SELECT * FROM test")
print(cursor.fetchall())
cursor.close()
connection.close()  # rolls back changes without .commit()

connection2 = sq.connect("test.db")
cursor2 = connection2.cursor()
cursor2.execute("SELECT * FROM test")
print(cursor2.fetchall())