pythonsqlitesqlcipher

Python sqlcipher3 INSERTS don't persist


I'm exploring sqlcipher3 with python, and I find that after inserting some rows, then closing and re-opening the database, my table is empty. This problem doesn't appear when I use the sqlcipher command-line utility itself.

from sqlcipher3 import dbapi2 as sqlcipher

db = sqlcipher.connect('testing.db')
db.execute('pragma key="testing"')
db.execute("create table people (name text primary key)")
db.execute("insert into people (name) values ('charlie'), ('huey')")
print(db.execute('select * from people').fetchall())
# => [('charlie',), ('huey',)]
db.close()

db = sqlcipher.connect('testing.db')
db.execute('pragma key="testing"')
print(db.execute('select * from people').fetchall())
db.close()
# => []

What have I missed here?


Solution

  • You are supposed to commit your transaction before closing the connection otherwise your transaction is lost : https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.close