Use sqlean if available in environment

[simonw]

Refs:

• 559

---

:books: Documentation preview :books:: https://sqlite-utils--560.org.readthedocs.build/en/560/

[codecov[bot]]

Codecov Report

Patch coverage: 80.55% and project coverage change: -0.15 :warning:

Comparison is base (2747257) 96.36% compared to head (5e7d27e) 96.22%.

Additional details and impacted files

```diff @@ Coverage Diff @@ ## main #560 +/- ## ========================================== - Coverage 96.36% 96.22% -0.15% ========================================== Files 6 6 Lines 2726 2752 +26 ========================================== + Hits 2627 2648 +21 - Misses 99 104 +5 ``` | [Impacted Files](https://app.codecov.io/gh/simonw/sqlite-utils/pull/560?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) | Coverage Δ | | |---|---|---| | [sqlite\_utils/utils.py](https://app.codecov.io/gh/simonw/sqlite-utils/pull/560?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison#diff-c3FsaXRlX3V0aWxzL3V0aWxzLnB5) | `94.56% <63.63%> (-0.62%)` | :arrow_down: | | [sqlite\_utils/db.py](https://app.codecov.io/gh/simonw/sqlite-utils/pull/560?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison#diff-c3FsaXRlX3V0aWxzL2RiLnB5) | `97.33% <86.36%> (-0.20%)` | :arrow_down: | | [sqlite\_utils/cli.py](https://app.codecov.io/gh/simonw/sqlite-utils/pull/560?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison#diff-c3FsaXRlX3V0aWxzL2NsaS5weQ==) | `95.22% <100.00%> (ø)` | |

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Do you have feedback about the report comment? Let us know in this issue.

[simonw]

Lots of failures now that I'm trying to run the tests against sqlean.py on macOS and Python 3.10: https://github.com/simonw/sqlite-utils/actions/runs/5371800108/jobs/9744802953

A bunch of these, because pysqlite3 chooses not to implement .iterdump():

    @pytest.fixture
    def db_to_analyze_path(db_to_analyze, tmpdir):
        path = str(tmpdir / "test.db")
        db = sqlite3.connect(path)
>       db.executescript("\n".join(db_to_analyze.conn.iterdump()))
E       AttributeError: 'sqlean.dbapi2.Connection' object has no attribute 'iterdump'

Also some of these:

    def test_analyze_whole_database(db):
        assert set(db.table_names()) == {"one_index", "two_indexes"}
        db.analyze()
>       assert set(db.table_names()) == {"one_index", "two_indexes", "sqlite_stat1"}
E       AssertionError: assert {'one_index',...'two_indexes'} == {'one_index',...'two_indexes'}
E         Extra items in the left set:
E         'sqlite_stat4'
E         Full diff:
E         - {'two_indexes', 'sqlite_stat1', 'one_index'}
E         + {'two_indexes', 'sqlite_stat1', 'sqlite_stat4', 'one_index'}
E         ?                                 ++++++++++++++++

Apparently sqlean.py adds a sqlite_stat4 table that the tests are not expecting.

Plus some errors that look like this:

    def test_enable_wal():
        runner = CliRunner()
        dbs = ["test.db", "test2.db"]
        with runner.isolated_filesystem():
            for dbname in dbs:
                db = Database(dbname)
                db["t"].create({"pk": int}, pk="pk")
                assert db.journal_mode == "delete"
            result = runner.invoke(cli.cli, ["enable-wal"] + dbs)
>           assert 0 == result.exit_code
E           AssertionError: assert 0 == 1
E            +  where 1 = <Result OperationalError('cannot change into wal mode from within a transaction')>.exit_code

Test summary:

============ 13 failed, 909 passed, 16 skipped, 2 errors in 19.29s =============

[simonw]

On my own laptop I got a crash running the tests - details here:

• https://github.com/nalgeon/sqlean.py/issues/3

[simonw]

I can use https://github.com/simonw/sqlite-dump as an optional dependency to handle the missing .iterdump() method.

[simonw]

I've fixed most of the test failures, but I still need to fix this one:

cannot change into wal mode from within a transaction

[simonw]

>>> import sqlite3
>>> db = sqlite3.connect("/tmp/1.db")
>>> db.execute('PRAGMA journal_mode=wal;')
<sqlite3.Cursor object at 0x102475ec0>
>>> import sqlean
>>> db2 = sqlean.connect("/tmp/2.db")
>>> db2.execute('PRAGMA journal_mode=wal;')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlean.dbapi2.OperationalError: cannot change into wal mode from within a transaction

[simonw]

I suspect this has something to do with autocommit mode in sqlite3 - which I may be able to turn off by setting con.isolation_level = None.

[simonw]

Yes that does seem to do the trick:

>>> import sqlean
>>> db = sqlean.connect("/tmp/4.db")
>>> db.execute('PRAGMA journal_mode;').fetchall()
[('delete',)]
>>> db.isolation_level
''
>>> db.execute('PRAGMA journal_mode=wal;')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlean.dbapi2.OperationalError: cannot change into wal mode from within a transaction
>>> db.isolation_level = None
>>> db.isolation_level
>>> db.execute('PRAGMA journal_mode=wal;')
<sqlean.dbapi2.Cursor object at 0x104437c20>

Weird how isolation_level of empty string causes the error, but setting that to None fixes the error.

[simonw]

Filed an issue about the above with pysqlite3 (which sqlean.py is based on) here:

• https://github.com/coleifer/pysqlite3/issues/58

[simonw]

Documentation preview: https://sqlite-utils--560.org.readthedocs.build/en/560/installation.html#alternatives-to-sqlite3