sqlite - 如何使用 SQLAlchemy 设置 SQLite PRAGMA 语句

Question

我希望 SQLAlchemy 将 SQLite .journal 文件放入内存中以提高性能。我试过这个:

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()

还有这个:

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")

运气不好。对于长事务，我仍然可以看到在磁盘上创建的 .journal 文件。还有其他方法可以设置吗？

*请注意，使用内置的 python sqlite 模块我没有问题

Answer 1

如何使用事件:

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()

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