sqlalcodegen The client cannot connect to the server because the requested instance was not available

Question

Currently I am trying to setup sqlalchemy orm with sql server express

The code below works with no issues and outputs correctly:

from sqlalchemy import create_engine
from sqlalchemy.orm import session, sessionmaker

import urllib
from dotenv import load_dotenv
import os
load_dotenv()

db_pass = os.getenv('DATABASE_PASS')

engine = create_engine(f'mssql+pyodbc://grimm:{db_pass}@localhost\\SQLEXPRESS/GrimmDB?driver=ODBC+Driver+17+for+SQL+Server')

Session = sessionmaker(bind=engine)
session = Session

with engine.connect() as conn:
    rs = conn.execute('SELECT * FROM Users')
    for row in rs:
        print(row)

output:
(1, 10101, True, 100)

However the issue lies when I try to use sqlacodegen:

sqlacodegen --outfile 'm.py' 'mssql+pyodbc://grimm:pass@localhost\\SQLEXPRESS/GrimmDB?driver=ODBC+Driver+17+for+SQL+Server'

Which gets me this error

Traceback (most recent call last):
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\base.py", line 3212, in _wrap_pool_connect
    return fn()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 307, in connect
    return _ConnectionFairy._checkout(self)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 767, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 425, in checkout
    rec = pool._do_get()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\impl.py", line 146, in _do_get
    self._dec_overflow()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__       
    compat.raise_(
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
    return self._create_connection()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 253, in _create_connection   
    return _ConnectionRecord(self)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 368, in __init__
    self.__connect()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 611, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 605, in __connect
    connection = pool._invoke_creator(self)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]The client cannot connect to the server because the requested instance was not available. Use SQL Server Configuration Manager to make sure the SQL Server instance is configured correctly.  (0) (SQLDriverConnect)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "C:\Users\jotha\AppData\Local\Programs\Python\Python39\Scripts\sqlacodegen.exe\__main__.py", line 7, in <module>
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlacodegen\main.py", line 57, in main
    metadata.reflect(engine, args.schema, not args.noviews, tables)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\sql\schema.py", line 4652, in reflect
    with inspection.inspect(bind)._inspection_context() as insp:
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\inspection.py", line 64, in inspect
    ret = reg(subject)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\reflection.py", line 182, in _engine_insp
    return Inspector._construct(Inspector._init_engine, bind)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\reflection.py", line 117, in _construct
    init(self, bind)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\reflection.py", line 128, in _init_engine
    engine.connect().close()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\base.py", line 3166, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\base.py", line 96, in __init__
    else engine.raw_connection()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\base.py", line 3245, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\base.py", line 3215, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\base.py", line 2069, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\base.py", line 3212, in _wrap_pool_connect
    return fn()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 307, in connect
    return _ConnectionFairy._checkout(self)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 767, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 425, in checkout
    rec = pool._do_get()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\impl.py", line 146, in _do_get
    self._dec_overflow()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
    return self._create_connection()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 253, in _create_connection
    return _ConnectionRecord(self)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 368, in __init__
    self.__connect()
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 611, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\pool\base.py", line 605, in __connect
    connection = pool._invoke_creator(self)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "c:\users\jotha\appdata\local\programs\python\python39\lib\site-packages\sqlalchemy\engine\default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]The client cannot connect to the server because the requested instance was not available. Use SQL Server Configuration Manager to make sure the SQL Server instance is configured correctly.  (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Any ideas? I am kinda stumped here.. the problem likely lies with the connection string but I have no clue what and I can't seem to find documentation.

Thanks in advance, Jothan Kelepolo

Answer 1

The answer lies in the escape sequence in the CLI before 'SQLEXPRESS'

Before

sqlacodegen --outfile 'm.py' 'mssql+pyodbc://grimm:pass@localhost\\SQLEXPRESS/GrimmDB?driver=ODBC+Driver+17+for+SQL+Server'

After

sqlacodegen --outfile 'm.py' 'mssql+pyodbc://grimm:pass@localhost\SQLEXPRESS/GrimmDB?driver=ODBC+Driver+17+for+SQL+Server'