How to set SQLite PRAGMA statements with SQLAlchemy
Question: I would like SQLAlchemy to put the SQLite .journal file in-memory to speed up
performance. I have tried this:

    
    
    sqlite_db_engine = create_engine('sqlite:///%s' % str(dbname), connect_args = {'PRAGMA     journal_mode':'MEMORY', 'PRAGMA synchronous':'OFF', 'PRAGMA temp_store':'MEMORY', 'PRAGMA cache_size':'5000000'})
    
    db = sqlite_db_engine.connect()
    

and this:

    
    
    sqlite_db_engine = create_engine('sqlite:///%s' % str(dbname))
    
    db = sqlite_db_engine.connect()
    db.execute("PRAGMA journal_mode = MEMORY")
    db.execute("PRAGMA synchronous = OFF")
    db.execute("PRAGMA temp_store = MEMORY")
    db.execute("PRAGMA cache_size = 500000")
    

With no luck. For long transactions I can still see the .journal file being
created on the disk. Is there another way to set this?

*note I have no problem doing it with the built-in python sqlite module


Answer: Basically you should be able to rewrite the examples about foreignkey to
achieve what you want. Take a look at
<http://stackoverflow.com/a/7831210/1890086>

    
    
    engine = create_engine(database_url)
    
    def _fk_pragma_on_connect(dbapi_con, con_record):
        dbapi_con.execute('PRAGMA journal_mode = MEMORY')
        # ...
    
    from sqlalchemy import event
    event.listen(engine, 'connect', _fk_pragma_on_connect)
    

