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

I also tried to add a listener like this thread: http://stackoverflow.com/questions/2614984/sqlite-sqlalchemy-how-to-enforce-foreign-keys — tomc, Mar 12 '12 at 18:35
I got the error `DBAPIError: (TypeError) 'PRAGMA cache_size' is an invalid keyword argument for this function None None` when I try to user your code. This same error you got? — Nilesh, Mar 14 '12 at 05:34
I don't get any errors, the code executes fine but apparently does nothing. — tomc, Mar 27 '12 at 15:24

score 17 · Answer 1 · edited Apr 02 '15 at 14:43

17

How about using events:

from sqlalchemy.engine import Engine
from sqlalchemy import event

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.close()

See http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html#foreign-key-support

edited Apr 02 '15 at 14:43

Garfield

2,487
4
31
54

answered May 14 '14 at 17:40

kalu

2,594
1
21
22

1

Is there a way to register different listeners depending on the target database (e.g. SQLite3 vs MySQL vs PostgreSQL) or to at least tell them apart after you're inside the listener's body (i.e. a way to programmatically check for this? – code_dredd Jul 11 '19 at 22:25
How can this be used in sqlalchemy though? – Nimrod Mpandari Oct 12 '22 at 02:08
Any explanation as to why this works better than the simpler code provided by OP? Why are events required? – static_rtti Nov 19 '22 at 10:55

score 9 · Answer 2 · edited May 23 '17 at 11:44

Basically you should be able to rewrite the examples about foreignkey to achieve what you want. Take a look at https://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)

score 1 · Answer 3 · edited May 23 '17 at 12:33

1

Two previous solutions did not work, so I have found the another one.

from sqlalchemy.interfaces import PoolListener
class MyListener(PoolListener):
    def connect(self, dbapi_con, con_record):
        dbapi_con.execute('pragma journal_mode=OFF')
        dbapi_con.execute('PRAGMA synchronous=OFF')
        dbapi_con.execute('PRAGMA cache_size=100000')

engine = create_engine('sqlite:///' + basefile,echo=False, listeners= [MyListener()])

edited May 23 '17 at 12:33

Community

1
1

answered Sep 12 '14 at 17:59

42n4

1,292
22
26

3

In recent versions this will give: SADeprecationWarning: The 'listeners' argument to Pool (and create_engine()) is deprecated. Use event.listen() – jpoppe Dec 19 '15 at 11:14

How to set SQLite PRAGMA statements with SQLAlchemy

3 Answers3

Linked