How to connect sqlalchemy to ms sql database

I have just installed MS SQL server on my system and I am having trouble using it from python. It works fine from the Management Studio, and I can see all my tables. I also have an MySQL server installed and it works fine from python using sqlalchemy. I would like now to use MS SQL as well. Here is what I tried:

from sqlalchemy import create_engine
import pyodbc

server = 'NEW-OFFICE\\NEWOFFICE'
database = 'testdb' 
username = 'NEW-OFFICE\user' 
password = 'password' 

DRIVER = "ODBC Driver 17 for SQL Server"
SERVERNAME = "NEWOFFICE"
INSTANCENAME = "\MSSQLSERVER_ZVI"

First I tried:

SQLALCHEMY_DATABASE_URI = "mssql+pyodbc://{username}:{password}@{hostname}/{database}".format(
    username=username,
    password=password,
    hostname=server,
    database=database,
)

Second I tried:

engine = create_engine('mssql+pyodbc://' + server + '/' + database + '?trusted_connection=yes&driver={DRIVER}')

Third I tried:

engine = create_engine(
    f"mssql+pyodbc://{username}:{password}@{SERVERNAME}{INSTANCENAME}/{database}?driver={DRIVER}", fast_executemany=True
)

Then the next code for all tries is:

db = SQLAlchemy(app)

db.app = app
db.init_app(app)
engine_container = db.get_engine(app)
engine_container.dispose()  # to close db connection

db.engine.connect()
hallDB.query.filter_by(client_id = 1).first()

At the last line I get an exception:

(sqlite3.OperationalError) no such table: hall

Of course table "hall" exists and I can query it in the Management Studio.

I am stuck so any help will be mush appreciated.

An Update

The following code did work, but I want the sqlalchemy model approach, which I am not able to use, as listed above.

u = 'DRIVER=ODBC Driver 17 for SQL Server;SERVER=NEW-OFFICE\user;DATABASE=testdb;Trusted_Connection=yes;'
cursor = pyodbc.connect(u).cursor()
cursor.execute("SELECT * FROM hall;")
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

Maybe this will show what needs to be done in order to connect using sqlalchemy.

An Update 2

The error related to sqlite3 was resolved after I saw the following warning

UserWarning: Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. Defaulting SQLALCHEMY_DATABASE_URI to "sqlite:

I realized that although I set SQLALCHEMY_DATABASE_URI, in the test code, I skipped the line app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI

After fixing it, and using:

SQLALCHEMY_DATABASE_URI = r'mssql+pyodbc://NEW-OFFICE\user/testdb;driver=ODBC Driver 17 for SQL Server;Trusted_Connection=yes'

or

SQLALCHEMY_DATABASE_URI = r"mssql+pyodbc://NEW-OFFICE\user/testdb?driver=ODBC Driver 17 for SQL Server?trusted_connection=yes"

the error now is:

Message=(pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC 
   Driver Manager] Data source name not found and no default driver 
   specified (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/rvf5)

And also a warning:

A Warning: No driver name specified; this is expected by PyODBC when using DSN-less connections