Using pysqlcipher with SqlAlchemy?

Question

I'm trying to add some code to my program to encrypt the sqlite database I use. I'm trying to prompt the user for password, and use that password to create a new encrypted database if it doesn't exist, or decrypt and load an existing DB. There just doesn't seem to be a whole lot of documenation that I could find and I'm not sure how to do this. My code follows:

if encryption is True:
   print("***PYPER TIMESHEET UTILITY***")
   print("\nEnter encryption password below:")
   key = getpass.getpass()
   DB_NAME = ".timesheet.db"
   engine = create_engine('sqlite:///{}'.format(DB_NAME), module=sqlite)

else:

   print("WARNING: Unencrypted session. Install pysqlcipher3 to enable encryption\n")
   DB_NAME = ".timesheet.db?cipher=aes-256-cfb&kdf_iter=64000"
   engine = create_engine('sqlite:///{}'.format(DB_NAME))
   DBSession = sessionmaker(bind=engine)
   session = DBSession()

EDIT: forgot to give some more info.

I've tried what's listed at sqlalchemy. In the example above, I realized I left out an important line,

from pysqlcipher import dbapi 2 as sqlite

Link to full code

Answer 1

You forgot to include the key into your DB connection, as the example said:

'sqlite+pysqlcipher://:testing@/foo.db?cipher=aes-256-cfb&kdf_iter=64000'

(the key in the example is "testing"), so try this after having obtained key:

engine = create_engine(
    'sqlite+pysqlcipher://:{0}@/{1}?'
    'cipher=aes-256-cfb&kdf_iter=64000'.format(key, DB_NAME))