Connect to an MSSQL database using Flask-SQLAlchemy

Question

I am trying to connect to a local MSSQL database through Flask-SQLAlchemy.

Here is a snippet of code from my file __init__.py:

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 file models.py:

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)

Then I connect to the database using the Python console in Pycharm by doing the following two lines:

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

This results 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 I can think of is that the connection string to my database is incorrect. I tried changing it to a more standard Pyodbc connection string, including driver={SQL SERVER}but not prevailing.

If anyone could help me with this, that would be very valuable.

thank

Answer 1

, .

SQL, , pyodbc :

# 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

, Flask-Migrate , , % URI . . ./migrations/env.py

:

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)

, , .

Answer 2

, . Flask-SQLAlchemy

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

, , .

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

Answer 3

- , pymssql pyodbc;

pip install pymssql

URI :

conn_uri = "mssql+pymssql://<username>:<password>@<servername>/<dbname>"

Answer 4

,

: pyodbc ....

app.config["SQLALCHEMY_DATABASE_URI"] = "mssql+pyodbc://user:pwd@server/database?driver=SQL+Server"