Connect to MSSQL Database using Flask-SQLAlchemy
If someone still stumbled upon this issue and trying to figure out another solution then try with pymssql
instead of pyodbc
;
pip install pymssql
Connection URI would be:
conn_uri = "mssql+pymssql://<username>:<password>@<servername>/<dbname>"
So I just had a very similar problem and was able to solve by doing the following.
Following the SQL Alchemy documentation I found I could use the my pyodbc connection string like this:
# Python 2.x
import urllib
params = urllib.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
# Python 3.x
import urllib
params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
# using the above logic I just did the following
params = urllib.parse.quote_plus('DRIVER={SQL Server};SERVER=HARRISONS-THINK;DATABASE=LendApp;Trusted_Connection=yes;')
app.config['SQLALCHEMY_DATABASE_URI'] = "mssql+pyodbc:///?odbc_connect=%s" % params
This then caused an additional error because I was also using Flask-Migrate and apparently it doesn't like % in the connection URI. So I did some more digging and found this post. I then changed the following line in my ./migrations/env.py
file
From:
from flask import current_app
config.set_main_option('sqlalchemy.url',
current_app.config.get('SQLALCHEMY_DATABASE_URI'))
To:
from flask import current_app
db_url_escaped = current_app.config.get('SQLALCHEMY_DATABASE_URI').replace('%', '%%')
config.set_main_option('sqlalchemy.url', db_url_escaped)
After doing all this I was able to do my migrations and everything seems as if it is working correctly now.
I just changed my connection string something like this and its worked perfectly
NOTE: you need to install pyodbc to work....
app.config["SQLALCHEMY_DATABASE_URI"] = "mssql+pyodbc://user:pwd@server/database?driver=SQL+Server"