I am developing a webpage built with flask for my company which requires me to connect to their SQL database. I am using SQLalchemy create_engine() function to create the connection. When developing this locally everything was working great, but after deploying to Heroku I have been faced with errors I have been spending all day trying to resolve. All functionalities of the live Heroku app work, but break when connection is attempted.
My connection is created like this.
def connect(self):
#Arguments for connection
DRIVER_NAME = 'SQL SERVER'
SERVER_NAME = 'SQLDATA'
DATABASE_NAME = 'Rehab'
#Connecting paramaters
connection_string = f"""
DRIVER={{{DRIVER_NAME}}};
SERVER={SERVER_NAME};
DATABASE={DATABASE_NAME};
Trust_Connection = yes;
uid = ###########; <--- this is correct just hiding
pwd = ##########; <--- this is correct just hiding
"""
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
engine = create_engine(connection_url)
return engine
When I run this function Locally I am faced with no issues.
The errors presented to me by heroku is
2022-06-14T21:29:20.172377+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 590, in connect
2022-06-14T21:29:20.172377+00:00 app[web.1]: return dialect.connect(*cargs, **cparams)
2022-06-14T21:29:20.172377+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 597, in connect
2022-06-14T21:29:20.172378+00:00 app[web.1]: return self.dbapi.connect(*cargs, **cparams)
2022-06-14T21:29:20.172378+00:00 app[web.1]: pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL SERVER' : file not found (0) (SQLDriverConnect)")```
Any help would be so appreciated! Thank you to anybody who takes the time. I apologize if the question is unclear I am new to asking stackoverflow questions. If you need any clarifications I will do my best to hastily reply!
