How to set sqlite TEMP_STORE to 3 with python-CodePudding

I am using python and sqlite3 and would like to use the memory for temp files. According to the docs, https://www.sqlite.org/compile.html, SQLITE_TEMP_STORE=3 means "Always use memory". I can check the current value with:

import sqlite3
conn = sqlite3.connect("test.db")
cur = conn.cursor()
check_db = conn.execute( """ select * from pragma_compile_options where compile_options like 'TEMP_STORE=%' """).fetchall() 

print("check_db:", check_db)

When I attempt to update:

sq_update = """ update pragma_compile_options set compile_options = 'TEMP_STORE=3' where compile_options like 'TEMP_STORE=1' """ 
conn.execute(sq_update) conn.commit()

The following error is returned. INTERNALERROR> sqlite3.OperationalError: table pragma_compile_options may not be modified

My goal is to set tell sqlite to use the memory for temp files.

CodePudding user response：

You need to examine the content of pragma_compile_options output to see the value of TEMP_STORE. You can only change the run-time setting if TEMP_STORE was explicitly set to non-zero value. In that case, use PRAGMA temp_store = 2 to achieve your goal. See https://www.sqlite.org/pragma.html#pragma_temp_store.