Azure Flask app | Azure SQL - pyodbc.OperationalError Timeout with load_dotenv()

Question

I have a Flask app running on Azure with a connection to an Azure SQL db and after the below developments I can't access the database any longer.

Here is what I recently added :

• created a .env file to secure the connection string

• added a .gitignore

• added the following to my main.py:

   connection_string = textwrap.dedent(f'''
        Driver={driver};
        Server={os.getenv("SERVER")};
        Database={os.getenv("DB_NAME")};
        Uid={os.getenv("DB_USER")};
        Pwd={os.getenv("PASSWORD")};
        Encrypt=yes;
        TrustServerCertificate=no;
        Connection Timeout=30;
    ''')
  

• I've also added the following to run in production :

   if __name__ == '__main__':
       from waitress import serve
       serve(app, host="0.0.0.0", port=8080)
  

It works perfectly locally but as soon as I deploy it on Azure, I have issues with the docker deployment in the background. It seems the connection strings params are not recognized from the following error (from Kudu) :

Warning
2021-10-15T12:42:51.4960727

[2021-10-15 12:42:51 +0000] [40] [ERROR] Exception in worker process

  Ok
2021-10-15T12:42:51.4961108

Traceback (most recent call last):

  Ok
2021-10-15T12:42:51.4961177

  File "/tmp/8d98fd8a8763182/antenv/lib/python3.8/site-packages/gunicorn/arbiter.py", line 589, in spawn_worker

  Ok
2021-10-15T12:42:51.4961354

    worker.init_process()

  Ok
2021-10-15T12:42:51.4961403

  File "/tmp/8d98fd8a8763182/antenv/lib/python3.8/site-packages/gunicorn/workers/base.py", line 134, in init_process

  Ok
2021-10-15T12:42:51.4961451

    self.load_wsgi()

  Ok
2021-10-15T12:42:51.4961495

  File "/tmp/8d98fd8a8763182/antenv/lib/python3.8/site-packages/gunicorn/workers/base.py", line 146, in load_wsgi

  Ok
2021-10-15T12:42:51.4961542

    self.wsgi = self.app.wsgi()

  Ok
2021-10-15T12:42:51.4961586

  File "/tmp/8d98fd8a8763182/antenv/lib/python3.8/site-packages/gunicorn/app/base.py", line 67, in wsgi

  Ok
2021-10-15T12:42:51.4961638

    self.callable = self.load()

  Ok
2021-10-15T12:42:51.4961682

  File "/tmp/8d98fd8a8763182/antenv/lib/python3.8/site-packages/gunicorn/app/wsgiapp.py", line 58, in load

  Ok
2021-10-15T12:42:51.4961729

    return self.load_wsgiapp()

  Ok
2021-10-15T12:42:51.4961772

  File "/tmp/8d98fd8a8763182/antenv/lib/python3.8/site-packages/gunicorn/app/wsgiapp.py", line 48, in load_wsgiapp

  Ok
2021-10-15T12:42:51.496182

    return util.import_app(self.app_uri)

  Ok
2021-10-15T12:42:51.4961864

  File "/tmp/8d98fd8a8763182/antenv/lib/python3.8/site-packages/gunicorn/util.py", line 359, in import_app

  Ok
2021-10-15T12:42:51.496191

    mod = importlib.import_module(module)

  Ok
2021-10-15T12:42:51.4961956

  File "/opt/python/3.8.6/lib/python3.8/importlib/__init__.py", line 127, in import_module

  Ok
2021-10-15T12:42:51.4962002

    return _bootstrap._gcd_import(name[level:], package, level)

  Ok
2021-10-15T12:42:51.496308

  File "", line 1014, in _gcd_import

  Ok
2021-10-15T12:42:51.496315

  File "", line 991, in _find_and_load

  Ok
2021-10-15T12:42:51.4963197

  File "", line 975, in _find_and_load_unlocked

  Ok
2021-10-15T12:42:51.4963244

  File "", line 671, in _load_unlocked

  Ok
2021-10-15T12:42:51.496329

  File "", line 783, in exec_module

  Ok
2021-10-15T12:42:51.4963337

  File "", line 219, in _call_with_frames_removed

  Ok
2021-10-15T12:42:51.4963383

  File "/tmp/8d98fd8a8763182/main.py", line 104, in 

  Ok
2021-10-15T12:42:51.4965707

    con, cur = AZconnect()

  Ok
2021-10-15T12:42:51.496577

  File "/tmp/8d98fd8a8763182/main.py", line 91, in AZconnect

  Ok
2021-10-15T12:42:51.4965817

    con: pyodbc.Connection = pyodbc.connect(connection_string)

  Warning
2021-10-15T12:42:51.4965863

pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

I'm quite sure I did something wrong with the .env and load_dotenv() but I don't see where.

Answer 1

I understood my mistake ; .gitignore prenvented to use the .env file as it should hence the environment variable couldn't be set. I then set these variable in Azure application settings and it worked just fine :