operational error when testing my CLI app with a SQLite DB

So I wanted to add tests to my CLI app, but I am struggling to replace my db with a temporary one. So far I have used SQLModel for database access, but for the app testing I want to assert the db values using raw SQL statements. However, I get a connection error when trying to access my temporary DB.

sqlite3.OperationalError: unable to open database file

My plan so far is the following: In my test file I replace the sqlite uri with one directing it to a sqlite db in a temporary dictionary (using pytest tmp_path). My assumption was, that if I create that DB with SQLModel, I'd be able to access it with sqlite3 later on. Below you'll find the code. (At the moment, I haven't added an assert yet. That'll be added of course as soon as the DB connection works)

Also, is there a better way to replace my actual db, or is it fine what I am doing here?

My test file:

import sqlite3

from typer.testing import CliRunner

from projects import db
from projects.app_typer import app


def temp_db(path):
    db.sqlite_url = f"sqlite:///{path}/db.db"


runner = CliRunner()


def test_url_replacement(tmp_path):
    temp_db(tmp_path)
    assert db.sqlite_url == f"sqlite:///{tmp_path}/db.db"


def test_add_item_to_db(tmp_path):
    temp_db(tmp_path)
    result = runner.invoke(app, ["add", "public", "-n", "Project", "-p", "00-00"])
    con = sqlite3.connect(f"sqlite:///{tmp_path}/db.db")
    cur = con.cursor()
    db_entry = cur.execute("SELECT * FROM project").fetchone()
    print(db_entry)

Excerpt from db.py:

from sqlmodel import Field, Session, SQLModel, create_engine, select


sqlite_url = "sqlite:///database.db"

engine = create_engine(sqlite_url)


def create_session_and_db():
    SQLModel.metadata.create_all(engine)

Excerpt from app_typer.py:

import typer

app = typer.Typer(add_completion=False)


@app.callback(invoke_without_command=True, no_args_is_help=True)
def main():
    create_session_and_db()

app.add_typer(add.app, name="add", help="Add a project to the DB.")

Edit

So I fixed the URI for sqlite3 thanks to @emilio-silva. However I bumped into a new problem: the database sqlmodel creates seems to be somewhere else than in the temporary directory I specified with tmp_path.

If I open the folder after running pytest, db.db exists, but is empty (Checked with DB Browser for SQLite). This makes me assume, that sqlite3 actually created a new DB. I can't figure out though, why sqlmodel doesn't put the db in the correct location. When setting echo=True for the engine (sqlmodel/sqlalchemy), I get the following output:

INFO     sqlalchemy.engine.Engine:base.py:2689 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:base.py:1848 PRAGMA main.table_info("project")
INFO     sqlalchemy.engine.Engine:base.py:1848 [raw sql] ()
INFO     sqlalchemy.engine.Engine:base.py:2695 COMMIT
INFO     sqlalchemy.engine.Engine:base.py:2689 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:base.py:1848 INSERT INTO project (name, project_number, offer_number, project_type, classification) VALUES (?, ?, ?, ?, ?)
INFO     sqlalchemy.engine.Engine:base.py:1848 [generated in 0.00037s] ('Project', '00-00', None, 'public', 'internal')
INFO     sqlalchemy.engine.Engine:base.py:2695 COMMIT
INFO     sqlalchemy.engine.Engine:base.py:2689 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:base.py:1848 SELECT project.id, project.name, project.project_number, project.offer_number, project.project_type, project.classification      
FROM project
WHERE project.id = ?
INFO     sqlalchemy.engine.Engine:base.py:1848 [generated in 0.00055s] (15,)
INFO     sqlalchemy.engine.Engine:base.py:2692 ROLLBACK

For completeness the error I get from sqlite3: sqlite3.OperationalError: no such table: project

And the directory of the tmp_path: C:\Users\**redacted**\AppData\Local\Temp\pytest-of-**redacted**\pytest-16\test_add_item_to_db0

Edit 2

Okay I figured out part of the solution.

Even though I change the database uri, the sqlmodel engine still connects to the original database, not the temporary one. My guess is, that the create_engine command runs at the point when I import db.py, not when I run my test. And since I change the database string after the import, it's too late to feed it to the engine.

My guess is, that the solution would be to somehow implement the database creation/binding with sqlmodel differently, can anyone point me to a fitting approach?