Connect to MSSQL Database using Flask-SQLAlchemy

Question

I'm trying to connect to a local MSSQL DB through Flask-SQLAlchemy.

Here's a code excerpt from my __init__.py file:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mssql+pyodbc://HARRISONS-THINK/LendApp'
db = SQLAlchemy(app)

SQLALCHEMY_TRACK_MODIFICATIONS = False

As you can see in SQL Server Management Studio, this information seems to match:

Here is the creation of a simple table in my models.py file:

from LendApp import db

class Transaction(db.model):
    transactionID = db.Column(db.Integer, primary_key=True)
    amount = db.Column(db.Integer)
    sender = db.Column(db.String(80))
    receiver = db.Column(db.String(80))

    def __repr__(self):
        return 'Transaction ID: {}'.format(self.transactionID)

I am then connecting to the database using a Python Console within Pycharm via the execution of these two lines:

>>> from LendApp import db
>>> db.create_all()

This is resulting in the following error:

DBAPIError: (pyodbc.Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

The only thing that I can think of is that my database connection string is incorrect. I have tried altering it to more of a standard Pyodbc connection string and including driver={SQL SERVER} but to no prevail.

If anyone could help me out with this it would be highly appreciated.

Thanks

Hi all, for the "no default driver specified" I figured in out in my own question, it might be relevant to this: https://stackoverflow.com/a/51927158/1896134 — JayRizzo, May 17 '22 at 22:29

Matt Camp · Accepted Answer · 2017-12-11T16:37:36.693

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.

score 4 · Answer 2 · answered Nov 27 '18 at 21:39

4

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>"

answered Nov 27 '18 at 21:39

AKJ

318
3
14

score 4 · Answer 3 · answered Aug 28 '19 at 19:51

4

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"

answered Aug 28 '19 at 19:51

Praveen

168
2
14

Thanks, I was misisng the `?driver=SQL+Server` – Ben-Coden Mar 25 '22 at 16:44

score 2 · Answer 4 · answered Apr 13 '20 at 14:26

I had the same problem, it was resolved by specifying:

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "mssql+pyodbc://MySQLServerName/MyTestDb?driver=SQL+Server?trusted_connection=yes"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db.init_app(app)

score 1 · Answer 5 · answered Nov 28 '17 at 22:31

I believe your connection string is missing the authentication details. From Flask-SQLAlchemy documentation, the connection string should have the following format

dialect+driver://username:password@host:port/database

From your example, I believe it will look something like this

app.config['SQLALCHEMY_DATABASE_URI'] = 'mssql+pyodbc://<username>:<password>@<Host>:<Port>/LendApp'

score 1 · Answer 6 · edited May 29 '20 at 03:58

using below solution i get resolve my connection issue with MSSQL server

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

If you are getting any Login failed for User error then please go to this http://itproguru.com/expert/2014/09/how-to-fix-login-failed-for-user-microsoft-sql-server-error-18456-step-by-step-add-sql-administrator-to-sql-management-studio/.

Connect to MSSQL Database using Flask-SQLAlchemy

6 Answers6

Linked