使用Flask-SQLAlchemy连接到MSSQL数据库 [英] Connect to MSSQL Database using Flask-SQLAlchemy

问题描述

我正在尝试通过Flask-SQLAlchemy连接到本地MSSQL DB.

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

这是我的__init__.py文件中的代码摘录:

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

正如您在SQL Server Management Studio中看到的那样，此信息似乎匹配:

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

这是在我的models.py文件中创建一个简单表的地方:

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)

然后我通过执行以下两行，使用Pycharm中的Python控制台连接到数据库:

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()

这会导致以下错误:

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

我唯一能想到的是我的数据库连接字符串不正确.我尝试将其更改为更多的标准Pyodbc连接字符串，包括driver={SQL SERVER}，但没有成功.

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.

谢谢

推荐答案

所以我只是遇到了一个非常相似的问题，并且可以通过执行以下操作来解决.

So I just had a very similar problem and was able to solve by doing the following.

遵循 SQL炼金术文档我发现我可以这样使用我的pyodbc连接字符串:

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

然后这引起了另一个错误，因为我也在使用Flask-Migrate，而且显然不喜欢连接URI中的％.所以我做了一些进一步的挖掘，发现了这个帖子.然后，我在./migrations/env.py文件

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 flask import current_app
config.set_main_option('sqlalchemy.url',
                   current_app.config.get('SQLALCHEMY_DATABASE_URI'))

收件人:

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.

这篇关于使用Flask-SQLAlchemy连接到MSSQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！