Menu
I tried to process the same request to the same database using "Python 2.7.4 + sqlite3" and "Firefox SQLite Manager 0.8.0".
On the tiny database (8000 records) both Python and Firefox work fast and give the same result.
On the bigger database (2600000 records):
What can be wrong with the following program, so python sqlite3 cannot process the query in reasonable time, while the same request can be processed faster?
import sqlite3
_sql1 = """SELECT DISTINCT J2.rule_description,
J2.feature_type,
J2.action_item_id,
J2.rule_items
FROM journal J1,
journal J2
WHERE J1.base = J2.base
AND J1.action_item_id=J2.action_item_id
AND J1.type="Action disabled"
AND J2.type="Action applied"
AND J1.rule_description="Some test rule"
AND J1.action_item_id IN (1, 2, 3, 14, 15, 16, 17, 18, 19, 30, 31, 32)
"""
if __name__ == '__main__':
sqlite_output = r'D:\results.sqlite'
with sqlite3.connect(sqlite_output) as connection:
for row in connection.execute(_sql1):
print row
UPDATE: Command Line Shell For SQLite also returns the same 24 records
UPDATE2: sqlite3.sqlite_version is '3.6.21'
987
The SQLite docs explains why this is so slow: Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe.
Python SQLite3 module is used to integrate the SQLite database with Python. It is a standardized Python DBI API 2.0 and provides a straightforward and simple-to-use interface for interacting with SQLite databases. There is no need to install this module separately as it comes along with Python after the 2.5x version.
3.0 Overview. This extended version of SQLite is able to read and write ordinary SQLite database files just like the public domain version. But this extended version also supports the ability to read and write compressed databases using an application-supplied compression function.
In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads. Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction.
It seems, that the problem is related with the old version of sqlite that shipped with Python 2.7. Everything works fine in python 3.3.
Thanks a lot to @CL for the great comment!
In python 2.7
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.6.21'
In python 3.3
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.7.12'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With