Python 使用 Flask-SQLAlchemy 连接到 MSSQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46739295/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connect to MSSQL Database using Flask-SQLAlchemy
提问by Harrison
I'm trying to connect to a local MSSQL DB through Flask-SQLAlchemy.
我正在尝试通过 Flask-SQLAlchemy 连接到本地 MSSQL 数据库。
Here's a code excerpt from my __init__.pyfile:
这是我的__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:
正如您在 SQL Server Management Studio 中看到的，此信息似乎匹配：
Here is the creation of a simple table in my models.pyfile:
这是在我的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)
I am then connecting to the database using a Python Console within Pycharm via the execution of these two lines:
然后我通过执行以下两行使用 Pycharm 中的 Python 控制台连接到数据库：
>>> 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.
我唯一能想到的是我的数据库连接字符串不正确。我尝试将其更改为更多的标准 Pyodbc 连接字符串，包括driver={SQL SERVER}但不占优势。
If anyone could help me out with this it would be highly appreciated.
如果有人能帮我解决这个问题，我将不胜感激。
Thanks
谢谢
回答by Matt Camp
So I just had a very similar problem and was able to solve by doing the following.
所以我只是遇到了一个非常相似的问题，并且能够通过执行以下操作来解决。
Following the SQL Alchemy documentationI found I could use the my pyodbc connection string like this:
按照SQL Alchemy 文档，我发现我可以像这样使用我的 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
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.pyfile
然后这导致了一个额外的错误，因为我也在使用 Flask-Migrate 并且显然它不喜欢连接 URI 中的 % 。所以我做了更多的挖掘并找到了这篇文章。然后我在我的./migrations/env.py文件中更改了以下行
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.
完成所有这些之后，我能够进行迁移，现在一切似乎都可以正常工作。
回答by GerardoMR
I believe your connection string is missing the authentication details. From Flask-SQLAlchemy documentation, the connection string should have the following format
我相信您的连接字符串缺少身份验证详细信息。从 Flask-SQLAlchemy 文档中，连接字符串应具有以下格式
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'
回答by AKJ
If someone still stumbled upon this issue and trying to figure out another solution then try with pymssqlinstead of pyodbc;
如果有人仍然偶然发现这个问题并试图找出另一个解决方案，请尝试使用pymssql而不是pyodbc;
pip install pymssql
pip install pymssql
Connection URI would be:
连接 URI 将是：
conn_uri = "mssql+pymssql://<username>:<password>@<servername>/<dbname>"
conn_uri = "mssql+pymssql://<username>:<password>@<servername>/<dbname>"
回答by Alex N
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)
回答by Naresh Nayak
using below solution i get resolve my connection issue with MSSQL server
使用以下解决方案，我解决了与 MSSQL 服务器的连接问题
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/.
如果您因用户错误而导致任何登录失败，请访问此 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/。
回答by Praveen
I just changed my connection string something like this and its worked perfectly
我只是改变了我的连接字符串这样的东西，它工作得很好
NOTE: you need to install pyodbc to work....
注意：您需要安装 pyodbc 才能工作....
app.config["SQLALCHEMY_DATABASE_URI"] = "mssql+pyodbc://user:pwd@server/database?driver=SQL+Server"
